**说明: 1. on duplicate key update 含义: 1)如果在INSERT语句末尾指定了 on duplicate key update, 并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值, 则在出现重复值的行执行UPDATE; 2)如果不会导致唯一值列重复的问题,则插入新行。 2. values(col_name)函数只是取当前插入语句中的插入值,并没有累加功能。 如:count = values(count) 取前面 insert into 中的 count 值,并更新 当有多条记录冲突,需要插入时,前面的更新值都被最后一条记录覆盖, 所以呈现出取最后一条更新的现象。 如:count = count + values(count) 依然取前面 insert into 中的 count 值, 并与原记录值相加后更新回数据库,这样,当多条记录冲突需要插入时, 就实现了不断累加更新的现象。 注:insert into ... on duplicate key update ... values() 这个语句 尽管在冲突时执行了更新,并没有插入,但是发现依然会占用 id 序号(自增), 出现很多丢失的 id 值,可参看下面案例
----《百度百科--MySQL函数》
函数 VALUES(col_name)
函数使用说明:在一个 INSERT … ON DUPLICATE KEY UPDATE … 语句中,你可以在 UPDATE 子句中使用 VALUES(col_name ) 函数,用来访问来自该语句的 INSERT 部分的列值。换言之,UPDATE 子句中的 VALUES(col_name ) 访问需要被插入的 col_name 的值 , 并不会发生重复键冲突。这个函数在多行插入中特别有用。 VALUES() 函数只在 INSERT ... UPDATE 语句中有意义,而在其它情况下只会返回 NULL。
**案例: 0. 创建案例表 word_count_0626(单词计数表) use test; CREATE TABLE IF NOT EXISTS word_count_0626 ( id int(11) NOT NULL AUTO_INCREMENT, word varchar(64) NOT NULL, count int(11) DEFAULT 0, date date NOT NULL, PRIMARY KEY (id), UNIQUE KEY word (word, date) // (word,date) 两字段组合唯一 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 注:curdate() 为 "2019-06-26" 1. 执行第一次:(首次数据库表中没有数据,正常插入) insert into word_count_0626 (word, count, date) values (‘a‘,5,curdate()) on duplicate key update count=values(count); # 结果显示: id word count date 1 a 5 2019-06-26 2. 执行第二次:(与第一次的唯一(word,date)冲突,执行更新) insert into word_count_0626 (word, count, date) values (‘a‘,6,curdate()) on duplicate key update count=values(count); # 结果显示: id word count date 1 a 6 2019-06-26 (更新) 3. 执行第三次: insert into word_count_0626 (word, count, date) values (‘a‘,6,curdate()-1), // 取前一天,不会冲突 (‘a‘,7,curdate()) // 冲突 on duplicate key update count=values(count); # 结果显示: id word count date 1 a 7 2019-06-26 (更新) 3 a 6 2019-06-25 (新插入) 4. 执行第四次:(更新冲突的最后一条插入值) insert into word_count_0626 (word, count, date) values (‘a‘,2,curdate()), // 冲突 (‘a‘,1,curdate()) // 冲突 on duplicate key update count=values(count); # 结果显示: id word count date 1 a 1 2019-06-26 (更新最后一条插入值) 3 a 6 2019-06-25 (不变) 5. 执行第五次:(更新冲突的累加插入值) insert into word_count_0626 (word, count, date) values (‘a‘,2,curdate()), (‘a‘,1,curdate()) on duplicate key update count=count+values(count); // 实现每行累加 # 结果显示: id word count date 1 a 4 2019-06-26 3 a 6 2019-06-25 6. 执行第六次:(无冲突插入,观察 id 键值,出现了很多丢失,id 直接跳到了 9) insert into word_count_0626 (word, count, date) values (‘b‘,2,curdate()) on duplicate key update count=count+values(count); # 结果显示: id word count date 1 a 4 2019-06-26 3 a 6 2019-06-25 9 b 2 2019-06-26
转载自:MySQL中 insert into ... on duplicate key update ... values() 的使用笔记