唯一性索引unique影響

唯一性索引表創(chuàng)建

    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 '創(chuàng)建時間',
                      `create_user_id` bigint(11) DEFAULT NULL COMMENT '創(chuàng)建人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='學生信息表';

在其中創(chuàng)建了唯一索引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 '創(chuàng)建時間',
                      `create_user_id` bigint(11) DEFAULT NULL COMMENT '創(chuàng)建人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 再次執(zhí)行sql:

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

此時再次查看表定義，會發(fā)現Auto_Increment=6

unique除了在插入重復數據的時候會報錯，還會使auto_increment自動增長

unique與primary key的區(qū)別

簡單的講，primary key=unique+not null

具體的區(qū)別：

(1) 唯一性約束所在的列允許空值，但是主鍵約束所在的列不允許空值。
(2) 可以把唯一性約束放在一個或者多個列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列并不是表的主鍵列。
(3) 唯一性約束強制在指定的列上創(chuàng)建一個唯一性索引。在默認情況下，創(chuàng)建唯一性的非聚簇索引，但是，也可以指定所創(chuàng)建的索引是聚簇索引。
(4) 建立主鍵的目的是讓外鍵來引用.
(5) 一個表最多只有一個主鍵，但可以有很多唯一鍵

存在唯一鍵沖突時，避免策略

insert ignore

insert ignore會忽略數據庫中已經存在的數據(根據主鍵或者唯一索引判斷)，如果數據庫沒有數據，就插入新的數據，如果有數據的話就跳過這條數據.

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

執(zhí)行上面的語句，會發(fā)現并沒有報錯，但是主鍵還是自動增長了。

replace into

replace into 首先嘗試插入數據到表中。如果發(fā)現表中已經有此行數據(根據主鍵或者唯一索引判斷)則先刪除此行數據，然后插入新的數據，否則，直接插入新數據。
使用replace into，你必須具有delete和insert權限

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

此時會發(fā)現呂布的班級跟年齡都改變了，但是id也變成最新的了，所以不是更新，是刪除再新增

insert on duplicate key update

如果在insert into 語句末尾指定了on duplicate key update，并且插入行后會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值，則在出現重復值的行執(zhí)行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分，現在插入，最后發(fā)現只有分數變成了100，班級并沒有改變。

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

id沒有發(fā)生變化，數據只更新，但是auto_increment還是增長1了。

死鎖

insert … on duplicate key 在執(zhí)行時，innodb引擎會先判斷插入的行是否產生重復key錯誤，如果存在，在對該現有的行加上S(共享鎖)鎖，如果返回該行數據給mysql,然后mysql執(zhí)行完duplicate后的update操作，然后對該記錄加上X(排他鎖)，最后進行update寫入。

如果有兩個事務并發(fā)的執(zhí)行同樣的語句，那么就會產生death lock，如