摘要：使用insert语句一次性插入多条数据。
和其它数据库一样，在MySQL中，insert用来向表中插入数据，但是有一个小扩展。我们通过例子看看。
库中已有表emp，并有5条数据。
root@database-one 22:26: [gftest]> select * from emp; +--------+------+---------+------------+--------+ | ename | age | sal | hiredate | deptno | +--------+------+---------+------------+--------+ | 郭军 | 27 | 8400.00 | 2019-12-08 | 10 | | 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 | | 王艳 | 24 | 6000.00 | 2020-01-05 | 20 | | 马丽 | 26 | 7200.00 | 2018-07-06 | 30 | | 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 | +--------+------+---------+------------+--------+ 5 rows in set (0.00 sec)
我们新建一张表emp_copy，照搬emp的定义，仅仅将表名修改为emp_copy
root@database-one 22:28: [gftest]> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
root@database-one 22:30: [gftest]> CREATE TABLE `emp_copy` (
-> `ename` varchar(10) DEFAULT NULL,
-> `age` int(11) DEFAULT NULL,
-> `sal` decimal(10,2) DEFAULT NULL,
-> `hiredate` date DEFAULT NULL,
-> `deptno` int(2) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.97 sec)
root@database-one 22:31: [gftest]> select * from emp_copy;
Empty set (0.00 sec)
先来验证下传统的insert方式：
root@database-one 22:31: [gftest]>
root@database-one 22:33: [gftest]> insert into emp_copy select * from emp limit 2;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@database-one 22:34: [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)
root@database-one 22:34: [gftest]> select * from emp_copy;
+--------+------+---------+------------+--------+
| ename | age | sal | hiredate | deptno |
+--------+------+---------+------------+--------+
| 郭军 | 27 | 8400.00 | 2019-12-08 | 10 |
| 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 |
+--------+------+---------+------------+--------+
2 rows in set (0.01 sec)
root@database-one 22:34: [gftest]> insert into emp_copy values('王艳',24,6000,'2020-01-05',20);
Query OK, 1 row affected (0.01 sec)
root@database-one 22:35: [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)
root@database-one 22:35: [gftest]> select * from emp_copy;
+--------+------+---------+------------+--------+
| ename | age | sal | hiredate | deptno |
+--------+------+---------+------------+--------+
| 郭军 | 27 | 8400.00 | 2019-12-08 | 10 |
| 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 |
| 王艳 | 24 | 6000.00 | 2020-01-05 | 20 |
+--------+------+---------+------------+--------+
3 rows in set (0.00 sec)
可以看到传统的insert select句式、insert values句式都支持的很好。
同时MySQL对insert还做了个小扩展，可以在一个insert里以非select句式插入多条记录：
root@database-one 22:35: [gftest]> insert into emp_copy (ename,age,sal,hiredate,deptno) values ('马丽',26,7200,'2018-07-06',30),('肖伟',29,8700,'2017-05-28',30);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@database-one 22:39: [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)
root@database-one 22:39: [gftest]> select * from emp_copy;
+--------+------+---------+------------+--------+
| ename | age | sal | hiredate | deptno |
+--------+------+---------+------------+--------+
| 郭军 | 27 | 8400.00 | 2019-12-08 | 10 |
| 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 |
| 王艳 | 24 | 6000.00 | 2020-01-05 | 20 |
| 马丽 | 26 | 7200.00 | 2018-07-06 | 30 |
| 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
这里再将详细的语法列出来供大家参考
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas.
INSERT INTO tablename (field1,field2,…,fieldn)
VALUES
(record1_values1,record1_values2,…,record1_valuesn),
(record2_values1,record2_values2,…,record2_valuesn),
…,
(recordn_values1,recordn_values2,…,recordn_valuesn);
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。