Mysql：on DUPLICATE key update

Mysql：on DUPLICATE key update

**ps:**由于工作需要（更换了新的项目组，而新的项目组又使用的是mysql数据库。之前一直用oralce），用过oracle的小伙伴都知道，oracle中的Merge函数。作用：根据指定的查询条件去关联。如果存在就更新，不存在就新增；（简单叙述一下）
思考：那么mysql中有没有一款函数能有类似的功能呢？
答：Mysql中是没有Merge函数的；Merge函数是oracle独有的；但是Mysql中有一款函数，可以满足类似的功能：on DUPLICATE key update；

下面我就用实践来进行验证和说明该函数如何使用：

1）创建表：
CREATE TABLE test_duplication (
id bigint(20) NOT NULL ,
name varchar(80) NULL DEFAULT NULL COMMENT ‘姓名’ ,
idcard varchar(80) NULL DEFAULT NULL COMMENT ‘身份证号码’ ,
PRIMARY KEY (id)
) COMMENT=‘测试on DUPLICATE key update函数临时表’;
ps: id 为主键索引；特性：不能为空，不能重复；

2）执行插入数据sql:
insert into test_duplication(ID,name,idcard) VALUES (1,‘1’,‘1’);

3)再次执行：
insert into test_duplication(ID,name,idcard) VALUES (1,‘1’,‘1’);
报错了，因为主键冲突了；

4）执行sql:
insert into test_duplication(ID,name,idcard) VALUES (1,‘1’,‘1’) on DUPLICATE key update id=2,NAME=‘2’,idcard=‘2’;

on DUPLICATE key update 函数，发挥了作用：因为已经存在了，所以执行了更新；否则执行insert;

那么问题来了：怎么判读已经存在了呢？又根据什么来判断的呢？
答：如果你插入的记录导致主键索引或者UNIQUE索引重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。
比如：我创建表的时候设置的唯一索引（复合索引）为字段（a，b，c），那么当a，b，c三个字段完全重复时候，此时就要执行更新语句。当然满足一部分唯一索引是不会触发更新操作的，此时会执行插入操作。
　　而至于要更新哪些字段（位于on DUPLICATE key update 语句后面的，即为需要更新的字段），要看我们自己的需求了。
　　
剖析一下上诉sql执行时的判断逻辑：
我们执行的sql: insert into test_duplication(ID,name,idcard) VALUES (1,‘1’,‘1’) on DUPLICATE key update id=2,NAME=‘2’,idcard=‘2’; 由于我们创建的表中id为主键索引，并且表中只有这一个索引；所以就会使用id=1,这个条件去数据库中查询；查询到没有就新增。查询到有就执行了更新；

5）我们创建idcard为唯一索引：
create UNIQUE index idcard_index on test_duplication(idcard);

6）再执行sql:
insert into test_duplication(ID,name,idcard) VALUES (3,‘3’,‘3’) on DUPLICATE key update id=2,NAME=‘2’,idcard=‘2’;

7）如果一个sql 中有两个索引，优先使用哪一个？
执行sql：insert into test_duplication(ID,name,idcard) VALUES (1000,‘3’,‘3’) on DUPLICATE key update NAME=‘4’,idcard=‘4’;
其中：id=1000是不存在的；但是idcard=‘3’ 是存在的；那么执行结果是什么呢？

运行结果如上图，是执行成功的，也就是说：主键索引id 、唯一索引idcard; 只要有一个是满足的，就可以执行成功；

8）如果我们执行：insert into test_duplication(ID,name,idcard) VALUES (2,‘3’,‘4’) on DUPLICATE key update NAME=‘5’,idcard=‘5’;
其中id=2是一条数据的主键。idcard=‘4’ 是另一条数据的 ；

思考： 如果sql中既存在id（主键索引）,又存在idcard（唯一索引）会以主键索引为准？？？？是否与顺序有关？那我们换一下id、idcard的顺序再试一下；
执行sql : insert into test_duplication(idcard,name,id) VALUES (‘5’,‘3’,3) on DUPLICATE key update NAME=‘6’,idcard=‘6’;

可见：如果 on DUPLICATE key update 语句中既存在id(主键索引)，又存在唯一索引（idcard）;那么都会以主键索引为主。如果用主键索引查询不存在记录，那么再使用唯一索引；（主键索引优先）

9）当插入多条数据，其中不只有表中已存在的，还有需要新插入的数据，Mysql会如何执行呢？会不会报错呢？
执行sql : insert into test_duplication(id,name,idcard) VALUES (3,‘6’,‘6’),(4,‘7’,‘7’)on DUPLICATE key update NAME=‘8’,idcard=‘8’;

结论：on DUPLICATE key 有多条数据要执行时，会根据每一条语句是否存在数据，进行针对性的逻辑处理；

9）那么问题又来了，有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值，如果我想要分别给不同的记录插入不同的值怎么办呢？
执行sql:
insert into test_duplication(id,name,idcard) VALUES (3,‘9’,‘9’),(4,‘10’,‘10’)on DUPLICATE key update NAME=values(name),idcard=values(idcard);

使用values可以进行动态的更换；