MySQL 觸發器實現字段級防篡改：BEFORE UPDATE 鎖定核心欄位（姓名、手機、郵箱、密碼、餘額）不可修改

業務需求：某些核心欄位（實名信息、聯繫方式、密碼、賬戶餘額）一旦寫入**永不允許修改**，但允許更新其他欄位。MySQL 觸發器可在行級攔截 UPDATE，將核心欄位強制恢復為舊值，實現「字段級 WORM（Write Once Read Many）」。

方案一：會員表核心信息防篡改

DROP TRIGGER IF EXISTS `trg_members_immutable`;
DELIMITER //
CREATE TRIGGER `trg_members_immutable`
BEFORE UPDATE ON `members`
FOR EACH ROW
BEGIN
  -- 強制恢復核心欄位為舊值
  SET NEW.`name` = OLD.`name`;
  SET NEW.`cellphone` = OLD.`cellphone`;
  SET NEW.`email` = OLD.`email`;
  SET NEW.`password` = OLD.`password`;
END//
DELIMITER ;

• BEFORE UPDATE：在寫入存儲引擎前修改 NEW 行，無額外 I/O。
• FOR EACH ROW：逐行生效，批量 UPDATE 同樣攔截。
• 應用層嘗試更新上述欄位時，**語句執行成功、受影響行數為 1，但數據實際未變**，無報錯，業務碼無需改動。

方案二：賬戶餘額防篡改（只允許通過流水表增減）

CREATE TABLE `account` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user` VARCHAR(50) NOT NULL DEFAULT '0',
  `cash` DECIMAL(18,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 業務只能 INSERT 流水，餘額由觸發器/應用層事務維護
INSERT INTO `account` (`user`, `cash`) VALUES ('neo', -10), ('neo', -5), ('neo', 30), ('neo', -20);

-- 防篡改觸發器
DROP TRIGGER IF EXISTS `trg_account_cash_immutable`;
DELIMITER //
CREATE TRIGGER `trg_account_cash_immutable`
BEFORE UPDATE ON `account`
FOR EACH ROW
BEGIN
  SET NEW.`cash` = OLD.`cash`;  -- 任何直接 UPDATE cash 皆無效
END//
DELIMITER ;

進階：審計日誌記錄嘗試修改行為

CREATE TABLE `audit_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `table_name` VARCHAR(64) NOT NULL,
  `row_id` BIGINT NOT NULL,
  `attempted_column` VARCHAR(64) NOT NULL,
  `old_value` TEXT,
  `new_value` TEXT,
  `user_host` VARCHAR(128),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DROP TRIGGER IF EXISTS `trg_members_audit`;
DELIMITER //
CREATE TRIGGER `trg_members_audit`
BEFORE UPDATE ON `members`
FOR EACH ROW
BEGIN
  IF NEW.`name` != OLD.`name` THEN
    INSERT INTO `audit_log` (`table_name`,`row_id`,`attempted_column`,`old_value`,`new_value`,`user_host`)
    VALUES ('members', OLD.`id`, 'name', OLD.`name`, NEW.`name`, USER());
  END IF;
  IF NEW.`cellphone` != OLD.`cellphone` THEN
    INSERT INTO `audit_log` ... VALUES ('members', OLD.`id`, 'cellphone', OLD.`cellphone`, NEW.`cellphone`, USER());
  END IF;
  -- 其他核心欄位同理
  -- 最後仍需恢復舊值
  SET NEW.`name` = OLD.`name`;
  SET NEW.`cellphone` = OLD.`cellphone`;
  SET NEW.`email` = OLD.`email`;
  SET NEW.`password` = OLD.`password`;
END//
DELIMITER ;

注意事項與避坑

1. 觸發器不支援事務回滾：若觸發器中 INSERT audit_log 失敗，原 UPDATE 也會失敗（報錯），需確保審計表寫入極高可用。
2. 性能影響微小：僅幾次內存賦值 + 可選審計寫入，微秒級開銷。
3. 不防 DROP/TRUNCATE/REPLACE：需配合權限控制（回收 DROP/TRUNCATE 權限）和備份策略。
4. 主從復制：觸發器定義會隨 CREATE TRIGGER 同步到從庫，行級觸發邏輯在從庫重放 UPDATE 時同樣生效，保持一致。
5. 5.7+ 多觸發器同一事件：可定義多個 BEFORE UPDATE，按創建順序執行，注意順序依賴。

替代方案對比

觸發器方案以「零業務改造、微秒級開銷、強制生效」成為核心欄位防篡改的首選利器。