MySQL/MariaDB全局触发器创建及Node.js应用同步技术问询

一、如何实现监控所有数据表的“全局”触发器 #

MySQL没有原生的全局触发器（确实无法用*作为表占位符），但我们可以通过动态生成触发器脚本的方式，为所有用户表批量创建行级触发器，达到类似“全局监控”的效果。具体步骤如下：

1. 先创建用于存储变更的changes表 #

按照你的需求先建好变更记录表，这里用JSON类型适配不同表的结构，不用因为表字段变动修改记录表：

CREATE TABLE IF NOT EXISTS changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `table` VARCHAR(255) NOT NULL, -- 发生变更的表名
    `row` JSON NOT NULL, -- 变更行的主键信息（方便快速定位行）
    value_new JSON, -- 新值（仅INSERT/UPDATE时有效）
    value_old JSON, -- 旧值（仅UPDATE/DELETE时有效）
    time_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. 编写存储过程批量生成触发器 #

我们可以通过查询information_schema.TABLES获取当前数据库的所有用户表，然后自动为每个表生成INSERT/UPDATE/DELETE三种触发器：

DELIMITER //
CREATE PROCEDURE CreateAllTableTriggers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblName VARCHAR(255);
    -- 游标遍历当前数据库的所有基础表（排除系统表）
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.TABLES 
        WHERE table_schema = DATABASE() 
          AND table_type = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tblName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 生成INSERT触发器
        SET @insertTrigger = CONCAT(
            'CREATE TRIGGER trg_', tblName, '_insert AFTER INSERT ON ', tblName,
            ' FOR EACH ROW ',
            'INSERT INTO changes (`table`, `row`, value_new) ',
            'VALUES (''', tblName, ''', ',
            'JSON_OBJECT(', 
                -- 动态拼接主键字段为JSON
                (SELECT GROUP_CONCAT('''', column_name, ''', NEW.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName 
                   AND column_key = 'PRI'),
            '), ',
            'JSON_OBJECT(',
                -- 拼接所有字段的新值为JSON
                (SELECT GROUP_CONCAT('''', column_name, ''', NEW.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName),
            '));'
        );
        PREPARE stmt FROM @insertTrigger;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 生成UPDATE触发器
        SET @updateTrigger = CONCAT(
            'CREATE TRIGGER trg_', tblName, '_update AFTER UPDATE ON ', tblName,
            ' FOR EACH ROW ',
            'INSERT INTO changes (`table`, `row`, value_new, value_old) ',
            'VALUES (''', tblName, ''', ',
            'JSON_OBJECT(', 
                (SELECT GROUP_CONCAT('''', column_name, ''', NEW.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName 
                   AND column_key = 'PRI'),
            '), ',
            'JSON_OBJECT(',
                (SELECT GROUP_CONCAT('''', column_name, ''', NEW.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName),
            '), ',
            'JSON_OBJECT(',
                (SELECT GROUP_CONCAT('''', column_name, ''', OLD.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName),
            '));'
        );
        PREPARE stmt FROM @updateTrigger;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 生成DELETE触发器
        SET @deleteTrigger = CONCAT(
            'CREATE TRIGGER trg_', tblName, '_delete AFTER DELETE ON ', tblName,
            ' FOR EACH ROW ',
            'INSERT INTO changes (`table`, `row`, value_old) ',
            'VALUES (''', tblName, ''', ',
            'JSON_OBJECT(', 
                (SELECT GROUP_CONCAT('''', column_name, ''', OLD.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName 
                   AND column_key = 'PRI'),
            '), ',
            'JSON_OBJECT(',
                (SELECT GROUP_CONCAT('''', column_name, ''', OLD.', column_name) 
                 FROM information_schema.COLUMNS 
                 WHERE table_schema = DATABASE() 
                   AND table_name = tblName),
            '));'
        );
        PREPARE stmt FROM @deleteTrigger;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

执行这个存储过程，就能为当前数据库所有表创建触发器：

CALL CreateAllTableTriggers();

提醒：后续新增表时，需要重新执行这个存储过程，为新表补上触发器。

二、触发器中可用的全局/特殊变量 #

在MySQL触发器里，你可以使用以下几类变量/函数：

1. 行级触发器专属变量 #

• NEW：仅在INSERT和UPDATE触发器中可用，代表触发事件后的行数据（新值）。
• OLD：仅在UPDATE和DELETE触发器中可用，代表触发事件前的行数据（旧值）。

2. 系统全局/会话变量 #

这些变量用@@global.xxx（全局范围）或@@session.xxx（当前会话）调用，常用的有：

• @@hostname：数据库服务器的主机名
• @@version：MySQL服务器版本
• @@session.user：当前连接的用户名
• @@global.max_connections：全局最大连接数

3. 常用内置函数（可替代变量使用） #

• USER()/CURRENT_USER()：获取当前操作的用户
• NOW()/SYSDATE()：获取当前时间（NOW()是语句开始执行的时间，SYSDATE()是函数执行的时间）
• DATABASE()：获取当前数据库名称

三、补充注意事项 #

• 权限要求：执行上述操作需要CREATE TRIGGER权限，以及对information_schema的查询权限。
• 性能影响：每个表加触发器会增加数据库的写操作开销，高并发场景建议评估性能，或者考虑用二进制日志（binlog）结合Debezium这类工具实现同步（更适合Node.js应用的异步同步需求）。
• 无主键表处理：上述存储过程假设所有表都有主键，如果存在无主键的表，需要调整逻辑（比如用所有字段拼接作为行标识，或者跳过这类表）。

内容的提问来源于stack exchange，提问作者Adrian Preuss