mysql唯一索引值很长_MySQL 唯一索引

MySQL 的唯一索引(unique index)很好理解,就是这个索引的值是唯一的.唯一索引的相关文档和概念不多,只要记住"唯一"这个核心概念就行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,并且可以有多个nullmysql> 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,并且可以有多个nullNormally, 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综合性能更好,功能也更全面.

本文原创链接: