mysql 查询表修改记录

基础概念

MySQL 是一个关系型数据库管理系统，用于存储和管理数据。查询表修改记录通常指的是跟踪表中数据的变更历史，包括插入、更新和删除操作。

类型

触发器（Triggers）：在 MySQL 中，可以通过创建触发器来记录表的修改记录。触发器可以在数据插入、更新或删除时自动执行特定的操作。
日志表（Log Tables）：创建一个专门用于记录修改历史的日志表，每次数据变更时，将变更记录插入到日志表中。
第三方工具：使用一些第三方工具如 pt-online-schema-change、gh-ost 等来跟踪表的修改记录。

应用场景

审计日志：对于需要记录用户操作的应用，如金融系统、医疗系统等。
数据恢复：在数据丢失或损坏时，可以通过查看修改记录进行数据恢复。
版本控制：对于需要版本控制的数据，如文档管理系统等。

遇到的问题及解决方法

问题：如何使用触发器记录表的修改记录？

解决方法：

-- 创建一个日志表
CREATE TABLE table_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data TEXT,
    new_data TEXT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER $$
CREATE TRIGGER after_table_change
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_log (table_name, operation, new_data)
    VALUES ('your_table', 'INSERT', CONCAT('ID: ', NEW.id, ', Data: ', NEW.column_name));
END$$
DELIMITER ;

CREATE TRIGGER after_table_change_update
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_log (table_name, operation, old_data, new_data)
    VALUES ('your_table', 'UPDATE', CONCAT('ID: ', OLD.id, ', Data: ', OLD.column_name), CONCAT('ID: ', NEW.id, ', Data: ', NEW.column_name));
END$$

CREATE TRIGGER after_table_change_delete
AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO table_log (table_name, operation, old_data)
    VALUES ('your_table', 'DELETE', CONCAT('ID: ', OLD.id, ', Data: ', OLD.column_name));
END$$
DELIMITER ;

参考链接：

MySQL 触发器文档

问题：如何使用日志表记录表的修改记录？

解决方法：

-- 创建一个日志表
CREATE TABLE table_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data TEXT,
    new_data TEXT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 在应用层记录修改操作
DELIMITER $$
CREATE PROCEDURE log_table_change(
    IN p_table_name VARCHAR(255),
    IN p_operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    IN p_old_data TEXT,
    IN p_new_data TEXT
)
BEGIN
    INSERT INTO table_log (table_name, operation, old_data, new_data)
    VALUES (p_table_name, p_operation, p_old_data, p_new_data);
END$$
DELIMITER ;

-- 在应用层调用存储过程记录修改操作
CALL log_table_change('your_table', 'INSERT', NULL, CONCAT('ID: ', NEW.id, ', Data: ', NEW.column_name));
CALL log_table_change('your_table', 'UPDATE', CONCAT('ID: ', OLD.id, ', Data: ', OLD.column_name), CONCAT('ID: ', NEW.id, ', Data: ', NEW.column_name));
CALL log_table_change('your_table', 'DELETE', CONCAT('ID: ', OLD.id, ', Data: ', OLD.column_name), NULL);

参考链接：