别踩坑!使用MySQL唯一索引请注意

背景

在程序设计中,我们往往需要确保数据的唯一性,比如在常见的注册模块,我们需要确保一个手机号只能注册为一个账号。这种情况下,我们的程序往往是第一道关卡,用户来注册之前,首先判断这个手机号是否已经注册,如果已经注册则返回错误信息,或直接去登录。但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号在系统的唯一性了。不同存储方案,解决方式不一样。对于常用的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设置新的唯一索引。

  • 30
    点赞
  • 68
    收藏
    觉得还不错? 一键收藏
  • 8
    评论
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值