主页 / dba / 问题 / 271302
Accepted
pbarney
Asked: 2020-07-21 12:44:10 +0800 CST

如何使用 MySQL 触发器为同一行组合多个更新语句

  • 772

背景：

每次修改列时，我都需要更新第二个表中的关联列（具有相同名称）。这是我第一次尝试使用触发器。

代码：

这是我正在尝试做的一个简化示例，它的工作很好，但效率低下：

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 个更新语句。

问题：

有没有办法将更新语句合并为一个？

mysql trigger

1 个回答

  1. Best Answer

    为此目的，您可以使用准备好的语句来只有一个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 审核还需要保存旧值。

    • 1

相关问题