PHP 匯出大資料 CSV 檔案

wanzi發表於2020-06-02
PHP大資料

今天進行了一個匯出大資料csv的開發，因為程式邏輯稍微亂一些，匯出也慢，經過優化後現在匯出10萬條資料10秒鐘即可。大家可以參考下。

原本的demo


set_time_limit(0);

       ini_set ('memory_limit', '256M');



       $db = $this->load->database('default',true);





       $sql = "SELECT * FROM `t_mobile_number_section` $condition";



       $query = $db->query($sql);



       $result = $query->result_array();



       $filename = 'number'.time().'.csv';



       header('Content-Type: application/vnd.ms-excel');

       header('Content-Disposition: attachment;filename="'.$filename.'"');

       header('Cache-Control: max-age=0');



       // 開啟PHP檔案控制程式碼，php://output 表示直接輸出到瀏覽器

       $fp = fopen('php://output', 'a');



       // 輸出Excel列名資訊

       $head = array($title['number_section'],$title['area_code'],$title['province'],$title['city']);

       foreach ($head as $i => $v) {

           // CSV的Excel支援GBK編碼，一定要轉換，否則亂碼

           $head[$i] = iconv('utf-8', 'gbk', $v);

       }



       // 將資料通過fputcsv寫到檔案控制程式碼

       fputcsv($fp, $head);



       // 計數器

       $cnt = 0;

       $limit = 100000;



       // 從資料庫中獲取資料，為了節省記憶體，不要把資料一次性讀到記憶體，從控制程式碼中一行一行讀即可



       $i = 2;



       $count = 0;

       foreach ($result as $key => $val) {

           $count ++;

           $cnt ++;



           //每隔$limit行，重新整理一下輸出buffer，不要太大，也不要太小 ,大資料量時處理

           if ($limit == $cnt) { //重新整理一下輸出buffer，防止由於資料過多造成問題

               ob_flush();

               flush();  //重新整理buffer

               $cnt = 0;

           }





           $rows[$i] = iconv('utf-8', 'gbk', $val['number_section']);

           $rows[$i+1] = iconv('utf-8', 'gbk', $val['area_code']);

           $rows[$i+2] = iconv('utf-8', 'gbk', $val['province']);

           $rows[$i+3] = iconv('utf-8', 'gbk', $val['city']);

           fputcsv($fp, $rows);



       }

依託自己業務開發後的

$where2 = 'a.reid ='.$reid;
 if (!empty($_GPC['time'])) {
  $where2 .= ' AND a.createtime >= :starttime AND a.createtime <= :endtime ';
  $params2[':starttime'] = $starttime;
  $params2[':endtime'] = $endtime;
  }
  if (!empty($_GPC['keywords'])) {
  $where2 .= ' and (a.member like :member or a.mobile like :mobile)';
  $params2[':member'] = "%{$_GPC['keywords']}%";
  $params2[':mobile'] = "%{$_GPC['keywords']}%";
  }
  if (!empty($_GPC['kf'])) {
  $where2 .= " and a.kf LIKE '%{$_GPC['kf']}%'";
  }
  if ($status != '') {
  $where2 .= " and a.status='{$status}'";
  }

  //設定不超時
  set_time_limit(0);
  ini_set ('memory_limit', '1028M');

  //查詢總資料
  $sql = 'SELECT a.rerid,a.reid,a.member,a.mobile,a.openid,a.thumb,a.yuyuetime,a.kf
 ,a.var1,a.var2,a.var3,a.linkage,a.createtime,a.kfinfo,a.status,b.data, b.refid,b.reid,b.reridFROM ' . tablename($this->tb_info) . ' as a left join'.  tablename($this->tb_data) .
  'as b ON a.rerid=b.rerid WHERE '.$where2.' AND b.reid= '.$reid.' ORDER BY a.createtime desc';

  $arr = pdo_fetchall($sql, $params2);

  //遍歷修改資料結構
  $newArr=[];
  $result=[];
 foreach ($arr as $k => $val) {
  $newArr[$val['rerid']][] = $val;
  }

  foreach ($newArr as $key => $val) {

  foreach ($val as $k => $v) {
  $val[0]['fields'][] = $v['data'];
  }

  $result[] = $val[0];
  }

  //命名
  $stime = date('Ymd', $starttime);
  $etime = date('Ymd', $endtime);
  $filename = $activity['title'].'=='.$stime.'-'.$etime;

  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment;filename="'.$filename.'.csv"');
  header('Cache-Control: max-age=0');

  // 開啟PHP檔案控制程式碼，php://output 表示直接輸出到瀏覽器

  $fp = fopen('php://output', 'a');
  //拼接頭
  $htmlheader = array("openid" => "粉絲編號", "member" => $activity['member'], "mobile" => $activity['phone']);
  $linkage = !empty($la['l1']) ? array("l1" => $la['l1'], "l2" => $la['l2']) : array();
  $pluraltit = !empty($activity['plural']) ? array("thumb" => $activity['pluraltit']) : array();
  $var1t = !empty($par['var1t']) ? array("var1" => $par['var1t']) : array();
  $var2t = !empty($par['var2t']) ? array("var2" => $par['var2t']) : array();
  $var3t = !empty($par['var3t']) ? array("var3" => $par['var3t']) : array();
  $htmlfoot = array("status" => "狀態", "kfinfo" => "回覆", "createtime" => "提交時間");
  $head = array_merge($htmlheader, $ds, $linkage, $pluraltit, $var1t, $var2t, $var3t, $htmlfoot);

  // 輸出Excel列名資訊
  foreach ($head as $i => $v) {
  // CSV的Excel支援GBK編碼，一定要轉換，否則亂碼
  $head[$i] = iconv('utf-8', 'gbk', $v);
  }
  // 將資料通過fputcsv寫到檔案控制程式碼
  fputcsv($fp, $head);
  // 計數器
  $cnt = 0;
  $limit = 1000;
  $i = 2;
  $count = 0;
 foreach ($result as $key => $val) {
  $count ++;
  $cnt ++;
  //每隔$limit行，重新整理一下輸出buffer，不要太大，也不要太小 ,大資料量時處理
  if ($limit == $cnt) { //重新整理一下輸出buffer，防止由於資料過多造成問題
  ob_flush();
  flush(); //重新整理buffer
  $cnt = 0;
  }
  $rows[$i] = iconv('utf-8', 'gbk', $val['openid']);
  $rows[$i+1] = iconv('utf-8', 'gbk', $val['member']);
  $rows[$i+2] = iconv('utf-8', 'gbk', $val['mobile']."\t");
 for ($j = 0;$j<count($val['fields']);$j++)
 {  $rows[$i+$j+3] = iconv('utf-8', 'gbk', $val['fields'][$j]."\t");
  }
  $count_head = count($head);
  $rows[$i+$count_head-3] =  iconv('utf-8', 'gbk',status_type($val['status']));
  $rows[$i+$count_head-2] = iconv('utf-8', 'gbk', $val['kfinfo']);
  $rows[$i+$count_head-1] = iconv('utf-8', 'gbk', date("Y-m-d H:i:s",$val['createtime'])."\t");
  fputcsv($fp, $rows);
  }
exit;

大家可以按原本的demo根據自己的業務就行開發，速度會比我的快，因為我的業務邏輯裡有很多迴圈處理了資料。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章