資料遷移或系統整合時，常遇到欄位內夾雜不規則路徑，需批量替換為統一格式。MySQL 內建 LOCATE() 定位子串位置、REPLACE() 全量替換，配合 SUBSTRING() 即可靈活處理「前綴固定、中間 ID 變動、後綴固定」的模式化字串。本文以實戰案例示範 4000 條記錄的路徑重寫。

場景還原

表 field_data_body，欄位 body_value 存放富文本內容，夾雜兩類歷史路徑：

• /eapdomain/UserFiles/1073/Image/... （1073 為用戶 ID，動態變化）
• /eapdomain/UserFiles/1073/File/...

目標：統一替換為 /sites/default/files/old/image/ 與 /sites/default/files/old/file/。

核心函數速查

單條 UPDATE 解決方案

利用 LOCATE 定位固定前綴 /eapdomain/UserFiles/ 與固定後綴 /Image/（或 /File/），計算中間動態段長度，提取完整舊路徑並整體替換：

-- 替換 Image 路徑
UPDATE field_data_body
SET body_value = REPLACE(
    body_value,
    SUBSTRING(
        body_value,
        LOCATE('/eapdomain/UserFiles/', body_value),
        LOCATE('/Image/', body_value) + 7 - LOCATE('/eapdomain/UserFiles/', body_value)
    ),
    '/sites/default/files/old/image/'
)
WHERE body_value LIKE '%/eapdomain/UserFiles/%/Image/%';

-- 替換 File 路徑
UPDATE field_data_body
SET body_value = REPLACE(
    body_value,
    SUBSTRING(
        body_value,
        LOCATE('/eapdomain/UserFiles/', body_value),
        LOCATE('/File/', body_value) + 6 - LOCATE('/eapdomain/UserFiles/', body_value)
    ),
    '/sites/default/files/old/file/'
)
WHERE body_value LIKE '%/eapdomain/UserFiles/%/File/%';

關鍵邏輯拆解

1. LOCATE('/eapdomain/UserFiles/', body_value) → 起始位置
2. LOCATE('/Image/', body_value) + 7 → 結束位置（含 /Image/ 7 字元）
3. 相減得長度 → SUBSTRING 精準切出完整舊路徑
4. REPLACE 全文替換該路徑為新前綴
5. WHERE 子句先過濾，避免全表掃描與無意義更新

進階技巧與避坑

• 多次出現： REPLACE 預設替換所有匹配，單條 SQL 即可覆蓋同一欄位多個舊路徑。
• 大小寫敏感： MySQL 預設不區分大小寫，若需嚴格匹配，用 COLLATE utf8mb4_bin。
• 效能優化： 4000 行秒級完成；百萬行建議分批 WHERE id BETWEEN ... AND ... 並加上 LIMIT，避免長鎖表。
• 備份先行： 生產庫務必先 mysqldump 或建立臨時表驗證。

UPDATE target_table
SET target_col = REPLACE(
    target_col,
    SUBSTRING(
        target_col,
        LOCATE('fixed_prefix', target_col),
        LOCATE('fixed_suffix', target_col) + CHAR_LENGTH('fixed_suffix') - LOCATE('fixed_prefix', target_col)
    ),
    'new_unified_prefix'
)
WHERE target_col LIKE '%fixed_prefix%fixed_suffix%';