批量导入参考https://blog.csdn.net/qq_20521363/article/details/102647756
<?php
/*
* 该方法是把数据库读出的数据进行CSV文件输出,能接受百万级别的数据输出,因为用生成器,不用担心内存溢出。
* @param string $sql 需要导出的数据SQL
* @param string $mark 生成文件的名字前缀
*
*/
function putCsv($sql, $mark,$sqlCount)
{
set_time_limit(0);
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
header('Content-Disposition: attachment;filename="' . $mark . '"');
header('Cache-Control: max-age=0');
//每次只从数据库取100000条以防变量缓存太大
$sqlLimit = 100000;
// 每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
$limit = 100000;
$fileNameArr = [];
$cnt = 0;
// 逐行取出数据,不浪费内存
for ($i = 0; $i < ceil($sqlCount / $sqlLimit); $i++) {
$fp = fopen($mark .'_'.$i .'.csv', 'w'); //生成临时文件
$fileNameArr[] = $mark .'_'.$i .'.csv';
fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码,防止乱码
fputcsv($fp, ['编号','年级','班级']);
foreach ( query($sql,$limit,$fp) as $a) {
// buffer计数器
$cnt++;
if ($limit == $cnt) {
//刷新一下输出buffer,防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
fputcsv($fp, $a);
}
fclose($fp); //每生成一个文件关闭
}
//进行多个文件压缩
$zip = new ZipArchive();
$filename = $mark . ".zip";
$zip->open($filename, ZipArchive::CREATE); //打开压缩包
foreach ($fileNameArr as $file) {
$zip->addFile($file, basename($file)); //向压缩包中添加文件
}
$zip->close(); //关闭压缩包
foreach ($fileNameArr as $file) {
unlink($file); //删除csv临时文件
}
//输出压缩文件提供下载
header("Cache-Control: max-age=0");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename=' . basename($filename)); // 文件名
header("Content-Type: application/zip"); // zip格式的
header("Content-Transfer-Encoding: binary"); //
header('Content-Length: ' . filesize($filename)); //
@readfile($filename);//输出文件;
unlink($filename); //删除压缩包临时文件
}
//生成器来缓存mysql查询结果,返回类型为数组
function query($sql,$limit,$fp){
$con = mysqli_connect("localhost", "root", "root");
if (!$con) {
die('Could not connect: ' . mysqli_error());
}
mysqli_select_db($con, "test_batch");
mysqli_query($con,'set names utf8');
// print_r(mysqli_query($con, $sql,MYSQLI_USE_RESULT) ); mysqli_result Object 返回
//该处用MYSQLI_USE_RESULT 就是不缓存结果集中,也是为了避免内存溢出,相当于mysql_unbuffered_query
foreach (mysqli_query($con, $sql,MYSQLI_USE_RESULT) as $row ){ //
yield $row; //生成器
}
$con->close();
}
$sqlCount= 1000000;
$sql = 'SELECT id,class,student FROM `test_yield` where id <= '.$sqlCount;
//$sql = 'SELECT id,ArticleClassify,ArticleTitle FROM `test_yield` where id < 20';
$mark = 'test';
putCsv($sql,$mark,$sqlCount);
?>
;