MySQL 8.0 换成 utf8mb4_0900_ai_ci 之后，唯一索引不再拦住带尾空格的重复值

## 现象

一套业务从 RDS for MySQL 5.7 迁到 8.0，字符集沿用 `utf8mb4`，没显式指定排序规则，于是 8.0 的默认值 `utf8mb4_0900_ai_ci` 生效了。迁移后两周，注册接口偶发「同一手机号注册两次」的工单。

排查发现 `user_account` 表上 `account` 列有唯一索引，但库里同时存在：

```

'zhangsan'

'zhangsan ' -- 末尾一个空格

```

在 5.7 上这两条是冲突的（插入第二条会报 `Duplicate entry`），在 8.0 上都能插进去。

## 原因：PAD SPACE 变成了 NO PAD

- 5.7 的 `utf8mb4_general_ci`、`utf8mb4_unicode_ci` 都是 **PAD SPACE**：比较时把字符串末尾的空格补齐到等长再比，所以 `'abc'` 和 `'abc '` 相等。

- 8.0 的 `utf8mb4_0900_ai_ci` 是 **NO PAD**：末尾空格参与比较，`'abc' <> 'abc '`。

这是 SQL 标准里两种都允许的行为，MySQL 8.0 选了后者（更符合大多数编程语言的字符串语义）。所以**不是 bug，是行为变更**。

验证：

```sql

SELECT 'abc' = 'abc ' COLLATE utf8mb4_general_ci; -- 1

SELECT 'abc' = 'abc ' COLLATE utf8mb4_0900_ai_ci; -- 0

SELECT @@collation_server, @@collation_database;

```

## 第二个副作用：ORDER BY 开始 filesort

迁移后另一条慢查询变慢了，`EXPLAIN` 显示 `Using filesort`，而 5.7 上是 `Using index`。原因是排序规则变了，索引里存的排序权重也变了：

- `0900_ai_ci` 基于 UCA 9.0.0，重音、变音、以及 `ß`/`ss` 这类扩展折叠规则和 `general_ci` 不同；

- 更常见的是：`ORDER BY` 的表达式在 8.0 里被解析成不同的 collation（比如 JOIN 两边列的 collation 不一致，隐式转换后索引失效）。

检查方法：

```sql

SELECT table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE table_schema = DATABASE() AND collation_name IS NOT NULL

  AND collation_name <> 'utf8mb4_0900_ai_ci';

```

只要一张表里混了两种 collation，JOIN 或 UNION 时就会发生隐式转换，索引直接废掉。**迁移后第一件事应该是跑这条 SQL，把 collation 不统一找出来**，而不是等慢查询告警。

## 怎么修

**一、先找已经进来的脏数据**

```sql

SELECT account, COUNT(*) c FROM user_account

GROUP BY account COLLATE utf8mb4_general_ci

HAVING c > 1;

```

这里反过来用 `general_ci` 聚合，就能把「在 5.7 语义下重复」的记录捞出来。注意 `GROUP BY ... COLLATE` 用不上索引，大表要分批（按 id 范围）跑。

**二、把列显式钉死 collation**

```sql

ALTER TABLE user_account

  MODIFY account VARCHAR(64)

  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

```

这会把该列恢复成 PAD SPACE 语义，唯一索引重新拦住尾空格。代价：这是一次 **copy 表的 DDL**（改 collation 必然重建），大表要用 gh-ost/pt-osc，或者用 RDS 的无锁变更。而且改完要重建相关索引，`ORDER BY` 的索引顺序也会重建。

**三、应用层兜底（必须做）**

不管数据库怎么配，写入前 `trim()` 是必须的。这次的重复记录就是某个内部服务直接透传了上游带空格的参数。**数据库约束只能兜住「已知形态」，兜不住「语义上相同」**。

**四、新库/新表统一口径**

建表时显式写 `COLLATE=utf8mb4_0900_ai_ci`（或团队约定的那个），不要依赖 `@@collation_server` 的默认值——默认值会随版本变，而列一旦建出来就不会跟着变，这才是「同一套代码在两个环境行为不同」的经典来源。

## 小结

8.0 默认排序规则的三处变化值得记住：NO PAD（尾空格不再相等）、UCA 9.0.0（部分字符权重变化）、以及 accented 字符的处理差异。迁移清单里除了「SQL 语法兼容性」，应该单独加一条「collation 审计」：查混用、查唯一索引上的尾空格历史数据、查依赖排序的索引是否还在用。