Three Methods and Speed Comparison of Mass Insertion of Database in PHP

Method 1: Insert using insert into with the following code:

$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 " );

Finally, it shows: 23:25:05 01:32:05, that is, it took more than 2 hours!

The second method: Using transaction commit, batch insert database (every 10W commit) finally shows that the time consumed is 22:56:1323:04:00, 1 for 8 minutes and 13 seconds, and 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: Use the optimized SQL statement: Splice the SQL statement, use insert into table () values (), (), (), () and then insert it once again. If the string is too long,

You need to configure MYSQL and run it on the mysql command line: set global max_allowed_packet = 2*1024*1024*10; The consumption time is 11:24:06 11:25:06;

It took only 1 minute to insert 200W 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);

Finally, when inserting a large number of data, the first method is undoubtedly the worst, while the second method is widely used in practical application, and the third method is more suitable and fast when inserting test data or other low requirements.