动态构造MySQL代码以创建触发器 [英] Dynamically constructing MySQL Code for creating a trigger

问题描述

我正在尝试为MySQL数据库实施审核跟踪/记录。现在，我创建了一个审核数据库（简称为 company_audit ），该数据库将包含主数据库中某些表的审核副本。 （对于 company.customers 说，我将创建 company_audit.customers 。审核表将包含与中相同的确切列

I am trying to implement audit trail/logging for a MySQL database. Now I have created an "audit" db (let's call it company_audit) which will contain "audit" copies of certain tables in the main db. (Say for company.customers I will create company_audit.customers. The audit table will contain the same exact columns as in the original table.

我想使用触发器将原始表中的更改插入审核表，如下所示：

I want to use a trigger to insert changes in the original table into the audit table like so:

DELIMITER $$
DROP TRIGGER IF EXISTS customers_history_AU$$
CREATE TRIGGER customers_history_AU
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO company_audit.customers (customer_id, col2, col3, col4, col5)
  VALUES (OLD.customer_id, OLD.col2, OLD.col3, OLD.col4, OLD.col5);
END$$
DELIMITER ;

现在我的问题是：我在上面的清单（使用存储过程）中构造了SQL字符串，以便可以动态获取列名（例如，从 information_schema 获取）。

Now to my question: How can I construct the SQL string in the listing above (using say, a stored procedure) such that the column names can be obtained dynamically (say, from information_schema).

我在这里看到了类似的内容： http://uber-code.blogspot.com/2011/02/mysql-audit-logging-triggers.html ，但此处的代码并不是要从原始表中检索列名，否则我可能无法如此成功地解决问题！

I have seen something similar here: http://uber-code.blogspot.com/2011/02/mysql-audit-logging-triggers.html, but the code here is not meant for retrieving column names from the original table, or perhaps I couldn't wrap my head around it so successfully!

谢谢。

推荐答案

对于此问题尚未收到任何明确的解决方案，我已开始研究概念验证选项（因为MySQL本身不允许您运行SQL代码使用Prepared Statements创建触发器）。

Not having received any definite solution for this question, I have proceeded to cobble up a proof of concept option (since MySQL natively would not let you run SQL code that creates a trigger, using Prepared Statements). Please feel free to make any positive input.

DELIMITER //
DROP PROCEDURE IF EXISTS createAuditTable//
CREATE PROCEDURE createAuditTable(tblname CHAR(30), sufftxt CHAR(10), pri CHAR(20), filename CHAR(255) )
BEGIN
    SELECT DATABASE() INTO @dbname;
    SET @srctbl = CONCAT(@dbname, ".", tblname);
    SET @destdb = CONCAT(@dbname, "_", sufftxt);
    SET @desttbl = CONCAT(@destdb, ".", tblname);

    SET @str1 = CONCAT( "CREATE DATABASE IF NOT EXISTS ", @destdb);
    PREPARE stmt1 FROM @str1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    SET @str2 = "SET FOREIGN_KEY_CHECKS=0";
    PREPARE stmt2 FROM @str2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    SELECT COUNT(*) FROM information_schema.tables WHERE table_name = tblname AND table_schema = @destdb INTO @tblcount;
    IF (@tblcount = 0) THEN 
        SET @str3 = CONCAT("CREATE TABLE ", @desttbl, " LIKE ", @srctbl);
        PREPARE stmt3 FROM @str3;
        EXECUTE stmt3;
        DEALLOCATE PREPARE stmt3;
    END IF;

    SELECT COUNT(*) FROM information_schema.columns WHERE table_name = tblname AND table_schema = @destdb AND column_key = 'PRI' INTO @keycount;

    IF (@keycount <> 0) THEN 
        SET @str4 = CONCAT("ALTER TABLE ", @desttbl, " DROP PRIMARY KEY, ADD INDEX ", pri, " (", pri, ")" );
        PREPARE stmt4 FROM @str4;
        EXECUTE stmt4;
        DEALLOCATE PREPARE stmt4;
    END IF;

SELECT CONCAT( "DELIMITER $$
DROP TRIGGER IF EXISTS ", tblname, "_history_BU$$
CREATE TRIGGER ", tblname, "_history_BU
BEFORE UPDATE ON ", tblname, "
FOR EACH ROW
BEGIN
    INSERT INTO ", @desttbl, " (",
(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = @dbname AND table_name = tblname), ") ",
    "
    VALUES(", 
(SELECT GROUP_CONCAT('OLD.', column_name) FROM information_schema.columns WHERE table_schema = @dbname AND table_name = tblname),
 ");
END$$
DELIMITER ;"
 ) AS qstr FROM DUAL INTO @triggertxt;

SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', filename, '"');
PREPARE stmt5 FROM @savestr;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;


END//
DELIMITER ;  

要使用，请致电以下程序：

TO USE, call the Procedure:

CALL createAuditTable('name_of_table', 'history', 'pri_key_fld', 'path/to/file.sql');

使用当前工作数据库的名称创建一个新数据库，后缀为 _history附加到它。在此新数据库中创建表 name_of_table，与原始表
相同。字段 pri_key_fld（应为表 name_of_table的主键/唯一键）被转换为普通的 INDEX键。这样做的目的是避免将来在对多行进行审核日志记录时出现唯一的违规行为。

A new database is created using the name of your current working DB, with a suffix of "_history" appended to it. The table "name_of_table" is created in this new DB, identical to the original table The field "pri_key_fld" (which should be the primary/unique key of the table "name_of_table") is converted into an ordinary "INDEX" key. The purpose of this is to avert unique violations during audit logging of multiple rows in the future.

在运行过程创建的文件后：
SOURCE'path / to / file.sql';
（或从该文件运行SQL的任何其他语法）

THEN Run the file created by the procedure: SOURCE 'path/to/file.sql'; (or any alternative syntax to run SQL from that file)

几个警告：
现在，您只能为 pri_key_fld提供一个字段。理想情况下，我们希望提供一个包含该表中所有唯一字段的数组。当前，如果您有多个唯一字段，则唯一冲突将阻止您记录多个行。

A couple of Caveats: Right now, you can only supply one field for "pri_key_fld". Ideally, we would want to supply an "array" containing all the unique fields in that table. Currently, if you have more than one unique field, unique violations will prevent you from logging more than one row. And that's not nice!

再次，显然，在磁盘上创建文件的过程非常笨拙且性能不佳，只能从同一目录读取SQL文件在下一个命令中。可以尝试改善的另一种方法是：从命令行运行 CALL createAuditTable 部分，将输出捕获为文本，然后在该命令的位置与SQL相同地运行线。我确实在Windows PowerShell上尝试过；但是输出中充满了文字 \r\n字符串（代表换行符）。我没有时间立即清理掉这个字符串，所以现在就在冰箱里！

Again, it is obviously very clumsy and non-performant to go through the process of creating a file on disk, only to read SQL from the same file in the next command. One alternative one can explore to ameliorate is this: Run the CALL createAuditTable portion from the command line, catch the output as text, then Run the same as SQL right there on the command line. I did attempt that on Windows PowerShell; but the output was riddled with literal "\r\n" strings (representing line breaks). I didn't have the time to immediately work on cleaning out this string, so it's in the fridge now!

最后，哦，MySQL忍者，请很好。我不是专业人士，真的。这只是解决自己的实际问题的一种尝试。

Finally, O ye MySQL ninjas, please be nice. I'm no pro, really. This is just a grow-your-own-grocery attempt at solving a practical problem.

谢谢。

这篇关于动态构造MySQL代码以创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！