語法說明
REPLACE(str, from_str, to_str)
-- 回傳將 str 中所有 from_str 替換為 to_str 後的字串
UPDATE table_name
SET column_name = REPLACE(column_name, '舊字串', '新字串')
WHERE column_name LIKE '%舊字串%';
完整實作範例
1. 建立測試資料
CREATE DATABASE cnnet;
USE cnnet;
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`url` VARCHAR(50) NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM;
INSERT INTO `test` (`url`) VALUES
('www.1aa.cm'), ('www.1aa.cm'), ('www.1aa.cm'), ('www.1aa.cm'),
('www.update8.com'), ('www.update8.com');
2. 確認目標資料
SELECT * FROM test WHERE url LIKE '%1aa.cm%';
/* 結果：
www.1aa.cm
www.1aa.cm
www.1aa.cm
www.1aa.cm */
3. 執行替換
UPDATE test
SET url = REPLACE(url, '1aa.cm', 'update8.com')
WHERE url LIKE '%1aa.cm%';
4. 驗證結果
SELECT * FROM test;
/* 結果：
www.update8.com
www.update8.com
www.update8.com
www.update8.com
www.update8.com
www.update8.com */
進階應用場景
| 場景 | SQL 範例 |
|---|
| 移除 HTML 標籤 | UPDATE content SET body = REPLACE(body, '<br>', '\n'); |
| 統一電話格式 | UPDATE users SET phone = REPLACE(REPLACE(phone, '-', ''), ' ', ''); |
| 修正錯誤域名 | UPDATE logs SET referer = REPLACE(referer, 'https://old.com', 'https://new.com'); |
| 清理特殊字元 | UPDATE import SET data = REPLACE(data, CHAR(0), ''); |
效能注意事項
- WHERE 條件必要：避免全表掃描與不必要的寫入。
- 大量資料分批處理：配合
LIMIT 迴圈，避免長鎖表。
- 備份先行：大量更新前務必備份或建立備份表。
- MySQL 8.0+ 可使用
REGEXP_REPLACE 處理複雜模式。
