MySQL的insert-on-duplicate语句详解

一、insert-on-duplicate语句语法

留意：ON DUPLICATE KEY UPDATE只是 MySQL的特有语法，并不是SQL标准语法！
INSERT INTO … ON DUPLICATE KEY UPDATE 是 MySQL 中一种用于插入数据并处理重复键辩论的语法。
这个语法实用于在 insert的时候，如果insert的数据会引起唯一索引(包括主键索引)的辩论，即唯一值重复了，则不会执行insert操作，而执行后面的update操作。
基本语法为：

1. INSERT INTO table_name (column1, column2, ...)
2. VALUES (value1, value2, ...)
3. ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;
5. -- 一般 Update子句可以使用 VALUES(col_name)获取 insert部分的值
6. INSERT INTO table_name (column1, column2, ...)
7. VALUES (value1, value2, ...)
8. ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2), ...;

复制代码

阐明：

• table_name 是要插入数据的表名。
  
• (column1, column2, …) 是要插入的列名列表。
  
• (value1, value2, …) 是要插入的对应列的值列表。
  
• ON DUPLICATE KEY UPDATE 子句后面指定了在辩论时需要执行的更新操作。
  
• column1 = value1, column2 = value2, … 是要更新的列和对应的新值。
  
• column1 = VALUES(column1), column2 = VALUES(column2), … 是要更新的列和对应的新值（insert部门的值）。
  

insert-on-duplicate语句处理逻辑：
   语句是根据唯一索引判断记载是否重复的。当执行插入操作时，如果唯一键不辩论(表中不存在记载)，则执行插入操作；如果遇到唯一键辩论(表中存在记载)，则会执行更新操作，使用给定的新值来更新辩论行中的列。
  

• 如果不存在记载，插入，则影响的行数为1；
  
• 如果存在记载，可以更新字段，则影响的行数为2；
  
• 如果存在记载，并且更新的值和原有的值相同，则影响的行数为0。
  

留意：如果表同时存在多个唯一索引，只会根据第一个在数据库中存在相应value的列唯一索引做duplicate判断。
二、示例表操作使用

t_user表布局：表中有一个主键id、一个唯一索引idx_name；

1. CREATE TABLE `t_user` (
2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
3.   `user_name` varchar(30) NOT NULL  COMMENT '用户名',
4.   `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
5.   `height` int DEFAULT '0' COMMENT '身高cm',
6.   `type` int(1) DEFAULT NULL COMMENT '类型',
7.   PRIMARY KEY (`id`),
8.   UNIQUE KEY `idx_name` (`user_name`) USING BTREE,
9.   KEY `idx_type` (`type`) USING BTREE
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  COMMENT='用户表';

复制代码

1、不存在记载，插入的环境

1. INSERT into t_user(user_name, age, height) VALUES("lisi", 17, 180) on DUPLICATE KEY UPDATE age = 18;

复制代码

2、存在记载，可以更新字段的环境

1. INSERT into t_user(user_name, age, height) VALUES("lisi", 17, 180) on DUPLICATE KEY UPDATE age = 18;

复制代码

3、存在记载，不可以更新字段的环境

1. INSERT into t_user(user_name, age, height, type) VALUES("lisi", 18, 180, 1) on DUPLICATE KEY UPDATE age = 18;

复制代码

4、存在多个唯一索引时

   如果表同时存在多个唯一索引，只会根据第一个在数据库中存在相应value的列唯一索引做duplicate判断。
  1）数据库中id = 2的记载不存在，user_name="lisi"的记载存在，以是会根据第二个唯一索引 user_name做duplicate判断：执行 update操作。

1. INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisi", 27, 280, 0) on DUPLICATE KEY UPDATE age = 28;

复制代码

2）数据库中id = 2的记载不存在，user_name="lisisi"的记载不存在，以是不存在重复键辩论：执行 insert操作。

1. INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 27, 280, 0) on DUPLICATE KEY UPDATE age = 28;

复制代码

3）数据库中 id = 2的记载存在，user_name="lisisi"的记载存在，以是会根据第一个唯一索引id做duplicate判断：执行 update操作。

1. INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 37, 380, 1) on DUPLICATE KEY UPDATE age = 38;

复制代码

4）数据库中 id = 2的记载存在，user_name="lisisi2"的记载不存在，以是会根据第一个唯一索引id做duplicate判断：执行 update操作。

1. INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi2", 47, 480, 0) on DUPLICATE KEY UPDATE age = 48;

复制代码

5、VALUES(col_name)使用

一样平常 Update子句可以使用 VALUES(col_name)获取 insert部门的值。也是项目中使用最多的方式。
留意：VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL。

1. INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 57, 480, 0) on DUPLICATE KEY UPDATE age = VALUES(age) + 100;

复制代码

6、批量操作

批量操作之前表中数据如下：

批量语句如下：

1. INSERT INTO t_user(user_name, age, height, type)
2. VALUES
3. ("lisi", 71, 701, 0),
4. ("lisisi", 72, 280, 1),
5. ("zhangsan", 73, 703, 0),
6. ("wangwu", 74, 704, null),
7. ("laoliu", 75, null, null)
8. ON DUPLICATE KEY UPDATE
9. user_name = VALUES(user_name),
10. age = VALUES(age),
11. height = VALUES(height),
12. type = VALUES(type);

复制代码

批量语句执行操作之后表中数据如下：

参考文章：

• 官方文档：https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
  

   – 求知若饥，虚心若愚。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。