MySQL REPLACE 函數實戰：欄位部分內容替換與資料清洗

REPLACE 語法

REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的字串
-- 區分大小寫，不支援正則表達式

實戰範例

建立測試表與資料

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'

常見資料清洗場景

-- 1. 統一電話號碼格式
UPDATE users SET phone = REPLACE(REPLACE(phone, '-', ''), ' ', '');

-- 2. 移除 HTML 標籤（簡易）
UPDATE content SET body = REPLACE(body, '
', '\n');

-- 3. 修正錯誤編碼字元
UPDATE products SET description = REPLACE(description, '’', '’');

-- 4. 批次更新網域
UPDATE sites SET url = REPLACE(url, 'https://old.com', 'https://new.com');

進階：多重替換與條件替換

-- 巢狀 REPLACE：依序執行
UPDATE t SET col = REPLACE(REPLACE(REPLACE(col, 'a', 'x'), 'b', 'y'), 'c', 'z');

-- 條件替換：僅含特定字串的行
UPDATE t SET col = REPLACE(col, 'old', 'new') WHERE col LIKE '%old%';

效能提示

• REPLACE 會掃描整欄位，大表更新前加 WHERE 縮小範圍
• 需正則替換請用 REGEXP_REPLACE (MySQL 8.0+) 或應用層處理
• 大量更新分批 LIMIT 避免長鎖表

小結

REPLACE(str, from, to) 是欄位字串替換的利器，語法簡單、效能尚可。資料清洗、格式統一、批次修正網域/電話等場景極好用。大表謹記 加 WHERE、分批次、備份先行。