导出百万级数据
记录一下(初步实现)
<?php
//让程序一直运行
set_time_limit(0);
//设置程序运行内存
ini_set('memory_limit', '128M');
$fileName = '商品导出数据';
header('Content-Encoding: UTF-8');
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
//打开php标准输出流
$fp = fopen('php:output', 'a');
//添加BOM头,以UTF8编码导出CSV文件,如果文件头未添加BOM头,打开会出现乱码。
fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
//添加导出标题
fputcsv($fp, ['商品', '价格', '库存']);
//链接数据库
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=db_www;charset=utf8";
$pdo = new PDO($dsn, 'root', '');
$step = 100;
//循环次数
$nums = 10000;
//每次导出数量
for ($i = 0; $i < $step; $i++) {
$start = $i * 10000;
$sql = "SELECT product_code,price,qty FROM `products` ORDER BY `id` LIMIT {$start},{$nums}";
$productMysql = $pdo->query($sql);
$result = $productMysql->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $item) {
fputcsv($fp, $item);
}
//每1万条数据就刷新缓冲区
ob_flush();
flush();
}
根据大佬们的建议修整后为
<?php
//让程序一直运行
set_time_limit(0);
//设置程序运行内存
ini_set('memory_limit', '128M');
$fileName = '商品导出数据';
header('Content-Encoding: UTF-8');
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
//打开php标准输出流
$fp = fopen('php:output', 'a');
//添加BOM头,以UTF8编码导出CSV文件,如果文件头未添加BOM头,打开会出现乱码。
fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
// 添加导出标题
fputcsv($fp, ['商品', '价格', '库存']);
// 链接数据库
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=db_www;charset=utf8";
$pdo = new PDO($dsn, 'root', '');
$nums = 10000; // 每次查询的数量
$lastId = 0; // 上一批数据的最后一个ID
do {
$sql = "SELECT product_code, price, qty FROM `products` WHERE id > :lastId ORDER BY `id` LIMIT :nums";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':lastId', $lastId, PDO::PARAM_INT);
$stmt->bindParam(':nums', $nums, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $item) {
fputcsv($fp, $item);
}
$lastId = $result[count($result) - 1]['id'];
ob_flush();
flush();
} while (count($result) > 0);
fclose($fp);
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: