MySQL REGEXP_LIKE 怎么排查匹配异常：字符集、大小写与转义边界

线上搜索接口突然把大小写不同的标识混在一起，开发先改了正则，结果中文和反斜杠场景又出现了另一套结果。MySQL 的 REGEXP_LIKE() 匹配异常，通常不是“正则写错”这么简单，而是列排序规则、表达式排序规则、客户端转义和正则引擎边界叠在了一起。

先用固定样例确认字符集与排序规则，再用显式 COLLATE 控制大小写，最后检查客户端是否把反斜杠吃掉；不要直接在生产查询上反复改模式。

先把“匹配异常”缩成一个可复现样例

排查时先不要带上业务表的几十个过滤条件。准备一组测试值覆盖大小写、中文和正则元字符场景，跑出来的结果可以在测试库里重复执行验证。

SET NAMES utf8mb4;

SELECT
  REGEXP_LIKE('Abc-12', '^abc-[0-9]+$') AS default_case,
  REGEXP_LIKE('Abc-12', '^abc-[0-9]+$', 'c') AS case_sensitive,
  REGEXP_LIKE('Abc-12', '^abc-[0-9]+$', 'i') AS case_insensitive,
  REGEXP_LIKE('C:\\tmp\\a.log', 'C:\\\\tmp\\\\.*\\.log', 'c') AS escaped_path;

如果同一实例、同一连接中跑出的结果就已经和预期不符，优先核查字符集和参数语义；如果这组样例返回结果完全正常，但业务查询仍有异常，再回头检查列定义、隐式转换逻辑和实际传入的 pattern。

第一层：确认列与连接到底使用什么字符集

完全相同的字符串，可能在客户端、连接、表和列四个层级使用不同字符集或排序规则。先把这些现场信息一次性记录完整：

SELECT
  @@character_set_client AS client_cs,
  @@character_set_connection AS connection_cs,
  @@character_set_results AS result_cs,
  @@collation_connection AS connection_collation;

SHOW CREATE TABLE user_alias;

SELECT column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'user_alias'
  AND column_name = 'alias';

重点不是强求所有层级名称完全相同，而是确认 pattern 和被匹配列能按可预期的字符集规则解释。MySQL 官方文档也提到，字符集决定字符的存储表示，排序规则决定字符的比较规则，不能只看数据库默认值就推断列的实际行为。

中文或重音字符结果不对怎么办

把问题缩小到单个列和单个常量值的范围，显式指定排序规则做对照测试：

SELECT alias,
       REGEXP_LIKE(alias COLLATE utf8mb4_0900_as_cs,
                   '^[一-龥A-Za-z]+$', 'c') AS strict_match
FROM user_alias
WHERE id IN (101, 102, 103);

如果显式指定排序规则后匹配结果发生改变，说明问题出在比较语义层面，不用继续反复调整正则符号。生产环境要确认目标排序规则确实存在，并用实际业务字符做回归验证，避免只在 ASCII 样本上得出结论就全量上线。

第二层：大小写不要交给默认值猜

正则匹配的大小写行为，可能同时受排序规则和 match-control 参数影响。针对登录名、设备编码这类字段，建议把匹配策略直接写在 SQL 中，后续维护人员一眼就能看出这段正则是大小写敏感还是不敏感。

-- 标识符必须大小写敏感
SELECT id, alias
FROM user_alias
WHERE REGEXP_LIKE(alias, '^svc-[a-z0-9]{6}$', 'c');

-- 搜索提示允许大小写不敏感
SELECT id, alias
FROM user_alias
WHERE REGEXP_LIKE(alias, 'mysql|mariadb', 'i');

如果业务规则同时要求某种排序规则，可以把 COLLATE 直接放在被匹配表达式上，再用 'c' 或 'i' 表达当前语句的意图。这样比依赖某台服务器的默认配置更容易测试和迁移。

第三层：把 SQL 字符串和正则转义分开看

路径字符串、反斜杠和字面量点号最容易出现“看起来完全一样、实际匹配结果不对”的问题。这里至少有两层解析逻辑：SQL 字符串先处理一次，正则引擎再处理一次。

-- 想匹配 C:\tmp\app.log 中的反斜杠与 .log
SELECT REGEXP_LIKE(
  'C:\\tmp\\app.log',
  '^C:\\\\tmp\\\\app\\.log$',
  'c'
) AS path_ok;

排查时把 pattern 单独查询出来，确认数据库实际收到的字符串内容：

SELECT '^C:\\\\tmp\\\\app\\.log$' AS pattern_seen,
       HEX('^C:\\\\tmp\\\\app\\.log$') AS pattern_bytes;

如果 SQL 客户端、ORM 或配置文件还会做一次额外转义，就不能直接复制命令行里的写法。最稳妥的验收方式是让应用日志记录参数长度和十六进制值，避免把用户输入原样拼进 pattern。

遇到误报和漏报时的证据顺序

1. 先保存一条原始值、实际 pattern 和连接字符集的记录，不要只保存最终布尔结果。
2. 再用同一个连接执行最小查询，分别加入 'c'、'i' 和显式 COLLATE。
3. 最后检查业务层是否重复转义、截断了 pattern，或把 NULL 转成了空字符串。

REGEXP_LIKE() 返回 NULL 时，也要把它和 FALSE 区分开：被匹配表达式或 pattern 为 NULL，通常代表输入缺失，不应该被当成“明确不匹配”写入统计。

上线前的最小验收清单

• 固定样例覆盖大小写、中文、反斜杠、点号和 NULL。
• 记录列字符集、列排序规则、连接字符集和实际 pattern。
• SQL 中显式写出 'c' 或 'i'，不要依赖环境默认值。
• 对每个修复样例保存期望结果，并在应用真实连接上复跑。
• 检查慢查询计划和返回行数，避免为排查问题把全表扫描直接带进生产。

常见问题

REGEXP_LIKE 和 REGEXP 有什么关系

两者都用于正则匹配；REGEXP_LIKE() 是函数形式，能把匹配控制参数放在参数列表中，排查大小写时通常更直观。

为什么本地能匹配，应用里却匹配不到

出现异常优先对比连接字符集、客户端转义和实际传入的 pattern。尤其是反斜杠，命令行、SQL 字符串和程序语言字符串可能各自再处理一次。

应该把正则写进索引吗

不要默认假设正则条件能像等值条件一样使用普通索引。先用执行计划和数据规模评估；高频检索通常更适合增加规范化列、前缀列或专门的搜索方案。

总结

REGEXP_LIKE 的异常排查顺序可以固定为：复现样例、读取字符集证据、显式控制大小写、核对两层转义，再用应用真实连接回归。把每一步的输入和结果留下来，下一次遇到同类问题就不用靠猜调试。