SQL batch insertion of data

SELECT * FROM Persons p
– Batch Insert
INSERT INTO PersonsVALUES('bx9','z','Shanghai','Songjiang','2')
,('bx1','z','Shanghai','Songjiang','2')
,('bx2','z','Shanghai','Songjiang','2')
,('bx3','z','Shanghai','Songjiang','2')
,('bx4','z','Shanghai','Songjiang','2')
,('bx5','z','Shanghai','Songjiang','2')
,('bx6','z','Shanghai','Songjiang','2')
,('bx7','z','Shanghai','Songjiang','2')
,(‘bx8’, ‘z’, ‘Shanghai’, ‘Songjiang’, ‘2’);
– Batch modification
The first method: use insert into insert, the code is as follows:

The code is as follows

$params=array{(}^{\prime }valu{e}^{\prime }={>}^{\prime }50^{\prime });se{t}_{t}im{e}_{l}imit(0);echodate("H:i:s");for($i=0;$i<2000000;$i++){
$connec{t}_{m}ysql->insert($params);
};
echo date(“H:i:s”);

The last display is: 23:25:05 01:32:05 It only took more than 2 hours!

The second method: Use transaction commits and batch insertion into the database (after every 10W commits) and the last display time is: 22:56:13 23:04:00, a total of 8 minutes and 13 seconds. The code is as follows:

The code is as follows
echo date(“H:i:s”);

$connect_mysql->query(‘BEGIN’);
$params=array{(}^{\prime }valu{e}^{\prime }={>}^{\prime }50^{\prime });for($i=0;$i<2000000;$i++){
$connec{t}_{m}ysql->insert($params);
if($i%100000==0){
$connect_mysql->query(‘COMMIT’);
$connect_mysql->query(‘BEGIN’);
}
}
$connect_mysql->query(‘COMMIT’);
echo date(“H:i:s”);

The third method: use optimized SQL statements: splice the SQL statements, use insert into table () values ​​(),(),(),(), and then insert it again. If the string is too long,

You need to configure MYSQL and run it in the mysql command line: set global max_allowed_packet = 210241024*10; Consumption time is: 11:24:06 11:25:06;

It only took 1 minute to insert 200W test data! The code is as follows:

The code is as follows
$sql="insertintotwent{y}_{m}illion(value)values";for($i=0;$i<2000000;$i++){
$sql.="(‘50’),";
};
$sql=substr($sql,0,strlen($sql)-1);
$connec{t}_{m}ysql->query($sql);