萬盛學電腦網

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

MySQL 字串替換實戰:REPLACE 函數與 UPDATE 聯用的完整指南

核心語法

MySQL 中批量替換欄位內容的標準寫法:

UPDATE `table_name`
SET `column_name` = REPLACE(`column_name`, '舊字串', '新字串')
[WHERE condition];
  • REPLACE(str, from_str, to_str):返回將 str 中所有 from_str 替換為 to_str 後的新字串。
  • 若 from_str 不存在,原值不變。
  • 區分大小寫(依欄位 Collation)。

實戰範例

1. 批量替換文章內容中的舊域名

UPDATE `caiji_wcontent`
SET `wcontent` = REPLACE(`wcontent`, 'WwW.111cn.net.NeT', 'm.111cn.net');

UPDATE `caiji_wcontent`
SET `wcontent` = REPLACE(`wcontent`, 'www.111cn.net.net', 'm.111cn.net');

2. 針對特定分類文章移除 HTML 標籤

UPDATE `article`
SET `body` = REPLACE(`body`, '</div>', ' ')
WHERE `typeid` = 21;

3. 更新配置表中的特定值

UPDATE `users_settings`
SET `ConfigValue` = REPLACE(`ConfigValue`, 'fromstr', 'tostr')
WHERE `ConfigName` = 'accesslist';

進階技巧

多重替換鏈式執行

UPDATE `content`
SET `body` = REPLACE(
                REPLACE(
                    REPLACE(`body`, 'old_a', 'new_a'),
                'old_b', 'new_b'),
            'old_c', 'new_c')
WHERE `id` < 10000;

配合正則表達式(MySQL 8.0+)

MySQL 8.0 引入 REGEXP_REPLACE,支援正則替換:

UPDATE `t`
SET `col` = REGEXP_REPLACE(`col`, '[0-9]{4}-[0-9]{2}-[0-9]{2}', 'YYYY-MM-DD')
WHERE `col` REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}';

MySQL 常用字串函數速查表

函數用途範例
CONCAT(s1,s2...)拼接字串CONCAT('My','SQL') → 'MySQL'
LENGTH(str)位元組長度LENGTH('中文') → 6 (UTF-8)
CHAR_LENGTH(str)字元長度CHAR_LENGTH('中文') → 2
SUBSTRING(str,pos,len)截取子串SUBSTRING('abcdef',2,3) → 'bcd'
LOCATE(sub,str)查找子串位置LOCATE('bar','foobar') → 4
TRIM([BOTH|LEADING|TRAILING] rem FROM str)去除首尾字元TRIM(' abc ') → 'abc'
LOWER/UPPER(str)大小寫轉換UPPER('mysql') → 'MYSQL'
REPLACE(str,from,to)全量替換REPLACE('a b c',' ','-') → 'a-b-c'
INSERT(str,pos,len,newstr)指定位置替換INSERT('Quadratic',3,4,'What') → 'QuWhattic'
REVERSE(str)反轉字串REVERSE('abc') → 'cba'

效能與避坑指南

  1. 避免全表掃描: 大表替換務必加 WHERE 條件縮小範圍,或分批次處理(LIMIT 1000 迴圈)。
  2. 索引失效: REPLACE 導致欄位值變更,若該欄位有索引,更新後索引自動維護,但大量更新會產生大量 Binlog,影響主從同步延遲。
  3. 事務包裹: 生產環境建議 START TRANSACTION; ... COMMIT;,便於回滾。
  4. 備份先行: 執行前先 mysqldump 相關表,或建立臨時表備份 CREATE TABLE bak AS SELECT * FROM t WHERE ...。
  5. 編碼一致: 確保連線字符集(SET NAMES utf8mb4)與表欄位 Collation 一致,避免替換後出現亂碼。

總結

UPDATE ... SET col = REPLACE(col, 'old', 'new') 是 MySQL 字串批量替換的標準高效寫法。結合 WHERE 條件控制範圍、REGEXP_REPLACE 處理複雜模式、事務保障原子性,即可安全完成從 CMS 域名遷移、敏感詞過濾、格式標準化等各類清洗任務。

copyright © 萬盛學電腦網 all rights reserved