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 不存在,原值不變。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');
UPDATE `article`
SET `body` = REPLACE(`body`, '</div>', ' ')
WHERE `typeid` = 21;
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 引入 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}';
| 函數 | 用途 | 範例 |
|---|---|---|
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' |
WHERE 條件縮小範圍,或分批次處理(LIMIT 1000 迴圈)。REPLACE 導致欄位值變更,若該欄位有索引,更新後索引自動維護,但大量更新會產生大量 Binlog,影響主從同步延遲。START TRANSACTION; ... COMMIT;,便於回滾。mysqldump 相關表,或建立臨時表備份 CREATE TABLE bak AS SELECT * FROM t WHERE ...。SET NAMES utf8mb4)與表欄位 Collation 一致,避免替換後出現亂碼。UPDATE ... SET col = REPLACE(col, 'old', 'new') 是 MySQL 字串批量替換的標準高效寫法。結合 WHERE 條件控制範圍、REGEXP_REPLACE 處理複雜模式、事務保障原子性,即可安全完成從 CMS 域名遷移、敏感詞過濾、格式標準化等各類清洗任務。