在MySQL触发器中循环遍历列

Question

当触发器内部时，是否可能循环遍历所有列名？

场景： 记录已修改的表中的所有列。 如果某些值没有更改，则不要记录这些值。

DROP TRIGGER IF EXISTS t_before_update_test;
DELIMITER $$
CREATE TRIGGER t_before_update_test
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    -- Loop here for all columns, not just col1
    IF OLD.col1 <> NEW.col1 THEN
        INSERT INTO change_logs(
            log_on, user_id,
            table_name, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'col1',
            OLD.col1, NEW.col1
        );
    END IF;
    -- process looping all columns
    -- col1, col2, ... should be dynamic per loop
END $$

这是一个工作副本示例，现在我需要循环遍历可用于OLD或NEW中的所有列。

- Bimal Poudel

Answer 1

很遗憾，在MySQL触发器中不允许使用动态SQL（即准备语句）。（这不能通过调用具有动态SQL的存储过程来绕过）。因此，我们必须在触发器中硬编码列名。但是，如果列发生更改，则触发器将由于不匹配的列而中断，从而停止UPDATE事务。因此，我们需要检查是否可以在change_logs表中执行日志记录工作。如果合法，则插入到change_logs表中；否则只需向警告表发送警告消息。假设测试表具有两个列，即id和datetm。并且提前创建了一个具有3个列（table_name，log_time，log_content）的警告表。change_logs表与OP的相同。其余内容是创建触发器（在workbench中编写和测试）：

delimiter //
 
drop trigger if exists t_before_update_test//
create trigger t_before_update_test  before  update on test for each row begin
if 
    'id' not in (select column_name from information_schema.columns where table_name='test')
    or 'datetm' not in (select column_name from information_schema.columns where table_name='test')
    or (select count(column_name) from information_schema.columns where table_name='test') !=2
then
    insert into warning_table values ('test',now(),'Table column structure has been changed!!');
else 
    IF old.id <> new.id THEN
        INSERT INTO change_logs(
            log_on, user_id,
            `table_name`, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'id',
            old.id, new.id
        );
    END IF;

    IF old.datetm <> new.datetm THEN
        INSERT INTO change_logs(
            log_on, user_id,
            `table_name`, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'datetm',
            old.datetm, new.datetm
        );
    END IF;
end if;
end // 

- blabla_bingo

Answer 2

很遗憾，您不能这样做。您可以通过访问 INFORMATION_SCHEMA 来获取列名，但无法从该列名访问 OLD 和 NEW 值。我认为这是有道理的，因为与存储过程不同，您正在为特定表创建触发器而不是为数据库创建触发器。在触发器内调用存储过程将帮助您减少代码量。

DROP TRIGGER IF EXISTS t_before_update_test;
DELIMITER $$
CREATE TRIGGER t_before_update_test
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    IF OLD.col1 <> NEW.col1 THEN
        /*pseudo*/
        CALL SP_insert_log (  
        'test', 
        'colum_name',
        'old_value',
        ''old_value');
    ELSEIF OLD.col2 <> NEW.col2 THEN
      //call above sp with this column related data
    END IF;
END $$

- rosh-dev851

Answer 3

我现在没有足够的时间来完成这个任务，但是我认为使用CONCAT()函数来准备语句，并将其结果用于条件语句中，可能可以实现你想要的功能。大致如下：

DECLARE num_rows INT DEFAULT 0;
DECLARE cols CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name' ORDER BY ordinal_position;

OPEN cols; 
SELECT FOUND_ROWS() INTO num_rows; 
SET @i = 1;

cols_loop: LOOP

    IF @i > num_rows THEN
        CLOSE cols;
        LEAVE cols_loop;
    END IF;

    FETCH cols INTO col;

    SET @do_stuff = 0;
    SET @s = CONCAT('SELECT IF(NEW.', col, ' <> OLD.', col, ', 1, 0) INTO @do_stuff');

    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    IF @do_stuff = 1 THEN
        SET @s2 = CONCAT('INSERT INTO change_logs(log_on, user_id, table_name, colum_name, old_data, new_data ) 
                          VALUES (UNIX_TIMESTAMP(NOW()), ''0'', ''test'', ''', col,''', OLD.', col, ', NEW.', col, ');');

        PREPARE stmt2 FROM @s2;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;
    END IF;

    SET @i = @i + 1;  
END LOOP cols_loop;

CLOSE cols; 

- derek.wolfe

---

预处理语句，不幸的是，无法在触发器内使用。https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html - Alden W.

@AldenW. 有没有办法使用 col 变量访问旧值？ - dlopezgonzalez

@dlopezgonzalez 在触发器中，您可以使用OLD.col_name访问旧值。相关文档在此处，https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html。 - Alden W.

@AldenW。我知道，问题是我们是否可以像OLD["col_name"]这样做些什么，我认为在MYSQL中不可能实现。 - dlopezgonzalez

我想知道触发器是否可以由事件调度程序生成的某些内容触发？也许可以设置一个定时事件，每隔15分钟或其他频率检查一次，并安排一个事件来触发自动化表单。根据您认为更改可能发生的频率增加计时器。 - easleyfixed

Answer 4

是的，在触发器中可以添加游标以循环遍历列。以下是一些链接：

mysql，通过列名进行迭代

https://dba.stackexchange.com/questions/22925/mysql-loop-over-cursor-results-ends-ahead-of-schedule

从经验上看，创建一个存储过程来完成循环插入的操作并从触发器中调用它可能更容易。

- Bobert123

---

在Stack Overflow上，仅提供链接的回答是不被赞同的。最好在此帖中包含相关要点，以防链接失效。 - Rich Benner

不清楚如何在触发器中使用这些链接中的解决方案（需要访问OLD和NEW）。 - adinas