MySQL修改记录查询常用操作方案（mysql中修改记录查询）

MySQL修改记录查询常用操作方案

MySQL是一种常用的关系型数据库管理系统，为了确保数据的完整性和可靠性，我们需要记录对数据的修改操作，并在需要时对修改记录进行查询。本文将介绍MySQL修改记录查询的常用操作方案。

一、通过Trigger记录修改

Trigger是MySQL中用来监控表结构变化和数据变化的一种机制。我们可以为一张表建立一个Trigger，当有数据被修改、插入或删除时，Trigger会自动将相关信息记录下来，这种方式非常适用于需要实时监控修改的情况。

下面是一个简单的Trigger示例，它会在修改记录时将修改前后的值记录到另一张表中：

CREATE TABLE history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tablename VARCHAR(255),
    action VARCHAR(255),
    oldvalue TEXT,
    newvalue TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER mytrigger AFTER UPDATE 
ON mytable FOR EACH ROW
BEGIN
    INSERT INTO history (tablename, action, oldvalue, newvalue)
    VALUES ('mytable', 'update', OLD.col1, NEW.col1);
END //
DELIMITER ;

该Trigger会在`mytable`发生UPDATE事件后，在`history`表中插入一条记录，记录了修改操作的相关信息。

二、通过binlog日志分析修改记录

MySQL提供了binlog日志，用于记录所有的修改操作，通过分析binlog日志可以准确地记录数据的修改历史，但是需要使用专业的binlog解析器，例如mysqlbinlog工具。

通过解析binlog日志可以得到每个事务的详细修改记录，下面是一个示例：

#191109 23:29:44 server id 1  end_log_pos 860 CRC32 0x1ebaff6a  GTID 0-1-3
Xid = 22
COMMIT/*!*/;
#191109 23:29:44 server id 1  end_log_pos 914 CRC32 0xc5e1c5e5  GTID 0-1-4
BEGIN
/*!*/;
# at 914
#191109 23:29:44 server id 1  end_log_pos 936 CRC32 0x3e5492fa  GTID 0-1-4
SET @@SESSION.GTID_NEXT= '0-1-5'/*!*/;
# at 936
#191109 23:29:44 server id 1  end_log_pos 973 CRC32 0xe7189901  GTID 0-1-4
SET @@SESSION.GTID_NEXT= '0-1-5'/*!*/;
# at 973
#191109 23:29:44 server id 1  end_log_pos 1049 CRC32 0x558c7176  GTID 0-1-4
use `test`/*!*/;
SET TIMESTAMP=1573339784/*!*/;
# at 1049
#191109 23:29:44 server id 1  end_log_pos 1099 CRC32 0x8bc771c  GTID 0-1-4
SET @@session.pseudo_thread_id=5/*!*/;
# at 1099
#191109 23:29:44 server id 1  end_log_pos 1167 CRC32 0xd84a9d45  GTID 0-1-4
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
# at 1167
#191109 23:29:44 server id 1  end_log_pos 1212 CRC32 0xc822e3e3  GTID 0-1-4
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
# at 1212
#191109 23:29:44 server id 1  end_log_pos 1264 CRC32 0x4eb4d0e7  GTID 0-1-4
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
# at 1264
#191109 23:29:44 server id 1  end_log_pos 1307 CRC32 0x7624919c  GTID 0-1-4
SET @@session.lc_time_names=0/*!*/;
# at 1307
#191109 23:29:44 server id 1  end_log_pos 1487 CRC32 0x442291f1  GTID 0-1-4
SET @@session.time_zone='+00:00'/*!*/;
# at 1487
#191109 23:29:44 server id 1  end_log_pos 1650 CRC32 0x8b22954  GTID 0-1-4
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1/*!*/;
# at 1650
#191109 23:29:44 server id 1  end_log_pos 1785 CRC32 0xc217a74e  GTID 0-1-4
INSERT INTO `mytable` (`id`, `col1`) VALUES (1, 'hello')/*!*/;

#191109 23:29:44 server id 1  end_log_pos 1827 CRC32 0x8367f21b  GTID 0-1-4
Xid = 23
COMMIT/*!*/;

三、通过审计插件记录修改记录

MySQL提供了审计插件，可以记录所有的查询和修改操作，类似于日志功能。使用审计插件可以集中管理MySQL中的所有修改操作记录，方便管理和查询。

下面是一个审计插件的示例配置：

[AUDIT]
audit_format=JSON
audit_output=file:/var/log/mysql_audit.log

该配置将审计记录输出到`/var/log/mysql_audit.log`文件中，并使用JSON格式记录。

总结

MySQL修改记录查询是一个非常重要的功能，能够方便我们追溯数据的变化历史，从而更好地管理和保护数据。本文介绍了通过Trigger、binlog日志分析以及审计插件等方式来记录和查询修改记录的常用操作方案。对于不同的场景和特定的需要，可以选择合适的方案来实现修改记录的查询。