Home » Code » Mysql on duplicate key update的用法

Mysql on duplicate key update的用法

接上篇《Mysql插入语句replace into》讨论的先判断记录是否存在,存在则更新,否则插入的问题,表依然是那个。用replace into可以达到效果,但毕竟不是在原记录上更新,而on duplicate key update则是直接在原记录上更新。

on duplicate key update

原表中已有记录

执行on duplicate key update的插入,userId为2的记录表中已经存在,执行后id和userId都不变,IP被更新为’5.5.5.5’。
INSERT INTO loginIP (id, userId, IP) VALUES (null, 2, '5.5.5.5') ON DUPLICATE KEY UPDATE IP = '5.5.5.5'

on duplicate key update

执行on duplicate key update后

对于加了on duplicate key update的INSERT,如果是插入了新行受影响的记录数是1,更新已有记录受影响的记录数则为2。由于是在原记录中直接更新,看似效果比replace into要好。

再来看有两个字段冲突的情况,执行以下语句:
INSERT INTO loginIP (id, userId, IP) VALUES (2, 3, '6.6.6.6') ON DUPLICATE KEY UPDATE IP = '6.6.6.6'

这里要插入的id值2在表中已经存在,userId值3在表中也已经存在,结果是怎么样的呢,看结果:

on duplicate key update

两个字段冲突

可以看到,它只是把id为2的记录更新了不会引起冲突的字段的值,最终表中根本没有2, 3, ‘6.6.6.6’这一条记录存在,而使用replace into则能在表中找到要插入的记录。这在官方手册中也有说明,所以在插入记录可能引起多个唯一索引冲突时,不建议使用on duplicate key update。实际使用中,我们要插入的记录一般也是只会引起一个唯一索引发生冲突,下面只讨论一个唯一索引冲突的情况。

假如要一次性插入多条,语句该怎么写能实现有则更新,无则插入呢。可以使用VALUES()函数:
INSERT INTO loginIP (id, userID, IP) VALUES
(null, 2, '7.7.7.7'),
(null, 3, '8.8.8.8'),
(null, 4, '9.9.9.9'),
(null, 2, '10.10.10.10'),
(null, 3, '11.11.11.11'),
(null, 5, '12.12.12.12')
ON DUPLICATE KEY UPDATE userId = VALUES(userId)

userId为2,3的记录已经存在,只把userId更新为原来的值(实际就是不变),userId为4,5的记录则为新插入的。

on duplicate key update

同时插入多条可能引起冲突的记录

VALUES()函数取的是当前要插入的记录的字段的值,看下边两要语句:
INSERT INTO loginIP (id, userId, IP) VALUES (2, 2, '0.0.0.0') ON DUPLICATE KEY UPDATE userId = userId + 10;

这语句的意思是如果冲突就把userId取原来的值再加上10。这里id和userId都冲突,最后只是把userId更新为2+10,其他不变。

on duplicate key update

取原值更新

如果语句为:
INSERT INTO loginIP (id, userId, IP) VALUES (2, 10, '0.0.0.0') ON DUPLICATE KEY UPDATE userId = VALUES(userId) + 10;

这里id冲突,把userId的值更新为当前要插入记录的userId的值10加上10,最后结果变为20

on duplicate key update

取要插入记录的值更新

咳咳,终于给写完了。至于实际中是用REPLACE INTO还是ON DUPLICATE KEY UPDATE,看了二者的异同,按需选择吧。

2014/5/25补充:对于多字段组成的唯一索引也是有效的哟!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.