数据库LIKE查询屡试不爽？揭秘大多数人都忽视的秘密操作符！

往期推荐：

• SpringCloud2023最新版本该如何进行组件选型？
• 如何简洁高效的搭建一个SpringCloud2023的maven工程
• MySQL中的嵌套join如果使用With替代会怎样
• 当谈论DDD到底在谈论什么
• 一次通过dump文件分析OutOfMemoryError异常代码定位过程
• MySQL千万级数据从190秒优化到1秒全过程

1. 问题背景

在某次数据库查询中，select * from sys_user where user_name LIKE concat( '%', '赵', '%' ) 能正确查询到包含“赵”的数据，而类似的条件 concat( '%', '赵小', '%' ) 却无法查询到“赵小强”。这一问题暴露了数据的隐藏异常。

通过 Hex() 函数进一步分析，发现“赵小强”的十六进制值为 E8B5B5E5B08FE5BCBA。然而，用 select hex(user_name) 查询时，结果包含了不可见的空白字符 E2808B。这些字符未被用户直观察觉，但干扰了 SQL 查询的匹配逻辑。

2. 不可见字符的影响与判定

不可见字符如 E2808B 是 Unicode 的零宽空白字符（Zero-Width Space），其存在通常由数据导入不规范或应用程序处理不当引起。这些字符对字符串显示无影响，但在计算机匹配时会导致异常行为，例如 SQL 查询失败。

通过进一步验证，问题可归因于零宽字符的存在。更新语句 UPDATE sys_user SET user_name = REPLACE(user_name, UNHEX('E2808B'), ''); 被提出用于移除这些字符。

3. 数据修复方法

针对上述问题，以下步骤被应用解决：

3.1 确认问题字符

使用 select hex(user_name) 查看目标字段的十六进制值，判定是否包含异常字符。

3.2 编写修复 SQL

利用 REPLACE() 函数结合 UNHEX() 替换掉指定不可见字符：

UPDATE sys_user SET user_name = REPLACE(user_name, UNHEX('E2808B'), '');

此语句逐行处理目标字段，将不可见字符替换为空字符串，从而修复数据。

4. 避免类似问题的建议

4.1 数据输入规范化

在数据导入或处理前，使用正则表达式过滤掉不可见字符，确保输入数据无异常。

4.2 数据校验机制

对关键字段定期运行十六进制检查，确保字段值符合预期格式，避免隐性问题。

4.3 字符串处理优化

在字符串操作函数中，明确考虑可能的隐藏字符，例如零宽空白符或其他控制字符。

5. 零宽空白字符

除了常见的零宽空白字符 E2808B（Zero-Width Space, U+200B），以下是其他常见的零宽字符及其特性：

零宽空格类字符：

• Zero Width Non-Joiner (U+200C) 用于防止两个字符连写，其十六进制表示为 E2808C。
• Zero Width Joiner (U+200D) 用于指示字符应连写，十六进制表示为 E2808D。
• Word Joiner (U+2060) 类似于零宽空格，但主要用于禁用断行，十六进制表示为 E281A0。

格式控制字符：

• Left-to-Right Mark (U+200E) 用于标记文本方向为从左到右，十六进制表示为 E2808E。
• Right-to-Left Mark (U+200F) 用于标记文本方向为从右到左，十六进制表示为 E2808F。
• Left-to-Right Embedding (U+202A) 指定嵌套的从左到右文本方向，十六进制表示为 E280AA。
• Right-to-Left Embedding (U+202B) 指定嵌套的从右到左文本方向，十六进制表示为 E280AB。
• Pop Directional Formatting (U+202C) 结束嵌套方向设置，十六进制表示为 E280AC。

其他不可见字符：

• Zero Width No-Break Space (U+FEFF) 原为字节顺序标记 (BOM)，现作为零宽字符使用，十六进制表示为 EFBBBF。
• Soft Hyphen (U+00AD) 用于指示潜在的断字位置，但通常不显示，十六进制表示为 C2AD。

COLLATE排序规则可能的影响

排序规则 (COLLATE) 定义了字符串比较和排序的规则，包括：

• 大小写敏感性：区分大小写的规则（如 _bin 排序规则）和不区分大小写的规则（如 _ci）。
• 字符比较规则：某些排序规则会将字符视为等价，比如带重音的字符（é 和 e）在一些规则中可能被视为相同。

常见排序规则对 LIKE 的影响：

以下是几种典型排序规则及其对 LIKE 的影响：

• 大小写不敏感（默认，如 utf8mb4_general_ci 或 utf8mb4_unicode_ci）： LIKE 'abc%' 将匹配 abc, Abc, ABC 等。
• 大小写敏感（如 utf8mb4_bin）： LIKE 'abc%' 仅匹配大小写完全一致的 abc。
• 如果排序规则忽略重音（如 utf8mb4_general_ci），则 LIKE 'cafe%' 可能匹配 café 和 cafe。在 utf8mb4_bin 中，重音符号会被严格区分，因此 café 和 cafe 是不同的。

6. 总结

不可见字符如零宽空白符可能引发查询和匹配异常，问题解决需从排查、修复和预防三方面入手。

通过合理的技术手段，数据库的完整性和查询准确性得以保障，同时为避免类似问题提供了经验参考。

关于作者

来自全栈程序员nine的探索与实践，持续迭代中。