如何使用 MySQL 觸發器為同一行組合多個更新語句

背景：

每次修改列時，我都需要更新第二個表中的關聯列（具有相同名稱）。這是我第一次嘗試使用觸發器。

程式碼：

這是我正在嘗試做的一個簡化範例，它的工作很好，但效率低下：

DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
 BEFORE UPDATE ON first_table 
 FOR EACH ROW
BEGIN
 /* putting IF statements on one line so it's easier to see what's happening */
 IF NOT(OLD.firstname <=> NEW.firstname)   THEN UPDATE second_table SET firstname  = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.lastname <=> NEW.lastname)     THEN UPDATE second_table SET lastname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.nickname <=> NEW.nickname)     THEN UPDATE second_table SET nickname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.dob <=> NEW.dob)               THEN UPDATE second_table SET dob        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.email <=> NEW.email)           THEN UPDATE second_table SET email      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.address <=> NEW.address)       THEN UPDATE second_table SET address    = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.city <=> NEW.city)             THEN UPDATE second_table SET city       = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.state <=> NEW.state)           THEN UPDATE second_table SET state      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.zip <=> NEW.zip)               THEN UPDATE second_table SET zip        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.phone <=> NEW.phone)           THEN UPDATE second_table SET phone      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
END;
//
DELIMITER;

問題：

如您所見，根據 first_table 中更新的列數，second_table 的同一行上最多可以有 11 個更新語句。

問題：

有沒有辦法將更新語句合併為一個？

為此目的，您可以使用準備好的語句來只有一個UPDATE命令。

SQL 注入的危險是不可能的，因為沒有使用者輸入。

DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
 BEFORE UPDATE ON first_table 
 FOR EACH ROW
BEGIN
   SET @switch = 0;
   SET @sql = 'UPDATE second_table SET ';
   IF NOT(OLD.firstname <=> NEW.firstname) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'firstname = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.middlename <=> NEW.middlename) THEN
           if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'middlename = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.lastname <=> NEW.lastname) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'lastname = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.nickname <=> NEW.nickname) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'nickname = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.dob <=> NEW.dob) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'dob = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.email <=> NEW.email) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'email = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.address <=> NEW.address) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'address = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.city <=> NEW.city) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'city = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.state <=> NEW.state) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'state = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.zip <=> NEW.zip) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'zip = CURRENT_TIMESTAMP');
   END IF;
   IF NOT(OLD.phone <=> NEW.phone) THEN 
       if @switch = 0 THEN
           SET @switch = 1;
       ELSE
           SET @sql = CONCAT(@sql,',');
       END IF;
       SET @sql = CONCAT(@sql, 'phone = CURRENT_TIMESTAMP');
   END IF;
   SET @sql = CONCAT ( @sql,' WHERE id = OLD.id;');
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

也就是說，GMP 審核還需要保存舊值。

引用自：https://dba.stackexchange.com/questions/271302