MySQL触发器获取导致触发器触发的当前查询 [英] MySQL Trigger get current query that caused trigger to fire

问题描述

过去4个小时，我一直在网上搜寻解决方案，但我能找到的只是:

I have scoured the web for the past 4 hours in search of a solution but all i can find is:

你不能. 不可能的. 不会发生的.

You cant. Impossible. Not gonna happen.

我不喜欢这种方法.

我的理论是，如果将它存储在information_schema.processlist中的某个位置，那么就有某种方法可以抓住它. 我试图使触发器在processlist表上运行，但无法使其正常工作，我认为是因为它是视图. 运行日志不是一种选择，因为它会影响已经拥挤的服务器的性能，并且有许多查询正在运行，我只希望它登录到特定表，然后再登录更新.

My theory is, if it gets stored at some point in information_schema.processlist, then there HAS to be someway to grab it. I tried to make a trigger run on the processlist table but couldnt get that to work, I think because its a view. Running the log is not an option as that will effect performance on an already croweded server, as well as there are many queries running and I only want it to log on a specific table and then only on update..

这是传说中的stackoverflow！在这里，您可以找到所有问题的解决方案，因为它充满了天才！！！

This is the legendary stackoverflow! Where you can find solutions to all your problems because it is overflowing with geniuses!!!

所以，我求求你，天才在哪里！展示自己！ :-)

So, I beg, where are the geniuses!! Show yourselves!! :-)

谢谢， Zedd

推荐答案

此处的问题是MySQL触发器的范围是行级的，而不是语句级的.这样，在触发器中，您可以访问给定行中每一列的OLD和NEW值，但是您无权访问导致触发器触发的语句.

The issue here is that the scope of MySQL triggers is row-level, not statement-level. As such, within the trigger you have access to the OLD and NEW values for each column in the given row, but you do not have access to the statement that caused the trigger to fire.

关于information_schema.processlist，该视图中实际上没有存储"(持久化)任何内容.这只是进程列表的SQL接口，在触发器的范围内无法访问导致触发器触发的语句.

In regard to information_schema.processlist, nothing is actually "stored" (persisted) in that view. It's just a SQL interface to the processlist, and the statement that caused the trigger to fire is not accessible within the scope of the trigger.

您说过，您不想启用常规查询日志，并且由于多种原因(包括event_Time的粒度为1秒)，这种方法也不是完美的方法，但这是一个如何重写您的示例.使用general_log表触发:

You said you don't want to enable the general query log, and this approach isn't perfect for multiple reasons (including the granularity of event_Time being 1 second), but here's an example of how you could re-write your trigger using the general_log table:

SET GLOBAL GENERAL_LOG='ON';
SET GLOBAL LOG_OUTPUT='TABLE';

DELIMITER || 

CREATE TRIGGER DEBUG_DATE BEFORE UPDATE ON db.tbl FOR EACH ROW 
BEGIN 
  DECLARE Q MEDIUMTEXT; 
  SELECT argument INTO Q 
  FROM mysql.general_log 
  where thread_id = connection_id() 
  order by event_time desc 
  limit 1;

  INSERT INTO db.tbl_log (INFO) 
  VALUES (Q); 

END ||

DELIMITER ;

这篇关于MySQL触发器获取导致触发器触发的当前查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！