mysql如何多行插入

mysql如何多行插入

测试开始前
mysql> show status like '%commit%' ;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Com_commit | 0 |
| Com_xa_commit | 0 |
| Handler_commit | 0 |
+----------------+-------+
3 rows in set (0.00 sec)
使用多行insert。
mysql> insert into t1(a)
-> values (1),
-> (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
数据库只有提交一次.
mysql> show status like '%commit%' ;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Com_commit | 0 |
| Com_xa_commit | 0 |
| Handler_commit | 1 |
+----------------+-------+
3 rows in set (0.00 sec)
可以看到通过这种方式可以对批量insert进行优化.
SQL> insert into t1(a)
2 values (1),
3 (2);

insert into t1(a)
values (1),
(2)

ORA-00933: SQL command not properly ended
对于这种直接insert多行的方式，oracle数据库不支持。

insert into persons (id_p, lastname , firstname, city ) values (200,'haha' , 'deng' , 'shenzhen'), (201,'haha2' , 'deng' , 'gd'), (202,'haha3' , 'deng' , 'beijing'); 这样就批量插入数据了， 遵循这样的语法，就可以批量插入数据了。