MySQL 5.7中如何用SQL批量替换wp_posts表内特定格式链接

核心需求 #

将wp_posts表post_content字段中所有形如http://example.com/component/tags/tag/[1-3位数字]-[文本].html的链接，替换为http://example.com/tag/[文本].html，其余内容保持不变。

实现SQL语句 #

MySQL 5.7不支持REGEXP_REPLACE，可通过字符串函数组合实现需求：

-- 先执行查询验证替换效果，确认无误后再执行更新
SELECT 
  post_content,
  REPLACE(
    post_content,
    CONCAT('http://example.com/component/tags/tag/', SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1), '.html'),
    CONCAT('http://example.com/tag/', SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1), LOCATE('-', SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1)) + 1), '.html')
  ) AS replaced_content
FROM wp_posts
WHERE post_content REGEXP 'http://example.com/component/tags/tag/[0-9]{1,3}-[a-zA-Z0-9_-]+\\.html';

-- 确认效果后执行更新
UPDATE wp_posts
SET post_content = 
  REPLACE(
    post_content,
    CONCAT('http://example.com/component/tags/tag/', SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1), '.html'),
    CONCAT('http://example.com/tag/', SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1), LOCATE('-', SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1), '.html', 1)) + 1), '.html')
  )
WHERE post_content REGEXP 'http://example.com/component/tags/tag/[0-9]{1,3}-[a-zA-Z0-9_-]+\\.html';

语句说明 #

1. 筛选条件：用REGEXP精准匹配目标链接格式，确保只处理符合「1-3位数字+连字符+文本+.html」规则的链接，避免误操作。
2. 字符串截取逻辑：
   • SUBSTRING_INDEX(post_content, 'http://example.com/component/tags/tag/', -1)：截取目标前缀后的所有内容
   • SUBSTRING_INDEX(..., '.html', 1)：提取出前缀后到.html之间的部分（如15-text）
   • LOCATE('-', ...)：定位连字符的位置，SUBSTR(..., LOCATE(...) +1)获取连字符后的文本内容（如text）
3. 替换拼接：将原链接片段替换为新的格式，拼接成最终的目标链接。

注意事项 #

• 执行更新前务必备份数据表，或者先通过SELECT语句验证替换结果，避免数据错误。
• 如果你的域名或链接前缀有变化，需对应调整语句中的字符串常量。

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