点击蓝色“Excel螺丝钉”关注我哦！
加个“星标”，快速获取新消息！
在程序设计中，我们往往需要确保数据的唯一性，比如在常见的注册模块，我们需要确保一个手机号只能注册为一个账号。这种情况下，我们的程序往往是第一道关卡，用户来注册之前，首先判断这个手机号是否已经注册，如果已经注册则返回错误信息,或直接去登录。但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中，因此这里就需要在更深的层次去确保手机号在系统的唯一性了。不同存储方案，解决方式不一样。对于常用的MySQL数据库，我们可以使用唯一索引的方式来作为我们的最后一道防线。
但是最近在使用数据库的唯一索引时，发现一个比较奇怪的现象。MySQL数据库，使用InnoDB存储引擎，创建了唯一索引时，在insert操作时，如果唯一索引上的字段有为NULL的情况，则可以无限插入。这有点匪夷所思，但是现实就是这么一个情况。现在就来具体分析这样的一个案例，来看看底层对于唯一索引是怎么设计的，来规避在数据库设计上犯错和踩坑。
案例
假设现在有一个用于保存用户信息的数据表user，是使用email注册的，当前使用email作为唯一索引，同时这一基本规则也被其他依赖系统作为设计数据模型的设计基础。假设现在设计这样一个user表：
CREATE TABLE `user` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',`email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',`name` varchar(11) DEFAULT '' COMMENT 'name',`age` int(11) DEFAULT NULL COMMENT 'age',PRIMARY KEY (`id`),UNIQUE KEY `uk-email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1@user.com来注册，执行insert语句,执行成功
INSERT INTO user (email,name,age) VALUES ('1@user.com','h1',18);
1@user.com再来注册，则再次执行，则报错。成功规避了用户多次创建导致系统产生脏数据问题。
Duplicate entry '1@user.com' for key 'uk-email'
从这里看，user表的设计是符合业务要求的，并没有出现同一个email出现多行的情况。随着业务发展，单单email注册的模式并不适合移动互联网时代，所以现在的要求在原有基础上增加了手机号的字段，并要求手机号也是唯一的。于是添加phone字段，并将原有唯一索引删除，为email和phone设置新的唯一索引。
ALTER TABLE `user` ADD COLUMN `phone` varchar(11) default NULL AFTER `age`;DROP INDEX `uk-email` ON `user`;ALTER TABLE `user` ADD UNIQUE KEY `uk-email-phone` (`email`,`phone`);
假设用户1再来用同样的email注册,可以注册成功：
INSERT INTO user (email,name,age,phone) VALUES (‘1@user.com’,‘h1’,18,NULL);
查询数据库数据，得到以下结果：
有两个email为1@user.com的记录，他们的phone都是NULL，这怎么可能存在？！难道是MySQL出问题了？！不可能，我们再试另外一个数据
INSERT INTO user (email,name,age,phone) VALUES ('2@user.com','h2',18,'18812345678');
连续执行两次，第一次执行成功，第二次报错：
Duplicate entry ‘2@user.com-18812345678’ for key ‘uk-email-phone’
查询user结果集，得到
从结果看这样MySQL的唯一索引也算是正常的啊，那这到底是怎么一回事呢？
原因探寻
业务中希望建立的唯一索引是email + phone的组合，但是由于phone一开始是没有数据的，所以新建字段时默认允许为NULL来兼容老数据。如果程序没有控制好，数据操作直接打到数据库，就产生了两条email为“1@user.com”且phone为NULL的数据，那么就会发生这种数据错乱的情况。
我从 MySQL 5.7官方文档 中找到了这个：
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.
官方的文档中明确说明在唯一索引中是允许存在多行值为NULL的数据存在的。
当然我们会认为这是MySQL的一个bug，其实早有人这么认为了，并给MySQL提出了这个问题https://bugs.mysql.com/bug.php?id=8173。但是MySQL的开发者并不认为这是一个bug，而是本身的一种设计。额，这么说，好像也说得过去。那这里就有一个问题了，我们知道索引是使用B+树来维护的，但是对于这种非唯一索引是怎么维护的？
带着这个问题，我觉得有两种可能：
（1）唯一索引时另外一种数据类型，正好把有值为NULL的字段过滤掉了，无需特殊处理。
（2）还是用的B+树索引，但是对于NULL的索引特殊处理了。
于是我对email=2@user.com且phone= 18812345678的数据执行了Explain执行计划
explain select * from user where `email` = '2@user.com' and `phone` = '18812345678';
对比上面两次不同数据的explain执行结果，可以看到其实都用了uk-email-phone的唯一索引，不同的是第一个type是const(通过一次索引就可以找到，用于primary key或unique index)，第二个type是ref(非唯一性索引扫描)，且rows为2。所以猜测这里极有可能是对NULL进行的特殊处理，唯一索引树还是用的和非NULL一样的唯一索引树。
结论
所以其实MySQL在唯一索引中允许存在值为NULL的字段。NULL值在MySQL可以代表是任意值，并且在有字段值为NULL时，不会参与校验这个组合的唯一索引，所以可能插入业务上不允许重复的数据，导致脏数据。
因此在创建属于唯一索引的列时，最好指定字段值不能为空，在已有值为NULL的情况下，创建的字段不允许为空，且默认值为空字符。如果已经创建了默认值为NULL的字段，则先将其update为空字符，然后再修改为NOT NULL DEFAULT ‘’。如上述情况建表语句改为
CREATE TABLE `user` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',`email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',`name` varchar(11) DEFAULT '' COMMENT 'name',`age` int(11) DEFAULT NULL COMMENT 'age',`phone` varchar(11) NOT NULL DEFAULT '',PRIMARY KEY (`id`),UNIQUE KEY `uk-email-phone` (`email`,`phone`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
划重点！！！
从今日起连续21天阅读公众号文章且关注本公众号，并将每日文章部分内容截图发到打卡小程序即为打卡成功。点击下面小程序即可进行打卡。
