MySQL数据库Insert语句7种写法

原创

mb5fdcad0be2e90 2021-03-01 17:10:59 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mb5fdcad0be2e90的原创作品，请联系作者获取转载授权，否则将追究法律责任

简介很多开发人员工作了几年之后，都会自嘲，自己啥技术都没学到，就会CRUD，可是我要说的是，CRUD你真的都会吗，你在MySQL数据库中，会几种insert语句写法呢。在这里我会7种写法，下面我就来给大家分别介绍一下。

创建测试表 在介绍Insert语句之前，先创建一张测试表


mysql> show create table t_test_2\G;
*************************** 1. row ***************************
       Table: t_test_2
Create Table: CREATE TABLE `t_test_2` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `name2` char(15) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_t_test_1_name2` (`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

第一种insert写法 不指定插入列写法，这个写法大家很非常熟悉。


mysql> select * from t_test_2;
Empty set (0.00 sec)

mysql> insert into t_test_2 values(20,'name20','tname20',0);
Query OK, 1 row affected (0.53 sec)

mysql> select * from t_test_2;
+----+--------+---------+--------+
| id | name   | name2   | status |
+----+--------+---------+--------+
| 20 | name20 | tname20 |      0 |
+----+--------+---------+--------+
1 row in set (0.00 sec)

可是在这里，我要告诉大家的是，不实际写代码的时候，不建议大家使用这种不指定插入列的insert写法，为什么呢，举个例子，当在表上添加一个新字段之后，你的insert语句就会报错，给大家举个例子


mysql> insert into t_test_3 values(20,'name20','tname20',0);
Query OK, 1 row affected (0.43 sec)

mysql> select * from t_test_3;
+----+--------+---------+--------+
| id | name   | name2   | status |
+----+--------+---------+--------+
| 20 | name20 | tname20 |      0 |
+----+--------+---------+--------+
1 row in set (0.00 sec)

mysql> alter table t_test_3 add name3 char(10);
Query OK, 0 rows affected (2.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t_test_3 values(21,'name21','tname21',0);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

看到了吧，执行insert语句报错了，如果你生产上代码也是这样，就是一个故障了。

第二种insert写法 指定插入列insert语句写法，写法如下所示


mysql> insert into t_test_2(id,name,name2,status) values(21,'name21','tname21',0);
Query OK, 1 row affected (0.39 sec)

mysql> select * from t_test_2;
+----+--------+---------+--------+
| id | name   | name2   | status |
+----+--------+---------+--------+
| 20 | name20 | tname20 |      0 |
| 21 | name21 | tname21 |      0 |
+----+--------+---------+--------+
2 rows in set (0.00 sec)

第三种insert写法 一次插入多行记录，写法如下所示，在这里，一次插入2条记录，当有批量插入的业务情景，这个insert写法就很方便了。

mysql> insert into t_test_2 values(22,'name22','tname22',0),(23,'name23','tname23',0);
Query OK, 2 rows affected (0.37 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_test_2;
+----+--------+---------+--------+
| id | name   | name2   | status |
+----+--------+---------+--------+
| 20 | name20 | tname20 |      0 |
| 21 | name21 | tname21 |      0 |
| 22 | name22 | tname22 |      0 |
| 23 | name23 | tname23 |      0 |
+----+--------+---------+--------+
4 rows in set (0.01 sec)

第四种insert写法 带select查询的insert语句，这种写法，可以很方便的在线备份一张表的记录

mysql> insert into t_test_2 select * from t_test_1;
Query OK, 10 rows affected (0.33 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from t_test_2;
+----+--------+---------+--------+
| id | name   | name2   | status |
+----+--------+---------+--------+
|  1 | name1  | 1001    |      0 |
|  2 | name1  | 1002    |      1 |
|  3 | name1  | 1003    |      1 |
|  4 | name1  | 1004    |      0 |
|  5 | name1  | 1005    |      1 |
|  6 | name1  | 1006    |      0 |
|  7 | name1  | 1007    |      2 |
|  8 | name1  | 1008    |      0 |
|  9 | name1  | 1009    |      1 |
| 10 | name10 | 1001    |      0 |
| 20 | name20 | tname20 |      0 |
| 21 | name21 | tname21 |      0 |
| 22 | name22 | tname22 |      0 |
| 23 | name23 | tname23 |      0 |
+----+--------+---------+--------+
14 rows in set (0.01 sec)

第五种insert写法 当用replace into向业务表中插入数据时，会依据主键或者唯一索引进行判断，是否存在冲突，如果存在，就会先删除冲突行，然后插入新的记录。如果不冲突，则直接将记录插入到业务表。需要注意的是，想要replace into，用户对表必须有insert,delete权限，在管理严格线上数据库，会禁止普通应用用户拥有delete权限。

举个例子，当你插入的数据发生主键冲突时，就可以使用replace into来解决了。下面来举个例子


mysql> select * from t_test_2 where id=1;
+----+-------+-------+--------+
| id | name  | name2 | status |
+----+-------+-------+--------+
|  1 | name1 | 1001  |      0 |
+----+-------+-------+--------+
1 row in set (0.00 sec)

mysql> replace into t_test_2 (id,name,name2,status) values(1,'testea','testea',1);
Query OK, 2 rows affected (0.16 sec)

mysql> select * from t_test_2 where id=1;
+----+--------+--------+--------+
| id | name   | name2  | status |
+----+--------+--------+--------+
|  1 | testea | testea |      1 |
+----+--------+--------+--------+
1 row in set (0.00 sec)

第六种insert写法 当用insert ignore向业务表插入数据时，会依据主键或者唯一索引进行判断，是否存在冲突，就跳过要插入的数据，如果不冲突，则直接将记录插入到业务表。

举一个业务场景，当你向表里插入记录，可是有些数据可能会与表里的记录发生主键冲突，而发生冲突的记录，你不想导入，这个时候，insert ignore就能帮上大忙了。举个实际例子


mysql> insert ignore into t_test_2 (id,name,name2,status) values(1,'testea','testea',1),(30,'teste30','teste30',1);
Query OK, 1 row affected, 1 warning (0.34 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> select * from t_test_2 where id in (1,30);
+----+---------+---------+--------+
| id | name    | name2   | status |
+----+---------+---------+--------+
|  1 | testea  | testea  |      1 |
| 30 | teste30 | teste30 |      1 |
+----+---------+---------+--------+
2 rows in set (0.01 sec)

第七种insert写法 当用insert on duplicate key update向业务表插入数据时，会依据主键或者唯一索引进行判断，是否存在冲突，则会更新指定字段，如果不冲突，则直接将记录插入到业务表。

举个例子


mysql> select * from t_test_2 where id in (1,31);
+----+--------+--------+--------+
| id | name   | name2  | status |
+----+--------+--------+--------+
|  1 | testea | testea |      1 |
+----+--------+--------+--------+
1 row in set (0.00 sec)

mysql> insert into t_test_2 (id,name,name2,status) values(1,'testea','testea',1) on duplicate key update status=4;
Query OK, 2 rows affected (0.32 sec)

mysql> insert into t_test_2 (id,name,name2,status) values(31,'testea31','testea31',1) on duplicate key update status=4;
Query OK, 1 row affected (0.14 sec)

mysql> select * from t_test_2 where id in (1,31);
+----+----------+----------+--------+
| id | name     | name2    | status |
+----+----------+----------+--------+
|  1 | testea   | testea   |      4 |
| 31 | testea31 | testea31 |      1 |
+----+----------+----------+--------+
2 rows in set (0.00 sec)

关注如果您喜欢这篇文章，请点赞+转发。

如果您特别喜欢，请加关注。