如何批量替换数据库表字段中竖线前的错误文件路径？

核心思路是自动拆分doc_filename字段的路径部分，按预设规则批量判断是否合规，不需要手动枚举所有错误路径即可完成批量替换，通用逻辑如下：

• 拆分doc_filename，分别取出|分隔符前的路径段、分隔符后的剩余内容
• 按业务规则判断路径段是否合规（比如是否以正确根路径开头、是否包含非法字符等）
• 不合规的记录，将正确路径和原剩余内容拼接后更新到字段中

MySQL 实现示例 #

假设你的正确根路径为/correct/root/path/，替换规则为所有不以正确根路径开头的路径都统一替换为正确根路径+原路径的最后一级文件名，语句如下：

UPDATE content_history
SET doc_filename = CONCAT(
  '/correct/root/path/', -- 替换为你的实际正确路径
  SUBSTRING_INDEX(SUBSTRING_INDEX(doc_filename, '|', 1), '/', -1),
  '|',
  SUBSTRING_INDEX(doc_filename, '|', -1)
)
WHERE SUBSTRING_INDEX(doc_filename, '|', 1) NOT LIKE '/correct/root/path/%';

如果需要直接把所有不合规的路径段替换为固定正确路径，可简化为：

UPDATE content_history
SET doc_filename = CONCAT(
  '/your/fixed/correct/path', -- 替换为你的固定正确路径
  '|',
  SUBSTRING_INDEX(doc_filename, '|', -1)
)
WHERE SUBSTRING_INDEX(doc_filename, '|', 1) NOT LIKE '/your/fixed/correct/path%';

PostgreSQL 实现示例 #

UPDATE content_history
SET doc_filename = CONCAT(
  '/correct/root/path/',
  SPLIT_PART(SPLIT_PART(doc_filename, '|', 1), '/', -1),
  '|',
  SPLIT_PART(doc_filename, '|', 2)
)
WHERE SPLIT_PART(doc_filename, '|', 1) NOT LIKE '/correct/root/path/%';

SQL Server 实现示例 #

UPDATE content_history
SET doc_filename = '/correct/root/path/' + 
  RIGHT(LEFT(doc_filename, CHARINDEX('|', doc_filename)-1), CHARINDEX('/', REVERSE(LEFT(doc_filename, CHARINDEX('|', doc_filename)-1)))-1)
  + '|' +
  STUFF(doc_filename, 1, CHARINDEX('|', doc_filename), '')
WHERE LEFT(doc_filename, CHARINDEX('|', doc_filename)-1) NOT LIKE '/correct/root/path/%';

注意事项 #

• 执行UPDATE前先执行SELECT查询验证替换结果是否符合预期，以MySQL为例：

SELECT 
  doc_filename AS 原始值,
  CONCAT('/correct/root/path/', SUBSTRING_INDEX(SUBSTRING_INDEX(doc_filename, '|', 1), '/', -1), '|', SUBSTRING_INDEX(doc_filename, '|', -1)) AS 替换后值
FROM content_history
WHERE SUBSTRING_INDEX(doc_filename, '|', 1) NOT LIKE '/correct/root/path/%';

• 建议更新前开启事务，确认更新行数和结果无误后再提交，出现异常可直接回滚。

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