🧑🏾‍🤝‍🧑🏽 🌳 👨🏽‍🤝‍👨🏼 如何教MySQL回顾过去 🖐🏼 🏦 👠

本文将重点介绍记录MySQL中的更改。我想展示登录触发器的实现以及您可以使用它执行哪些令人惊奇的事情。

为什么要触发？因为没有访问二进制日志的权限。尽管更难开发，但使用二进制日志的实现可能会更有效率，因为需要解析日志。

我想立即警告您，此方法会在服务器上创建额外的负载。而且，如果您正在主动更改数据，则此解决方案可能不适合您，或者需要进行一些调整和改进。

通常，解决方案是完整而复杂的。它可以“按原样”实现，并且可以完美地完成其任务。

以下所有内容均在MariaDB 10.0.32版上实现
记录具有类型的列：数字，字符串，日期。记录的表必须具有唯一的NOT NULL数字ID字段。

首先，使用日志记录配置创建一个表：

DROP TABLE IF EXISTS protocol_config; CREATE TABLE protocol_config ( id int(11) NOT NULL PRIMARY KEY auto_increment , command VARCHAR(50) NOT NULL --  , table_name VARCHAR(50) --   , column_name VARCHAR(50) --   , denormalize_column VARCHAR(50) --     protocol , UNIQUE (command, table_name, column_name, denormalize_column) ) DEFAULT CHARSET=utf8 COMMENT=' ';

在生成记录触发器的过程中将应用所有选项。即更改设置时，您需要重新生成触发器。

命令字段-协议配置选项：

disable_protocol-禁用日志记录。
exclude_table-指示要从日志中排除的表。默认情况下，所有BASE TABLE ENGINE = InnoDB都参与日志记录。
举个例子
exclude_table协议
exclude_table protocol_pos
exclude_column-指示要从日志中排除的字段。例如，触发器支持的非规范化字段。

举个例子
exclude_column文档总和
denormalize_column-指示必须另外对协议进行规范化的列（协议表）。默认情况下，所有字段都记录在protocol_pos表中。

举个例子
denormalize_column文档ID doc_id
从docs表中， id字段将被记录到doc_id列的协议表中。协议表中的doc_id字段必须手动创建。
denormalize_column doc_pos doc_id doc_id
doc_id字段将从doc_pos表记录到doc_id列的协议表中。

协议表：

 DROP TABLE IF EXISTS protocol_pos; DROP TABLE IF EXISTS protocol; CREATE TABLE protocol ( id BIGINT NOT NULL PRIMARY KEY auto_increment , date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP --    , oper VARCHAR(1) NOT NULL --  I, U, D , table_name VARCHAR(50) NOT NULL --   , table_id BIGINT NOT NULL --   id    , username VARCHAR(50) NOT NULL --      , ip varchar(45) -- IP   , user_agent varchar(256) --  , KEY (table_name, date) ) DEFAULT CHARSET=utf8 COMMENT=' ';

Protocol_pos表：

 DROP TABLE IF EXISTS protocol_pos; CREATE TABLE protocol_pos ( prot_id BIGINT NOT NULL --   protocol.id , column_name VARCHAR(50) NOT NULL --      , old_val VARCHAR(2000) --    , new_val VARCHAR(2000) --    , PRIMARY KEY (prot_id, column_name) , FOREIGN KEY (prot_id) REFERENCES protocol(id) ) DEFAULT CHARSET=utf8 COMMENT='  ';

在协议表中，我们提交操作，在协议_pos表中，输入更改的字段。

现在，以我之前的文章“在MySQL中实现业务逻辑”中的触发器生成器为基础，并基于它编写用于日志记录的生成器。

业务逻辑触发器生成函数gen_bl_trigger查看过程<table name> _trg_proc的存在

gen_bl_trigger

 DELIMITER $ DROP FUNCTION IF EXISTS gen_bl_trigger$ CREATE FUNCTION gen_bl_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN DECLARE text TEXT; DECLARE f_proc INT; SET group_concat_max_len = 9000000; SET f_proc := (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = CONCAT(table_name, '_trg_proc') AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = DATABASE() ); IF IFNULL(f_proc, 0) = 0 THEN RETURN ''; END IF; SET text := CONCAT('\nbl_proc: BEGIN IF @disable_', table_name, '_bl_trg = 1 OR @disable_all_bl_trg = 1 THEN LEAVE bl_proc; END IF;'); IF trigger_time = 'BEFORE' THEN --    SET text := CONCAT(text, '\nCREATE TEMPORARY TABLE '); --        INSERT INTO ... ON DUPLICATE KEY UPDATE   IF NOT EXISTS --  INSERT IGNORE   AFTER TRIGGER,    IF trigger_type IN ('INSERT', 'UPDATE') THEN SET text := CONCAT(text, 'IF NOT EXISTS '); END IF; SET text := CONCAT(text, table_name, '_tmp_trg ('); SET text := CONCAT(text, '\ntime VARCHAR(1)'); SET text := CONCAT(text, '\n, type VARCHAR(1)'); SET text := CONCAT(text, '\n, col_changed VARCHAR(1000)'); SET text := CONCAT(text, (SELECT GROUP_CONCAT('\n, new_', COLUMN_NAME, ' ', COLUMN_TYPE , '\n, old_', COLUMN_NAME, ' ', COLUMN_TYPE SEPARATOR '') text FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' )); SET text := CONCAT(text, ') ENGINE=MEMORY;'); --   SET text := CONCAT(text, (SELECT GROUP_CONCAT('\nSET @new_', COLUMN_NAME, ' := ' , IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';' , '\nSET @old_', COLUMN_NAME, ' := ' , IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';' SEPARATOR '') text FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' )); END IF; SET text := CONCAT(text, '\nINSERT INTO ', table_name, '_tmp_trg VALUES ("', SUBSTR(trigger_time, 1, 1), '", "', SUBSTR(trigger_type, 1, 1), '", '); --  col_changed  UPDATE IF trigger_type = 'UPDATE' THEN SET text := CONCAT(text, 'CONCAT(' , (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.' , COLUMN_NAME, ', "-") != IFNULL(OLD.', COLUMN_NAME, ', "-"), "|', COLUMN_NAME, '|", "")' ) SEPARATOR ', ') text FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' ) , '), '); ELSE SET text := CONCAT(text, 'NULL, '); END IF; --   SET text := CONCAT(text, (SELECT GROUP_CONCAT( CASE WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME) WHEN trigger_type = 'DELETE' THEN 'NULL' ELSE CONCAT('NEW.', COLUMN_NAME) END , ', ' , CASE WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME) WHEN trigger_type = 'INSERT' THEN 'NULL' ELSE CONCAT('OLD.', COLUMN_NAME) END SEPARATOR ', ') text FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' )); SET text := CONCAT(text, ');'); SET text := CONCAT(text, '\nCALL ', table_name, '_trg_proc;'); IF trigger_time = 'BEFORE' THEN SET text := CONCAT(text , IF(trigger_type = 'DELETE' , '' , (SELECT CONCAT('\nSELECT ' , GROUP_CONCAT('new_', COLUMN_NAME SEPARATOR ', ') , '\nINTO ', GROUP_CONCAT('@new_', COLUMN_NAME SEPARATOR ', ') , '\nFROM ', table_name, '_tmp_trg;' , GROUP_CONCAT('\nSET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME, ';' SEPARATOR '') ) text FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' ) ) ); SET text := CONCAT(text, '\nDELETE FROM ', table_name, '_tmp_trg;'); ELSE SET text := CONCAT(text, '\nDROP TEMPORARY TABLE ', table_name, '_tmp_trg;'); END IF; SET text := CONCAT(text, '\nEND;'); RETURN text; END$

记录触发器生成函数gen_prot_trigger ：

gen_prot_trigger

 DELIMITER $ DROP FUNCTION IF EXISTS gen_prot_trigger$ CREATE FUNCTION gen_prot_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN DECLARE text TEXT; DECLARE denormalize_columns TEXT; DECLARE denormalize_values TEXT; DECLARE f_exclude_table INT; SET group_concat_max_len = 9000000; --       ,     id    SET f_exclude_table := ( SELECT CASE WHEN pd.id IS NOT NULL THEN 1 WHEN pc.id IS NOT NULL THEN 1 WHEN C.COLUMN_NAME IS NULL THEN 1 END FROM (SELECT NULL FROM dual) d LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = table_name LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = DATABASE() AND C.TABLE_NAME = table_name AND C.COLUMN_NAME = 'id' ); IF trigger_time = 'BEFORE' OR f_exclude_table = 1 OR table_name IN ('protocol', 'protocol_pos') THEN RETURN ''; END IF; SET text := CONCAT('\nprot_proc: BEGIN DECLARE prot_id INT; IF @disable_', table_name, '_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF;'); --     1 ,     IF trigger_type = 'UPDATE' THEN SET text := CONCAT(text , '\nIF ' , (SELECT GROUP_CONCAT('IFNULL(NEW.' , C.COLUMN_NAME, ', "-") = IFNULL(OLD.', C.COLUMN_NAME, ', "-")' SEPARATOR ' AND ' ) text FROM INFORMATION_SCHEMA.COLUMNS C LEFT JOIN protocol_config ec ON ec.command = 'exclude_column' AND ec.table_name = C.TABLE_NAME AND ec.column_name = C.COLUMN_NAME WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' AND ec.id IS NULL) , ' THEN LEAVE prot_proc; END IF;' ); END IF; --     protocol SELECT IFNULL(GROUP_CONCAT(', ', dc.denormalize_column ORDER BY dc.id SEPARATOR ''), '') denormalize_columns , IFNULL(GROUP_CONCAT(', ' , CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END , dc.column_name ORDER BY dc.id SEPARATOR ', ' ) , '') denormalize_values INTO denormalize_columns, denormalize_values FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN protocol_config dc ON dc.command = 'denormalize_column' AND dc.table_name = C.TABLE_NAME AND dc.column_name = C.COLUMN_NAME WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() ; --     SET text := CONCAT(text, '\nINSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent' , denormalize_columns, ') SELECT IFNULL(u.email, USER()) username, "', SUBSTR(trigger_type, 1, 1), '", "', table_name, '"' , ', ', CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END, '.id' , ', au.ip, au.user_agent' , denormalize_values, ' FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID();'); --         SET text := CONCAT(text , '\nINSERT INTO protocol_pos (prot_id, column_name, ' , CASE trigger_type WHEN 'INSERT' THEN 'new_val' WHEN 'UPDATE' THEN 'old_val, new_val' WHEN 'DELETE' THEN 'old_val' END , ')\n' , (SELECT GROUP_CONCAT('SELECT prot_id, "', C.COLUMN_NAME, '", ' , CASE WHEN trigger_type = 'UPDATE' THEN CONCAT('OLD.', C.COLUMN_NAME, ', NEW.', C.COLUMN_NAME, ' FROM dual WHERE IFNULL(NEW.', C.COLUMN_NAME, ', "-") != IFNULL(OLD.', C.COLUMN_NAME, ', "-")') WHEN trigger_type = 'INSERT' THEN CONCAT('NEW.', C.COLUMN_NAME) WHEN trigger_type = 'DELETE' THEN CONCAT('OLD.', C.COLUMN_NAME) END SEPARATOR '\nUNION ALL ' ) text FROM INFORMATION_SCHEMA.COLUMNS C LEFT JOIN protocol_config ec ON ec.command = 'exclude_column' AND ec.table_name = C.TABLE_NAME AND ec.column_name = C.COLUMN_NAME WHERE C.TABLE_NAME = table_name AND C.TABLE_SCHEMA = DATABASE() AND C.COLUMN_TYPE != 'text' AND ec.id IS NULL ) , ';\nEND;' ); RETURN text; END$

Generate_trigger函数-业务逻辑+日志记录：

generate_trigger

 DELIMITER $ DROP FUNCTION IF EXISTS generate_trigger$ CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN DECLARE text TEXT; DECLARE bl_text TEXT; DECLARE prot_text TEXT; DECLARE trigger_time_short VARCHAR(3); DECLARE trigger_type_short VARCHAR(3); SET group_concat_max_len = 9000000; SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3)); SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3)); SET text := ''; SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$'); SET bl_text := gen_bl_trigger(table_name, trigger_time, trigger_type); SET prot_text := gen_prot_trigger(table_name, trigger_time, trigger_type); IF bl_text = '' AND prot_text = '' THEN RETURN text; END IF; SET text := CONCAT(text, '\nCREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROW trg_proc:BEGIN IF @disable_', table_name, '_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF;' , bl_text , prot_text , '\nEND$\n' ); RETURN text; END$

generate_triggers函数生成表上所有触发器的文本：

generate_triggers

 DELIMITER $ DROP FUNCTION IF EXISTS generate_triggers$ CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN DECLARE table_name VARCHAR(200); DECLARE text TEXT; SET group_concat_max_len = 9000000; SET table_name := p_table_name; SET text := ''; SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR '\n') FROM (SELECT 'BEFORE' trigger_time UNION ALL SELECT 'AFTER' trigger_time) trigger_time , (SELECT 'INSERT' trigger_type UNION ALL SELECT 'UPDATE' trigger_type UNION ALL SELECT 'DELETE' trigger_type ) trigger_type); RETURN text; END$

在“在MySQL上实现行级安全性”一文中描述了授权

 DELIMITER ; DROP TABLE IF EXISTS users; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(100) NOT NULL, `pass` varchar(30) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) DEFAULT CHARSET=utf8 COMMENT=' '; DROP TABLE IF EXISTS auth_users; CREATE TABLE `auth_users` ( `conn_id` bigint(20) NOT NULL, `user_id` int(11) NOT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ip` varchar(45) DEFAULT NULL, `user_agent` varchar(256) DEFAULT NULL, PRIMARY KEY (`conn_id`) -- , FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT=' ';

现在创建几个测试模式：

 DROP TABLE IF EXISTS doc_pos; DROP TABLE IF EXISTS docs; CREATE TABLE `docs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` VARCHAR(20) NOT NULL, `date` DATE NOT NULL, `warehouse` VARCHAR(100) NOT NULL, `partner` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8 COMMENT=''; DROP TABLE IF EXISTS doc_pos; CREATE TABLE `doc_pos` ( `id` int(11) NOT NULL AUTO_INCREMENT, `doc_id` int(11) NOT NULL, `material` VARCHAR(100) NOT NULL, `amount` int(11) NOT NULL, `price` int(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (doc_id) REFERENCES docs(id) ) DEFAULT CHARSET=utf8 COMMENT=' ';

让我们执行一个请求来控制数据库中触发器的正确性：

控制数据库中触发器正确性的请求

 SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")') WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")') END ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN ' ' WHEN pc.id IS NOT NULL THEN ' ' WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN ' ' WHEN C.COLUMN_NAME IS NULL THEN '  id' END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc') AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE' AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ;

 + --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- -------------------------------------- +
 |表名|注释|行cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
 + --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- -------------------------------------- +
 | docs |文档|  0 |  0.02 |  NULL | NULL | SELECT generate_triggers（“ docs”）|
 | doc_pos |文件位置|  0 |  0.02 |  NULL | NULL | SELECT generate_triggers（“ doc_pos”）|
 |协议|更改协议|  0 |  0.02 |  NULL |未记录| NULL |
 | protocol_config |配置日志记录|  0 |  0.02 |  | NULL | NULL | SELECT generate_triggers（“ protocol_config”）|
 | protocol_pos |更改协议字段|  0 |  0.02 |  NULL |未记录| NULL |
 |用户|系统用户|  0 |  0.02 |  NULL | NULL | SELECT generate_triggers（“用户”）|
 + --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- -------------------------------------- +

系统为我们提供了在docs，doc_pos，protocol_config和users表上创建日志记录触发器的功能

用SELECT包裹上一个查询，然后再次执行：

控制数据库中触发器正确性的请求

 SELECT GROUP_CONCAT(create_trg SEPARATOR '\nUNION ALL ') sql_text FROM ( SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")') WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")') END ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN ' ' WHEN pc.id IS NOT NULL THEN ' ' WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN ' ' WHEN C.COLUMN_NAME IS NULL THEN '  id' END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc') AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE' AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ) d ;

结果：

 SELECT generate_triggers("docs") UNION ALL SELECT generate_triggers("doc_pos") UNION ALL SELECT generate_triggers("protocol_config") UNION ALL SELECT generate_triggers("users") ;

现在执行此请求：

SELECT generate_triggers（docs）UNION ALL SELECT ....

 DROP TRIGGER IF EXISTS docs_bef_ins_trg$ DROP TRIGGER IF EXISTS docs_aft_ins_trg$ CREATE TRIGGER docs_aft_ins_trg AFTER INSERT ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "I", "docs", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, "id", NEW.id UNION ALL SELECT prot_id, "num", NEW.num UNION ALL SELECT prot_id, "date", NEW.date UNION ALL SELECT prot_id, "warehouse", NEW.warehouse UNION ALL SELECT prot_id, "partner", NEW.partner; END; END$ DROP TRIGGER IF EXISTS docs_bef_upd_trg$ DROP TRIGGER IF EXISTS docs_aft_upd_trg$ CREATE TRIGGER docs_aft_upd_trg AFTER UPDATE ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, "-") = IFNULL(OLD.id, "-") AND IFNULL(NEW.num, "-") = IFNULL(OLD.num, "-") AND IFNULL(NEW.date, "-") = IFNULL(OLD.date, "-") AND IFNULL(NEW.warehouse, "-") = IFNULL(OLD.warehouse, "-") AND IFNULL(NEW.partner, "-") = IFNULL(OLD.partner, "-") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "U", "docs", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-") != IFNULL(OLD.id, "-") UNION ALL SELECT prot_id, "num", OLD.num, NEW.num FROM dual WHERE IFNULL(NEW.num, "-") != IFNULL(OLD.num, "-") UNION ALL SELECT prot_id, "date", OLD.date, NEW.date FROM dual WHERE IFNULL(NEW.date, "-") != IFNULL(OLD.date, "-") UNION ALL SELECT prot_id, "warehouse", OLD.warehouse, NEW.warehouse FROM dual WHERE IFNULL(NEW.warehouse, "-") != IFNULL(OLD.warehouse, "-") UNION ALL SELECT prot_id, "partner", OLD.partner, NEW.partner FROM dual WHERE IFNULL(NEW.partner, "-") != IFNULL(OLD.partner, "-"); END; END$ DROP TRIGGER IF EXISTS docs_bef_del_trg$ DROP TRIGGER IF EXISTS docs_aft_del_trg$ CREATE TRIGGER docs_aft_del_trg AFTER DELETE ON docs FOR EACH ROW trg_proc:BEGIN IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "D", "docs", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, "id", OLD.id UNION ALL SELECT prot_id, "num", OLD.num UNION ALL SELECT prot_id, "date", OLD.date UNION ALL SELECT prot_id, "warehouse", OLD.warehouse UNION ALL SELECT prot_id, "partner", OLD.partner; END; END$ DROP TRIGGER IF EXISTS users_bef_ins_trg$ DROP TRIGGER IF EXISTS users_aft_ins_trg$ CREATE TRIGGER users_aft_ins_trg AFTER INSERT ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "I", "users", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, "id", NEW.id UNION ALL SELECT prot_id, "email", NEW.email UNION ALL SELECT prot_id, "pass", NEW.pass; END; END$ DROP TRIGGER IF EXISTS users_bef_upd_trg$ DROP TRIGGER IF EXISTS users_aft_upd_trg$ CREATE TRIGGER users_aft_upd_trg AFTER UPDATE ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, "-") = IFNULL(OLD.id, "-") AND IFNULL(NEW.email, "-") = IFNULL(OLD.email, "-") AND IFNULL(NEW.pass, "-") = IFNULL(OLD.pass, "-") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "U", "users", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-") != IFNULL(OLD.id, "-") UNION ALL SELECT prot_id, "email", OLD.email, NEW.email FROM dual WHERE IFNULL(NEW.email, "-") != IFNULL(OLD.email, "-") UNION ALL SELECT prot_id, "pass", OLD.pass, NEW.pass FROM dual WHERE IFNULL(NEW.pass, "-") != IFNULL(OLD.pass, "-"); END; END$ DROP TRIGGER IF EXISTS users_bef_del_trg$ DROP TRIGGER IF EXISTS users_aft_del_trg$ CREATE TRIGGER users_aft_del_trg AFTER DELETE ON users FOR EACH ROW trg_proc:BEGIN IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "D", "users", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, "id", OLD.id UNION ALL SELECT prot_id, "email", OLD.email UNION ALL SELECT prot_id, "pass", OLD.pass; END; END$ DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$ CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "I", "doc_pos", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, "id", NEW.id UNION ALL SELECT prot_id, "doc_id", NEW.doc_id UNION ALL SELECT prot_id, "material", NEW.material UNION ALL SELECT prot_id, "amount", NEW.amount UNION ALL SELECT prot_id, "price", NEW.price; END; END$ DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$ CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, "-") = IFNULL(OLD.id, "-") AND IFNULL(NEW.doc_id, "-") = IFNULL(OLD.doc_id, "-") AND IFNULL(NEW.material, "-") = IFNULL(OLD.material, "-") AND IFNULL(NEW.amount, "-") = IFNULL(OLD.amount, "-") AND IFNULL(NEW.price, "-") = IFNULL(OLD.price, "-") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "U", "doc_pos", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-") != IFNULL(OLD.id, "-") UNION ALL SELECT prot_id, "doc_id", OLD.doc_id, NEW.doc_id FROM dual WHERE IFNULL(NEW.doc_id, "-") != IFNULL(OLD.doc_id, "-") UNION ALL SELECT prot_id, "material", OLD.material, NEW.material FROM dual WHERE IFNULL(NEW.material, "-") != IFNULL(OLD.material, "-") UNION ALL SELECT prot_id, "amount", OLD.amount, NEW.amount FROM dual WHERE IFNULL(NEW.amount, "-") != IFNULL(OLD.amount, "-") UNION ALL SELECT prot_id, "price", OLD.price, NEW.price FROM dual WHERE IFNULL(NEW.price, "-") != IFNULL(OLD.price, "-"); END; END$ DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$ DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$ CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW trg_proc:BEGIN IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "D", "doc_pos", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, "id", OLD.id UNION ALL SELECT prot_id, "doc_id", OLD.doc_id UNION ALL SELECT prot_id, "material", OLD.material UNION ALL SELECT prot_id, "amount", OLD.amount UNION ALL SELECT prot_id, "price", OLD.price; END; END$ DROP TRIGGER IF EXISTS protocol_config_bef_ins_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_ins_trg$ CREATE TRIGGER protocol_config_aft_ins_trg AFTER INSERT ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "I", "protocol_config", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, new_val) SELECT prot_id, "id", NEW.id UNION ALL SELECT prot_id, "command", NEW.command UNION ALL SELECT prot_id, "table_name", NEW.table_name UNION ALL SELECT prot_id, "column_name", NEW.column_name UNION ALL SELECT prot_id, "denormalize_column", NEW.denormalize_column; END; END$ DROP TRIGGER IF EXISTS protocol_config_bef_upd_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_upd_trg$ CREATE TRIGGER protocol_config_aft_upd_trg AFTER UPDATE ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; IF IFNULL(NEW.id, "-") = IFNULL(OLD.id, "-") AND IFNULL(NEW.command, "-") = IFNULL(OLD.command, "-") AND IFNULL(NEW.table_name, "-") = IFNULL(OLD.table_name, "-") AND IFNULL(NEW.column_name, "-") = IFNULL(OLD.column_name, "-") AND IFNULL(NEW.denormalize_column, "-") = IFNULL(OLD.denormalize_column, "-") THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "U", "protocol_config", NEW.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val) SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-") != IFNULL(OLD.id, "-") UNION ALL SELECT prot_id, "command", OLD.command, NEW.command FROM dual WHERE IFNULL(NEW.command, "-") != IFNULL(OLD.command, "-") UNION ALL SELECT prot_id, "table_name", OLD.table_name, NEW.table_name FROM dual WHERE IFNULL(NEW.table_name, "-") != IFNULL(OLD.table_name, "-") UNION ALL SELECT prot_id, "column_name", OLD.column_name, NEW.column_name FROM dual WHERE IFNULL(NEW.column_name, "-") != IFNULL(OLD.column_name, "-") UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column, NEW.denormalize_column FROM dual WHERE IFNULL(NEW.denormalize_column, "-") != IFNULL(OLD.denormalize_column, "-"); END; END$ DROP TRIGGER IF EXISTS protocol_config_bef_del_trg$ DROP TRIGGER IF EXISTS protocol_config_aft_del_trg$ CREATE TRIGGER protocol_config_aft_del_trg AFTER DELETE ON protocol_config FOR EACH ROW trg_proc:BEGIN IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN LEAVE trg_proc; END IF; prot_proc: BEGIN DECLARE prot_id INT; IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN LEAVE prot_proc; END IF; INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent) SELECT IFNULL(u.email, USER()) username, "D", "protocol_config", OLD.id, au.ip, au.user_agent FROM (SELECT NULL FROM dual) d LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID() LEFT JOIN users u ON u.id = au.user_id; SET prot_id := LAST_INSERT_ID(); INSERT INTO protocol_pos (prot_id, column_name, old_val) SELECT prot_id, "id", OLD.id UNION ALL SELECT prot_id, "command", OLD.command UNION ALL SELECT prot_id, "table_name", OLD.table_name UNION ALL SELECT prot_id, "column_name", OLD.column_name UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column; END; END$

我们得到了触发器的文本，并执行了它（使用DELIMITER $），

现在记录了我们的表，并将所有数据更改都写入了协议。
使用第一个请求检查日志记录设置：

控制数据库中触发器正确性的请求

 DELIMITER ; SELECT table_name, comment, rows_cn, data_len_mb , MAX(need_bl_trg) need_bl_trg , MAX(exclude_prot) exclude_prot , MAX(CASE WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")') WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")') END ) create_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg FROM ( SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement , gen_trg FROM ( SELECT t.TABLE_NAME table_name , t.TABLE_COMMENT comment , t.TABLE_ROWS rows_cn , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg , CASE WHEN pd.id IS NOT NULL THEN ' ' WHEN pc.id IS NOT NULL THEN ' ' WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN ' ' WHEN C.COLUMN_NAME IS NULL THEN '  id' END exclude_prot , tr.ACTION_STATEMENT action_statement , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc') AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol' LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id' LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'BASE TABLE' AND t.ENGINE = 'InnoDB' ) d) d) d GROUP BY table_name, comment, rows_cn, data_len_mb ORDER BY table_name ;

+ --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- ----- +
|表名|注释|行_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
+ --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- ----- +
| docs |文档| 0 | 0.02 | NULL | NULL | NULL |
| doc_pos |文件位置| 0 | 0.02 | NULL | NULL | NULL |
|协议|更改协议| 0 | 0.02 | NULL |未记录| NULL |
| protocol_config |配置日志记录| 0 | 0.02 | NULL | NULL | NULL |
| protocol_pos |更改协议字段| 0 | 0.02 | NULL |未记录| NULL |
|用户|系统用户| 0 | 0.02 | NULL | NULL | NULL |
+ --------------- + -------------------------- + ------ -+ ----------- + ----------- + ------------------ + ----- ----- +
设置6行，0个警告（5.33秒）

添加用户并登录：

 /* DELETE FROM doc_pos; DELETE FROM docs; DELETE FROM auth_users; DELETE FROM users; DELETE FROM protocol_pos; DELETE FROM protocol; */ INSERT INTO users (email, pass) VALUES ('test@test.ru', '12345'); Query OK, 1 row affected (0.01 sec) INSERT INTO auth_users (conn_id, user_id) SELECT CONNECTION_ID() conn_id , (SELECT u.id FROM users u WHERE u.email = 'test@test.ru') user_id ; Query OK, 1 row affected (0.00 sec)

插入测试文档：

 BEGIN; INSERT INTO docs (num, date, warehouse, partner) VALUES ('1', '2018-07-17', ' ', ', '); SET @doc_id := LAST_INSERT_ID(); INSERT INTO doc_pos (doc_id, material, amount, price) VALUES (@doc_id, ' ', 10, 52) , (@doc_id, ' ', 20, 165) , (@doc_id, ' ', 7, 30); COMMIT;

让我们看看协议中发生了什么：

 SELECT id, date, oper, table_name, table_id , (SELECT GROUP_CONCAT(pp.column_name, ': (' , IFNULL(pp.old_val, 'NULL') , ', ' , IFNULL(pp.new_val, 'NULL') , ')' SEPARATOR ', ' ) FROM protocol_pos pp WHERE pp.prot_id = p.id ) vals , p.username FROM protocol p;

要求html

 SELECT id, date, oper, table_name, table_id , (SELECT CONCAT('<table class="table table-bordered" style="width: 100%; margin: -9px;">' , GROUP_CONCAT('<tr><td style="font-weight: bold; width: 20%;">', pp.column_name, '</td>' , '<td style="width: 40%;">', IFNULL(pp.old_val, "<span style='color: #FF0000; font-style: italic;'>NULL</span>"), '</td>' , '<td style="width: 40%;">', IFNULL(pp.new_val, "<span style='color: #FF0000; font-style: italic;'>NULL</span>"), '</td></tr>' SEPARATOR '' ) , '</table>' ) FROM protocol_pos pp WHERE pp.prot_id = p.id ) vals , p.username FROM protocol p;

+ ---- + --------------------- + ------ + ------------ +- -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +
 |id | 日期| 歌剧| table_name | table_id | vals | 用户名|
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+
 | 1 | 2018-10-09 17:21:27 | I | users | 1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345) | admin@myhosting.ru|
 | 2 | 2018-10-09 17:21:51 | I | docs | 1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, , ), warehouse: (NULL,  )| test@test.ru |
 | 3 | 2018-10-09 17:21:51 | I | doc_pos | 1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL,  ), price: (NULL, 52) | test@test.ru |
 | 4 | 2018-10-09 17:21:51 | I | doc_pos | 2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL,  ), price: (NULL, 165) | test@test.ru |
 |5 | 2018-10-09 17:21:51 | 我 doc_pos | 3 | 数量：（NULL，7），doc_id：（NULL，1），id：（NULL，3），材料：（NULL，圆珠笔），价格：（NULL，30）| test@test.ru |
+ ---- + --------------------- + ------ + ------------ +- -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +

如您所见，从用户插入开始，将记录所有数据库更改。

我们将收到销售报告：

 SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date;

+ --------------------- + ------------ + ------ +
 |report_time | 日期| 总和
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:23:47 | 2018-07-17 | 4030 |
+ --------------------- + ------------ + ------ +

现在更改现有文档并添加另一个文档：

 BEGIN; SET @doc_id := (SELECT id FROM docs WHERE num = '1'); UPDATE docs SET date = '2018-07-16', warehouse = warehouse WHERE id = @doc_id; DELETE FROM doc_pos WHERE doc_id = @doc_id AND material = ' '; UPDATE doc_pos p SET p.price = 105, p.material = ' ' WHERE p.doc_id = @doc_id AND p.material = ' '; INSERT INTO docs (num, date, warehouse, partner) VALUES ('2', '2018-07-18', ' ', ', '); SET @doc_id := LAST_INSERT_ID(); INSERT INTO doc_pos (doc_id, material, amount, price) VALUES (@doc_id, ' 10*15', 5, 102) , (@doc_id, ' 4', 2, 165); COMMIT;

这看起来像一个新协议

 SELECT id, date, oper, table_name, table_id , (SELECT GROUP_CONCAT(pp.column_name, ': (' , IFNULL(pp.old_val, 'NULL') , ', ' , IFNULL(pp.new_val, 'NULL') , ')' SEPARATOR ', ' ) FROM protocol_pos pp WHERE pp.prot_id = p.id ) vals , p.username FROM protocol p;

+ ---- + --------------------- + ------ + ------------ +- -------- + ----------------------------------------- -------------------------------------------------- --------------------------------- + ---------------- -+
 |id | 日期| 歌剧| table_name | table_id | vals | 用户名|
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+
 | 1 | 2018-10-09 17:21:27 | I | users | 1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345) | admin@myhosting.ru|
 | 2 | 2018-10-09 17:21:51 | I | docs | 1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, , ), warehouse: (NULL,  ) | test@test.ru |
 | 3 | 2018-10-09 17:21:51 | I | doc_pos | 1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL,  ), price: (NULL, 52) | test@test.ru |
 | 4 | 2018-10-09 17:21:51 | I | doc_pos | 2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL,  ), price: (NULL, 165) | test@test.ru |
 | 5 | 2018-10-09 17:21:51 | I | doc_pos | 3 | amount: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), material: (NULL,  ), price: (NULL, 30) | test@test.ru |
 | 6 | 2018-10-09 17:24:27 | U | docs | 1 | date: (2018-07-17, 2018-07-16) | test@test.ru |
 | 7 | 2018-10-09 17:24:27 | D | doc_pos | 3 | amount: (7, NULL), doc_id: (1, NULL), id: (3, NULL), material: ( , NULL), price: (30, NULL) | test@test.ru |
 | 8 | 2018-10-09 17:24:27 | U | doc_pos | 2 | material: ( ,  ), price: (165, 105) | test@test.ru |
 | 9 | 2018-10-09 17:24:27 | I | docs | 2 | date: (NULL, 2018-07-18), id: (NULL, 2), num: (NULL, 2), partner: (NULL, , ), warehouse: (NULL,  )| test@test.ru |
 | 10 | 2018-10-09 17:24:27 | I | doc_pos | 4 | amount: (NULL, 5), doc_id: (NULL, 2), id: (NULL, 4), material: (NULL,  10*15), price: (NULL, 102) | test@test.ru |
 | 11 | 2018-10-09 17:24:27 | I | doc_pos | 5 | amount: (NULL, 2), doc_id: (NULL, 2), id: (NULL, 5), material: (NULL,  4), price: (NULL, 165) | test@test.ru |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+

获取新报告：

 SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date;

+ --------------------- + ------------ + ------ +
 |report_time | 日期| 总和
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:26:18 | 2018-07-16 | 2620 |
 |2018-10-09 17:26:18 | 2018-07-18 | 840 |
+ --------------------- + ------------ + ------ +

我们查看了报告，但找不到2018-07-17的数据，尽管我们确切地记得它们是什么，但我们甚至在2018-10-09 17:23:47中 有一份印刷报告，

让我们教MySQL回顾过去！为此，我们将根据协议编写能够回滚更改的过程。exec_protocol

过程在协议行（p_prot_id）上执行更改

exec_protocol

 DELIMITER $ DROP PROCEDURE IF EXISTS exec_protocol$ CREATE PROCEDURE exec_protocol(p_prot_id BIGINT, direction INT) BEGIN DECLARE p_sql_text TEXT; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT CONCAT( CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1 THEN 'INSERT INTO' WHEN p.oper = 'U' THEN 'UPDATE' WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1 THEN 'DELETE FROM' END , ' ', p.table_name, ' ' , CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1 THEN CONCAT('(', GROUP_CONCAT(pos.column_name ORDER BY pos.column_name SEPARATOR ', '), ')' , ' VALUES (', GROUP_CONCAT(QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END) ORDER BY pos.column_name SEPARATOR ', ' ) , ')' ) WHEN p.oper = 'U' THEN CONCAT('SET ', GROUP_CONCAT(pos.column_name , ' = ', QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END) ORDER BY pos.column_name SEPARATOR ', ' ) , ' WHERE id = ', p.table_id ) WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1 THEN CONCAT('WHERE id = ', p.table_id) END ) sql_text FROM protocol p INNER JOIN protocol_pos pos ON p.id = pos.prot_id WHERE p.id = p_prot_id ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO p_sql_text; IF done THEN LEAVE read_loop; END IF; SET @exec_protocol_sql_text := p_sql_text; SET @disable_all_prot_trg = 1; -- SELECT @exec_protocol_sql_text; PREPARE c_sql FROM @exec_protocol_sql_text; EXECUTE c_sql; DEALLOCATE PREPARE c_sql; SET @disable_all_prot_trg = NULL; END LOOP; CLOSE cur; END$

set_prot_snapshot_id过程回滚/在id范围内回滚协议更改

set_prot_snapshot_id

 DELIMITER $ DROP PROCEDURE IF EXISTS set_prot_snapshot_id$ CREATE PROCEDURE set_prot_snapshot_id(p_beg_prot_id BIGINT, p_end_prot_id BIGINT, direction INT) BEGIN DECLARE p_prot_id BIGINT; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT p.id FROM protocol p WHERE p.id >= p_beg_prot_id AND (p.id <= p_end_prot_id OR p_end_prot_id IS NULL) ORDER BY p.id * direction ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO p_prot_id; IF done THEN LEAVE read_loop; END IF; CALL exec_protocol(p_prot_id, SIGN(direction)); --  direction = -2,       IF direction = -2 THEN DELETE FROM protocol WHERE id = p_prot_id; END IF; END LOOP; CLOSE cur; END$

set_prot_snapshot_date过程回滚/回滚协议更改一段时间

set_prot_snapshot_date

 DELIMITER $ DROP PROCEDURE IF EXISTS set_prot_snapshot_date$ CREATE PROCEDURE set_prot_snapshot_date(p_beg_date TIMESTAMP, p_end_date TIMESTAMP, direction INT) BEGIN DECLARE beg_prot_id BIGINT; DECLARE end_prot_id BIGINT; SET beg_prot_id := (SELECT id FROM protocol WHERE date >= p_beg_date ORDER BY id LIMIT 1); SET end_prot_id := (SELECT id FROM protocol WHERE date <= p_end_date ORDER BY id DESC LIMIT 1); CALL set_prot_snapshot_id(beg_prot_id, end_prot_id, direction); END$

现在，我们可以轻松获取最后日期的销售报告：

 DELIMITER ; BEGIN; CALL set_prot_snapshot_date('2018-10-09 17:23:47', NULL, -1); SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date; ROLLBACK;

+ --------------------- + ------------ + ------ +
 |report_time | 日期| 总和
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:28:30 | 2018-07-17 | 4030 |
+ --------------------- + ------------ + ------ +

如您所见，该报告的结果与过去完全相同。
而且因为我们做了ROLLBACK，现在就可以轻松获得新的报告：

 SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum FROM docs d INNER JOIN doc_pos p ON d.id = p.doc_id GROUP BY d.date;

+ --------------------- + ------------ + ------ +
 |report_time | 日期| 总和
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:29:18 | 2018-07-16 | 2620 |
 |2018-10-09 17:29:18 | 2018-07-18 | 840 |
+ --------------------- + ------------ + ------ +

日志记录可以提供什么功能：

能够完全按照过去的任何时间接收报告。
搜索“弄乱”数据库中数据的用户。
Analytics数据更改历史记录。例如，在系统中传递文档的速度，更改状态的速度。
放弃更改。例如，当删除文档而不是其他问题：“您真的要删除吗？”时，您可以实现取消更改的功能。
扩展名p。4，篮子，取消更改，根据文档的更改历史记录回滚。

UPD1：性能测试

插入10,000行，每行4个字段
1.带触发器和日志记录-6.89c
2.带触发器，通过编程禁用日志记录-2.17s
3.不带触发器-1.37c

SQL查询

 DELIMITER $ DROP PROCEDURE IF EXISTS speed_test$ CREATE PROCEDURE speed_test(n INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < n DO INSERT INTO docs (num, date, warehouse, partner) VALUES (CONCAT('', i), DATE(NOW()), ' ', ', '); SET i := i + 1; END WHILE; END$ -- 1.     DELIMITER ; BEGIN; CALL speed_test(10000); ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (6.89 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.88 sec) -- 2.  ,    DELIMITER ; BEGIN; SET @disable_all_prot_trg = 1; CALL speed_test(10000); SET @disable_all_prot_trg = NULL; ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> SET @disable_all_prot_trg = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (2.17 sec) MariaDB [test-habr]> SET @disable_all_prot_trg = NULL; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.12 sec) -- 3.   DELIMITER ; DROP TRIGGER IF EXISTS docs_aft_ins_trg; BEGIN; CALL speed_test(10000); ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> DROP TRIGGER IF EXISTS docs_aft_ins_trg; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (1.37 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.12 sec)

UPD2：替代系统

1. 在MariaDB 10.3.4中出现了基于系统版本的表
，其优点是：SQL：2011标准中描述的本机解决方案比触发器上的解决方案更有效，这是一个
很大的缺点-您无法找出是谁对数据库进行了更改
2. php-audit，类似于我的解决方案在触发器上，触发器是由php
由专业人士生成的：该解决方案是在github上
从缺点中精心设计的：针对每个目标的单独协议表的存在不允许这样做，首先，很容易跟踪该时段内的所有用户操作，其次，它不便于编写用于回滚的脚本某些数据库个状态

如何教MySQL回顾过去

UPD1：性能测试

UPD2：替代系统

More articles: