在插入或更新时获取完整的 MySQL 查询字符串

2022-01-01 00:00:00 logging mysql triggers

需要有关 MySQL 的帮助，因为它不是我的强项.因此，任何帮助表示赞赏.

Need help with MySQL as it's not really my forte. So any help is appreciated.

我在我的网站上遇到了UPDATE 或INSERT 缺失值的问题.这导致了站点上其他函数的一些问题，但我无法找到在任何类中完成 UPDATE 或 INSERT 的位置.

I have issues on my site where UPDATE or INSERT were done with missing values. This caused some issues on other functions on the site, but I am not able to find where the UPDATE or INSERT were done in any of the classes.

有什么办法，也许是 MySQL 触发器，我可以添加到这些表中，以便我存储 UPDATE 或 INSERT 的原始或完整查询.我尝试过日志记录，但这适用于整个数据库，并且占用了太多磁盘空间.

Is there any way, maybe a MySQL trigger, that I could add to these tables that would allow me to store the original or full query of the UPDATE or INSERT. I have tried logging but that applies to the whole database and it takes up too much diskspace.

提前感谢您的回复.

PS:目前PHP类有点乱，因为我们还处于开发阶段，所以在更新或插入函数中添加异常会花费太多时间.所以请集中回答问题.再次感谢.

PS: At the moment, the PHP classes are a bit messy as we're still in the development stage, so adding exceptions to the updates or inserts functions will take too much time. So please focus the answer to the question. Thanks again.

推荐答案

您可以使用以下语句以字符串形式获取当前 SQL 查询:

You can get the current SQL query as a string with the following statement: 

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

所以你需要做的是创建一个 TRIGGER，它在你的表上的插入和/或更新操作上运行，它应该 (i) 获取当前的 sql 语句并且 (ii) 将其插入到另一个表中，如下所示:

So what you have to do is to create a TRIGGER which runs on insert and/or update operations on your table which should (i) get the current sql statement and (ii) insert it into another table, like so:

DELIMITER |

CREATE TRIGGER log_queries_insert BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    DECLARE original_query VARCHAR(1024);
    SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
    INSERT INTO `app_sql_debug_log`(`query`) VALUES (original_query);
END;
|
DELIMITER ;

您必须创建两个触发器 - 一个用于更新，一个用于插入.触发器将新查询作为字符串插入到 app_sql_debug_log 表的 query 列中.

You will have to create two triggers - one for updates and one for inserts. The trigger inserts the new query as a string in the app_sql_debug_log table in the query column.

相关文章