MySQL中REPLACE函数用法详解与实战案例从入门到精通

一、前言

在日常的MySQL开发工作中，清理字段内容是一个非常频繁的操作：去除固定前缀、替换敏感字符、清洗URL多余字符串、手机号脱敏、日志文本清洗……几乎每天都会遇到这些场景。

REPLACE() 是MySQL内置的字符串替换函数，使用起来确实简单，基本没有什么学习门槛。但很多开发者往往只停留在“会用”的层面，对其底层执行机制、容易踩的坑、以及和 SUBSTR 截取函数之间的性能差异，可能并不了解。

本文将从 REPLACE 的基础语法开始，结合接口日志处理的真实业务场景，逐步讲解它的使用技巧、常见陷阱以及性能对比。先说几个核心判断：它在固定文本清理时确实方便，但底层采用全字符串遍历，性能弱于按位置截取；它不支持正则表达式，大小写敏感，还会导致索引失效。掌握这些，才能用得稳、用得准。

二、函数基础语法

语法格式

REPLACE(str, find_string, replace_with)

参数说明

1. str：原始字符串，通常来自数据表的某个字段；
2. find_string：需要被查找并替换掉的子串；
3. replace_with：用来替换的新字符串，传空字符串时，相当于直接删除目标子串。

核心特性

1. 全局匹配：它会将字符串中所有匹配到的子串全部替换，不只是替换第一个；
2. 区分大小写：英文大小写不一样，匹配就不会成功；
3. 只做字符替换，不支持正则表达式；
4. 不会修改原始表数据，仅在查询结果中临时处理字符串。

三、基础使用示例

示例1：删除固定前缀（业务中常见场景）

把固定URL前缀直接替换为空，从而提取后面的参数值：

SELECT REPLACE('/openapi/verify_code_identify/?verify_idf_id=16','/openapi/verify_code_identify/?verify_idf_id=','');-- 返回结果：16

示例2：字符替换，修改指定内容

-- 把逗号替换成竖线SELECT REPLACE('张三,李四,王五', ',', '|');-- 结果：张三|李四|王五

示例3：清理多余符号，去除空格

SELECT REPLACE(' 1 2 3 ', ' ', '');-- 结果：123

示例4：多段清理嵌套使用

多层嵌套可以连续替换多个不同字符：

SELECT REPLACE(REPLACE(path,' ',''),'&','');

四、业务实战：接口日志提取URL参数

业务场景

数据表 openapi_apilog 中，path字段存储了接口请求地址：/openapi/verify_code_identify/?verify_idf_id=16。现在需要提取末尾的数字16。

使用REPLACE实现

SELECTlogin_ip,`path`,price,creat_time,-- 移除固定前缀SUBSTRING_INDEX(REPLACE(`path`, '/openapi/verify_code_identify/?verify_idf_id=', ''), '&', 1) AS verify_idf_idFROM openapi_apilog WHERE `user_id` = '{}' AND `date` = '{}';

这里的实现逻辑是：先用 REPLACE 删除整条固定前缀，再用 SUBSTRING_INDEX 截断后续的 & 及其他参数，避免URL携带多个参数导致数据错乱。

五、REPLACE 与 SUBSTR 性能深度对比（重点）

底层执行逻辑

• REPLACE：会从头到尾逐字符遍历整个字符串，不断匹配目标子串，匹配成功后执行字符删除与重组。字符串越长、数据量越大，CPU消耗就越高。
• SUBSTR + LENGTH：只计算一次前缀长度，直接定位截取起始下标，仅做指针偏移截取，不需要逐字符遍历匹配，运算开销极小。

效率排名

SUBSTR截取 > REPLACE替换 > 多层SUBSTRING_INDEX分割

在百万行数据量的测试中，REPLACE 的执行耗时比固定位置截取高出 20% 到 50%。只有当待删除前缀不固定、无法确定起始下标时，才适合使用 REPLACE。

六、高频踩坑总结

坑1：全局全部替换，容易误删内容

REPLACE 会替换所有匹配文本。如果原始字符串多处包含目标子串，会全部清空，造成数据异常。来看一个例子：

REPLACE('id=16&id=17','id=','')

两条参数都会被处理，很容易产生脏数据。

坑2：不支持正则

REPLACE 只能写固定文本，不能用正则匹配模糊内容。如果需要模糊替换，得改用 REGEXP_REPLACE（MySQL 8.0及以上版本才支持）。

坑3：字段上使用函数，索引失效

无论是 REPLACE 还是 SUBSTR，只要在查询字段上包裹了函数，就无法命中索引。对于大批量的日志查询，优化方案是：把URL参数预先拆分存入单独字段，避免在运行时处理字符串。

坑4：严格区分大小写

REPLACE('Verify_ID=16','verify_id=','')

大小写不一致，匹配不到内容，替换就会失效。

坑5：无法反向截取

REPLACE 只能依靠文本匹配来操作，不能像 SUBSTR 那样通过下标反向截取末尾字符。

七、拓展：REPLACE的两种使用场景

场景一：SELECT查询中临时处理字符串（只读清洗）

这就是上面用到的写法，只改变查询结果，原表数据保持不变。

场景二：UPDATE更新数据表（修改原始数据）

批量清理字段内容：

UPDATE openapi_apilog SET path = REPLACE(path, '/openapi/verify_code_identify/?verify_idf_id=', '')WHERE date = '2026-06-30';

注意：执行 UPDATE 之前，一定要先用 SELECT 验证一下数据，避免批量改错。

八、REPLACE 适用场景总结

适合的场景：

1. 需要删除或替换一段固定不变的文本；
2. 字符串前缀不统一，无法用固定下标截取；
3. 批量清洗字段里的特殊符号、空格、多余字符。

不推荐的场景：

1. 前缀完全固定的URL截取（优先用 SUBSTR，性能更好）；
2. 千万级大表批量查询，会拉高CPU开销；
3. 需要模糊匹配、正则替换（改用 REGEXP_REPLACE）。

九、总结

1. REPLACE(str, 旧内容, 新内容)，匹配所有子串并全局替换，置空文本就相当于删除字符串；
2. 固定文本清理时简单易用，但底层是全字符串遍历，性能弱于按位置截取的 SUBSTR；
3. 处理URL参数时，REPLACE 配合 SUBSTRING_INDEX 可以保证兼容性；
4. 避免在索引字段上嵌套函数，防止索引失效；批量更新前务必校验数据。