MySQL INSERT ... ON DUPLICATE KEY UPDATE

备注:测试数据库版本为MySQL 8.0

真实环境中会有一种应用场景，如存在更新，不存在则更新，Oracle中有merge语句，可以实现此功能。

MySQL中可以用INSERT … ON DUPLICATE KEY UPDATE来实现。

文章目录

• 一.从一个简单的例子看INSERT ... ON DUPLICATE KEY UPDATE
• 二.模拟一个同事存在insert+update的例子

一.从一个简单的例子看INSERT … ON DUPLICATE KEY UPDATE

代码:

create table t1(a int, b int not null, c int not null ,primary key (a));

INSERT INTO t1 (a,b,c) VALUES (1,2,3);
           

如果指定了ON DUPLICATE KEY UPDATE子句，而要插入的行将导致UNIQUE索引或PRIMARY KEY中出现重复值，则对旧行进行更新.

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;
           

测试记录:

mysql> create table t1(a int, b int not null, c int not null ,primary key (a));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a,b,c) VALUES (1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (a,b,c) VALUES (1,2,3)
    ->   ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 4 |
+---+---+---+
1 row in set (0.00 sec)

mysql> UPDATE t1 SET c=c+1 WHERE a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 5 |
+---+---+---+
1 row in set (0.00 sec)

mysql> 
           

二.模拟一个同事存在insert+update的例子

数据接着上例数据，模拟一个insert+update的例子。

如果不存在则insert，如果存在 c = a + b

代码:

select * from t1;
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4, 5, 6)
  ON DUPLICATE KEY UPDATE c=a+b;
select * from t1;
           

测试记录:

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 5 |
+---+---+---+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4, 5, 6)
    ->   ON DUPLICATE KEY UPDATE c=a+b;
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+---+---+---+
2 rows in set (0.00 sec)

mysql> 
           

在测试一个insert into select

代码:

select * from t1;

INSERT INTO t1 (a,b,c) 
select tmp.a,tmp.b,tmp.c from 
(
select a, b, c from t1
union all
select 100, 101, 102
) tmp
  ON DUPLICATE KEY UPDATE c=tmp.a + tmp.b;

select * from t1;
           

测试记录:

mysql> select * from t1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+---+---+---+
2 rows in set (0.00 sec)
mysql> INSERT INTO t1 (a,b,c) 
    -> select tmp.a,tmp.b,tmp.c from 
    -> (
    -> select a, b, c from t1
    -> union all
    -> select 100, 101, 102
    -> ) tmp
    ->   ON DUPLICATE KEY UPDATE c=tmp.a + tmp.b;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> select * from t1;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
|   1 |   2 |   3 |
|   4 |   5 |   9 |
| 100 | 101 | 102 |
+-----+-----+-----+
3 rows in set (0.00 sec)

mysql> 
           

参考:

1.https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html