关于大数据导出的方法
一般来说用PHP导出上万条数据的话 没有好的方法很容易出现超时或者失败的问题。
今天就说一下我用的方法 :
$commit = DB::connection('数据库')->table('表名')
->where(查询的条件)
->get();
$count = count($commit);
$filename = '文件名称.csv';
set_time_limit(0);
ini_set('memory_limit', '1024M');//设置内存限制
$head = [ '序号', '内容1', '内容2', '内容3' ]; //表头
header("Content-Disposition: attachment; filename=\"" . $filename . ".csv\"");
header('Content-Type:APPLICATION/OCTET-STREAM');
header('Access-Control-Allow-Origin: *'); (可不写 但是可能会报跨域问题 可自行测试运行)
$fp = fopen('php://output', 'a');
mb_convert_variables('GBK', 'UTF-8', $head); //导出表头的字体设置
fputcsv($fp, $head);
$perSize = 1000; //每次执行1000次
$pages = ceil($count / $perSize);
for($i = 1; $i <= $pages; $i++) {
DB::connection('数据库')->table('表名')
->limit($perSize)
->offset(($i-1)*$perSize)
->get()->each(function ($row) use (&$fp) {
$rowData = [
'`' . $row->id,
$row->name1,
$row->name2,
$row->name3,
];
mb_convert_variables('GBK', 'UTF-8', $rowData); //内容字体格式
fputcsv($fp, $rowData);
});
unset($db_data);
ob_flush();
flush();
}
fclose($fp);
exit();
方法二
/**
* 分批导出
* @param Request $request
*/
public function bpOut(Request $request){
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
ini_set("memory_limit", "2048M");
set_time_limit(0);
$lot = $request->get('lot');
$type = $request->get('type');
if($type == 'increment'){
$filename ="订单日增量表格_".$lot.".csv";
$count = DB::table('orders_temporary')
->where('lot', $lot)
->Where(function ($query) {
$query->where(function ($query){
$query->where('status', 3);
})->orWhere(function ($query) {
$query->where('status', 2);
});
})->distinct('order_sn')->orderBy('id','asc')->count();
}else{
$filename ="上传结果表格_".$lot.".csv";
$count = $orders = DB::table('orders_temporary')->where('lot', $lot)->count();
}
$limit = 10000;//限制查询
$page =ceil($count/$limit);//页数
header('Content-Description: File Transfer');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="'. $filename .'"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
$fp = fopen('php://output', 'a');//打开output流
ob_clean();
$title = ['订单编号', '旺旺ID', '用户昵称', '手机号', 'id号', '信息', '原因'];
mb_convert_variables('GBK', 'UTF-8', $title);
fputcsv($fp, $title);
for ($i=0; $i<$page; $i++){
$offset = $i*$limit;
if($type == 'increment'){
$dbs = DB::table('orders_temporary')
->where('lot', $lot)
->Where(function ($query) {
$query->where(function ($query){
$query->where('status', 3);
})->orWhere(function ($query) {
$query->where('status', 2);
});
})->distinct('order_sn')->offset($offset)->orderBy('id','asc')->limit($limit)->get()->toArray();
}else{
$dbs = DB::table('orders_temporary')->where('lot', $lot)->offset($offset)->orderBy('id','asc')->limit($limit)->get()->toArray();
}
foreach ($dbs as $db){
$db->order_sn= $this->numToString($db->order_sn);
$db->wangwang_id = $db->wangwang_id;
$db->username = $db->username;
$db->phone = $db->phone;
$db->id_number = $this->numToString($db->id_number);
$db->status_messgae = $db->status_messgae;
$db->message = $db->message;
$data=[$db->order_sn,$db->wangwang_id,$db->username,$db->phone,$db->id_number,$db->status_messgae,$db->message];
mb_convert_variables('GBK', 'UTF-8', $data);
fputcsv($fp, $data);
}
unset($dbs);
}
ob_flush();
flush();
fclose($fp);
exit();
}
以上内容可参考 如有问题可加微信沟通👇:
希望对您有所帮助。
注: 如有版权问题请联系本人 微信号:Strive-juvenile