MySQL 8.4 REGEXP_REPLACE 怎么清洗订单编码：捕获组、NULL 与替换结果核对

订单导入后，order_code 里同时出现了 AB- 2026 / 0042、AB20260043 和空值。要把编码统一成可检索的形式，MySQL 8.4 的 REGEXP_REPLACE() 可以一次完成模式匹配和替换，但前提是先把“哪些字符该删、哪些部分必须保留”写成可核对的规则。

REGEXP_REPLACE(expr, pat, repl) 会替换所有匹配片段；输入任一关键参数为 NULL 时结果也应按 NULL 处理，正式更新前先用 SELECT 对照原值和清洗值。

先把订单编码规则缩成一个可验证的表达式

这次清洗的目标很窄：保留大写字母和数字，去掉空格、短横线与斜杠。不要一上来直接覆盖生产表，先建立一组代表性输入，让每一种变化都有可见结果。

CREATE TEMPORARY TABLE import_orders (
  order_id BIGINT PRIMARY KEY,
  order_code VARCHAR(64)
);

INSERT INTO import_orders VALUES
  (1, 'AB- 2026 / 0042'),
  (2, 'AB20260043'),
  (3, NULL),
  (4, ' ab-2026/0044 ');

SELECT order_id,
       order_code AS original_code,
       REGEXP_REPLACE(order_code, '[^A-Z0-9]', '') AS cleaned_code
FROM import_orders
ORDER BY order_id;

这里的 [^A-Z0-9] 表示“不是大写字母或数字的字符”。它会同时移除空格、短横线和斜杠，但不会把 AB20260043 再改一遍。官方文档把 REGEXP_REPLACE() 定义为替换匹配正则的子串，MySQL 8.4 的正则实现使用 ICU；因此生产数据如果包含小写字母，要先决定是转大写，还是把小写纳入允许集合，而不是让正则替你做业务决策。

捕获组适合保留订单前缀，不适合盲目替换整串

有些编码不是单纯删分隔符，而是要把区域前缀和流水号重新拼接。例如旧系统写成 SH/2026-0042，新字段希望得到 SH-0042。这时用捕获组比多次 REPLACE() 更容易把边界写清楚。

SELECT order_code,
       REGEXP_REPLACE(
         order_code,
         '^([A-Z]{2})/[0-9]{4}-([0-9]{4})$',
         '\\1-\\2'
       ) AS replacement
FROM import_orders
WHERE order_code IS NOT NULL;

上面的 replacement 只保留两个大写字母前缀和四位流水号；不符合完整格式的行不会凭空变成“看起来正确”的编码。应用层拼接 SQL 时还要注意反斜杠转义，建议把这条 SELECT 固化进迁移验收脚本，先查看原值、匹配结果和不匹配样本。

NULL、空字符串和不匹配结果要分开验收

NULL 表示值不存在，'' 表示值存在但为空；两者不应被同一个“清洗成功”计数覆盖。对导入数据先做分组统计：

SELECT
  SUM(order_code IS NULL) AS null_count,
  SUM(order_code = '') AS empty_count,
  SUM(order_code IS NOT NULL AND order_code  '') AS non_empty_count,
  SUM(REGEXP_LIKE(order_code, '^[A-Z]{2}[0-9]{8}$')) AS already_clean_count
FROM import_orders;

如果只是删除分隔符，NULL 输入仍应得到 NULL，而不是空串。捕获组规则则应把不匹配行列出来，交给业务确认：

SELECT order_id, order_code
FROM import_orders
WHERE order_code IS NOT NULL
  AND order_code  ''
  AND NOT REGEXP_LIKE(order_code, '^[A-Z]{2}/[0-9]{4}-[0-9]{4}$');

确认对照结果后再更新正式表

测试表的结果符合预期后，再用事务包住正式更新。更新条件要和 SELECT 对照查询保持一致，不能把所有非空值一锅端。

START TRANSACTION;

UPDATE orders
SET order_code = REGEXP_REPLACE(order_code, '[^A-Z0-9]', '')
WHERE order_code IS NOT NULL
  AND order_code  ''
  AND order_code REGEXP '[^A-Z0-9]';

SELECT ROW_COUNT() AS changed_rows;

SELECT order_id, order_code
FROM orders
WHERE order_code IS NOT NULL
  AND NOT REGEXP_LIKE(order_code, '^[A-Z]{2}[0-9]{8}$')
LIMIT 20;

-- 验收无误后提交；异常则回滚
COMMIT;

如果目标表还没有唯一约束或历史数据不满足八位流水号，最后一条查询不应被强行解释为“失败更新”；它只是提醒你规则覆盖范围之外仍有数据。先保存异常样本，再决定是否增加小写转换、长度检查或人工修复。

常见问题

REGEXP_REPLACE() 会只替换第一个匹配吗？

不会，函数用于替换表达式中匹配正则的子串。要保留某些片段，应把保留范围写进模式或捕获组。

为什么清洗后 NULL 没有变成空字符串？

这是应保留的区别。NULL 表示缺失值，清洗表达式不应把缺失值伪装成已处理的空串。

REGEXP_REPLACE() 能代替数据校验吗？

不能。它负责生成替换结果；格式是否合格仍要用正则匹配、长度检查、唯一性约束或业务校验单独确认。

把清洗规则留成可回放的验收记录

这类 SQL 最容易出问题的地方不是函数语法，而是模式比业务规则更宽。保留原值、清洗值、异常样本和 ROW_COUNT()，下次导入仍能复查同一条规则；确认无误后再提交事务，才算完成一次可回放的数据清洗。