MySQL LIKE异常区分大小写影响用户认证,如何设置为不区分大小写
核心原理
MySQL的LIKE操作符不存在固定的“默认不区分大小写”规则,匹配时的大小写敏感程度完全由关联对象的字符集排序规则(Collation)决定:
- 排序规则后缀为
_ci(Case Insensitive,不区分大小写)时,LIKE匹配自动忽略大小写差异 - 排序规则后缀为
_cs(Case Sensitive,区分大小写)或_bin(二进制逐字节比较)时,LIKE会严格区分大小写
大家常说的“MySQL LIKE默认不区分大小写”,是基于官方安装包默认启用带_ci后缀排序规则的场景,一旦环境配置被修改、或单字段单独指定了非_ci类的排序规则,就会出现当前遇到的异常匹配问题。
逐层排查步骤
- 检查全局连接与库级的默认排序规则配置:执行
SHOW VARIABLES LIKE 'collation_%';,核对返回的collation_server、collation_database、collation_connection三个值的后缀,若后缀为_cs或_bin,说明全局默认配置已被修改 - 检查目标表目标字段的实际绑定规则:执行
SHOW FULL COLUMNS FROM 你的用户信息表名 WHERE Field = '用户名字段名';,核对对应行的Collation列值,很多场景下全局配置无异常,但单独给用户名字段设置了_cs或_bin的排序规则,会单独触发大小写敏感匹配 - 检查查询逻辑的隐式转换:如果查询语句中使用了
CAST(用户名字段 AS BINARY)、或连接层以二进制类型传入查询参数,会强制触发二进制比较,哪怕列本身绑定了_ci类的排序规则,也会变成区分大小写的匹配
可选修复方案
按改动范围从小到大选择对应方案即可:
- 快速临时修复:不改动现有配置,在查询语句中强制指定不区分大小写的排序规则,示例:
SELECT * FROM user WHERE username LIKE CONCAT('%', LOWER('传入的用户名参数'), '%') COLLATE utf8mb4_general_ci;
该方案适合紧急救急,不影响现有线上表结构 - 单字段定向修复:把用户表的用户名字段绑定为不区分大小写的排序规则,执行语句:
ALTER TABLE 你的用户信息表名 MODIFY username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
该方案仅影响当前字段的匹配规则,改动范围可控,适合认证场景的长期需求 - 全局配置修复:如果需要全库所有匹配都默认不区分大小写,可修改MySQL配置文件,在
[mysqld]配置段下添加collation-server = utf8mb4_general_ci,重启MySQL实例后生效。注意该配置仅影响新建的库、表、字段,已经存在的表结构不会自动同步修改
内容的提问来源于stack exchange,提问作者TheJoe
相关产品推荐
相关产品推荐

