萬盛學電腦網

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

SQL REPLACE 函數實戰:批量清洗欄位中特定字串(含編碼陷阱)

一行 SQL 解決髒資料:REPLACE() 函數批量替換欄位內容

採集/匯入資料常帶入垃圾字串(如 ``)。MySQL/SQL Server/Oracle/PostgreSQL 皆支援 REPLACE(str, from_str, to_str),可直接在 UPDATE 中批量清洗,避免逐筆程式處理。

標準語法

UPDATE table_name
SET column_name = REPLACE(column_name, '搜尋字串', '替換字串')
WHERE column_name LIKE '%搜尋字串%';  -- 加 WHERE 可減少無異動列的寫入開銷

實戰範例:清除文章標題中的垃圾圖片標籤

-- MySQL / SQL Server / PostgreSQL 通用
UPDATE `article`
SET `title` = REPLACE(`title`, '', '')
WHERE `title` LIKE '%%';

執行後,所有標題中的該片段瞬間清空,2000+ 筆資料秒級完成。

進階用法:巢狀 REPLACE 多重替換

UPDATE `content`
SET `body` = REPLACE(
                REPLACE(
                  REPLACE(`body`, ' ', ' '),
                  '
', '\n'), '

', '') WHERE `body` LIKE '% %' OR `body` LIKE '%
%' OR `body` LIKE '%

%';

編碼與大小寫陷阱(必讀)

資料庫大小寫敏感度編碼注意
MySQL (預設排序規則)不敏感(ci 結尾)搜尋字串需與儲存編碼一致(utf8mb4 推薦)
SQL Server (預設)不敏感 (CI)N'前綴處理 Unicode:REPLACE(col, N'字串', N'')
Oracle敏感若需不敏感:REPLACE(UPPER(col), UPPER('搜尋'), '替換')
PostgreSQL敏感使用 regexp_replace(col, '搜尋', '替換', 'gi') 達成不敏感

效能提示

  1. 加 WHERE 過濾:只更新真正包含目標字串的列,減少 WAL/Redo log 產生、鎖持有時間。
  2. 大表分批次:WHERE id BETWEEN 1 AND 10000 迴圈執行,避免長事務鎖表、主從延遲。
  3. 索引失效:REPLACE 導致無法使用該欄位索引,若頻繁查詢該欄位,考慮建立計算欄位/全文索引/觸發器同步乾淨版本。

替代方案:應用層清洗 vs 資料庫層清洗

  • 資料庫層:一次性修正歷史髒資料、無應用程式部署窗口、DBA 直接操作。
  • 應用層:新寫入資料即時清洗(輸入驗證/過濾器)、複雜邏輯(正則、HTML 解析)、需單元測試覆蓋。
  • 最佳實務:寫入端攔截(應用層)+ 歷史資料一次性修正(資料庫層),雙管齊下。
copyright © 萬盛學電腦網 all rights reserved