How to teach MySQL to look into the past

The article focuses on the logging of changes in MySQL . I want to show the implementation of logging on triggers and what amazing things you can do with it.

Why on triggers? Because there is no access to the binary log. An implementation with a binary log is potentially more productive, although more difficult to develop, since Parse log is required.
')
I just want to warn you that this method will create additional load on the server. And if you have actively changing data, then this solution may not be suitable for you or will require some adjustments and improvements.

In general, the solution is complete and complex. It can be implemented "as is" and perfectly cope with its task.

Everything below is implemented on MariaDB 10.0.32
Recorded columns with types: numbers, lines, dates. The logged table must have a unique NOT NULL numeric id field.

First, create a table with a logging config:

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=' ';

All options are applied during the generation of the trigger for logging. Those. when changing settings, it is necessary to regenerate the triggers.

Command field - protocol configuration option:

disable_protocol - turns off logging.
exclude_table - specifies the table to exclude from logging. By default, all BASE TABLE ENGINE = InnoDB are involved in the logging.
For example,
exclude_table protocol
exclude_table protocol_pos
exclude_column - indicates the field to be excluded from logging. For example, a denormalized field supported by triggers.

For example,
exclude_column docs sum
denormalize_column - indicates the column that must be additionally denormalized to the protocol ( protocol table). By default, all fields are logged in the protocol_pos table.

For example,
denormalize_column docs id doc_id
From the docs table, the id field will be logged to the protocol table in the doc_id column. The doc_id field in the protocol table must be created manually.
denormalize_column doc_pos doc_id doc_id
from the doc_pos table, the doc_id field will be logged into the protocol table in the doc_id column.

Protocol table:

 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 table:

 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='  ';

In the protocol table, we fix the operation, and in the protocol_pos table we enter the changed fields.

Now let's take as a basis the trigger generator from my previous article “Implementing business logic in MySQL” and based on it we will write a generator for logging.

The function gen_bl_trigger of trigger generation of business logic looks at the presence of the procedure <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$

Function gen_prot_trigger generating a logging 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$

The function generate_trigger - business logic + logging:

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$

The generate_triggers function to generate the text of all triggers on the table:

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$

Authorization is described in the article “Implementing Row Level Security on 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=' ';

Now create a couple of test tables:

 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=' ';

Perform a query to check the correctness of triggers in the database:

Request to control the correctness of triggers in the database

 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 ;

 + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
 | table_name | comment | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
 + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
 | docs | Documents |  0 |  0.02 |  NULL | NULL | SELECT generate_triggers ("docs") |
 | doc_pos | Positions of documents |  0 |  0.02 |  NULL | NULL | SELECT generate_triggers ("doc_pos") |
 | protocol | Change Protocol |  0 |  0.02 |  NULL | Not logged | NULL |
 | protocol_config | Setting up logging |  0 |  0.02 |  NULL | NULL | SELECT generate_triggers ("protocol_config") |
 | protocol_pos | Change Log Fields |  0 |  0.02 |  NULL | Not logged | NULL |
 | users | System Users |  0 |  0.02 |  NULL | NULL | SELECT generate_triggers ("users") |
 + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +

The system suggests us to create logging triggers on docs, doc_pos, protocol_config and users tables

Wrap the previous SELECT query and execute it again:

Request to control the correctness of triggers in the database

 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 ;

Result:

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

Now execute this query:

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 ;

+---------------+--------------------------+-------+-----------+-----------+------------------+----------+
|table_name |comment |rows_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 |
|protocol |  | 0| 0.02| NULL| |NULL |
|protocol_config| | 0| 0.02| NULL|NULL |NULL |
|protocol_pos |   | 0| 0.02| NULL| |NULL |
|users |  | 0| 0.02| NULL|NULL |NULL |
+---------------+--------------------------+-------+-----------+-----------+------------------+----------+
6 rows in set, 0 warnings (5.33 sec)

 /* 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 | date | oper | table_name | table_id | vals | username |
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+
 |  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 |
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+

, .

:

 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 | date | sum |
+---------------------+------------+------+
 | 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 | date | oper | table_name | table_id | vals | username |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+
 |  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 | date | sum |
+---------------------+------------+------+
 | 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 | date | sum |
+---------------------+------------+------+
 | 2018-10-09 17:28:30 | 2018-07-17 | 4030 |
+---------------------+------------+------+

, .
And since 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 | date | sum |
+---------------------+------------+------+
 | 2018-10-09 17:29:18 | 2018-07-16 | 2620 |
 | 2018-10-09 17:29:18 | 2018-07-18 | 840 |
+---------------------+------------+------+

, .
, «» .
. , , .
. , : « ?», .
.4, , , .

UPD1:

10000 , 4
1. — 6.89c
2. , — 2.17
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. System-Versioned Tables MariaDB 10.3.4
: , SQL:2011,
—
2. php-audit , , php
: github
: , -, , -,

Source: https://habr.com/ru/post/425769/

All Articles

How to teach MySQL to look into the past

UPD1:

UPD2:

More articles: