SQL REPLACE 函數實戰：批量更新字段部分字符串與 PHP 配合範例

函數語法

REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的新字符串

• 區分大小寫。
• from_str 不能為空字符串 (返回原值)。
• 不支援正則，需正則用 REGEXP_REPLACE (MySQL 8.0+)。

核心用法：UPDATE 批量替換

UPDATE table_name
SET column_name = REPLACE(column_name, 'old_text', 'new_text')
WHERE column_name LIKE '%old_text%';

WHERE 子句可利用索引快速定位需更新行，避免全表掃描。

完整實戰流程

1. 建表示例

CREATE TABLE `test` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(50) NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `test` (`url`) VALUES
('www.1aa.cm'), ('www.1aa.cm'), ('www.1aa.cm'), ('www.1aa.cm'),
('www.bKjia.c0m'), ('www.bKjia.c0m');

2. PHP 腳本執行替換

<?php
$mysqli = new mysqli('127.0.0.1', 'root', 'root', 'cnnet');
if ($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);

$old = '1aa.cm';
$new = 'bKjia.c0m';

// 1. 預覽受影響行
$preview = $mysqli->query("SELECT id, url FROM test WHERE url LIKE '%$old%'");
echo "將更新 {$preview->num_rows} 行：\n";
while ($row = $preview->fetch_assoc()) {
    echo "ID {$row['id']}: {$row['url']}\n";
}

// 2. 執行替換
$sql = "UPDATE test SET url = REPLACE(url, ?, ?) WHERE url LIKE ?";
$stmt = $mysqli->prepare($sql);
$like = "%$old%";
$stmt->bind_param('sss', $old, $new, $like);
$stmt->execute();
echo "實際更新行數: {$stmt->affected_rows}\n";

// 3. 驗證結果
$result = $mysqli->query("SELECT url FROM test");
while ($row = $result->fetch_assoc()) {
    echo $row['url'] . "\n";
}
?>

3. 執行結果

將更新 4 行：
ID 1: www.1aa.cm
ID 2: www.1aa.cm
ID 3: www.1aa.cm
ID 4: www.1aa.cm
實際更新行數: 4
www.bKjia.c0m
www.bKjia.c0m
www.bKjia.c0m
www.bKjia.c0m
www.bKjia.c0m
www.bKjia.c0m

進階技巧

性能與安全提示

1. 大表分批更新：WHERE id BETWEEN start AND end LIMIT 1000 循環提交。
2. 備份先行：CREATE TABLE test_bak AS SELECT * FROM test;。
3. 使用參數化查詢 (如上例 bind_param) 防注入。
4. 全文索引列替換後需 REPAIR TABLE 重建索引 (MyISAM)。