REGEXP_REPLACE 是什么
REGEXP_REPLACE 用正则表达式批量替换字符串，适合清洗手机号、脱敏、格式规整这类场景：MySQL 8.0 与 PostgreSQL 都提供 REGEXP_REPLACE(源串, 模式, 替换串) 函数，PostgreSQL 还支持指定匹配起点与标志位。两库的默认替换范围都是全部匹配，不需要额外加全局标志。正则方言与捕获组的引用写法不同，跨库改写要逐条核对，避免替换结果与预期不符。
基本信息
| MySQL | REGEXP_REPLACE(str, pattern, replace)，8.0 起支持 |
|---|---|
| PostgreSQL | regexp_replace(str, pattern, replace [, flags]) |
| 替换范围 | 默认替换全部匹配，无需全局标志 |
| 捕获组引用 | PostgreSQL 用 \1，MySQL 8.0 用 $1 |
| 常见用途 | 脱敏、去除非数字字符、格式规整 |
| 性能 | 对列做正则替换无法使用索引，适合批量清洗而非在线查询 |
典型使用场景
- 手机号脱敏：中间四位替换成星号
- 身份证与银行卡号脱敏：保留首尾
- 清洗非数字字符：把带分隔符的数字规整成纯数字
- 统一格式：把多种日期分隔写法统一成一种
- 去除乱码与异常符号：导入数据后统一清理
- 关键词替换：批量修正同一类文案错误
示例
示例
-- MySQL 8.0：手机号中间四位脱敏
SELECT REGEXP_REPLACE(phone, '^(.{3}).{4}(.{4})$', '$1****$2') AS masked
FROM users;
-- 去掉所有非数字字符（仅用于格式化展示，保留原值）
SELECT REGEXP_REPLACE(' 138-0013-8000 ', '[^0-9]', '') AS digits;
-- PostgreSQL：捕获组用 \1 引用
SELECT regexp_replace(phone, '^(.{3}).{4}(.{4})$', '\1****\2') AS masked
FROM users;
-- 统一日期格式：把 2024/5/1 规整为 2024-05-01
SELECT REGEXP_REPLACE('2024/5/1', '/([0-9])/', '-0\1-') AS normalized;写法与差异
| 对比项 | MySQL 8.0 | PostgreSQL |
|---|---|---|
| 函数名 | REGEXP_REPLACE | regexp_replace |
| 捕获组引用 | $1、$2 | \1、\2 |
| 替换范围 | 全部匹配 | 全部匹配 |
| 可选标志 | 有限支持 | 支持 i（忽略大小写）、g 等 |
| 位置参数 | 不支持 | 可指定起始位置 |
清洗类操作先把替换结果 SELECT 出来核对，确认样例符合预期后再执行 UPDATE，避免批量替换把数据改错。
脱敏结果建议只在展示层使用，原表保留完整数据；把脱敏结果写回原表会让数据无法恢复。
对列使用正则替换无法使用索引，在线查询里加正则条件会全表扫描；这类逻辑适合放在离线清洗或数据接入环节。
使用要点
- 先用 SELECT 验证替换结果，再考虑写回
- 脱敏只影响展示，原始数据保留在受控的表里
- 跨库改写时核对捕获组引用写法
- 正则尽量写具体，避免过于宽泛的匹配误伤其他数据
- 批量替换前备份，替换后抽样比对
- 把清洗规则写进文档，多人协作时保持一致