MySQL INSERT ... ON DUPLICATE KEY UPDATE

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

真实环境中会有一种应用场景,如存在更新,不存在则更新,Oracle中有merge语句,可以实现此功能。
MySQL中可以用INSERT … ON DUPLICATE KEY 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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值