⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 cnblogs.com/hongdada/p/9970176.html 「hongdada」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注**微信公众号：【芋道源码】**有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

唯一性索引unique影响

唯一性索引表创建

    DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
                      `id` int(11) NOT NULL AUTO_INCREMENT,
                      `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
                      `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
                      `score` int(11) DEFAULT NULL,
                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
                      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

在其中创建了唯一索引name,就是说这个学生表同名的学生只能由一位。

命令添加unique:

    alter table sc add unique (name);

alter table sc add unique key `name_score` (`name`,`score`);

删除：

alter table sc drop index `name`;

唯一性索引作用

先行插入部分数据：

    insert into sc (name,class,score) values ('吕布','一年二班',67);
insert into sc (name,class,score) values ('赵云','一年二班',90);
insert into sc (name,class,score) values ('典韦','一年二班',89);
insert into sc (name,class,score) values ('关羽','一年二班',70);

再次查看表定义：

    show create table sc;

CREATE TABLE `sc` (
                      `id` int(11) NOT NULL AUTO_INCREMENT,
                      `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
                      `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
                      `score` int(11) DEFAULT NULL,
                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
                      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

这时的Auto_Increment=5 再次执行sql:

insert into sc (name,class,score) values ('吕布','二年二班',77)
> 1062 - Duplicate entry '吕布' for key 'name'
> 时间: 0.01s

此时再次查看表定义，会发现Auto_Increment=6

unique除了在插入重复数据的时候会报错，还会使auto_increment自动增长

unique与primary key的区别

简单的讲，primary key=unique+not null

具体的区别：

(1) 唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。

(2) 可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。

(3) 唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。

(4) 建立主键的目的是让外键来引用.

(5) 一个表最多只有一个主键，但可以有很多唯一键

存在唯一键冲突时，避免策略

insert ignore

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

insert ignore into sc (name,class,score) values ('吕布','二年二班',77)

执行上面的语句，会发现并没有报错，但是主键还是自动增长了。

replace into

replace into 首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
使用replace into，你必须具有delete和insert权限

replace into sc (name,class,score) values ('吕布','二年二班',77);

此时会发现吕布的班级跟年龄都改变了，但是id也变成最新的了，所以不是更新，是删除再新增

insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。
使用insert into，你必须具有insert和update权限
如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

    insert into sc (name,class,score) values ('关羽','二年二班',80) on duplicate key update score=100;
> Affected rows: 2
    > 时间: 0.008s

旧数据中关羽是一年二班，70分，现在插入，最后发现只有分数变成了100，班级并没有改变。

4 关羽  一年二班  100 2018-11-16 15:32:18   2018-11-16 15:51:51

id没有发生变化，数据只更新，但是auto_increment还是增长1了。

死锁

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如

img

解决办法：

1、尽量对存在多个唯一键的table使用该语句
2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论：

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
insert ignore能忽略重复数据，只插入不重复的数据。
replace into和insert … on duplicate key update，都是替换原有的重复数据，区别在于replace

into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

芋道源码 —— 纯源码解析博客

面试官：MySQL 唯一索引为什么会导致死锁？