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 综合性能更好, 性能也更全面.