我可以在 mysql 中的 select 语句上启动触发器吗？

Can I launch a trigger on select statement in mysql?

提问by Ahmad A Bazadgha

I am trying to run an INSERTstatement on table X each time I SELECTany record from table Y is there anyway that I can accomplish that using MySQL only?

INSERT每次我SELECT从表 Y 中的任何记录时，我都试图在表 X 上运行一条语句，无论如何我只能使用 MySQL 来完成它？

Something like triggers?

触发器之类的？

回答by ypercube??

Short answer is No. Triggers are triggered with INSERT, UPDATEor DELETE.

简短的回答是否定的。触发器由INSERT,UPDATE或触发DELETE。

Possible solution for this. rather rare scenario:

可能的解决方案。比较少见的场景：

• First, write some stored procedures that do the SELECTs you want on table X.
• Then, restrict all users to use only these stored procedures and do not allow them to directly use SELECTon table X.
• Then alter the stored procedures to also call a stored procedure that performs the action you want (INSERTor whatever).

• 首先，编写一些存储过程，SELECT在表 X 上执行您想要的操作。
• 然后，限制所有用户只能使用这些存储过程，不允许他们直接SELECT在表 X 上使用。
• 然后更改存储过程以调用执行您想要的操作（INSERT或其他操作）的存储过程。

回答by Ian Wood

Nope - you can't trigger on SELECT - you'll have to create a stored procedure (or any other type of logging facility - like a log file or what ever) that you implicitly call on any query statement - easier if you create a wrapper that calls your query, calls the logging and returns query results.

不-您不能在 SELECT 上触发-您必须创建一个存储过程（或任何其他类型的日志记录工具-例如日志文件或其他任何东西），您在任何查询语句上隐式调用-如果您创建一个，则更容易调用您的查询、调用日志记录并返回查询结果的包装器。

回答by David Townes

If you're trying to use table X to log the order of SELECTqueries on table Y (a fairly common query-logging setup), you can simply reverse the order of operations and run the INSERTquery first, thenrun your SELECTquery.

如果您尝试使用表 X 来记录SELECT表 Y 上的查询顺序（一种相当常见的查询记录设置），您可以简单地颠倒操作顺序并首先运行INSERT查询，然后运行您的查询。SELECT

That way, you don't need to worry about linking the two statements with a TRIGGER: if your server crashes between the two statements then you already logged what you care about with your first statement, and whether the SELECTquery runs or fails has no impact on the underlying database.

这样，您无需担心将两个语句与 a 链接TRIGGER：如果您的服务器在两个语句之间崩溃，那么您已经用第一个语句记录了您关心的内容，并且SELECT查询是否运行或失败对底层数据库。

If you're not logging queries, perhaps you're trying to use table Y as a task queue -- the situation I was struggling with that lead me to this thread -- and you want whichever session queries Y first to lock all other sessions out of the rows returned so you can perform some operations on the results and insert the output into table X. In that case, simply add some logging capabilities to table Y.

如果您不记录查询，也许您正尝试将表 Y 用作任务队列——我一直在努力解决的情况导致我进入这个线程——并且您希望哪个会话查询 Y 首先锁定所有其他会话在返回的行之外，您可以对结果执行一些操作并将输出插入表 X。在这种情况下，只需向表 Y 添加一些日志记录功能。

For example, you could add an "owner" column to Y, then tack the WHEREpart of your SELECTquery onto an UPDATEstatement, run it, and then modify your SELECTquery to only show the results that were claimed by your UPDATE:

例如，您可以向 Y 添加一个“所有者”列，然后WHERE将SELECT查询的一部分附加到UPDATE语句上，运行它，然后修改您的SELECT查询以仅显示由您声明的结果UPDATE：

UPDATE Y SET owner = 'me' WHERE task = 'new' AND owner IS NULL;
SELECT foo FROM Y WHERE task = 'new' AND owner = 'me';

...do some work on foo, then...

...对 foo 做一些工作，然后...

INSERT INTO X (output) VALUES ('awesomeness');

Again, the key is to log first, then query.

再次，关键是要登录先，然后查询。