mysql中 replace into ... 和 insert into ... ON DUPLICATE KEY UPDATE ... 有则更新无则添加

一、insert into ... ON DUPLICATE KEY UPDATE ...

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，

　　并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE；

　　如果不会导致唯一值列重复的问题，则插入新行。

　　例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

INSERT INTO table (a,b,c) 
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

　　如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

如想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明，详见MySQL参考文档：13.2.4. INSERT语法

　　　现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？

　　要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。

　　这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。

举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在记录(2,2,9)和(3,2,1)，如果插入记录的a值与原有记录重复，则更新原有记录，否则插入新行：

INSERT INTO table (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

　　　　以上SQL语句的执行，发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突，则执行ON DUPLICATE KEY UPDATE，

　　将原有记录(2,2,9)更新成(2,5,9)，将(3,2,1)更新成(3,3,1)，插入新记录(1,2,3)和(4,8,2)

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！

二、replace into ...

replace into t_table(dt, lodge_id, platform_id, price,  online_time) 
values('2018-12-10',111,6,31238232903,null)

　　其中 dt, lodge_id, platform_id 为联合唯一索引

　replace语句会删除原有的一条记录，并且插入一条新的记录来替换原记录。具体如下：

当replace into 记录只与主键冲突的时候，auto_increment不会增加，它会对与主键冲突的那一条记录进行更新，没有指定的列将会被更新为默认值
当replace into 记录与主键跟唯一索引同时冲突的时候，auto_increment不会增加
1. 如果冲突的主键和索引在同一行记录，则replace into只做更新，对于没有指定值的其他列，将会被更新为默认值，
2. 如果冲突的主键和索引分别对应2行数据，则MySQL将会删除唯一索引的那一行记录，更新对应主键的那一行记录。
当replace into 记录只与唯一索引进行冲突的时候，auto_increment + 1，再对数据进行更新。

最后我们可以对总结分析下，MySQL对replace into的操作是首先是insert操作，如果insert失败，则对insert失败的这条记录进行update，如果update还是失败，则会进行delete操作之后再update。
具体流程是这样的：insert记录，发现主键冲突，则update这一行，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。如果insert成功，auto_increment自然+1了，然后对这条记录进行update，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。