记一次MySQL空字符串引发的唯一约束冲突排查
原创
记一次MySQL空字符串引发的唯一约束冲突排查
原创
技术环境
- MySQL版本：5.7.28
- 存储引擎：InnoDB
- 编程语言：Java + Spring Boot 2.3.4
- 连接池：HikariCP
- ORM框架：MyBatis
Bug现象
在用户注册功能中，我们要求用户的手机号必须唯一。为此我们在数据库中对mobile字段建立了唯一索引。但在生产环境中，偶尔会出现Duplicate entry '' for key 'mobile_unique'的错误，即出现了空字符串的唯一键冲突。
令人困惑的是，我们系统中理应只有一个用户的手机号可以为空（未绑定手机的用户），但实际上却出现了多个空字符串手机号的用户记录。
排查步骤
第一步：确认表结构
首先检查用户表结构：
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`mobile` varchar(20) DEFAULT '',
`email` varchar(100) DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `mobile_unique` (`mobile`),
UNIQUE KEY `email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;第二步：查询重复数据
查询数据库中到底有多少空字符串的手机号：
SELECT id, username, mobile
FROM t_user
WHERE mobile = ''
ORDER BY id;结果确实返回了多条记录，它们的mobile字段都是空字符串。
第三步：检查代码逻辑
检查用户注册和更新的代码，重点关注mobile字段的处理：
// UserService.java
public void createUser(User user) {
// 业务逻辑校验
if (StringUtils.isEmpty(user.getMobile())) {
user.setMobile(""); // 确保空值转为空字符串
}
userMapper.insert(user);
}
// UserMapper.xml
<insert id="insert" parameterType="User">
INSERT INTO t_user (username, mobile, email)
VALUES (#{username}, #{mobile}, #{email})
</insert>第四步：复现问题
通过测试代码尝试复现该问题：
@Test
public void testDuplicateEmptyMobile() {
User user1 = new User();
user1.setUsername("user1");
user1.setMobile("");
userService.createUser(user1);
User user2 = new User();
user2.setUsername("user2");
user2.setMobile("");
userService.createUser(user2); // 这里应该抛出异常
}但测试代码并没有抛出预期的异常，而是成功插入了两条记录。
第五步：深入研究MySQL唯一索引机制
查阅MySQL文档后发现，在MySQL中唯一索引对NULL值的处理是特殊的：多个NULL值不被认为是重复的，但空字符串被认为是普通值，会受到唯一约束的限制。
然而我们的表结构中mobile字段的默认值是空字符串，不是NULL。问题出现在当我们显式插入NULL时：
INSERT INTO t_user (username, mobile) VALUES ('user1', NULL); -- 成功
INSERT INTO t_user (username, mobile) VALUES ('user2', NULL); -- 成功但当我们查询时：
SELECT * FROM t_user WHERE mobile = ''; -- 不会返回上面两条记录这是因为NULL与空字符串在MySQL中是不同的概念。
根本原因
问题出现在我们的代码逻辑和数据库设计的不一致：
- 数据库层面：
mobile字段允许NULL，默认值为空字符串 - 代码层面：将NULL转换为空字符串，但无法处理已经存在的NULL值
- 业务层面：我们希望手机号要么为有效的号码，要么为空（但应该唯一）
当我们直接执行SQL插入NULL值时，代码中的空字符串转换逻辑无法生效，导致插入了多个NULL值。这些NULL值在唯一索引检查时被允许，但当我们通过代码更新这些记录时，NULL被转换为空字符串，从而触发了唯一约束冲突。
解决方案
方案一：修改数据库设计（采用）
-- 首先清理现有数据
UPDATE t_user SET mobile = NULL WHERE mobile = '';
-- 修改表结构
ALTER TABLE t_user
MODIFY COLUMN mobile varchar(20) NULL UNIQUE;方案二：统一代码层面的处理
// 在所有涉及mobile字段的地方加强校验
public void validateAndNormalizeUser(User user) {
if (user.getMobile() != null && user.getMobile().trim().isEmpty()) {
user.setMobile(null);
}
}
// 在插入和更新前调用
public void createUser(User user) {
validateAndNormalizeUser(user);
userMapper.insert(user);
}方案三：添加数据库触发器
DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON t_user
FOR EACH ROW
BEGIN
IF NEW.mobile = '' THEN
SET NEW.mobile = NULL;
END IF;
END;
//
DELIMITER ;我们最终选择了方案一，因为这是最根本的解决方案。
避坑总结
- 明确NULL和空字符串的区别：在数据库中，NULL表示未知/缺失值，空字符串表示已知的空值
- 唯一索引处理方式不同：MySQL中唯一索引允许多个NULL值，但不允许多个空字符串
- 保持数据一致性：代码逻辑和数据库设计应该保持一致，要么都使用NULL，要么都使用空字符串
- 谨慎设计默认值：默认值为空字符串的字段如果需要唯一约束，要考虑清楚业务逻辑
- 数据迁移时注意兼容性：修改现有表结构时，要处理好已有数据的转换
后续思考
这个bug教会我们在设计数据库时需要考虑的细节：
- 明确每个字段的"空值"表示什么业务含义
- 统一团队内的空值处理规范
- 重要的约束尽量在数据库层面实现，而不只是依赖代码逻辑
- 定期审查数据库中的数据一致性
希望通过这次经验分享，能帮助大家避免类似的坑。在MySQL中处理空字符串和NULL值时，一定要谨慎考虑业务场景和数据库特性。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
