MySQL中字符串不区分大小写导致的重复记录错误及解决方案

MySQL中字符串不区分大小写导致的重复记录错误及解决方案

引用

CSDN

1.

https://blog.csdn.net/Hi_alan/article/details/124746034

问题背景

在日常工作中，需要每天对公司的数据进行统计。某天，工作人员在对账时发现某一天的记录缺失。尝试重新统计并入库时，却遇到了一个奇怪的问题：数据无法插入，MySQL报出"Duplicate entry for key 'XXX'"的错误。

解决过程

1. 首先检查表中的唯一索引，确认没有重复记录
2. 为了排除其他数据的影响，将表中的数据清空（在本地环境进行），但问题依旧存在
3. 使用断点调试，输出相关数据，发现控制台只打印了一次，且调试器只停留一次

经过上述排查，问题依然无法解决。虽然可以在程序层面通过判断记录是否存在来避免插入，但这显然不是根本解决方案。接下来，只能直接操作数据库进行进一步查询。

查询唯一索引时发现了一个有趣的现象：查询全部小写字符串"titapotp"时，结果如下图所示：

这表明，问题的根本原因在于MySQL在处理字符串时默认不区分大小写。

解决方式

要解决这个问题，可以采取以下几种方法：

1. 在查询时强制区分大小写

   select * from usertable where binary 字段名='xxxx';
   

2. 在建表时设置字段为区分大小写

   create table `usertable`( 
     字段名 varchar(32) binary, 
     PRIMARY KEY (`id`) 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

   或者

   CREATE TABLE `usertable` ( 
     字段名 varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', 
     PRIMARY KEY (`id`) 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

由于表已经创建，不需要删除重建，可以直接修改字段属性。使用Navicat等数据库管理工具可以很方便地进行设置：

可以通过勾选"Binary"选项或设置排序规则为二进制来实现区分大小写。完成设置后，再次查询即可解决问题。