字符列尾随空格怎样影响唯一性判断

AB12 和 AB12 能否同时出现在唯一索引里，不能只看列是不是 VARCHAR。同样的字符集，换一种排序规则，答案也可能改变。数据库升级、表重建或字符集整理之后才冒出来的重复键，常常与这个细节有关。

这篇把范围收窄到 MySQL 8.4、InnoDB、VARCHAR，比较 utf8mb4_bin 与 utf8mb4_0900_bin。它们都区分字符的二进制权重，但尾随空格规则不同：前者是 PAD SPACE，后者是 NO PAD。看到名称里都有 bin 就认为完全等价，会把迁移判断带偏。

先把空格变成可见的字节

在独立实验库运行。表名中的 nopad 表示源表，后面会把它迁到另一种比较规则。

CREATE TABLE code_source_nopad (
    id BIGINT NOT NULL,
    code VARCHAR(20)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
    note VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_source_code (code)
) ENGINE=InnoDB;

INSERT INTO code_source_nopad (id, code, note) VALUES
    (1, 'AB12',   '系统一'),
    (2, 'AB12 ',  '系统二'),
    (3, 'AB12  ', '历史导入'),
    (4, 'XY90',   '独立编号');

SELECT id, CONCAT('[', code, ']') AS displayed,
       HEX(code) AS bytes,
       CHAR_LENGTH(code) AS characters,
       OCTET_LENGTH(code) AS byte_length
FROM code_source_nopad
ORDER BY id;

前三行的长度分别是 4、5、6，十六进制末尾逐次增加 20。终端和管理工具经常把这几行显示得几乎一样，复制进聊天窗口后空格还可能被裁掉。检查唯一性问题时，字节和长度才是证据。

确认实例中排序规则的属性，不必靠记忆猜：

SELECT COLLATION_NAME, CHARACTER_SET_NAME, PAD_ATTRIBUTE
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME IN ('utf8mb4_bin', 'utf8mb4_0900_bin');

接着让两个字面值显式使用相同字符集和指定规则，排除连接默认排序规则的干扰：

SELECT
  _utf8mb4'AB12' COLLATE utf8mb4_bin =
  _utf8mb4'AB12 ' COLLATE utf8mb4_bin AS pad_equal,
  _utf8mb4'AB12' COLLATE utf8mb4_0900_bin =
  _utf8mb4'AB12 ' COLLATE utf8mb4_0900_bin AS nopad_equal;

预期第一列是 1，第二列是 0。PAD SPACE 比较时忽略末尾空格，不表示存入 VARCHAR 时把空格删除。存储结果与比较结果要分开观察。

这也解释了一个看似矛盾的现场：HEX(code) 显示值不同，唯一索引仍然拒绝插入。唯一索引按列的比较规则判断重复，不是拿管理工具显示的字符串做逐字节检查。

让唯一索引亲自拒绝一次

CREATE TABLE code_target_pad (
    id BIGINT NOT NULL,
    code VARCHAR(20)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    note VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_target_code (code)
) ENGINE=InnoDB;

INSERT INTO code_target_pad (id, code, note)
VALUES (1, 'AB12', '系统一');

-- 单独执行；预期重复键失败。
INSERT INTO code_target_pad (id, code, note)
VALUES (2, 'AB12 ', '系统二');

SELECT id, HEX(code), note
FROM code_target_pad;

第二条语句失败后，目标表仍只有第一行。这不是长度超限，也不是严格模式问题。调整 sql_mode 不会把 PAD SPACE 变成 NO PAD。

不要用 INSERT IGNORE 让这一步“通过”。如果三个外部编号分别代表三个对象，忽略两行就是丢对象；如果它们本来应该归为同一个对象，也需要决定哪一行保留、关联记录如何合并。数据库只能执行比较规则，无法替业务做身份合并。

改列之前，按目标规则扫描冲突

迁移前在源表直接按目标规则分组：

SELECT
    code COLLATE utf8mb4_bin AS target_comparison_key,
    COUNT(*) AS members,
    GROUP_CONCAT(id ORDER BY id) AS source_ids
FROM code_source_nopad
GROUP BY code COLLATE utf8mb4_bin
HAVING COUNT(*) > 1;

该数据集会把 1、2、3 归到同一组。分组结果中的编号字符串只是定位线索，不能依赖它保留完整大批量清单，GROUP_CONCAT 有长度上限。需要完整冲突明细时，把分组结果连接回源表：

SELECT s.id, HEX(s.code) AS source_bytes, s.note
FROM code_source_nopad AS s
JOIN (
    SELECT code COLLATE utf8mb4_bin AS collision_key
    FROM code_source_nopad
    GROUP BY code COLLATE utf8mb4_bin
    HAVING COUNT(*) > 1
) AS c
  ON s.code COLLATE utf8mb4_bin = c.collision_key
ORDER BY s.id;

这个查询回答的是“哪些当前不同的值，在目标规则里会撞到一起”。它比先执行 ALTER TABLE 等待失败更适合作为迁移准入检查。若迁移方向相反，从 PAD SPACE 到 NO PAD，原来已存在的行通常不会仅因尾空格变得更难区分，但新写入的身份语义会放宽。应用此前用 code = ? 查询到的对象，今后可能因参数末尾多一个空格就查不到。只检查 DDL 能否成功还不够。

别把比较规则修复写成一次无条件 TRIM

TRIM(code) 看上去能让数据整齐，实际修改的是业务键。对于三个来源系统，末尾空格可能只是导入瑕疵，也可能是供应商编码规范的一部分。未经核对就批量去空格，效果与把三个账户合并到一个账户相似，不只是清理显示格式。

即使决定统一去空格，顺序也应先检查规范化后的冲突、制定保留和引用映射，再执行修改。直接对现有唯一列做全表更新，很可能执行到重复键时失败；靠 IGNORE 跳过则会留下部分规范化、部分未处理的混合状态。发生冲突的对象没有消失，它们只是被 SQL 处理方式遮住了。

这里讨论的是普通尾随空格。制表符、换行、不间断空格以及全角空格不能都用“看起来空白”归为一类。输入规范如果允许多种空白，应列出具体码点，在应用中按这份规范处理，并用十六进制样本核验。不要把 PAD SPACE 理解为数据库自动帮忙清洗所有不可见字符。

同理，这两个排序规则的选择只隔离了尾随空格问题。若改成不区分大小写、重音的规则，还可能出现另一组冲突：这次应按实际目标规则重新执行扫描，不能拿本文两个 bin 规则的结果替代。一次迁移可能同时改变多个比较维度，最好让测试数据分别覆盖它们。

从演练到切换，需要一个稳定的数据边界

上面的分组检查是读时快照。如果应用随后又写入 AB12，旧的冲突清单就过期。小表维护可以在受控停写期间重新扫描并复制；持续写入的大表则要把增量同步、冲突拒绝和最终收敛一起设计。只在周一做过一次查询，周三直接改排序规则，没有任何一致性保证。

保留清单还应能解释哪些行未复制。若源表有四行、目标表只有两行，少两行是经过确认的身份合并，还是导入程序丢行，单看目标计数分不出来。实验里源表保持不动，清单保存保留编号；正式迁移应把被合并编号到新编号的映射也留在审计记录中，并检查所有业务引用已经按这个映射处理。

冲突扫描对列施加了不同于现有索引的排序规则，大表上不应默认它能使用原有唯一索引完成分组。先查看执行计划和临时空间需求，再选择离线副本预演、分批提取或维护窗口。这个扫描的目的在于证明目标约束能成立，不能为了让它运行得快，就去掉 COLLATE 而改查源规则；后者会漏掉本来要发现的碰撞。

处理决定留下记录，源数据不先删

下面假定业务已经核对：1、2、3 是同一个对象，保留编号 1；4 不受影响。把这一决定做成明确清单，避免用 MIN(id) 暗中代替业务判断。

CREATE TABLE code_migration_keep (
    source_id BIGINT NOT NULL,
    reason VARCHAR(200) NOT NULL,
    PRIMARY KEY (source_id)
) ENGINE=InnoDB;

INSERT INTO code_migration_keep (source_id, reason) VALUES
    (1, '已确认 1、2、3 为同一业务对象，保留 1'),
    (4, '无比较规则冲突');

DELETE FROM code_target_pad;

START TRANSACTION;
INSERT INTO code_target_pad (id, code, note)
SELECT s.id, s.code, s.note
FROM code_source_nopad AS s
JOIN code_migration_keep AS k ON k.source_id = s.id;

SELECT
  (SELECT COUNT(*) FROM code_migration_keep) AS approved_count,
  (SELECT COUNT(*) FROM code_target_pad) AS copied_count;
COMMIT;

这个实验没有关联业务表，所以只演示保留清单。真实迁移如果还有订单、权限或历史事件引用 2、3，切换前必须完成引用迁移和对应关系记录，不能因为目标表插入成功就删除源对象。

复制后继续按字节核对保留行，别再使用会忽略空格的普通字符相等作为唯一验收：

SELECT t.id, HEX(s.code) AS before_bytes,
       HEX(t.code) AS after_bytes
FROM code_target_pad AS t
JOIN code_source_nopad AS s ON s.id = t.id
WHERE CAST(s.code AS BINARY) <> CAST(t.code AS BINARY)
   OR CAST(s.note AS BINARY) <> CAST(t.note AS BINARY);

应返回零行。目标索引负责目标语义下的唯一性，字节核对负责证明复制没有另行修改保留值，两者检查不同的东西。

CHAR 和 LIKE 不能拿来替代这个实验

把实验列改成 CHAR，取值时的尾空格处理会再引入一层差异。这里特意使用 VARCHAR，就是为了先确认“存下来的值不同，但比较结果相同”这一个问题。对已有 CHAR 业务键，先检查真实取值语义，不能把这份迁移脚本直接当作无损转换方案。

LIKE 的尾随空格行为也不能当成等号的证据。检索条件、分组和唯一索引应分别核对，尤其不能因为 LIKE 'AB12 ' 没找到普通 AB12，就断言两者一定能同时写进唯一索引。

如果业务规范明确要求去掉末尾空格，就在入口规范化并拒绝不合规编号，再配合相符的数据库约束；如果空格确实属于编号，选择保留其区分能力的类型和规则。最难收拾的组合，是入口说空格无关、数据库允许两条身份记录、下游又自行 TRIM。这会让“同一个编号”在三个系统里出现三种答案。