PHP code example to export millions of data from the database (CSV file)

<?php
set_time_limit(0);
ini_set(&#39;memory_limit&#39;, &#39;128M&#39;);
 
$fileName = date(&#39;YmdHis&#39;, time());
header(&#39;Content-Encoding: UTF-8&#39;);
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
header(&#39;Content-Disposition: attachment;filename="&#39; . $fileName . &#39;.csv"&#39;);
 
 
//打开php标准输出流
//以写入追加的方式打开
$fp = fopen(&#39;php://output&#39;, &#39;a&#39;);

//连接数据库
$dbhost = &#39;127.0.0.1&#39;;
$dbuser = &#39;root&#39;;
$dbpwd = &#39;&#39;;
$con = mysqli_connect($dbhost, $dbuser, $dbpwd);
if (mysqli_connect_errno()) {
   die(&#39;connect error&#39;);
}
//选择数据库
$database = &#39;test&#39;;
mysqli_select_db($con, $database);
//设置编码
mysqli_query($con, "set names UTF8");

 
//我们试着用fputcsv从数据库中导出1百万的数据
//我们每次取1万条数据，分100步来执行
//如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
$step = 100;
$nums = 10000;
$where = ""; //筛选条件
 
//设置标题
$title = array(&#39;ID&#39;,&#39;姓名‘,&#39;年龄&#39;,&#39;性别&#39;);
foreach($title as $key => $item) {
    $title[$key] =iconv("UTF-8", "GBK", $item);
}
//将标题写到标准输出中
fputcsv($fp, $title);

for($s = 1; $s <= $step; $s++) {
    $start = ($s - 1) * $nums;
    $result = mysqli_query($con,"SELECT * FROM `test` ".$where." ORDER BY `id` LIMIT {$start},{$nums}");
    if($result) {
        while($row = mysqli_fetch_assoc($result)) {
            foreach($row as $key => $item) {
                //这里必须转码，不然会乱码
                $row[$key] = iconv("UTF-8", "GBK", $item);
            }
            fputcsv($fp, $row);
        }
        mysqli_free_result($result); //释放结果集资源

        //每1万条数据就刷新缓冲区
        ob_flush();
        flush();
    }
}
//断开连接
mysqli_close($con);