字符串比较 collation怎么配置或排查

MySQL 字符串比较“不对”的时候，先别急着把所有字段改成同一个 collation。真正要查的是比较双方的字符集和排序规则，以及 MySQL 最后按哪一方的优先级做了转换。实战中最稳的顺序是：先看列和会话，再用表达式函数确认来源，临时问题用查询级 COLLATE，长期模型问题才改列或表。

一、先确认真正参与比较的 collation

我排查这类问题时先做一次“范围盘点”，因为数据库默认值、表默认值和列的显式定义可能完全不同。下面的查询只读元数据，不会修改现有结构：

-- 查看当前连接如何解释客户端发送的字符串
SHOW SESSION VARIABLES LIKE 'character_set%';
SHOW SESSION VARIABLES LIKE 'collation%';

-- 查看目标表和列的实际字符集与排序规则
SHOW CREATE TABLE customer;
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'customer'
  AND COLUMN_NAME = 'display_name';

重点看列定义，而不是只看 collation_connection。MySQL 官方文档明确说明：连接排序规则对字面量比较重要，但拿字面量和列值比较时，列自身的排序规则优先级更高。若要挑选可用值，可先执行：

-- 只列出 utf8mb4 可用的排序规则，避免拼错名称
SHOW COLLATION WHERE Charset = 'utf8mb4';

二、用表达式函数判断是哪一方在主导

只看建表语句仍可能漏掉字面量或函数结果。可以把比较双方单独投影出来，观察字符集、排序规则和可强制程度：

-- 用函数显示列值与字面量的实际表达式属性
SELECT
  COLLATION(display_name) AS column_collation,
  CHARACTER_SET(display_name) AS column_charset,
  COLLATION('张三') AS literal_collation,
  CHARACTER_SET('张三') AS literal_charset,
  COERCIBILITY(display_name) AS column_coercibility,
  COERCIBILITY('张三') AS literal_coercibility
FROM customer
LIMIT 1;

常见判断是：显式 COLLATE 的可强制程度最低，列或存储过程变量其次，普通字面量再次。因而 display_name = '张三' 通常沿用列的规则；如果写成 '张三' COLLATE utf8mb4_0900_as_cs，就把比较意图明确写在字面量一侧。两边都显式指定不兼容的规则时，MySQL 可能直接报“非法混合排序规则”，这不是继续调连接变量能解决的。

三、先用查询级 COLLATE 做最小修复

如果只是一个报表、迁移脚本或特定搜索需要不同语义，不要为了它改整张表。COLLATE 可以放在比较、模糊匹配、排序和分组表达式上：

-- 只让本次查询按大小写敏感规则比较，不改变列定义
SELECT id, display_name
FROM customer
WHERE display_name COLLATE utf8mb4_0900_as_cs = _utf8mb4'ZhangSan'
  COLLATE utf8mb4_0900_as_cs;

-- 排序和分组也要把业务语义写在对应表达式上
SELECT display_name, COUNT(*) AS total_count
FROM customer
GROUP BY display_name COLLATE utf8mb4_0900_as_cs
ORDER BY display_name COLLATE utf8mb4_0900_as_cs;

这里的关键不是盲目选择带 _cs 或 _ci 的名字，而是先用代表性数据确认大小写、重音和语言排序是否符合业务。字符集不匹配时，先选同一字符集下的兼容 collation；不要只把名称替换成看起来相近的后缀。

四、模型问题再改列或表，并做反向验证

当同一列被大量业务查询使用，且大家都需要同一种比较语义，才考虑把定义统一到列级或表级。修改前先确认索引、唯一约束和重复数据边界，再安排变更窗口：

-- 示例：把单列统一到明确的字符集与排序规则
ALTER TABLE customer
  MODIFY display_name VARCHAR(120)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci
  NOT NULL;

-- 修改后重新读取定义，确认没有被表默认值覆盖
SHOW FULL COLUMNS FROM customer LIKE 'display_name';

最后用四组数据反向验证：仅大小写不同、仅重音不同、末尾多空格、跨字符集输入。再分别测试 =、LIKE、ORDER BY 和唯一索引约束。若只是当前连接的字面量解释错了，可以在连接初始化时显式设置字符集与排序规则；不要把会话变量当成列定义的替代品。

相关问题

为什么改了 collation_connection，列比较还是没变化？

因为列自身的 collation 通常拥有更高优先级。先用 COLLATION(列名) 检查列定义，再决定是否在表达式上显式使用 COLLATE。

什么时候不应该直接 ALTER TABLE？

只影响一条查询或一个导入任务时，优先用查询级修复；全表变更可能影响排序结果、唯一索引冲突和索引计划，应先用代表性数据回归。