唯一性索引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后的语句。