MySQL 字符串查找替換與條件更新實戰技巧

場景描述

需求：將 address 欄位中的「中國」字樣移除，並根據地址前綴自動填寫 province_id。

方法一：REPLACE() 全量替換

UPDATE `table` SET `address` = REPLACE(`address`, '中國', '');

REPLACE(str, from_str, to_str) 會將所有匹配的 from_str 替換為 to_str，適用於批量清洗髒數據。

方法二：LEFT() 前綴匹配更新省份

UPDATE `table` SET `province_id` = 11 WHERE LEFT(`address`, 2) = '福建';

LEFT(str, len) 取字串左側 len 個字符。此處取前 2 個字符判斷是否為「福建」，匹配則設省份 ID 為 11（需對應省份表主鍵）。

進階組合：單條 SQL 完成清洗與分省

UPDATE `table`
SET
  `address` = REPLACE(`address`, '中國', ''),
  `province_id` = CASE
    WHEN LEFT(`address`, 2) = '福建' THEN 11
    WHEN LEFT(`address`, 2) = '廣東' THEN 12
    WHEN LEFT(`address`, 2) = '浙江' THEN 13
    ELSE 0
  END;

效能提示

• 大表更新建議分批處理（加 LIMIT 或按主鍵範圍分塊），避免長事務鎖表。
• 為 address 加索引可加速 LEFT() 條件掃描（MySQL 8.0+ 支援函數索引）。
• 執行前先用 SELECT 驗證匹配行數，確認無誤再執行 UPDATE。

相關函數速查