MySQL REPLACE 函數實戰：字段部分內容替換與批量更新技巧

MySQL REPLACE 函數實戰：字段部分內容替換與批量更新技巧

REPLACE(str, from_str, to_str) 是 MySQL 字符串處理的利器，可將字段中所有 from_str 替換為 to_str。本文通過實例演示單條更新、批量替換及前綴拼接場景。

基礎語法

UPDATE table_name 
SET column_name = REPLACE(column_name, 'old_substr', 'new_substr')
WHERE condition;

實例 1：刪除字段中特定子串

-- 測試表
CREATE TABLE `t` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain` TINYTEXT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t(domain) VALUES ('j054,j020,mc032,her302');

-- 刪除 ',her302'
UPDATE t SET domain = REPLACE(domain, ',her302', '') WHERE id = 1;
-- 結果: 'j054,j020,mc032'

實例 2：批量替換字段前綴 (圖片域名遷移)

SET @prefix = 'img.youacom/';
SELECT CONCAT(@prefix, SUBSTRING_INDEX(imgurl, '/', -1)) AS new_imgurl 
FROM ly_img;

SUBSTRING_INDEX(str, delim, -1) 取最後一段路徑，拼接新域名前綴，生成遷移後的完整 URL，可直接用於 UPDATE。

跨數據庫對比：FoxPro REPLACE 命令

FoxPro 原生 REPLACE 命令支持 STRTRAN() 函數：

REPLACE ALL field_name WITH STRTRAN(field_name, '中國人', '中國')
-- 參數: STRTRAN(cSource, cSearch, cReplace, nStart, nCount)

MySQL 無原生 STRTRAN，但 REPLACE 覆蓋 90% 場景；複雜模式可結合 REGEXP 或存儲函數實現。

性能與注意事項

• REPLACE 區分大小寫 (依賴字符集 Collation)。
• 大表批量更新建議分批提交，避免長事務鎖表：WHERE id BETWEEN x AND y LIMIT 1000。
• 需替換多個不規則模式時，考慮編寫存儲函數循環處理，或應用層處理後回寫。
• REPLACE 不支持正則表達式，MySQL 8.0+ 可用 REGEXP_REPLACE()。

擴展：多字段同步替換

UPDATE uc_user 
SET 
  mobile = REPLACE(REPLACE(...), '０', '0'),  -- 10層嵌套轉全角數字
  user_name = REPLACE(REPLACE(...), '０', '0')
WHERE mobile IS NOT NULL;

實際項目中，全角/半角、編碼轉換等通用清洗邏輯，強烈建議封裝為 決定性存儲函數，保證 SQL 可讀性與複用性。