从 ORDER BY 乱序到 ICU 对齐：MySQL 迁 PG 排序避坑实录

问题背景：同样的 ORDER BY，为什么 PG 排序不一样

很多团队从 MySQL 迁移 PostgreSQL 时，表结构、数据量、索引都核对过，业务却在上线后出现一类很隐蔽的问题：按名称排序的列表顺序变化，带连字符、空格、大小写的字符串排名不同，甚至唯一索引报重复键。表面看是 SQL 兼容性问题，根因往往是两套数据库默认排序规则不同。

MySQL 常使用 utf8mb4_general_ci、utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci，默认大小写不敏感，部分排序还会忽略标点差异；PostgreSQL 在 Linux 上默认常依赖系统 libc locale，例如 en_US.UTF-8、zh_CN.UTF-8。从 PostgreSQL 15 开始，ICU collation 可作为更稳定的选择，但“可使用 ICU”并不等于“排序结果自动与 MySQL 一致”。

典型故障现象包括：

• 分页查询第一页和第二页出现重复或漏数据；
• MySQL 中 A1、a1 被唯一约束视为相同，PG 默认却允许同时存在；
• iPhone-AB、iPhone AB、iPhoneAB 的相对顺序与旧库不同；
• 不同服务器初始化的 PG 实例，相同 SQL 返回顺序不同。

这些问题不能只靠修改几条 SQL 解决，需要先识别 collation 版本、规则和索引依赖，再统一迁移策略。

MySQL、libc 与 ICU collation 对比

排序规则决定字符串比较时是否区分大小写、重音、标点，以及多语言字符按什么权重排序。MySQL 的 ci 后缀表示 case insensitive，ai 表示 accent insensitive；PostgreSQL 的普通 collation 通常由操作系统或 ICU 提供，命名和语义并不一一对应。

三种方案的取舍如下：

方案	优点	风险	适用场景
直接使用 PG 默认 libc	初始化简单，无需额外建 collation	随 glibc 版本、镜像、地区配置变化	临时环境、对顺序无强要求
使用 ICU 系统预定义 collation	跨发行版更稳定，支持版本查看	与 MySQL 规则仍非字节级一致	新系统、多语言排序
自建 ICU 定制 collation	可控制大小写、重音、numeric 等规则	需要标准化 DDL，索引需重建	MySQL 迁移、长期运行系统

关键结论是：ICU 解决的是“PostgreSQL 各环境之间可复现”的问题，不自动承诺“与 MySQL 完全相同”。迁移时必须用业务真实字符集做样本验收。

实操步骤：用可运行 SQL 复现并定位差异

以下示例适用于 PostgreSQL 15 及以上版本，假设数据库编译时带 ICU 支持。先确认服务器环境：

-- 查看 PostgreSQL 是否支持 ICU，以及当前数据库默认排序
SELECT version();

SELECT datname,
       datcollate,
       datctype
FROM pg_database
WHERE datname = current_database();

-- 查看当前实例中可用的 ICU collation
SELECT collname,
       collcollate,
       collctype,
       collversion
FROM pg_collation
WHERE collprovider = 'i'
ORDER BY collname
LIMIT 20;

然后构造一组最容易暴露差异的数据。不要只测英文字母，应包含大小写、数字、连字符、下划线、重音字符和中文：

DROP TABLE IF EXISTS collation_demo;

CREATE TABLE collation_demo (
    id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

INSERT INTO collation_demo(name)
VALUES
    ('iPhone 10'),
    ('iPhone 2'),
    ('iphone-10'),
    ('IPHONE_2'),
    ('café'),
    ('Cafe'),
    ('张三'),
    ('李四'),
    ('A1'),
    ('a1');

-- 使用数据库默认 collation 排序
SELECT name
FROM collation_demo
ORDER BY name
LIMIT 10;

-- 显式使用 ICU 的 en-US 排序
-- 如果实例中不存在该名称，可先查询 pg_collation
SELECT name
FROM collation_demo
ORDER BY name COLLATE "en-US"
LIMIT 10;

如果需要模拟 MySQL 常见的大小写不敏感、重音不敏感语义，可以创建定制 ICU collation。下面的 ks=level1 表示只使用主权重，通常会忽略大小写和重音差异；kn=true 表示启用数字感知排序，使 2 排在 10 前面：

-- 创建一个可移植的 ICU 排序规则：
-- ks=level1：忽略大小写、重音等次级差异
-- kn=true：数字按数值感知排序
CREATE COLLATION IF NOT EXISTS utf8mb4_like_icu (
    provider = icu,
    locale = 'en-US-u-ks-level1-kn'
);

-- 查看自建 collation 及版本
SELECT c.collname,
       c.collprovider,
       c.collcollate,
       c.collversion
FROM pg_collation c
WHERE c.collname = 'utf8mb4_like_icu';

-- 对比默认排序与定制 ICU 排序
SELECT name,
       row_number() OVER (ORDER BY name) AS default_pos,
       row_number() OVER (
           ORDER BY name COLLATE utf8mb4_like_icu
       ) AS icu_pos
FROM collation_demo
ORDER BY icu_pos;

接着验证唯一性。MySQL 的 ci collation 常把 A1 与 a1 视为相同值；PostgreSQL 普通 text 唯一索引默认按数据库 collation 比较，若要得到类似效果，必须让索引使用不区分大小写的 collation：

-- 普通唯一索引：A1 与 a1 通常会被视为两个不同值
CREATE UNIQUE INDEX IF NOT EXISTS uk_demo_name_default
ON collation_demo(name);

-- 删除后改用定制 ICU collation
DROP INDEX IF EXISTS uk_demo_name_default;

CREATE UNIQUE INDEX IF NOT EXISTS uk_demo_name_icu
ON collation_demo(name COLLATE utf8mb4_like_icu);

如果样本中同时存在 A1 和 a1，创建该唯一索引会失败，这正是迁移前必须暴露的语义差异。不要在切换后才由业务写入触发重复键错误。

索引、分页与查询条件的联动修复

排序规则不是单纯的展示规则，它会影响索引选择、唯一约束、DISTINCT、GROUP BY、窗口函数和 WHERE 比较。将列默认 collation 固化到表定义，通常比在每条 SQL 中临时写 COLLATE 更可靠：

-- 新建表时直接指定列级 collation
CREATE TABLE app_account (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    login_name text COLLATE utf8mb4_like_icu NOT NULL,
    display_name text
);

CREATE UNIQUE INDEX uk_app_account_login_name
ON app_account(login_name);

-- 已有表可修改列定义，但 PostgreSQL 会要求重建依赖索引
ALTER TABLE collation_demo
ALTER COLUMN name TYPE text COLLATE utf8mb4_like_icu;

REINDEX INDEX CONCURRENTLY uk_demo_name_icu;

对于分页接口，要避免“只按非唯一排序列翻页”。即使 collation 对齐了，当多个值比较相等时，数据库仍可能以任意顺序返回它们。应追加唯一键作为稳定次序：

-- 推荐：业务排序键 + 主键，保证分页顺序确定
SELECT id, name
FROM collation_demo
ORDER BY name COLLATE utf8mb4_like_icu, id
LIMIT 20;

-- 深分页更推荐键集分页，而不是不断增大 OFFSET
SELECT id, name
FROM collation_demo
WHERE (name, id) > (
    'iphone-10' COLLATE utf8mb4_like_icu,
    100
)
ORDER BY name COLLATE utf8mb4_like_icu, id
LIMIT 20;

迁移阶段还应对 MySQL 与 PG 的排序结果做抽样比对。可以抽取主键和关键排序列，在两侧分别生成排名，再比较相邻关系或 Top N 结果。若业务包含大量中文、日文、 emoji 或特殊符号，不要只使用 en-US，应按真实用户地区选择 root、zh 或具体 locale，并保留 DDL 审核记录。

避坑总结：至少检查这三类问题

1. 只迁移字符集，不迁移比较语义

常见错误现象是表已经使用 UTF8，但 WHERE login_name = 'Tom' 查不到 tom，或 MySQL 唯一键在 PG 中失效。修正方法是在迁移评估表中为每个字符串列记录源库 collation、是否区分大小写、是否区分重音、是否用于唯一约束，而不是只记录 varchar 长度。

2. 测试环境和生产环境排序结果不同

现象是同一份数据在开发机、容器、RDS 上顺序不同。根因常是使用了 libc collation，却没有固定操作系统版本和 locale。修正方法是优先使用 ICU，并在版本库中保存 CREATE COLLATION DDL；实例初始化后查询 pg_collation.collversion，作为发布检查项。

3. 修改 collation 后忘记重建索引

现象是 ALTER COLUMN ... COLLATE 后查询仍走旧计划，或唯一索引偶发报错、排序结果不符合预期。PostgreSQL 索引与排序规则绑定，collation 版本变化也可能导致索引需要维护。修正方法是在低峰期使用 REINDEX INDEX CONCURRENTLY 或新建索引、切换、删除旧索引，并复查执行计划。

4. 用 ORDER BY 非唯一列做分页

现象是翻页过程中重复、漏行，数据不变但每次顺序变化。即使排序规则忽略大小写和重音，相等键之间也没有天然顺序。修正方法是所有分页 SQL 都追加主键或唯一键，例如 ORDER BY name COLLATE utf8mb4_like_icu, id，大偏移量场景改用键集分页。

5. 误以为 ICU 与 MySQL unicode_ci 完全等价

ICU、MySQL UCA、glibc 的权重表和标点处理存在版本差异，尤其在 emoji、组合字符、全角符号和多语言混排时更明显。修正方法是建立业务专属排序样本，迁移前跑双库对比；对无法接受差异的字段，可增加规范化列，统一 lower()、去空格或标准化标点后再排序和建唯一索引。

排序规则问题的排查成本不高，但漏检代价很大。最稳妥的迁移路径是：先盘点 MySQL 字段语义，再固定 PostgreSQL ICU collation，用真实样本验证排序与唯一性，最后重建索引并改造分页 SQL。这样才能把“数据库能查”提升到“业务顺序和唯一性都一致”。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。