萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 字串替換技巧:REPLACE 函數與模擬正則替換實戰

MySQL 字串替換核心:REPLACE 函數詳解

在 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:新字串

實戰演練:將 type 欄位前綴 78 改為 79

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 起算),找不到回傳 0
  • SUBSTRING(str, pos, len):從位置 pos 截取長度 len 的子字串

實戰案例:清除 XML 標籤內容

資料表 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
    ), 
    ''
);

邏輯解析:

  1. LOCATE('<contact>', name) 找到起始標籤位置
  2. LOCATE('</contact>', name) 找到結束標籤位置
  3. 兩者相減加上結束標籤長度(10 字元),得到完整區段長度
  4. SUBSTRING 擷取該區段,REPLACE 以空字串取代

執行結果

+----+-------------------+
| id | name              |
+----+-------------------+
| 1  | sdfsfactsldjfsld  |
| 2  | sdfsfactsldjfsld  |
| 3  | sdfsfactsldjfsld  |
| 4  | sdfsfactsldjfsld  |
+----+-------------------+

標籤及內容已完整移除。注意:此法僅處理單一出現的標籤對;若同一欄位重複出現,需分多次執行或改用程式語言處理。

總結

  • 簡單替換:REPLACE() 配合 WHERE 最快捷
  • 模式化替換:善用 LOCATE() + SUBSTRING() 組合擷取動態區段
  • 複雜正則:建議在應用層(Python、PHP 等)處理後再寫回資料庫

掌握這些技巧,能大幅提升資料清洗與維護效率。

copyright © 萬盛學電腦網 all rights reserved