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

The content of this article is about the code example (CSV file) of PHP exporting millions of data from the database. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. help.

Replace the database connection information, query conditions, and title information with real data to use.

<?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);

The above is the detailed content of PHP code example to export millions of data from the database (CSV file). For more information, please follow other related articles on the PHP Chinese website!