這篇文章主要講解了“MySQL支不支持unique索引”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“mysql支不支持unique索引”吧！

新城ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書合作）期待與您的合作！

mysql支持unique索引。在MySQL中，UNIQUE索引允許用戶在一個或多個列中強(qiáng)制實(shí)現(xiàn)值的唯一性，可以防止表中一個或多個列中擁有重復(fù)的值；每個表可以有多個UNIQUE索引，且UNIQUE索引中可以具有多個NULL值。

mysql支持unique索引。在MySQL中，UNIQUE索引可以防止表中一個或多個列中擁有重復(fù)的值。

MySQL UNIQUE索引簡介

要強(qiáng)制執(zhí)行一個或多個列的唯一性值，我們經(jīng)常使用PRIMARY KEY約束。

但是，每個表只有一個主鍵。 如果要使用多個列或一組具有唯一值的列，則不能使用主鍵約束。

幸運(yùn)的是，MySQL提供了另一種稱為UNIQUE索引的索引，它允許您在一個或多個列中強(qiáng)制實(shí)現(xiàn)值的唯一性。 與PRIMARY KEY索引不同，每個表可以有多個UNIQUE索引。

要創(chuàng)建一個UNIQUE索引，請使用CREATE UNIQUE INDEX語句如下：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

在一個或多個列中強(qiáng)制實(shí)現(xiàn)值的唯一性的另一種方法是使用唯一約束。創(chuàng)建唯一約束時，MySQL會在幕后創(chuàng)建一個UNIQUE的索引。

以下語句說明了如何在創(chuàng)建表時創(chuàng)建唯一約束。

CREATE TABLE table_name(
...
   UNIQUE KEY(index_column_,index_column_2,...) 
);

也可以使用UNIQUE INDEX而不是UNIQUE KEY。 它們被稱為相同的東西。

如果要向現(xiàn)有表添加唯一約束，可以使用ALTER TABLE語句，如下所示：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

MySQL UNIQUE索引和NULL

與其他數(shù)據(jù)庫系統(tǒng)不同，MySQL將NULL值視為不同的值。 因此，您可以在UNIQUE索引中具有多個NULL值。

這就是MySQL的設(shè)計(jì)方式。 這不是一個錯誤，即使它被報告為一個錯誤。

另一重要的一點(diǎn)是UNIQUE約束不適用于除了BDB存儲引擎之外的NULL值。

MySQL UNIQUE索引示例

假設(shè)想在應(yīng)用程序中管理聯(lián)系人。還希望contacts表中的email列必須是唯一的。

要執(zhí)行此規(guī)則，請?jiān)贑REATE TABLE語句中創(chuàng)建唯一的約束，如下所示：

USE testdb;

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY unique_email (email)
);

如果使用SHOW INDEXES語句，將看到MySQL為email列創(chuàng)建了一個UNIQUE索引。

SHOW INDEXES FROM contacts;

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contacts |          0 | PRIMARY      |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| contacts |          0 | unique_email |            1 | email       | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

接下來，向contacts表中插入一行。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

現(xiàn)在，如果您嘗試插入電子郵件是max.su@yiibai.com的行數(shù)據(jù)，您將收到一條錯誤消息。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

執(zhí)行上面語句后，應(yīng)該會看到以下結(jié)果 -

1062 - Duplicate entry 'max.su@yiibai.com' for key 'unique_email'

假設(shè)您不僅希望電子郵件是唯一的，而且first_name，last_name和phone的組合也是唯一的。 在這種情況下，可以使用CREATE INDEX語句為這些列創(chuàng)建一個UNIQUE索引，如下所示：

CREATE UNIQUE INDEX idx_name_phone
ON contacts(first_name,last_name,phone);

將以下行添加到contacts表中會導(dǎo)致錯誤，因?yàn)閒irst_name，last_name和phone的組合已存在。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','john.d@yiibai.com');

執(zhí)行上面語句后，應(yīng)該會看到以下結(jié)果 -

1062 - Duplicate entry 'Max-Su-(+86)-999-9988' for key 'idx_name_phone'

可以看到，不可以將重復(fù)電話號碼插入到表中。

唯一性索引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='學(xué)生信息表';

在其中創(chuàng)建了唯一索引name,就是說這個學(xué)生表同名的學(xué)生只能由一位。

命令添加unique:

alter table sc add unique (name);

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

刪除：

alter table sc drop index `name`;

唯一性索引作用：

先行插入部分?jǐn)?shù)據(jù)：

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 ('關(guān)羽','一年二班',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='學(xué)生信息表';

這時的Auto_Increment=5

再次執(zhí)行sql:

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

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

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

unique與primary key的區(qū)別：

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

具體的區(qū)別：

• (1) 唯一性約束所在的列允許空值，但是主鍵約束所在的列不允許空值。

• (2) 可以把唯一性約束放在一個或者多個列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列并不是表的主鍵列。

• (3) 唯一性約束強(qiáng)制在指定的列上創(chuàng)建一個唯一性索引。在默認(rèn)情況下，創(chuàng)建唯一性的非聚簇索引，但是，也可以指定所創(chuàng)建的索引是聚簇索引。

• (4) 建立主鍵的目的是讓外鍵來引用.

• (5) 一個表最多只有一個主鍵，但可以有很多唯一鍵

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

insert ignore:

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

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

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

replace into:

• replace into 首先嘗試插入數(shù)據(jù)到表中。 如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)，否則，直接插入新數(shù)據(jù)。

• 使用replace into，你必須具有delete和insert權(quán)限

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

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

insert on duplicate key update:

• 如果在insert into 語句末尾指定了on duplicate key update，并且插入行后會導(dǎo)致在一個UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則在出現(xiàn)重復(fù)值的行執(zhí)行UPDATE；如果不會導(dǎo)致重復(fù)的問題，則插入新行，跟普通的insert into一樣。

• 使用insert into，你必須具有insert和update權(quán)限

• 如果有新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2；如果記錄被更新前后值是一樣的，則受影響行數(shù)的值顯示0

insert into sc (name,class,score) values ('關(guān)羽','二年二班',80) on duplicate key update score=100;
> Affected rows: 2
> 時間: 0.008s

舊數(shù)據(jù)中關(guān)羽是一年二班，70分，現(xiàn)在插入，最后發(fā)現(xiàn)只有分?jǐn)?shù)變成了100，班級并沒有改變。

4	關(guān)羽	一年二班	100	2018-11-16 15:32:18		2018-11-16 15:51:51

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

死鎖：

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

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

解決辦法：

1、盡量對存在多個唯一鍵的table使用該語句

2、在有可能有并發(fā)事務(wù)執(zhí)行的insert 的內(nèi)容一樣情況下不使用該語句

結(jié)論：

• 這三種方法都能避免主鍵或者唯一索引重復(fù)導(dǎo)致的插入失敗問題。

• insert ignore能忽略重復(fù)數(shù)據(jù)，只插入不重復(fù)的數(shù)據(jù)。

• replace into和insert ... on duplicate key update，都是替換原有的重復(fù)數(shù)據(jù)，區(qū)別在于replace into是刪除原有的行后，在插入新行，如有自增id，這個會造成自增id的改變；insert ... on duplicate key update在遇到重復(fù)行時，會直接更新原有的行，具體更新哪些字段怎么更新，取決于update后的語句。

感謝各位的閱讀，以上就是“mysql支不支持unique索引”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對mysql支不支持unique索引這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！

標(biāo)題名稱：mysql支不支持unique索引
文章源于：http://jinyejixie.com/article6/igogog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、動態(tài)網(wǎng)站、網(wǎng)站內(nèi)鏈、商城網(wǎng)站、小程序開發(fā)、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)