MySQL 的惟一索引(unique index)很好了解,就是这个索引的值是惟一的.惟一索引的相干文档和概念不多,只有记住"惟一"这个外围概念就行

这是MySQL Unique Indexes的官网原文

Unique Indexes

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

惟一索引创立了一个束缚,它规定索引中的全副的值必须是不同的惟一的.如果尝试增加一个与现有行索引值相等新行,就会产生谬误.局部索引能够设置为惟一索引,然而被设置为惟一的局部索引值也必须是惟一的.

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

这个其实是以一个面试点,被设置为惟一索引的列的值是容许有null值的,并且这个列中容许有多个反复的null值.

代码验证:

mysql> create table unique_index_test(    ->     id int primary key,    ->     name varchar(16),    ->     unique index uk_name (name)    -> );Query OK, 0 rows affected (0.03 sec)mysql> desc unique_index_test;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | NO   | PRI | NULL    |       || name  | varchar(16) | YES  | UNI | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> show index from unique_index_test;+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| unique_index_test |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || unique_index_test |          0 | uk_name  |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+2 rows in set (0.01 sec)

咱们验证下惟一索引是否能够允许值为null,并且能够有多个null

mysql> insert into unique_index_test values (1,"thinktik1");Query OK, 1 row affected (0.00 sec)mysql> insert into unique_index_test values (2,"thinktik2");Query OK, 1 row affected (0.01 sec)mysql> insert into unique_index_test values (3,null);Query OK, 1 row affected (0.00 sec)mysql> insert into unique_index_test values (4,null);Query OK, 1 row affected (0.01 sec)mysql> select * from unique_index_test;+----+-----------+| id | name      |+----+-----------+|  3 | NULL      ||  4 | NULL      ||  1 | thinktik1 ||  2 | thinktik2 |+----+-----------+4 rows in set (0.00 sec)mysql> insert into unique_index_test values (5,"thinktik1");ERROR 1062 (23000): Duplicate entry 'thinktik1' for key 'unique_index_test.uk_name'

论断是: 惟一索引能够允许值为null,并且能够有多个null

同时惟一索引在应用的时候有一些附加束缚: PRIMARY KEY and UNIQUE Index Constraints

Normally, errors occur for data-change statements (such as INSERT or UPDATE) that would violate primary-key, unique-key, or foreign-key constraints. If you are using a transactional storage engine such as InnoDB, MySQL automatically rolls back the statement. If you are using a nontransactional storage engine, MySQL stops processing the statement at the row for which the error occurred and leaves any remaining rows unprocessed.

通常，违反主键、惟一键或外键束缚的数据更改语句(如INSERT或UPDATE)会产生谬误。如果你应用的是事务性存储引擎，比方InnoDB, MySQL会主动回滚语句。如果您应用的是非事务性存储引擎，MySQL将进行解决产生谬误的行上的语句，并保留任何残余的行不解决.

我集体认为还是InnoDB这类反对事务的MySQL引擎好,MySQL 8曾经默认应用InnoDB作为引擎了,比MyISAM综合性能更好,性能也更全面.