如何实现MySQL对某一张表的binlog日志进行记录

在 MySQL 中，使用触发器（Triggers）来记录表的变更是一种常见的方法。下面是具体的配置和步骤：

1. 创建日志表

首先，需要创建一个日志表，用于存储变更记录。

CREATE TABLE my_table_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• id：日志条目的唯一标识。

• operation：记录操作类型（INSERT、UPDATE、DELETE）。

• old_data：变更前的数据（适用于 UPDATE 和 DELETE）。

• new_data：变更后的数据（适用于 INSERT 和 UPDATE）。

• changed_at：记录变更发生的时间。

2. 创建触发器

接下来，需要创建触发器来监控表的变更并将变更记录到日志表中。假设我们要监控的表是 my_table。

2.1 创建 AFTER INSERT 触发器

当 my_table 表中插入数据时，记录变更：

DELIMITER //

CREATE TRIGGER my_table_after_insert
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
    INSERT INTO my_table_log (operation, new_data)
    VALUES ('INSERT', JSON_OBJECT('id', NEW.id, 'data', NEW.data));
END //

DELIMITER ;

2.2 创建 AFTER UPDATE 触发器

当 my_table 表中更新数据时，记录变更：

DELIMITER //

CREATE TRIGGER my_table_after_update
AFTER UPDATE ON my_table
FOR EACH ROW
BEGIN
    INSERT INTO my_table_log (operation, old_data, new_data)
    VALUES (
        'UPDATE',
        JSON_OBJECT('id', OLD.id, 'data', OLD.data),
        JSON_OBJECT('id', NEW.id, 'data', NEW.data)
    );
END //

DELIMITER ;

2.3 创建 AFTER DELETE 触发器

当 my_table 表中删除数据时，记录变更：

DELIMITER //

CREATE TRIGGER my_table_after_delete
AFTER DELETE ON my_table
FOR EACH ROW
BEGIN
    INSERT INTO my_table_log (operation, old_data)
    VALUES ('DELETE', JSON_OBJECT('id', OLD.id, 'data', OLD.data));
END //

DELIMITER ;

3. 验证触发器

可以通过对 my_table 执行插入、更新和删除操作来测试触发器是否按预期工作。例如：

-- 插入数据
INSERT INTO my_table (id, data) VALUES (1, 'example');

-- 更新数据
UPDATE my_table SET data = 'updated' WHERE id = 1;

-- 删除数据
DELETE FROM my_table WHERE id = 1;

之后，可以查询 my_table_log 表以确认日志记录是否成功：

SELECT * FROM my_table_log limit 100;

4. 注意事项

1. 性能影响：触发器会对表操作增加额外的开销，特别是在高频次的操作场景中，可能会影响性能。

2. 事务：触发器操作会在事务中执行，如果事务回滚，触发器的操作也会被回滚。

3. 调试：如果触发器未按预期工作，请检查触发器的定义以及表结构是否匹配。