MySQL資料庫Insert語句7種寫法

簡介

很多開發人員工作了幾年之後，都會自嘲，自己啥技術都沒學到，就會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)

關注