MySQL字段默认null唯一索引
我是大明哥 人气:0正文
在日常业务开发中，会经常遇到需要保证唯一性的数据业务，如用户注册业务。一般注册业务中允许用户以手机号或email注册账号，且需要保证唯一，不允许重复注册。当用户输入手机号或email登录时，程序会判定输入信息的存在与否性，存在则走登录，不存在则走注册。而保证唯一性就不仅仅需要在程序端做判断，还需要MySQL的唯一索引去做最后一道防线。那么唯一索引在一些业务中使用，如果唯一索引字段中默认值设置为了null，会造成什么后果呢？
在阿里的《阿里巴巴Java开发手册》中关于MySQL-索引规范中写道： 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须创建唯一索引。
说明：
不要以为唯一索引影响了insert速度，这个速度的损耗可以忽略不计，但提高查找的速度是明显的；
另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
看一下为何唯一索引为影响insert速度
在MySQL中，唯一索引树是一个非聚簇索引，每次插入数据时，都会在唯一索引树上进行遍历查找该插入值是否唯一，这也就是为什么会影响insert的速度，因为多一步遍历判断唯一性。
MySQL版本：在docker中启动一个mysql
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数据
#第一次插入：
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 < n_unique; i++) {
if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
return (FALSE);
}
}
}
也就是说，在唯一索引中字段为NULL的情况下，返回false，没有抛出DB_DUPLICATE_KEY异常.
经验
唯一索引重复插入之终极解决方案：给字段设置空字符串初始值，NOT NULL DEFAULT ''即可，不要用null值作为初始值。
加载全部内容