MySQL LOAD DATA
<?php /** * @Author: Awe * @Date: 2016-10-26 17:26:54 * @Last Modified by: Awe * @Last Modified time: 2017-05-30 12:57:18 */ class EmailBusiness extends AbstractModel{ public function sendEmail(){ $sql = "SELECT email,body,`subject`,name FROM hr_send_email_all_task WHERE is_lock=0 AND (send_status=0 OR send_status=2);"; $rows = $this->db(0)->find($sql); foreach ($rows as $val) { $accepter = $val['email']; $tite = $val['subject']; $bodys = $val['body']; $result = Common::Postmail($accepter ,$tite , $bodys); $this->setsendemailalltask($result); } echo "success"; } //更新邮件发送状态 private function setsendemailalltask($result) { $now = date('Y-m-d H:i:s'); if ($result) { $sql = "UPDATE hr_send_email_all_task SET is_lock=1,lock_date='{$now}',send_status=1,send_date='{$now} '"; } else { $sql = "UPDATE hr_send_email_all_task SET is_lock=1,lock_date='{$now}',send_status=2 "; } $this->db(0)->Exec($sql); } public function addMailQueue(){ $file = APP_PATH . "/log/tmp/mailQueue.lock.txt" ; $fp = fopen($file,'a+'); if (flock($fp, LOCK_EX)) { $logFile = Log_file::getInstance(array('filename' => "addMailQueueLog" )); $lock_message = "file is locked time is :".date("Y-m-d H:i:s", time())." " .PHP_EOL; fwrite($fp, $lock_message); $result = $this->doAddMailQueue(); $logFile->Write("info" , $result ); echo $result ; flock($fp,LOCK_UN); }else{ echo "其他的进程正在处理。。。"; } fclose($fp); } public function doAddMailQueue(){ $sql = "select * from mail_queue where status = 0 and ischeck = '1' order by create_date desc limit 1 " ; $info = $this->db(4)->findOne($sql); if(empty($info)){ return "no data"; } $file = $info['file']; $body = $info['content']; $subject = $info['title']; $emailType = "queue_{$info['id']}"; $pre_count = 1000; if(!file_exists($file)){ return "file $file is not exists"; } $csvreader = new CsvReader( $file); $line_number = $csvreader->get_lines(); $date = date("Y-m-d H:i:s"); $sqlFile = str_replace("\\", "/", APP_PATH) . "/tmp/"."sql_" . date("Y_m_d_H_i_s",time()) . ".txt"; $page = intval($line_number/$pre_count)+1; $successNum = 0; for ($i = 0;$i < $page;$i++){ $str = '' ; $data = $csvreader->get_data($pre_count , $i * $pre_count); if(empty($data)){ continue ; } foreach ($data as $key => $value) { $email = isset($value[0]) ? trim($value[0]) : '' ; if(!filter_var($email, FILTER_VALIDATE_EMAIL)){ continue ; } $bodys = $this->replaceEmailBody($email , $body , $emailType ); $str.= $email . "$#####$$" . $bodys . "$#####$$" . $date ."$#####$$" . $subject ."v_@rdasd32eplace@" ; $successNum++; } $str = rtrim($str , "v_@rdasd32eplace@" ); file_put_contents($sqlFile , $str , FILE_APPEND ); //unset($psql); unset($data); } $sql = "LOAD DATA INFILE '{$sqlFile}' INTO TABLE email_all_task CHARACTER SET utf8 FIELDS TERMINATED BY '$#####$$' LINES TERMINATED BY 'v_@rdasd32eplace@' (email,body,create_date,subject)" ; $num = $this->db(4)->Exec($sql); if($num > 0 ){ $now =date("Y-m-d H:i:s") ; $this->db(4)->Exec("update mail_queue set status = 1 ,success_num = '{$successNum}' , operate_date = '{$now}' where id = '{$info['id']}' "); //删除csv文件 @unlink($file); return "success---编号id为:{$info['id']}---标题是:{$subject}"; }else{ return "fail"; } } function replaceEmailBody($email , $content , $emailType = '' ){ $url = "http://testwww.shixiba.com/analyze.php?email={$email}&emailType={$emailType}"; $content .= '<img src="'.$url.'" style="display:none;">' ; return $content ; } }