Geekly articles weekly

Bagaimana cara mengajar MySQL untuk melihat ke masa lalu

Bagaimana cara mengajar MySQL untuk melihat ke masa lalu

Artikel ini akan fokus pada pencatatan perubahan di MySQL . Saya ingin menunjukkan implementasi pencatatan pada pemicu dan hal-hal menakjubkan apa yang dapat Anda lakukan dengannya.

Mengapa menggunakan pemicu? Karena tidak ada akses ke log biner. Implementasi dengan log biner berpotensi lebih produktif, meskipun lebih sulit untuk dikembangkan, karena diperlukan untuk mengurai log.

Saya ingin segera memperingatkan Anda bahwa metode ini akan membuat beban tambahan di server. Dan jika Anda secara aktif mengubah data, maka solusi ini mungkin tidak cocok untuk Anda atau akan memerlukan beberapa penyesuaian dan peningkatan.

Secara umum, solusinya lengkap dan kompleks. Itu dapat diimplementasikan "apa adanya" dan dengan sempurna mengatasi tugasnya.

Semuanya di bawah ini diimplementasikan pada MariaDB versi 10.0.32
Kolom dengan tipe dicatat: angka, string, tanggal. Tabel yang dicatat harus memiliki bidang ID numerik BUKAN NULL yang unik.

Pertama, buat tabel dengan konfigurasi logging:

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

Semua opsi diterapkan selama pembuatan trigger untuk logging. Yaitu saat mengubah pengaturan, Anda perlu membuat ulang pemicu.

Bidang perintah - opsi konfigurasi protokol:

  1. disable_protocol - menonaktifkan logging.
  2. exclude_table - menunjukkan tabel yang dikecualikan dari logging. Secara default, semua ENGINE BASE TABLE = InnoDB terlibat dalam logging.
    Sebagai contoh
    protokol exclude_table
    protokol_pos exclude_table
  3. exclude_column - menunjukkan bidang yang dikecualikan dari logging. Misalnya, bidang denormalized yang didukung oleh pemicu.

    Sebagai contoh
    jumlah dokumen exclude_column
  4. denormalize_column - menunjukkan kolom yang harus didenormalisasi tambahan ke dalam protokol (tabel protokol ). Secara default, semua bidang dicatat dalam tabel protocol_pos .

    Sebagai contoh
    denormalize_column docs id doc_id
    dari tabel docs , bidang id akan login ke tabel protokol di kolom doc_id . Bidang doc_id dalam tabel protokol harus dibuat dengan tangan.
    denormalize_column doc_pos doc_id doc_id
    bidang doc_id akan dicatat dari tabel doc_pos ke tabel protokol di kolom doc_id .

Tabel protokol:

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

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

Dalam tabel protokol , kami melakukan operasi, dan dalam tabel protocol_pos kami memasukkan bidang yang diubah.

Sekarang mari kita ambil pemicu generator dari artikel saya sebelumnya "Menerapkan logika bisnis di MySQL" sebagai dasar dan menulis generator untuk logging berdasarkan itu.

Fungsi generasi pemicu logika bisnis gen_bl_trigger melihat keberadaan prosedur <nama tabel> _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$


Log fungsi pemicu generasi 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$


Fungsi Generate_trigger - logika bisnis + masuk:

menghasilkan_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$


Fungsi generate_triggers untuk menghasilkan teks dari semua pemicu pada tabel:

menghasilkan_trigger
 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$


Otorisasi dijelaskan dalam artikel "Menerapkan Keamanan Level Baris pada 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=' ';

Sekarang buat beberapa pola pengujian:

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

Mari kita jalankan permintaan untuk mengontrol kebenaran pemicu dalam database:

Permintaan untuk mengontrol kebenaran pemicu dalam 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 | komentar | 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 | Posisi dokumen |  0 |  0,02 |  NULL | NULL | SELECT generate_triggers ("doc_pos") |
 | protokol | Protokol perubahan |  0 |  0,02 |  NULL | Tidak dicatat | NULL |
 | protocol_config | Konfigurasi Logging |  0 |  0,02 |  NULL | NULL | SELECT generate_triggers ("protocol_config") |
 | protocol_pos | Ubah Bidang Protokol |  0 |  0,02 |  NULL | Tidak dicatat | NULL |
 | pengguna | Pengguna sistem |  0 |  0,02 |  NULL | NULL | SELECT generate_triggers ("pengguna") |
 + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +

Sistem menawarkan kami untuk membuat pemicu pencatatan pada tabel docs, doc_pos, protocol_config dan pengguna

Bungkus kueri sebelumnya dengan SELECT dan jalankan lagi:

Permintaan untuk mengontrol kebenaran pemicu dalam 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 ;

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

Mari kita lakukan permintaan ini sekarang:

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$


Kami mendapatkan teks dari pemicu, jalankan (dengan DELIMITER $ )

Sekarang tabel kami dicatat dan semua perubahan data ditulis ke protokol.
Periksa pengaturan logging dengan permintaan pertama:
Permintaan untuk mengontrol kebenaran pemicu dalam database
 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 | komentar | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
| docs | Documents | 0 | 0,02 | NULL | NULL | NULL |
| doc_pos | Posisi dokumen | 0 | 0,02 | NULL | NULL | NULL |
| protokol | Protokol perubahan | 0 | 0,02 | NULL | Tidak dicatat | NULL |
| protocol_config | Konfigurasi Logging | 0 | 0,02 | NULL | NULL | NULL |
| protocol_pos | Ubah Bidang Protokol | 0 | 0,02 | NULL | Tidak dicatat | NULL |
| pengguna | Pengguna sistem | 0 | 0,02 | NULL | NULL | NULL |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
6 baris diatur, 0 peringatan (5,33 detik)

Tambahkan pengguna dan masuk:
 /* 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)

Masukkan dokumen uji:

 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;

Mari kita lihat apa yang terjadi dalam protokol:

 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;

Minta 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 | tanggal | oper | table_name | table_id | vals | nama pengguna |
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+
 | 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 | Saya | doc_pos | 3 | jumlah: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), bahan: (NULL, Ballpoint pen), harga: (NULL, 30) | test@test.ru |
+ ---- + --------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +

Seperti yang Anda lihat, semua perubahan basis data dicatat, mulai dari penyisipan pengguna.

Kami akan menerima laporan penjualan:

 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 | tanggal | jumlah |
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:23:47 | 2018-07-17 | 4030 |
+ --------------------- + ------------ + ------ +

Sekarang ubah dokumen yang ada dan tambahkan yang lain:
 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;

Ini akan terlihat seperti protokol baru
 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 |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+
Dapatkan laporan baru:
 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 | tanggal | jumlah |
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:26:18 | 2018-07-16 | 2620 |
 |2018-10-09 17:26:18 | 2018-07-18 | 840 |
+ --------------------- + ------------ + ------ +

Kami melihat laporan dan tidak dapat menemukan data untuk 2018-07-17 , meskipun kami ingat persis apa itu, kami bahkan memiliki laporan cetak pada 2018-10-09 17:23:47

Mari kita ajarkan MySQL untuk melihat ke masa lalu! Untuk melakukan ini, kami akan menulis prosedur yang, menurut protokol, dapat mengembalikan perubahan.

Prosedur exec_protocol melakukan perubahan pada baris protokol (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$


Prosedur set_prot_snapshot_id memutar kembali / menggulung perubahan protokol pada rentang 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$


Prosedur set_prot_snapshot_date memutar kembali / menggulung perubahan protokol selama suatu periode

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$


Sekarang kita dapat dengan mudah mendapatkan laporan penjualan untuk tanggal terakhir:

 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 | tanggal | jumlah |
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:28:30 | 2018-07-17 | 4030 |
+ --------------------- + ------------ + ------ +

Seperti yang Anda lihat, laporan itu ternyata persis seperti dulu.
Dan sejak itukami melakukan ROLLBACK , sekarang mudah untuk mendapatkan laporan baru:

 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 | tanggal | jumlah |
+ --------------------- + ------------ + ------ +
 |2018-10-09 17:29:18 | 2018-07-16 | 2620 |
 |2018-10-09 17:29:18 | 2018-07-18 | 840 |
+ --------------------- + ------------ + ------ +


Fungsionalitas apa yang dapat dicatat oleh logging:

  1. Kemampuan untuk menerima laporan pada tanggal berapa pun di masa lalu, persis seperti saat itu.
  2. Cari pengguna yang "mengacaukan" data dalam database.
  3. Riwayat perubahan data Analytics. Misalnya, kecepatan dokumen yang lewat dalam sistem, mengubah status.
  4. Buang perubahan. Misalnya, saat menghapus dokumen alih-alih pertanyaan tambahan: "Apakah Anda benar-benar ingin menghapus?", Anda dapat menyadari kemampuan untuk membatalkan perubahan.
  5. Ekstensi hal. 4, keranjang, pembatalan perubahan, kembalikan sesuai dengan sejarah perubahan dokumen.


UPD1: tes kinerja


Masukkan 10.000 baris, 4 bidang per baris
1. dengan pemicu dan pencatatan - 6.89c
2. dengan pemicu, pencatatan dinonaktifkan secara pemrograman - 2.17s
3. tanpa pemicu - 1.37c
Pertanyaan 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: sistem alternatif


1. Tabel System-Versioned muncul di MariaDB 10.3.4
dari pro: solusi asli, yang dijelaskan dalam standar SQL: 2011, mungkin bekerja lebih efisien daripada solusi pemicu,
minus yang signifikan - Anda tidak dapat menemukan siapa yang membuat perubahan pada database
2. php-audit , solusi yang mirip dengan tambang pada pemicu, pemicu dihasilkan oleh php
dari pro: solusinya dirancang dengan indah pada github
dari minus: keberadaan tabel protokol terpisah untuk setiap target tidak memungkinkan, pertama, mudah untuk melacak semua tindakan pengguna untuk periode tersebut, dan kedua, tidak mudah untuk menulis skrip untuk rollback DB di tertentu th negara

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

More articles:


All Articles