仅保留URL路径最后部分并替换其余内容的数据库查询方案

我之前刚好处理过类似的批量URL替换需求，针对你这种content字段里混杂其他内容、仅需替换URL前缀并保留末尾文件名的场景，下面分常用数据库给出具体实现方案：

核心思路 #

利用数据库的正则替换函数，匹配content字段中所有符合URL格式的字符串，提取出每个URL的末尾文件名部分（即最后一个/之后的内容），然后将原URL的前缀部分替换为你指定的新域名+目录。

1. MySQL 实现方案 #

MySQL 8.0+ 支持REGEXP_REPLACE函数，刚好能满足需求。假设你的新前缀是https://new-domain.com/new-dir/，可以用以下SQL：

-- 先测试替换效果，确认无误再执行更新
SELECT 
  content,
  REGEXP_REPLACE(
    content,
    'https?://[^/]+/.*?/([^/]+)$', -- 匹配URL，捕获最后一个/后的文件名
    'https://new-domain.com/new-dir/$1' -- 替换为新前缀+捕获的文件名
  ) AS updated_content
FROM your_table;

-- 确认测试结果正确后，执行批量更新
UPDATE your_table
SET content = REGEXP_REPLACE(
    content,
    'https?://[^/]+/.*?/([^/]+)$',
    'https://new-domain.com/new-dir/$1'
  );

正则说明：

• https?://：匹配http或https开头的URL
• [^/]+/：匹配原域名部分（直到第一个/结束）
• .*?/：匹配域名后的任意路径（非贪婪模式，直到最后一个/）
• ([^/]+)$：捕获最后一个/之后的文件名（直到字符串结尾）
• $1：引用捕获到的文件名部分

2. PostgreSQL 实现方案 #

PostgreSQL同样支持REGEXP_REPLACE，语法和MySQL略有差异，实现如下：

-- 测试替换效果
SELECT 
  content,
  REGEXP_REPLACE(
    content,
    'https?://[^/]+/.*?/([^/]+)$',
    'https://new-domain.com/new-dir/\1', -- PostgreSQL用\1引用捕获组
    'g' -- 全局替换，匹配content中的所有URL
  ) AS updated_content
FROM your_table;

-- 执行批量更新
UPDATE your_table
SET content = REGEXP_REPLACE(
    content,
    'https?://[^/]+/.*?/([^/]+)$',
    'https://new-domain.com/new-dir/\1',
    'g'
  );

注意事项 #

• 备份优先：批量更新前一定要备份数据表，或者先在测试环境验证SQL逻辑，避免不可逆的错误。
• 正则调整：如果你的URL有特殊格式（比如包含参数?），需要调整正则表达式，比如把([^/]+)$改成([^/?]+)来忽略参数部分，只保留文件名。
• 性能考虑：3000条数据量不大，但如果content字段内容很长，建议分批更新或者添加合适的索引提升效率。

内容的提问来源于stack exchange，提问作者andy