MySQL触发器 - AFTER INSERT触发器 + UDF sys_exec()问题

Question

问题：我有一个存储某些记录的表格。在完成插入后，我想通过MySQL的sys_* UDFs调用外部程序（php脚本）。 现在的问题是，我编写的触发器将记录的ID传递给脚本。但是，当我尝试通过脚本提取数据时，我得到了0行。 在我的测试中，我得出了这样的结论：触发器在实际插入之前调用php脚本并传递参数，因此我无法获得给定ID的记录。 我已经在Ubuntu操作系统上测试了MySQL 5.0.75和5.1.41。 我可以确认参数在实际插入之前被传递到脚本中，因为我在我的php脚本中添加了sleep(2);，然后正确获取到了数据。如果没有sleep()语句，我会收到给定ID的0条记录。

我的问题是-如何解决这个问题，而不必在php脚本中硬编码一些延迟？ 我不能假设2秒钟（或10秒钟）的延迟足够，所以我希望一切都能“自然”流动，即一个命令完成后，另一个被执行。

我认为，如果触发器是AFTER INSERT类型，那么触发器主体内的所有内容都将在MySQL实际插入数据后执行。

表格布局：

CREATE TABLE test (
id int not null auto_increment PRIMARY KEY,
random_data varchar(255) not null
);

触发布局：

DELIMITER $$

CREATE TRIGGER `test_after_insert` AFTER INSERT ON `test` 
FOR EACH ROW BEGIN

SET @exec_var = sys_exec(CONCAT('php /var/www/xyz/servers/dispatcher.php ', NEW.id));
END;
$$

DELIMITER ;

免责声明：我知道使用sys_exec函数存在安全问题，但我的问题是MySQL不会先插入数据，然后再调用带有必要参数的脚本。如果有人能够说明如何解决这个问题，或者有一种不涉及SELECT INTO OUTFILE和使用FAM的不同方法，我将非常感激。提前致谢。

- Mihael

---

请澄清：PHP脚本dispatcher.php启动，建立到您的数据库的客户端连接，然后进行查询？是这样吗？ - O. Jones

数据被插入，触发器被调用并将ID传递给dispatcher.php。Dispatcher.php检索该行并将其发送到浏览器。我之所以不在插入时发送全部数据，是因为我将插入到一个表中，但我将从视图中提取数据 - 因此我无法知道在任何给定时间将使用所有的列。 - Mihael

那么，dispatcher.php有自己的客户端连接到MySQL？这不会可靠地工作，因为触发器运行的插入/更新事务直到触发器完成后才算完成。因此，dispatcher.php看到的是不一致或预事务数据。无论如何，这就像在馅饼还在烤箱里的时候吃一块馅饼。触发器应该留在数据库服务器内部。 - O. Jones

你要么没有读我写的内容，要么不理解问题，因为在我已经用相当简单的方式解释了正在发生的事情之后，你还问了一个问题。我不想无礼，但是你的回答并没有以任何方式帮助我。 - Mihael

您可以通过MySQL CLI开始测试它，mysql> SELECT sys_exec('php /var/www/xyz/servers/dispatcher.php 100'); 如果无法正常工作，请确保mysql用户具有'rwx'权限。如果没有，请查看dispatcher.php文件的chmod或chown。 - Ivan Carrasco Quiroz

Answer 1

即使您使用AFTER触发器，该行也尚未提交。但是sys_exec（）直到php脚本退出才返回，因此AFTER触发器无法完成，因此您也无法提交INSERT。

这是有意设计的。毕竟，您可以在同一事务中执行更多操作，或者可以回滚事务。这就是从触发器调用外部进程的问题：外部进程无法看到数据库事务范围内的数据。

您不应该使用触发器来执行此任务。最好的方法是使用触发器设置“标志”列，然后编写一个外部进程来查找已设置标志的行，然后调用该PHP脚本。这样只有已成功插入和提交的行才会被处理。

- Bill Karwin

---

@JDPeckham，当然你可以将整行数据传递给脚本。但是如果INSERT语句是事务的一部分，并最终被回滚了呢？那么脚本就会对永远不会在数据库中显示的数据进行操作。 - Bill Karwin

谢谢Bill！那很有道理。我猜可能需要调用一个额外的过程或其他什么东西。 - JDPeckham

Answer 2

如果我理解正确的话，您在DB中插入了一行。这会触发一个触发器，启动一个用PHP编写的外部命令。该命令反过来使用已插入行的id查询同一DB？我不认为这是“延迟”的问题。真正的“问题”是您的初始插入和外部命令在两个不同的sessions中连接到同一DB--可能在两个不同的transactions中（取决于您的数据库引擎和事务隔离级别）。我假设，在触发器被调用时，行插入还没有提交到DB。因此，外部命令仍然看到先前的DB。

---

顺便说一句，如果上述解释过于牵强 -- 对我来说更明显的是你应该考虑一个不同的设计，而不是试图让它按原样工作。

- Sylvain Leroux