在 MySQL 資料庫操作中,字串替換是常見需求。最直接的方法是結合 UPDATE 與內建函數 REPLACE(),配合 WHERE 子句精準控制影響範圍。
REPLACE(str, from_str, to_str)
功能:在字串 str 中尋找所有 from_str,並以 to_str 取代。
UPDATE `table_name`
SET `field_name` = REPLACE(`field_name`, 'from_str', 'to_str')
WHERE `field_name` LIKE '%from_str%';
table_name:資料表名稱field_name:目標欄位from_str:欲替換的原字串to_str:新字串mysql> SELECT id, type FROM items LIMIT 10;
+--------+--------+
| id | type |
+--------+--------+
| 0001 | 780000 |
| 0002 | 780000 |
...
+--------+--------+
mysql> UPDATE items SET type = REPLACE(type, '78', '79');
Query OK, 17536 rows affected (0.72 sec)
mysql> SELECT id, type FROM items LIMIT 10;
+--------+--------+
| id | type |
+--------+--------+
| 0001 | 790000 |
| 0002 | 790000 |
...
+--------+--------+
執行結果顯示 17,536 筆資料成功更新,驗證替換邏輯正確。
MySQL 的 REPLACE() 不支援正則表達式。若需刪除 <contact>...</contact> 之間的動態內容,可結合 LOCATE() 與 SUBSTRING() 實現:
LOCATE(substr, str):回傳子字串首次出現位置(從 1 起算),找不到回傳 0SUBSTRING(str, pos, len):從位置 pos 截取長度 len 的子字串資料表 mt2:
+----+------------------------------------------+
| id | name |
+----+------------------------------------------+
| 1 | sdfsf<contact>beijing</contact>sldjfsld |
| 2 | sdfsf<contact>shanghai</contact>sldjfsld |
| 3 | sdfsf<contact>jn</contact>sldjfsld |
| 4 | sdfsf<contact>qd</contact>sldjfsld |
+----+------------------------------------------+
目標:移除 <contact> 至 </contact> 間的所有字元。
UPDATE mt2
SET name = REPLACE(
name,
SUBSTRING(
name,
LOCATE('<contact>', name),
LOCATE('</contact>', name) - LOCATE('<contact>', name) + 10
),
''
);
邏輯解析:
LOCATE('<contact>', name) 找到起始標籤位置LOCATE('</contact>', name) 找到結束標籤位置SUBSTRING 擷取該區段,REPLACE 以空字串取代+----+-------------------+
| id | name |
+----+-------------------+
| 1 | sdfsfactsldjfsld |
| 2 | sdfsfactsldjfsld |
| 3 | sdfsfactsldjfsld |
| 4 | sdfsfactsldjfsld |
+----+-------------------+
標籤及內容已完整移除。注意:此法僅處理單一出現的標籤對;若同一欄位重複出現,需分多次執行或改用程式語言處理。
REPLACE() 配合 WHERE 最快捷LOCATE() + SUBSTRING() 組合擷取動態區段掌握這些技巧,能大幅提升資料清洗與維護效率。