Export large amounts of PHP data (more than 500,000) to Excel in one minute

In PHP, we have learned so much about PHP. I don’t know how much you have mastered the skills to use PHP. I believe that a large number of people will understand but will not use the knowledge, so don’t rush this. This article is to lead everyone to use PHP more deeply.

Analysis

1. Tools

First clarify the quantity we need to export 50W. Secondly, we all know that the maximum number of rows in the Excel 2003 version is 65535 rows. The number of rows in the Excel 2007 version (1048576 rows) can meet our requirements, so the version of Excel we need to use must be the 2007 version and above.

2. Class library

Currently the most commonly used class library for PHP to process Excel is PHPExcel (exported 1w pieces of data after example testing, plus various data judgments It took me 5-8 minutes to process); I also found a product called PHPOffice on the Internet (I have never used this, you can try it, it is said to be more efficient than PHPExcel, I will write a blog post to introduce it next time); also There is fputcsv(), which exports a csv format file and can also be opened with an Excel file.

1. Timeout

This is direct, the request times out. Supermarkets are very common when processing this many data requests. At this time, we need to modify the PHP configuration: set_time_limit(0).

2. Overflow

We take out all the data from the database and fill it into the memory at one time. Not only is the memory storage insufficient, but also if the server CPU configuration is not high, the utilization rate can be reached. 100%, which will cause load on the server card. Regarding overflow, we also need to modify the PHP configuration: ini_set('memory_limit', '1024M').

Solution

1. Use PHPExcel to export

The experience of exporting 500,000 pieces of data using this solution is very unpleasant. Well, after testing, it is feasible and can also be exported on the server, but the time will be slower and it will always be downloaded. If you use the php class library, it is recommended to upgrade php to the latest php7 version to save memory and make the calculation faster

2. Use csv export

If you are not using php class library, then fputcsv() is the best way, which is much better than PHPExcel; because the csv file is smaller, the amount of data that can be stored in the csv file can be large or small, and csv generation does not take up a lot of CPU and time, while Excel's The generation time increases as the amount of file data increases.

3. Ideal solution

Split the data according to certain conditions to reduce the amount of exported data. For example, export according to date, which can be exported once a week or one day. Once, data segmentation is definitely a necessary step to prevent memory overflow and run timeout;

Carry out algorithm optimization to reduce nested loops when data is looped, and when generating csv or Excel data objects, it can be formed in one go to reduce Number of database queries. If you need to use other data, please generate the corresponding data structure in advance.

4. The fastest solution

Hand over the requirements to operation and maintenance, and directly run the sql statement to export:

SELECT
	*
FROM
	table_name 
INTO OUTFILE 'F:/test.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

The following may occur when running this statement Error:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This is due to the configuration permission problem of Mysql. You can run SHOW VARIABLES LIKE '%secure%'; to see what the current value of secure-file-priv is. NULL means export is prohibited, so it needs to be set. one time. In the my.ini file under the Mysql installation path, modify and set the path. If not, add this configuration:

secure_file_priv=F:/

#50w data. You will find that it will be exported quickly. . You can also write this statement to know the path of this file on the server and package it for download.

Related learning video sharing: php video tutorial

The above is the detailed content of Export large amounts of PHP data (more than 500,000) to Excel in one minute. For more information, please follow other related articles on the PHP Chinese website!