Comparison of three methods and speeds for PHP mass database insertion _ php instance

This article mainly introduces three methods and speed comparison of PHP large-volume database insertion. The three methods use common insert statements, insertinto statements, and transaction commit respectively. For more information, see Method 1:USE insert into to insert. The Code is as follows:

$params = array(‘value'=>'50′);set_time_limit(0);echo date(“H:i:s”);for($i=0;$i<2000000;$i++){$connect_mysql->insert($params);};echo date(“H:i:s”);

It is shown as follows: 23: 25: 05 01:32:05, that is, it took more than two hours!

Method 2:Use transaction commit, batch insert database (every commit records), the last time consumed is: 22: 56: 13 23:04:00, a total of 8 minutes 13 seconds, the Code is as follows:

echo date(“H:i:s”);$connect_mysql->query(‘BEGIN');$params = array(‘value'=>'50′);for($i=0;$i<2000000;$i++){$connect_mysql->insert($params);if($i%100000==0){$connect_mysql->query(‘COMMIT');$connect_mysql->query(‘BEGIN');}}$connect_mysql->query(‘COMMIT');echo date(“H:i:s”);

Method 3:Optimize SQL statement: concatenate the SQL statement, insert into table () values (), and insert () at a time. If the string is too long,

You need to configure MYSQL and run: set global max_allowed_packet = 2*1024*1024*10 in the mysql command line; time consumed: 11: 24: 06 11:25:06;

It takes only one minute to insert million pieces of test data! The Code is as follows:

$sql= “insert into twenty_million (value) values”;for($i=0;$i<2000000;$i++){$sql.=”('50′),”;};$sql = substr($sql,0,strlen($sql)-1);$connect_mysql->query($sql);

In conclusion, when inserting a large volume of data, the first method is undoubtedly the worst, and the second method is widely used in practical applications, the third method is suitable for inserting test data or other low requirements, and the speed is indeed fast.