共计 5385 个字符，预计需要花费 14 分钟才能阅读完成。
| 导读 | 这篇文章主要为大家介绍了 MySQL 的字段默认 null 对唯一索引的影响详解，有需要的朋友可以借鉴参考下，希望能够有所帮助，祝大家多多进步，早日升职加薪 |
在日常业务开发中，会经常遇到需要保证唯一性的数据业务，如用户注册业务。一般注册业务中允许用户以手机号或 email 注册账号，且需要保证唯一，不允许重复注册。当用户输入手机号或 email 登录时，程序会判定输入信息的存在与否性，存在则走登录，不存在则走注册。而保证唯一性就不仅仅需要在程序端做判断，还需要 MySQL 的唯一索引去做最后一道防线。那么唯一索引在一些业务中使用，如果唯一索引字段中默认值设置为了 null，会造成什么后果呢？
在阿里的《阿里巴巴 Java 开发手册》中关于 MySQL- 索引规范中写道：【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须创建唯一索引。
说明：
不要以为唯一索引影响了 insert 速度，这个速度的损耗可以忽略不计，但提高查找的速度是明显的；
另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
在 MySQL 中，唯一索引树是一个非聚簇索引，每次插入数据时，都会在唯一索引树上进行遍历查找该插入值是否唯一，这也就是为什么会影响 insert 的速度，因为多一步遍历判断唯一性。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.00 sec)# 建表语句
CREATE TABLE `user_1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`email` varchar(32) NOT NULL DEFAULT ''COMMENT' 邮箱 ',
`name` varchar(11) DEFAULT ''COMMENT' 名字 ',
PRIMARY KEY (`id`),
UNIQUE KEY `uk-email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 第一次插入：insert into user(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
#再次插入同样的数据：insert into user(email,name) values('aaa@qq.com','aaa');
1062 - Duplicate entry 'aaa@qq.com' for key 'uk-email', Time: 0.005000s此时对于唯一性来说是没问题的，可以保证业务的 email 的唯一性。假设随着业务的发展，此时需要增加手机号注册功能，那么表中就需要增加手机号字段，且需要保证手机号和邮箱的关联唯一性。
# 建表语句，注意此时 phone 字段的默认值为 null
CREATE TABLE `user_2` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`email` varchar(32) NOT NULL DEFAULT ''COMMENT' 邮箱 ',
`phone` char(11) DEFAULT NULL COMMENT '手机号',
`name` varchar(11) DEFAULT ''COMMENT' 名字 ',
PRIMARY KEY (`id`),
UNIQUE KEY `uk-email-phone` (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert 数据
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
1062 - Duplicate entry 'bbb@qq.com-13333333333' for key 'uk-email-phone', Time: 0.002000s此时会发现，不带 phone 值得前三条数据都能插入成功，带上邮箱和手机号的值却能正常判断唯一性
mysql> select * from user_2;
+----+------------+-------------+------+
| id | email | phone | name |
+----+------------+-------------+------+
| 1 | aaa@qq.com | NULL | aaa |
| 2 | aaa@qq.com | NULL | aaa |
| 3 | aaa@qq.com | NULL | aaa |
| 4 | bbb@qq.com | 13333333333 | bbb |
+----+------------+-------------+------+
4 rows in set (0.00 sec)这时就需要牵扯到 MySQL 的唯一索引机制了：在 MySQL 官方文档中 MySQL 索引文档，描述到：
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.解释一下：唯一索引创建一个约束，使得索引中的所有值都必须是不同的。如果尝试添加一个键值与现有行匹配的新行，则会发生错误。如果在唯一索引中为列指定前缀值，则列值在前缀长度内必须是唯一的。唯一索引允许包含空值的列有多个空值。
先看下 explain 执行计划：
mysql> explain select * from user_2 where email='aaa@qq.com' and phone is NULL;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | user_2 | NULL | ref | uk-email-phone | uk-email-phone | 132 | const,const | 3 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql>
mysql> explain select * from user_2 where email='bbb@qq.com' and phone='13333333333';
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | user_2 | NULL | const | uk-email-phone | uk-email-phone | 132 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)有没有发现一个有趣的现象，虽然两个 sql 语句都使用到了 uk-email-phone 唯一索引，但是 第一条 sql 的 type 为 ref 第二条 sql 的 type 为 const 我们知道，explain 执行计划中，const 一般是主键查询或者唯一索引查询是才会出现，而 ref 一般是使用普通索引时出现。所以，可以得出结论，MySQL 在底层对唯一索引的 null 值做了特殊处理。
我们通过查看源码文件的 1863 行，有这么个注释：
Scans a unique non-clustered index at a given index entry to determine whether a uniqueness violation has occurred for the key value of the entry. Set shared locks on possible duplicate records意思是扫描给定索引项处的唯一非聚集索引以确定条目的键值是否发生唯一性冲突。对可能重复的记录设置共享锁。
也就是说 row_ins_scan_sec_index_for_duplicate() 该方法就是处理唯一索引的，继续往下看，在 1892 行，有一串注释：
If the secondary index is unique, but one of the fields in the n_unique first fields is NULL, a unique key violation cannot occur, since we define NULL != NULL in this case如果二级索引是唯一的，但是唯一索引的字段存在 NULL，则不会发生唯一性冲突，在此代码中定义了 NULL != NULL
继续往下走，在 1996 行，走到了 row_ins_dupl_error_with_rec() 函数，该函数在 1825 行。在该函数中有以下代码：
/* In a unique secondary index we allow equal key values if they
contain SQL NULLs
在唯一的二级索引中，如果包含 sql NULL 值
*/
if (!index->is_clustered() && !index->nulls_equal) {
for (i = 0; i
也就是说，在唯一索引中字段为 NULL 的情况下，返回 false，没有抛出 DB_DUPLICATE_KEY 异常.
唯一索引重复插入之终极解决方案：给字段设置空字符串初始值，NOT NULL DEFAULT '' 即可，不要用 null 值作为初始值。
