文章目录

在这里插入图片描述

每日一句正能量

“你可以共情，但请不要内耗，让自己保持清醒的头脑和平和的内心。”
共情是连接他人的桥，内耗是燃烧自己的火。你可以理解别人的情绪，但不必把别人的情绪背在自己身上。清醒的头脑让你看清哪些是别人的事，平和的内心让你有余力关照自己的事。

主题：字符排序规则差异 / MySQL → KingbaseES / 客户主数据迁移
重点：Collation、大小写、重音、尾随空格、唯一约束、比对 SQL、修正策略、数据校验与回退
适用场景：客户编码、登录名、邮箱、手机号、客户姓名、地址、外部系统编码等核心主数据。

1. 背景与问题：Collation不是“ORDER BY排什么顺序”这么简单

很多数据库迁移项目把字符集和排序规则放在同一个检查项里：

MySQL utf8mb4
→
KingbaseES UTF8

然后认为字符问题基本结束。

实际上：

Character Set

回答的是：

字符怎么编码、能表示哪些字符。

而：

Collation

回答的是：

两个字符串什么时候相等、谁排在谁前面。

这直接影响：

=
<>
LIKE
ORDER BY
GROUP BY
DISTINCT
UNIQUE INDEX
PRIMARY/UNIQUE约束

因此客户主数据迁移时，排序规则差异可能直接导致数据库无法建立唯一索引。

假设 MySQL 中有：

customer_code='ABC001'
customer_code='abc001'

如果源列使用大小写敏感排序规则，两行可以同时存在。

目标 KingbaseES 如果选择不区分大小写的排序规则：

ABC001 = abc001

那么目标建立：

UNIQUE(customer_code)

时就会失败。

反过来也一样。

源 MySQL 若使用 _ci：

ABC001
abc001

本来就被判为相同；目标如果改成大小写敏感，则以后应用可能成功插入第二条，从而改变原有唯一性规则。

所以 Collation 迁移的核心不是：

“目标能不能排序中文？”

而是：

目标库能否复现源系统的字符串等价关系，以及我们是否有意改变这种关系。

MySQL 8.4 官方文档明确说明，排序规则名称中的：

_ci = case-insensitive
_cs = case-sensitive
_ai = accent-insensitive
_as = accent-sensitive
_bin = binary

直接表达比较特征。

KingbaseES 当前排序规则文档也支持：

CI / CS
AI / AS
KI / KS
WI / WS

分别控制大小写、重音、假名和全半角敏感性。

这意味着目标库能力不是问题，真正的问题是如何正确选择和验证。

在这里插入图片描述

2. 环境与数据：先画出Collation地图，再迁数据

示例环境：

源库：MySQL 8.0/8.4
目标：KingbaseES V9
系统：客户主数据中心
客户量：1.2亿
唯一业务字段：
  customer_code
  external_customer_no
  email
部分查询字段：
  customer_name
  address
迁移方式：全量 + CDC

示例源表：

CREATE TABLE customer_master (
    customer_id BIGINT PRIMARY KEY,

    customer_code VARCHAR(64)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        NOT NULL,

    customer_name VARCHAR(200)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        NOT NULL,

    email VARCHAR(320)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci,

    UNIQUE KEY uk_customer_code(customer_code)
);

2.1 第一件事：盘点四层规则

MySQL 排序规则可能定义在：

Server
Database
Table
Column

列级规则最终最重要，因为同一张表完全可能：

customer_code → utf8mb4_bin
customer_name → utf8mb4_0900_ai_ci

所以要查：

SELECT
    table_schema,
    table_name,
    column_name,
    character_set_name,
    collation_name
FROM information_schema.columns
WHERE table_schema=DATABASE()
  AND character_set_name IS NOT NULL;

不要只执行：

SHOW VARIABLES LIKE 'collation_server';

数据库默认规则不能代表每一列。

2.2 _ci和_ai必须翻译成业务含义

例如：

utf8mb4_0900_ai_ci

其含义包括：

accent-insensitive
case-insensitive

因此很多拉丁字符比较会忽略大小写和重音差异。

例如是否把：

Jose
José

视为相同，要由具体 collation 决定。

这个规则对于：

客户姓名

也许合理。

但对于：

外部客户编码
API Key
大小写敏感账号

可能完全不合理。

迁移窗口正好应该把：

“所有VARCHAR都使用数据库默认collation”

改成：

“按字段业务语义选择collation”

2.3 KingbaseES不是只有一个排序规则

KingbaseES 当前官方文档说明，COLLATE 可以应用于：

数据库
列
字符串表达式

并支持大小写、重音、假名和全半角敏感控制。

例如文档中：

CI = 不区分大小写
CS = 区分大小写

AI = 不区分重音
AS = 区分重音

WI = 不区分全半角
WS = 区分全半角

因此不要设计：

整个KingbaseES数据库只选一个规则，然后所有客户字段都继承

更成熟的设计是：

字段 推荐关注
customer_code 通常强调精确性/大小写策略
email 需结合业务规范，而不是想当然 LOWER
customer_name 更偏用户友好搜索
mobile 通常不应依赖语言排序
external_id 多数应精确比较
address 展示排序和搜索需求优先

3. 复现过程：四类最容易在唯一约束上爆炸的边界

在这里插入图片描述

3.1 大小写冲突

源数据：

CUST001
cust001
Cust001

如果源列大小写敏感：

3条合法记录

目标改成 CI：

3条变成同一个等价值

建 UNIQUE 就会失败。

检测候选：

SELECT
    LOWER(customer_code),
    COUNT(*)
FROM customer_master
GROUP BY LOWER(customer_code)
HAVING COUNT(*)>1;

注意：

LOWER()

只是预扫描工具，不等于某个复杂 Unicode collation 的完整权重算法。

最终仍需要在目标 collation 上做真实重复检测。

3.2 重音冲突

例如：

Jose
José

MySQL _ai 表示不区分重音。

如果客户编码里允许 Unicode 字母，这类差异可能直接进入唯一约束。

姓名字段则更复杂：

比较相等

和：

是不是同一个自然人

绝对不是同一件事。

所以不要因为 collation 判等，就自动合并客户主记录。

3.3 尾随空格

这个问题非常容易漏。

MySQL 8.4 的 INFORMATION_SCHEMA.COLLATIONS 提供：

PAD_ATTRIBUTE

值可能为：

PAD SPACE
NO PAD

官方文档明确说明：

  • PAD SPACE 下，非二进制字符串比较可能忽略尾随空格；
  • NO PAD 下，尾随空格像普通字符一样有意义。

因此：

'ABC'
'ABC '

到底是否相等，取决于具体排序规则。

而且这个差异会影响：

UNIQUE

MySQL 官方明确指出，在忽略尾随空格的比较规则下，如果唯一列中已有：

'a'

再插入：

'a '

会发生 duplicate-key。

迁移前必须查询：

SELECT
    collation_name,
    pad_attribute
FROM information_schema.collations;

不能只看 _ci/_cs。

3.4 全角/半角

客户编码经人工录入时可能出现：

ABC123
ＡＢＣ１２３

视觉非常接近，但 Unicode 字符不同。

KingbaseES 排序规则支持：

WI / WS

控制是否区分全半角。

客户名称搜索可以考虑宽松规则。

但：

客户编码

是否允许自动视为相等，需要业务 Owner 明确。

如果无业务证据，不建议迁移工具自行合并。

4. 方案实施：先复现源语义，再做治理升级

4.1 第一步：给字符字段分级

建议三类：

A类：标识符
customer_code
external_id
login_name

关注：

唯一性
字节/字符精确性
大小写规则
尾空格
B类：自然语言
customer_name
address
company_name

关注：

语言排序
大小写
重音
全半角
C类：格式化标识
mobile
email
certificate_no

需要由业务规范定义标准化方式。

例如邮箱是否统一小写，不能仅由数据库 collation 决定。

4.2 第二步：源端做冲突候选扫描

大小写：

SELECT LOWER(customer_code), COUNT(*)
FROM customer_master
GROUP BY LOWER(customer_code)
HAVING COUNT(*)>1;

尾空格：

SELECT RTRIM(customer_code), COUNT(*)
FROM customer_master
GROUP BY RTRIM(customer_code)
HAVING COUNT(*)>1;

原始字节：

SELECT
    customer_id,
    customer_code,
    HEX(customer_code)
FROM customer_master;

为什么保存 HEX？

因为用户界面里：

不可见空格
全角字符
Unicode特殊字符

很难肉眼看出来。

4.3 第三步：建立冲突候选表

不要直接 UPDATE。

建立：

CREATE TABLE migration_collation_conflict (
    conflict_group_id BIGINT,
    customer_id BIGINT,
    field_name VARCHAR(64),
    source_value VARCHAR(500),
    source_hex VARCHAR(1000),
    normalized_candidate VARCHAR(500),
    reason_code VARCHAR(64),
    decision VARCHAR(32)
);

reason：

CASE_COLLISION
ACCENT_COLLISION
TRAILING_SPACE
WIDTH_COLLISION
MULTI_DIMENSION

决策：

KEEP_BOTH
MERGE
RENAME_CODE
MANUAL_REVIEW

4.4 第四步：目标Collation用测试数据选，不用名字猜

KingbaseES 可以通过：

数据库默认
列级 COLLATE
表达式 COLLATE

指定规则。

官方文档还支持通过 CREATE COLLATION 使用：

libc
ICU

等 provider 创建排序规则，并提供 DETERMINISTIC 属性。

这意味着目标实例的可用规则要：

实际查询系统目录
+
实际插入边界用例

验证。

不要因为名字像：

xxx_ci_ai

就直接认为与 MySQL 某个 UCA 版本完全等价。

不同排序引擎、Unicode 版本和实现细节可能存在差异。

4.5 第五步：唯一约束最后创建

错误顺序：

CREATE TABLE + UNIQUE
→ 导数
→ 导到一半duplicate key

更稳的顺序：

建无唯一约束目标/staging
→ 全量导数
→ 按目标collation查重复
→ 冲突治理
→ 重复=0
→ 再建UNIQUE

这是客户主数据迁移非常重要的做法。

4.6 第六步：大小写不敏感不要一律用LOWER函数索引替代

一种常见改法：

UNIQUE(LOWER(customer_code))

它有时很好用，但不是万能。

因为：

LOWER

只解决大小写折叠。

它不等于：

重音不敏感
全半角不敏感
语言特定排序
PAD SPACE

因此如果目标真正需要的是完整 collation 规则，应该让约束和比较使用相同的 collation 语义。

4.7 第七步：ORDER BY补稳定唯一键

源：

ORDER BY customer_name

如果很多客户姓名相同：

张伟
张伟
张伟

不同数据库排序权重相同后，等价行内部顺序可能不同。

分页：

第1页
第2页

就可能漂移。

推荐：

ORDER BY
    customer_name,
    customer_id;

把唯一主键作为最终 tie-breaker。

这样即使源目标对某些名字权重处理相同，分页也更稳定。

4.8 第八步：应用查询也要扫描COLLATE和BINARY

MySQL 应用可能显式写：

WHERE BINARY customer_code = ?

或者：

COLLATE utf8mb4_bin

这说明开发人员在某些 SQL 中主动覆盖了列默认排序规则。

迁移扫描必须包含：

COLLATE
BINARY
LOWER
UPPER
LIKE
REGEXP
ORDER BY
DISTINCT
GROUP BY

否则仅迁列定义仍然不完整。

5. 结果对比：验证“集合、顺序、唯一性”三个维度

在这里插入图片描述

5.1 唯一键重复数

目标建立 UNIQUE 之前：

SELECT
    customer_code,
    COUNT(*)
FROM customer_master_new
GROUP BY customer_code
HAVING COUNT(*)>1;

必须：

0 rows

注意要使用目标实际 collation。

5.2 等值比较用例

至少准备：

Alice / ALICE
Jose / José
ABC / ABC<space>
ABC / ＡＢＣ

对源、目标分别执行：

a = b

记录布尔结果。

如果不同：

这是计划内治理变化
还是迁移缺陷？

必须有明确答案。

5.3 DISTINCT/GROUP BY

排序规则影响的不只是排序。

如果：

ABC
abc

在新规则中等价：

SELECT DISTINCT customer_code

行数会减少。

因此至少比较：

COUNT(*)
COUNT(DISTINCT customer_code)
GROUP BY customer_code行数

5.4 排序与分页

准备固定客户集合，源目标执行：

ORDER BY customer_name, customer_id

比较：

第1～1000行主键顺序

如果业务要求源目标排序完全一致，这一项尤其重要。

如果只是要求：

同名客户稳定分页

则主键 tie-breaker 已经能消除大量不确定性。

5.5 搜索行为

测试：

WHERE customer_name='alice'
WHERE customer_name LIKE 'ali%'
LOWER(customer_name)=...

还要检查执行计划。

因为一个查询从：

直接列比较

改成：

LOWER(column)

可能导致普通索引不能直接使用。

所以结果一致不等于性能一致。

5.6 示例验收表

用例 源规则 目标规则 结果
Alice/ALICE 相等 相等 通过
Jose/José 相等 相等 通过
ABC/ABC空格 相等 相等 通过
全角/半角编码 不自动合并 不自动合并 通过
UNIQUE冲突候选 125组 清洗后0 通过
姓名分页 稳定 稳定 通过

以上是验收模板示例，不是本文声称的生产测试结果。

6. 风险与复盘：排序规则改变，本质上可能是数据模型改变

6.1 风险一：为了兼容而错误合并客户

Collation 认为：

A = a

并不意味着两个客户一定是同一个人。

唯一键冲突可能只是：

编码规则历史不一致

而不是重复客户。

因此自动合并客户主数据需要更强证据：

证件号
手机号
外部主键
业务审核

不能仅根据字符等价。

6.2 风险二：为了保留两条记录而随意改业务编码

例如：

CUST001
cust001

迁移后目标 CI 冲突。

直接把第二条改：

cust001_2

会破坏所有下游引用。

客户编码治理必须联动：

主数据
订单
CRM
数仓
消息
外部接口

如果编码是跨系统键，通常更适合先选择区分大小写规则保持两条，再单独治理。

6.3 风险三：MySQL UCA版本与目标规则不是简单一一对应

MySQL：

utf8mb4_0900_ai_ci

明确基于特定 Unicode Collation Algorithm 版本。

KingbaseES 可以使用 ICU/libc 等 provider。

即使都写：

case-insensitive
accent-insensitive

边界字符排序也不应只靠名字推断完全一致。

必须使用真实业务字符集做 golden dataset。

6.4 风险四：尾随空格被清洗后影响外部系统

数据库可能把：

ABC
ABC

视为相同。

但外部文件或签名系统可能把字节视为不同。

所以：

RTRIM清洗

之前要确认字段是不是参与：

数字签名
文件交换
外部系统精确ID

6.5 风险五：唯一索引重建会暴露多年隐藏数据债

这其实是好事，但切换计划要预留时间。

最差情况：

全量导数结束
→ CREATE UNIQUE
→ 一次发现50万组冲突

所以冲突扫描必须前置。

6.6 风险六：不同列不应该统一使用同一规则

客户姓名：

更希望用户友好搜索

客户编码：

更希望严格唯一

邮箱：

需要结合公司规范

如果全表都继承一个默认 collation，容易把不同业务语义揉在一起。

6.7 风险七：自定义Collation也有运维成本

KingbaseES 支持 CREATE COLLATION。

但自定义 ICU/libc 规则可能涉及：

操作系统locale
ICU版本
排序规则版本
升级验证

因此能用产品内置、明确版本化规则解决时，优先选择简单方案。

回退方案：保留冲突映射和原始字节

在这里插入图片描述

排序规则迁移的回退，不能只说：

切回MySQL

如果切流窗口中已经对：

customer_code

做过大小写归一、RTRIM 或冲突合并，就必须能恢复原值。

建议审计：

customer_id
source_value
source_hex
target_value
rule_id
batch_id
winner_id

回退触发条件

目标UNIQUE冲突 > 0
客户查询结果差异不可解释
分页重复/漏行
DISTINCT/GROUP BY计数变化异常
关键编码被错误归并
P95显著升高

回退步骤

1. 停止继续扩大灰度
2. query/service切回MySQL
3. 固化Kingbase窗口期新增/更新
4. 依据source_value映射恢复需要反向同步的数据
5. 保留冲突表，不立即删除目标数据
6. 修正collation或清洗规则
7. 用边界用例重新验证
8. 再次灰度

最终复盘

MySQL 到 KingbaseES 的字符排序规则迁移可以概括为：

先盘点规则
→ 再预测冲突
→ 再选目标Collation
→ 再清洗
→ 最后建立唯一约束

而不是：

先建表
→ 导数
→ duplicate key之后再排查

如果只记住一句话：

Collation 决定的不只是字符串“怎么排”，还决定了数据库认为什么叫“同一个值”。

对客户主数据来说，这直接关系到：

客户编码是否唯一
邮箱是否重复
姓名如何搜索
页面如何分页
历史记录能否完整迁移

所以它应该被当成数据模型兼容项，而不是数据库初始化参数里的一个小选项。

附录 A：MySQL排序规则盘点

SELECT
    table_schema,
    table_name,
    column_name,
    character_set_name,
    collation_name
FROM information_schema.columns
WHERE table_schema=DATABASE()
  AND character_set_name IS NOT NULL;

附录 B：PAD属性

SELECT
    collation_name,
    character_set_name,
    pad_attribute
FROM information_schema.collations;

附录 C：大小写冲突预扫描

SELECT
    LOWER(customer_code),
    COUNT(*)
FROM customer_master
GROUP BY LOWER(customer_code)
HAVING COUNT(*)>1;

附录 D：稳定分页

SELECT
    customer_id,
    customer_name
FROM customer_master
ORDER BY
    customer_name,
    customer_id
LIMIT 100;

附录 E：最低验收清单

[ ] Server/DB/Table/Column Collation已盘点
[ ] _ci/_cs已识别
[ ] _ai/_as已识别
[ ] PAD_ATTRIBUTE已盘点
[ ] UNIQUE列冲突已预扫描
[ ] 大小写边界已验证
[ ] 重音边界已验证
[ ] 尾随空格已验证
[ ] 全半角已验证
[ ] DISTINCT/GROUP BY已验证
[ ] ORDER BY分页已增加唯一tie-breaker
[ ] LIKE/LOWER查询已回归
[ ] 目标UNIQUE创建前重复数=0
[ ] 冲突记录原始HEX已保留
[ ] 回退映射已演练

转载自：https://blog.csdn.net/u014727709/article/details/163729211
欢迎 👍点赞✍评论⭐收藏，欢迎指正

Logo
openEuler 社区

openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构

更多推荐

cover

手把手实战 MATLAB 通信系统仿真 02

avatar openEuler 社区
cover

【Linux系统】【Linux发展历史及环境部署、Xshell使用】流食般投喂

avatar openEuler 社区
cover

从概率智能到真理智能：人工智能范式转型的认识论、技术、利益激励与组织制度障碍研究——兼论幻觉、认知偏置、范式锁定、沉没成本与风险不对称下的AI航向纠偏机制

avatar openEuler 社区