mysql insert into, insert ignore into, insert into on duplicate key update

目錄

• insert into
• insert ignore into
• insert into on duplicate update
• • 不存在記錄時
• insert ignore into on duplicate update
• • 不存在記錄時

幾個sql的執行情況彙總如下表：

指令	已存在	不存在
insert	報錯	插入
insert ignore	忽略	插入
insert into on duplicate key update	更新	插入
insert ignore into on duplicate key update	更新	插入

資料表要求有主鍵或唯一鍵。

另外，以上操作，都會使自增字段自增。

下面做下具體的測試。

建表

CREATE TABLE `test5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `age` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;
           

id

是主鍵，自增，

name

是唯一鍵。

insert into

> insert into test5 (name, age) values('John', 1);
Query OK, 1 row affected (0.00 sec)
           

查詢

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   1 |
+----+------+-----+
1 row in set (0.00 sec)
           

已插入新記錄。

再次插入

mysql> insert into test5 (name, age) values('John', 1);
ERROR 1062 (23000): Duplicate entry 'John' for key 'name_UNIQUE'
           

報錯，唯一鍵沖突。

insert ignore into

mysql> insert ignore into test5 (name, age) values('John', 1);
Query OK, 0 rows affected, 1 warning (0.00 sec)
           

查詢

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   1 |
+----+------+-----+
1 row in set (0.00 sec)
           

ignore 會忽略錯誤。

insert into on duplicate update

mysql> insert into test5 (name, age) values('John', 2) on duplicate key update age=2;
Query OK, 2 rows affected (0.00 sec)
           

查詢

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   2 |
+----+------+-----+
1 row in set (0.00 sec)
           

age更新為2。

不存在記錄時

mysql> insert into test5 (name, age) values('Alex', 2) on duplicate key update age=2;
Query OK, 1 row affected (0.00 sec)
           

查詢

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   2 |
|  7 | Alex |   2 |
+----+------+-----+
2 rows in set (0.00 sec)
           

會插入新記錄。

insert ignore into on duplicate update

mysql> insert ignore into test5 (name, age) values('John', 3) on duplicate key update age=3;
Query OK, 2 rows affected (0.01 sec)
           

查詢

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   3 |
|  7 | Alex |   2 |
+----+------+-----+
2 row in set (0.00 sec)
           

John的age更新為3。

不存在記錄時

mysql> insert ignore into test5 (name, age) values('Bob', 3) on duplicate key update age=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test5;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |   3 |
|  7 | Alex |   2 |
|  8 | Bob  |   3 |
+----+------+-----+
3 rows in set (0.00 sec)
           

會插入新記錄。