用 SQL 批量替换字段里的文本，最典型的事故是"只替换了每行的第一处"和"大小写没匹配上"。原因是 REPLACE 与 REGEXP_REPLACE 的默认行为不同，而 TRANSLATE 又是第三种完全不同的语义。本文依据 PostgreSQL 官方字符串函数文档原文整理。

一、replace 替换全部，regexp_replace 默认只替换第一处

PostgreSQL 字符串函数页（实测 200）对 replace ( string, from, to ) 的定义原文是 "Replaces all occurrences in string of substring from with substring to"，即全量替换、且只做子串精确匹配、不区分大小写以外的任何规则。

而 regexp_replace ( string, pattern, replacement [, flags ] ) 的定义是 "Replaces the substring that is the first match to the POSIX regular expression pattern, or all such matches if the g flag" 才成立。官方示例 regexp_replace('Thomas', '.[mN]a.', 'M') 返回 ThM。

这就是最常见的 bug 来源：把 replace 的直觉套到正则上，漏写 g，结果一行里多处匹配只改了第一个。需要多处时必须显式加 'g'。

二、start 与 N：可以只替换第几处

文档还给出带位置的形态："Replaces the substring that is the N'th match ... beginning the search from the start position"。官方两个示例：regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas；具名参数写法 regexp_replace(string, pattern, replacement, start, N) → helXXo world（对 hello world 中的 l 从第 1 位起找第 2 处匹配替换为 XX）。两个要点：start 是按字符计的起始位置，不是偏移字节；具名传参时 N 是保留字，必须加引号才能写成参数名，这是很多人抄示例报错的原因。

三、translate 是按字符映射，不是替换子串

同页对 translate ( string, from, to ) 的定义是 "Replaces each character in string that matches a character in the from set with the corresponding character in the to set, and eliminates any such occurrences where there is no corresponding character"。也就是说它是逐字符一一对应映射，from 比 to 长时多出来的字符会被删除。想做子串替换却写成 translate，会得到"某些字符凭空消失"的诡异结果；反过来，想把中文标点批量换成英文标点（一对多除外）、或删掉所有某类字符，translate 比 replace 高效。

相关工具函数还有 position('om' in 'Thomas')（返回子串位置）、substring('Thomas' from 2 for 3)（按位置截取，另有 from 正则、similar 两种形态）、overlay('Txxxxas' placing 'hom' from 2 for 4)（按位置覆盖片段）。

四、大小写：replace 是精确匹配

由于 replace 做的是子串精确匹配，大小写不同就不匹配。示例里正则用 [mN] 这种字符类来显式容纳两种写法，正好说明需要忽略大小写时要靠模式或先统一转换（先 lower() 再处理，或用带 i 标志的正则，具体能力依数据库而定）。批量清洗数据前，务必先 SELECT count(*) FROM t WHERE col LIKE '%目标串%' 用大小写敏感与不敏感两种方式各测一次，确认差异范围。

五、UPDATE 批量替换的安全流程

写操作比读函数更容易出事，建议固定四步：

• 先备份或确认有可回滚的时间点；
• 用 SELECT 预览：SELECT id, replace(col, '旧', '新') FROM t WHERE col LIKE '%旧%' LIMIT 200;
• 包在事务里执行，行数符合预期再提交：BEGIN; UPDATE ... ; （PG 可用 UPDATE ... RETURNING id 留痕）
• 大表分批，按主键区间逐段 UPDATE，避免一次锁全表与长事务膨胀。

别忘了给 UPDATE 加 WHERE，无条件全表替换会重写所有行并产生大量 WAL，即使值没变。

六、性能与定位技巧

在 WHERE 里用 col LIKE '%关键词%' 前置通配无法命中普通 B-tree 索引，会全表扫描。可行做法：把关键词匹配改成后缀索引或全文索引（PG 的 trigram / tsvector），或先在应用层用主键范围缩小候选集再替换。正则比字面替换更耗 CPU，能不用正则就不用。

七、常见问题 FAQ

问：为什么只改了一个位置？
答：正则版默认只替换首个匹配，需加全局标志；字面版 replace 才是一次全量。

问：替换后字段变短或变空？
答：八成误用了 translate，它在 from 长于 to 时会删除多余字符。

问：能替换 NULL 吗？
答：传入 NULL 结果仍是 NULL，要兜底需配合空值替换函数。

八、总结

SQL 文本替换的选择依据是：字面全量替换用 replace（Replaces all occurrences、精确匹配不分大小写宽容）；模式替换用 regexp_replace，且默认只换第一处、必须显式加 g 才全量，需要指定第几处时用 start 与 N（N 作参数名要加引号）；字符级映射与删除用 translate，注意 from 长于 to 会吞字符；批量 UPDATE 必须先预览、事务包裹、大表分批并带 WHERE。各家默认参数不同，跨库迁移前建议用官方文档核对并实测一次。

• sql
• 字符串函数
• 数据清洗