MySql索引失效-联合唯一索引含null值
一.背景今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。二.问题复现现在我们来复现一下这个场景。2.1 首先创建一个 user 表,指定 user_name + address 为联合唯一索引CREATE TABLE `t_user` (`id` bigint(20)
·
一.背景
今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。
二.问题复现
现在我们来复现一下这个场景。
2.1 首先创建一个 user 表,指定 user_name + address 为联合唯一索引
CREATE TABLE `t_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_name` varchar(256) NOT NULL COMMENT '用户名',
`sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别: 0:男,1:女',
`address` varchar(256) NULL COMMENT '住址',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name_address` (`user_name`,`address`) COMMENT '唯一键:姓名+地址'
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
2.2 插入一行数据
INSERT INTO `t_user` (`user_name`, `sex`, `address`) values ('dkangel', 0, 'SZ');
2.3 插入同样一条数据,发现mysql报错:
ERROR 1062 (23000): Duplicate entry 'dkangel-SZ' for key 't_user.uk_name_address'
4. 再插入address不同的数据
INSERT INTO `t_user` (`user_name`, `sex`, `address`) values ('dkangel', 0, 'HZ');
2.5 到这里一切都还是正常的,联合唯一索引都是生效的
2.6 让我们再插入一条 address 为空的数据
INSERT INTO `t_user` (`user_name`, `sex`) values ('dkangel_yc', 0);
7. 再插入一样的数据,发现插入成功了,联合唯一索引在值有为 null 的情况下失效
问题解决
- 不要将可能为 null 的值设置为联合索引的一部分
- 用特殊值来代替可能为空的值,如:数字类型用0,字符串类型用空字符等
在本例中,只需要将 address 的默认值设置为空字符""即可
更多推荐
已为社区贡献1条内容
所有评论(0)