MySQL中 insert into … on duplicate key update … values() 的使用笔记

  • Post author:
  • Post category:mysql


**说明:
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

参考文章:


1. MySQL ON DUPLICATE KEY UPDATE 语法


2. 百度百科–MySQL函数



版权声明:本文为weixin_39469127原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。