Let's talk about how to write millions of data into csv in php

This article brings you relevant knowledge about PHP csv. It mainly introduces how to write millions of data into csv through script files in PHP. Let’s take a look at how to achieve it. I hope it will be helpful to everyone. helpful.

Requirements:

Write millions of data to csv.

Framework:

N The old framework many years ago cannot be found on Baidu, so the writing method is closer to the original

Analysis:

The amount of data is too large and cannot be written to csv download using the browser request, so write a script to retrieve it

Pseudo code:

//xxx - 根据自己项目替换
//调取脚本文件
exec(xxx);

//脚本文件
//设置执行时间和内存
set_time_limit(0);
ini_set('memory_limit', '128M');
//循环获取
$id = 0;
$data = 'xxx'.'\n';//表头
while(true){
    //SQL
    $list = xxx WHERE id > $id ORDER BY id ASC LIMIT 10000; //每次取1w防止数据库压力大,根据sql来，我这个有联表，
    if(empty($list)){
        break;
    }
    foreach($list as $row){
        $data .= '"' . $row['xxx'] . '",';
        $data .= "\n";
        $id = $row['id'];//更新最大id
    }
    //追加写入csv
    file_put_contents('xxx', mb_convert_encoding($data, 'gbk'),FILE_APPEND);
    unset($data);//基础不好不确定初始化能不能清内存就先unset了
    $data = '';//初始化
}

Essentially, it is written in batches. When I first started experimenting, I planned not to write file_put_contents in the loop. Later, I found that when the amount of data is large, hundreds of thousands of data, $data memory will be exceeded. In addition, if there are more than one million data, it will be necessary. When exporting files, Excel does not support opening more than 1,048,576 rows. Theoretically, you can add the $all_count parameter to count the total number of current queries. If it exceeds one million, add new files.

If there is a better method or code optimization, please discuss it.

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of Let's talk about how to write millions of data into csv in php. For more information, please follow other related articles on the PHP Chinese website!