您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 批量更新欄位值：SET col1=col2 跨欄位複製與 REPLACE 字串替換實戰

編輯：MySQL 綜合教學

MySQL 支援單語句將某欄位值批量複製至另一欄位（SET target = source），或用 REPLACE() 進行字串級批量替換。本文實戰演示「郵件激活重置：yz=regdate」與「字串批量替換：'1'→'2'」兩大場景，含執行後修正策略與效能注意事項。

場景一：跨欄位批量複製（SET 目標欄位 = 來源欄位）

業務背景

會員表 pw_members，欄位 yz 存激活狀態（1=已激活、時間戳=待激活）。
需求：將所有會員 yz 重置為 regdate（註冊時間戳），強制下次登入重新激活郵件。

單語句完成

UPDATE pw_members SET yz = regdate;

效果：每行 yz 變為該行 regdate 值，無需應用層迴圈、效能極高。
副作用：創始人/管理員帳號也被重置，需事後手動修正。

事後修正：恢復特定帳號正常狀態

-- 方法 1：phpMyAdmin 瀏覽模式逐行編輯 yz=1
-- 方法 2：SQL 精確修正
UPDATE pw_members SET yz = 1 WHERE uid IN (1, 2, 100);  -- 管理員 UID 列表

-- 方法 3：後台會員管理介面手動激活

場景二：字串級批量替換（REPLACE 函數）

UPDATE pw_members
SET yz = REPLACE(yz, '1', '2')
WHERE yz LIKE '%1%';

REPLACE(str, from_str, to_str)：將字串中所有 from_str 替換為 to_str。
WHERE yz LIKE '%1%'：縮小掃描範圍，避免全表掃描（需索引支援）。
適用：格式修正、舊域名替換、敏感字過濾。

效能與安全注意事項

風險	影響	對策
全表掃描鎖表	大表長時間阻塞寫入	加 `WHERE` 縮小範圍、分批 `LIMIT 1000` 迴圈、低峰期執行
Binlog 膨脹	主從延遲、磁碟佔用	分批提交、`SET sql_log_bin=0`（謹慎，僅非關鍵資料）
誤更新無法回滾	資料永久損壞	先 `SELECT` 驗證影響行數、備份表 `CREATE TABLE bak LIKE tbl; INSERT INTO bak SELECT * FROM tbl;`
觸發器連鎖反應	非預期副作用	確認無 `AFTER UPDATE` 觸發器或暫時禁用

大表分批更新範本

DELIMITER //
CREATE PROCEDURE batch_update_yz()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE last_id INT DEFAULT 0;
    WHILE NOT done DO
        START TRANSACTION;
        UPDATE pw_members
        SET yz = regdate
        WHERE uid > last_id
        ORDER BY uid LIMIT 1000;
        SET last_id = (SELECT MAX(uid) FROM pw_members WHERE uid > last_id LIMIT 1);
        IF ROW_COUNT() = 0 THEN SET done = 1; END IF;
        COMMIT;
        DO SLEEP(0.05);
    END WHILE;
END//
DELIMITER ;

CALL batch_update_yz();