MySQL中关于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);