Guadazi
0%

MySQL upsert语法

Posted on Edited on In

upsert: on duplicate key update

MySQL upsert 语法

不能加 where 条件

因为这是个插入语句，所以不能加 where 条件。

影响的行数

  • 如果是插入操作，受到影响行的值为 1；
  • 如果更新操作，受到影响行的值为 2；
  • 如果更新的数据和已有的数据一样（就相当于没变，所有值保持不变），受到影响的行的值为 0。

样例一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
> desc t1;
|-------|------|------|-----|---------|----------------|
| Field | Type | Null | Key | Default | Extra          |
|-------|------|------|-----|---------|----------------|
| f1    | int  | NO   | PRI | <null>  | auto_increment |
| f2    | int  | YES  |     | <null>  |                |
| f3    | int  | YES  |     | <null>  |                |
|-------|------|------|-----|---------|----------------|

> select * from t1;
|----|--------|----|
| f1 | f2     | f3 |
|----|--------|----|

> insert into t1(f1,f3) values (1,3),(2,7) on duplicate key update f3=f3|1;
Query OK, 2 rows affected

> select * from t1;
|----|--------|----|
| f1 | f2     | f3 |
|----|--------|----|
| 1  | <null> | 3  |
| 2  | <null> | 7  |
|----|--------|----|
2 rows in set

> insert into t1(f1,f3) values (1,3),(2,7) on duplicate key update f3=f3|1;
Query OK, 4 rows affected

> select * from t1;
|----|--------|----|
| f1 | f2     | f3 |
|----|--------|----|
| 1  | <null> | 4  |
| 2  | <null> | 8  |
|----|--------|----|
2 rows in set


> insert into t1(f1,f3) values (1,3),(1,7) on duplicate key update f3=f3|1;
-- 同一行冲突两次，会执行两次更新语句
> select * from t1;
|----|--------|----|
| f1 | f2     | f3 |
|----|--------|----|
| 1  | <null> | 6  |
| 2  | <null> | 8  |
|----|--------|----|
2 rows in set


> select * from t1;
|----|--------|----|
| f1 | f2     | f3 |
|----|--------|----|
| 1  | <null> | 6  |
| 2  | <null> | 8  |
|----|--------|----|
2 rows in set


VALUES 引用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
> truncate t1;
> INSERT INTO t1 (f1,f2,f3) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE f3=VALUES(f1)|VALUES(f2);
Query OK, 2 rows affected
Time: 0.003s

> select * from t1;
-- 没有出现冲突，直接赋值
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|
| 1  | 2  | 3  |
| 4  | 5  | 6  |
|----|----|----|
2 rows in set
Time: 0.008s

-- 再执行一次，冲突了.执行冲突后的处理语句

> INSERT INTO t1 (f1,f2,f3) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE f3=VALUES(f1)|VALUES(f2);
Query OK, 2 rows affected
Time: 0.002s

> select * from t1;
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|
| 1  | 2  | 3  |
| 4  | 5  | 9  |
|----|----|----|
2 rows in set
Time: 0.008s

ALAIS 引用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
> truncate table t1;
> INSERT INTO t1 (f1,f2,f3) VALUES (1,2,3),(4,5,6) AS new  ON DUPLICATE KEY UPDATE f3 = new.f1|new.f2;
-- 空表没有冲突，直接添加了
> select * from t1;
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|
| 1  | 2  | 3  |
| 4  | 5  | 6  |
|----|----|----|
2 rows in set
Time: 0.008s
-- 再来
> INSERT INTO t1 (f1,f2,f3) VALUES (1,2,3),(4,5,6) AS new  ON DUPLICATE KEY UPDATE f3 = new.f1|new.f2;
-- 出现冲突了, 两行更新
> select * from t1;
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|
| 1  | 2  | 3  |
| 4  | 5  | 9  |
|----|----|----|
2 rows in set

等价写法

1
2
3
4
5
6
7
INSERT INTO t1 (f1,f2,f3) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m|n;
INSERT INTO t1 SET f1=1,f2=2,f3=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a|new.b;

INSERT INTO t1 SET f1=1,f2=2,f3=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m|n;

UNION 引用

1
2
3
4
5
6
INSERT INTO t1 (f1, f2)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE f2 = f2 | f3;

key 自增情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
> truncate table t1;
> select * from t1;
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|

> insert into t1(f2,f3) values (1,3),(1,7) on duplicate key update f3=f2|f3;
Query OK, 2 rows affected
Time: 0.002s

> insert into t1(f2,f3) values (1,3),(1,7) on duplicate key update f3=f2|f3;
Query OK, 2 rows affected
Time: 0.002s

-- 不存在主键冲突，连续可以插入两次

> select * from t1;
|----|----|----|
| f1 | f2 | f3 |
|----|----|----|
| 1  | 1  | 3  |
| 2  | 1  | 7  |
| 3  | 1  | 3  |
| 4  | 1  | 7  |
|----|----|----|
4 rows in set

同一个表有多个unique index会出现冲突

on duplicate key 可以设置多余字段

1
2
3
4
5
6
7
8
CREATE TABLE `duplication_key` (
`f1` int NOT NULL,
`f2` varchar(10) DEFAULT NULL,
`f3` bigint DEFAULT NULL,
`f4` decimal(10, 0) DEFAULT NULL,
PRIMARY KEY (`f1`),
UNIQUE KEY `f2_f3` (`f2`, `f3`)
);
f1 f2 f3 f4
1 1 1 1
2 2 2 2

验证primary key冲突情况

1
2
3
4
5
6
insert into duplication_key
values('2','3','3','20') on duplicate key update
`f1`=VALUES(`f1`),
`f2`=VALUES(`f2`),
`f3`=VALUES(`f3`),
`f4`=VALUES(`f4`);
f1 f2 f3 f4
1 1 1 1
2 3 3 20

f1是主键，出现冲突，更新数据

验证唯一index冲突情况

1
2
3
4
5
6
insert into duplication_key
values('3','3','3','30') on duplicate key update
`f1`=VALUES(`f1`),
`f2`=VALUES(`f2`),
`f3`=VALUES(`f3`),
`f4`=VALUES(`f4`);
f1 f2 f3 f4
1 1 1 1
3 3 3 30

只要有一个冲突就会出现更新

如果同一张表使用不同的unique key来更新数据，那么会出现错误更新问题