MySQL INSERT ... ON DUPLICATE KEY UPDATE

╰+哭是因爲堅強的太久メ 2022-10-15 13:57 142阅读 0赞

备注:测试数据库版本为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