西瓜教程

Appearance

MySQL 唯一索引 ​

唯一索引与 NULL ​

null破坏了唯一性。我们用示例看下如何破坏的。

单列唯一 ​

创建表，c1列加上唯一索引：

sql
create table test_table (
    c1 CHAR(2),
    UNIQUE KEY uk_c1(c1)
) engine = InnoDB character set = utf8mb4;

虽然 c1 列被设置为保证唯一性，但对 NULL 是无效的，即可以插入多条 NULL 数据。我们看下效果：

步骤执行的SQL结果
1insert into test_table(c1) values('你好');成功
2insert into test_table(c1) values('你好');失败，报错 (1062, u"Duplicate entry '\u4f60\u597d' for key 'uk_c1'")
3insert into test_table(c1) values(NULL);成功
4insert into test_table(c1) values(NULL);成功

查看下表中数据：

mysql> select * from test_table;
+--------+
| c1     |
+--------+
| <null> |
| <null> |
| 你好   |
+--------+

多列唯一 ​

创建表，c1和c2列加上联合的唯一索引。

sql
create table test_table (
    c1 CHAR(2),
    c2 CHAR(2),
    UNIQUE KEY uk_c1_c2(c1, c2)
) engine = InnoDB character set = utf8mb4;

当有数据为null时，唯一性无法满足。

步骤执行的SQL结果
1insert into test_table(c1, c2) values('你好', '世界');成功
2insert into test_table(c1, c2) values('你好', '世界');执行失败，报错 (1062, u"Duplicate entry '\u4f60\u597d-\u4e16\u754c' for key 'uk_c1_c2'")
3insert into test_table(c1, c2) values('你好', null);成功
4insert into test_table(c1, c2) values('你好', null);成功
5insert into test_table(c1, c2) values(null, null);成功
6insert into test_table(c1, c2) values(null, null);成功

查看表中数据：

mysql> select * from test_table;
+--------+--------+
| c1     | c2     |
+--------+--------+
| <null> | <null> |
| <null> | <null> |
| 你好   | <null> |
| 你好   | <null> |
| 你好   | 世界   |
+--------+--------+

null破坏了唯一性，怎么办？ ​

当唯一列出现null时，唯一性得不到满足，怎么办？

  1. 不允许列出现 null 值，即not null
  2. 给列增加 default 值，注意default值不能是 null。

唯一索引的单列长度限制 ​

对于使用utf8mb4字符集的表，TEXT、VARCHAR、CHAR等类型的列，只能用前面的 191 个字符做索引，因为 191×4=764，192×4=768，191个字符正好没有超过 767 字节的限制。如果 char、varchar 等定义的长度超过了 191，而指定索引时未说明索引长度，则会自动使用前191个字符做索引。

示例：

创建表：

sql
create table test_table (
    c1 varchar(200) not null,
    INDEX idx_c1(c1)
) engine = InnoDB character set = utf8mb4;

查看创建表语句：

mysql> show create table test_table
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `c1` varchar(200) NOT NULL,           |
|            |   KEY `idx_c1` (`c1`(191))              |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

可以看到索引中 c1 自动取了前191个字符。

但是，若是唯一索引，单列的长度不能超过 191个字符，否则报错。

sql
create table test_table (
    c1 varchar(200) not null,
    UNIQUE INDEX idx_c1(c1)
) engine = InnoDB character set = utf8mb4;

上面的建表语句会报错如下：

(1071, u'Specified key was too long; max key length is 767 bytes')

为什么唯一索引不自动取前191个字符呢？因为若这样做，唯一性就无法保证了。

字符集排序规则对唯一索引的影响 ​

utf8mb4_general_ci 不区分大小写，所以... ​

建表：

sql
USE test;
CREATE TABLE `test_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `data` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_data` (data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

测试插入数据：

sql
mysql root@127.0.0.1:test> insert into test_table (data) values('hi');
Query OK, 1 row affected
Time: 0.003s
mysql root@127.0.0.1:test> insert into test_table (data) values('HI');
(1062, u"Duplicate entry 'HI' for key 'uk_data'")

插入 HI 的时候报错唯一索引冲突。为什么？因为 utf8mb4_general_ci 不区分大小写，hi 和 HI 也就被认为是相等的。

查询数据时，也不会区分大小写：

sql
mysql root@127.0.0.1:test> select * from test_table where data = 'HI';
+----+------+
| id | data |
+----+------+
| 1  | hi   |
+----+------+

utf8mb4_bin 区分大小写，所以... ​

建表：

sql
USE test;
CREATE TABLE `test_table_2` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `data` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_data` (data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;

测试插入数据：

sql
mysql root@127.0.0.1:test> insert into test_table_2 (data) values('hi');
Query OK, 1 row affected
Time: 0.003s
mysql root@127.0.0.1:test> insert into test_table_2 (data) values('HI');
Query OK, 1 row affected
Time: 0.004s

可以看到 hi 和 HI 都正常插入了。这是因为 utf8mb4_bin 区分大小写，所以 hi 和 HI 是不相等的。

查询数据时也会区分大小写：

mysql root@127.0.0.1:test> select * from test_table_2 where data = 'HI';
+----+------+
| id | data |
+----+------+
| 2  | HI   |
+----+------+

唯一索引冲突会消耗主键 ID ​

示例：

建表：

sql
create table test_table (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name varchar(32) not null,
    PRIMARY KEY (`id`),
    UNIQUE KEY uk_name(`name`)
) engine = InnoDB character set = utf8mb4;

插入数据：

sql
insert into test_table(name) values('aaa');
insert into test_table(name) values('aaa'); -- 会报错 Duplicate entry 'aaa' for key 'uk_name'
insert into test_table(name) values('bbb');

查询数据：

sql
mysql> select * from test_table;
+----+------+
| id | name |
+----+------+
| 1  | aaa  |
| 3  | bbb  |
+----+------+
2 rows in set

可以看到 id 2 因为唯一索引冲突被消耗掉了。