ON DUPLICATE KEY UPDATE 用法

注意：ON DUPLICATE KEY UPDATE 是Mysql特有的语法，仅Mysql有效。

作用：当执行insert操作时，有已经存在的记录，执行update操作。

用法：

有一个test表，id为主键。第一次插入数据

INSERT INTO test(id,name,age)VALUES(1,'2',3),(11,'22',33)

此时表中数据增加了一条主键’id’为‘1’和‘11’的两条记录，当我们再次执行一条id为1的插入语句时，会发生什么呢？

INSERT INTO test(id,name,age)VALUES(1,'张三',13)

INSERT INTO test(id,name,age)VALUES(1,'张三',13)
> 1062 - Duplicate entry '1' for key 'PRIMARY'
> 时间: 0.034s

Mysql告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE
INSERT INTO test ( id, NAME, age )
VALUES( 1, '张三', 13 ) 
    ON DUPLICATE KEY UPDATE id = 1,
    NAME = '张三',
    age = 13

执行结果中受影响的行数是2。

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE
INSERT INTO test ( id, NAME, age )
VALUES( 1, '张三', 13 ) 
    ON DUPLICATE KEY UPDATE id = 1,
    NAME = '张三',
    age = 13
> Affected rows: 2
> 时间: 0.18s

执行上面的语句结果

此时我们如果再次插入( 1, '张三', 13 ) 的数据时会有什么结果

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE
INSERT INTO test ( id, NAME, age )
VALUES( 1, '张三', 13 ) 
    ON DUPLICATE KEY UPDATE id = 1,
    NAME = '张三',
    age = 13
> Affected rows: 0
> 时间: 0.013s

可以看到影响的行数为0。插入的时候主键冲突，ON DUPLICATE KEY UPDATE会执行更新操作，更新为id = 1,NAME = '张三',age = 13 ，但是并没有我们想象的执行更新。

总结：ON DUPLICATE KEY UPDATE首先会检查插入的数据主键是否冲突，如果冲突则执行更新操作，如果ON DUPLICATE KEY UPDATE的子句中要更新的值与原来的值都一样，则不更新。如果有一个值与原值不一样，则更新：

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE
INSERT INTO test ( id, NAME, age )
VALUES( 1, '张三', 13 ) 
    ON DUPLICATE KEY UPDATE id = 1,
    NAME = '张三',
    age = 133
> Affected rows: 2
> 时间: 0.014s

执行完毕，id为1的age值改为133

目前id为1的数据age字段值为13，我们执行插入语句时只改变了其中一个值age=133,则影响行数为2。此时注意VALUES( 1, '张三', 13 ) 中age值为13，ON DUPLICATE KEY UPDATE子句中age值为133。

如果插入的数据主键有冲突，则修改字段值以ON DUPLICATE KEY UPDATE子句的值为准。

ON DUPLICATE KEY UPDATE子句写的是固定值，怎么动态赋值呢？如果一次插入多条数据，怎么动态获取主键冲突所要更新的值呢？

ON DUPLICATE KEY UPDATE age = VALUES(age)

总结：

1. ON DUPLICATE KEY UPDATE检查主键或唯一索引字段是否冲突。

2. update的字段值与现存的字段值相同，则不更新。

3. 动态更新字段值用VALUES(字段名称)。