mysql支援unique索引嗎-mysql教程-PHP中文網

mysql支援unique索引。在MySQL中，UNIQUE索引允許使用者在一個或多個欄位中強制實現值的唯一性，可以防止表中一個或多個欄位中擁有重複的值；每個表可以有多個UNIQUE索引，且UNIQUE索引中可以具有多個NULL值。

mysql支援unique索引嗎

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

mysql支援unique索引。在MySQL中，UNIQUE索引可以防止表中一個或多個欄位中擁有重複的值。

MySQL UNIQUE索引簡介

要強制執行一個或多個欄位的唯一性值，我們經常使用PRIMARY KEY限制。

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

幸運的是，MySQL提供了另一種稱為UNIQUE索引的索引，它允許您在一個或多個欄位中強制實作值的唯一性。與PRIMARY KEY索引不同，每個表可以有多個UNIQUE索引。

要建立UNIQUE索引，請使用CREATE UNIQUE INDEX語句如下：

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

登入後複製

在一個或多個欄位中強制實作值的唯一性的另一種方法是使用唯一限制。建立唯一約束時，MySQL會在幕後建立一個UNIQUE的索引。

以下語句說明如何在建立表格時建立唯一約束。

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

登入後複製

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

如果要為現有資料表新增唯一約束，可以使用ALTER TABLE語句，如下所示：

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

登入後複製

MySQL UNIQUE索引和NULL

#與其他資料庫系統不同，MySQL將NULL值視為不同的值。因此，您可以在UNIQUE索引中具有多個NULL值。

這就是MySQL的設計方式。這不是一個錯誤，即使它被報告為一個錯誤。

另一個重要的一點是UNIQUE約束不適用於除了BDB儲存引擎之外的NULL值。

MySQL UNIQUE索引範例

假設想在應用程式中管理聯絡人。也希望contacts表中的email列必須是唯一的。

要執行此規則，請在CREATE TABLE語句中建立唯一的約束，如下所示：

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列建立了一個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表中插入一行。

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

登入後複製

現在，如果您嘗試插入電子郵件是max.su@yiibai.com的行數據，您將收到錯誤訊息。

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

登入後複製

執行上面語句後，應該會看到以下結果 -

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

登入後複製

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

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

登入後複製

將以下行新增到contacts表中會導致錯誤，因為first_name，last_name和phone的組合已存在。

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

登入後複製

執行上面語句後，應該會看到以下結果 -

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

登入後複製

可以看到，不可以將重複電話號碼插入表中。

唯一性索引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 &#39;创建时间&#39;,
  `create_user_id` bigint(11) DEFAULT NULL COMMENT &#39;创建人id&#39;,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT &#39;最后修改时间&#39;,
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT &#39;最后修改人id&#39;,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=&#39;学生信息表&#39;;

登入後複製

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

指令新增unique:

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

登入後複製

刪除：

1	alter table sc drop index `name`;

登入後複製

唯一性索引作用：

先行插入部分數據：

insert into sc (name,class,score) values (&#39;吕布&#39;,&#39;一年二班&#39;,67);
insert into sc (name,class,score) values (&#39;赵云&#39;,&#39;一年二班&#39;,90);
insert into sc (name,class,score) values (&#39;典韦&#39;,&#39;一年二班&#39;,89);
insert into sc (name,class,score) values (&#39;关羽&#39;,&#39;一年二班&#39;,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 &#39;创建时间&#39;,
  `create_user_id` bigint(11) DEFAULT NULL COMMENT &#39;创建人id&#39;,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT &#39;最后修改时间&#39;,
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT &#39;最后修改人id&#39;,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=&#39;学生信息表&#39;;

登入後複製

這時的Auto_Increment=5

再次執行sql:

insert into sc (name,class,score) values (&#39;吕布&#39;,&#39;二年二班&#39;,77)
> 1062 - Duplicate entry &#39;吕布&#39; for key &#39;name&#39;
> 时间: 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会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

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

登入後複製

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

replace into:

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

1	`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 (&#39;关羽&#39;,&#39;二年二班&#39;,80) on duplicate key update score=100;
> Affected rows: 2
> 时间: 0.008s

登入後複製

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

1	`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，如

mysql支援unique索引嗎

解决办法：

1、尽量对存在多个唯一键的table使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论：

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。

insert ignore能忽略重复数据，只插入不重复的数据。

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

【相关推荐：mysql视频教程】

以上是mysql支援unique索引嗎的詳細內容。更多資訊請關注PHP中文網其他相關文章！