insert...on duplicate key update语法详解

时间:2023-02-02 ZhaoYingChao88 人气:0

一.作用和使用场景

在mysql入库时，不能出现两条数据主键一致的情况，因为在两条数据的主键一致的情况下，mysql就会判定为待插入数据在数据库中存在重复数据，也就是说判断数据是否重复是根据主键来区别的。

但是有一些场景，如日志文件解析入库，消息队列接收数据入库等情况下可能解析到或者接收到待插入的重复数据存在重复数据则更新，不存在则插入。

这时如下语句的写法就派上用场了，on duplicate key update的作用也就是说存在重复数据则更新，不存在则插入。

二.例子详细讲解

场景大概是这样的，业务方的需求是查询一条语句在不在，如果在就给出一个update语句，更新这条记录，如果不在，就给出一个insert语句，插入这条记录。逻辑大概是:

result =  select * from table;
if result = 0
   insert the record into table;
else
   update the record;

这样的操作乍一看没有什么问题，但是仔细分析分析，还是有些瓶颈的，目前来看，我能分析到的瓶颈有两个，

其一:

每次要执行2个SQL，效率比较差；

其二：

当我们在高并发的情况下跑这条语句，如果程序崩溃，不能保证操作的原子性。

说明：
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 值，可参看下面案例

函数使用说明：在一个 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

说明：

insert...on duplicate key方法

简单写一个例子,内容大致如下：

1、首先创建一个包含id,name,age的表，其中id是主键；

2、在这个表中插入一条id=1的记录；

3、使用insert...on duplicate key update语法插入一条id=2的记录；

4、使用同样的语法更新id=1的记录；

mysql 23:12:32>>create table test_1( 
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int not null
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.08 sec)
 
mysql 23:13:26>>insert into test_1 values (1,'yyz',16);
Query OK, 1 row affected (0.01 sec)
 
mysql 23:13:58>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
+----+------+-----+
1 row in set (0.00 sec)
 
 
mysql 23:14:36>>insert into test_1 (id,name,age) values (2,'yyz',18) on duplicate key update age=18;
Query OK, 1 row affected (0.01 sec)
 
mysql 23:15:08>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)
 
mysql 23:15:17>>insert into test_1 (id,name,age) values (1,'yyz',18) on duplicate key update age=18;
Query OK, 2 rows affected (0.00 sec)
 
mysql 23:15:28>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  18 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)

insert...on duplicate key update语法的作用，可以分析到，当发生主键冲突的时候，可以直接进行update操作，这个update操作里面可以更新任意想要更新的列；而没有主键冲突的时候，相当于对这个表进行了一次插入操作。

Replace操作

Replace语句。使用Replace插入一条记录时，如果不重复，Replace就和Insert的功能一样，如果有重复记录，Replace就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将Delete和Insert合二为一，形成一个原子操作。这样就可以不必考虑在同时使用Delete和Insert时添加事务等复杂操作了。

在使用Replace时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则Replace就和Insert完全一样的。

在执行Replace后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了Delete删除这条记录，然后再记录用Insert来插入这条记录。

不同之处

有了上面的知识储备，这两条命令的不同之处就显而易见了，replace是删除记录，然后再重新insert，而insert...on duplicate key update是直接在该条记录上修改，所以二者的差别主要有以下两处：

1、当表中存在自增值的时候，如果表中存在某条记录，replace语法会导致自增值+1，而insert...on duplicate key update语法不会；

2、当表中的某些字段中包含默认值的时候，replace操作插入不完全字段的记录，会导致其他字段直接使用默认值，而insert...on duplicate key update操作会保留该条记录的原有值。

加载全部内容