在插入或更新时获取完整的MySQL查询字符串 [英] Get Full MySQL Query String on Insert or Update

问题描述

需要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，该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 ;

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

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.

这篇关于在插入或更新时获取完整的MySQL查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！