从 MySQL 触发器调用 PHP 脚本

Invoking a PHP script from a MySQL trigger

提问by Shyju

Is there any way how to invoke a PHP page / function when a record is inserted to a MySQL database table? We don't have control over the record insertion procedure. Is there a trigger mechanism which can call a PHP script back?

当记录插入到 MySQL 数据库表时，有什么方法可以调用 PHP 页面/函数吗？我们无法控制记录插入过程。是否有可以调用 PHP 脚本的触发机制？

采纳答案by Pascal MARTIN

The trigger is executed on the MySQL server, not on the PHP one (even if those are both on the same machine).

触发器在 MySQL 服务器上执行，而不是在 PHP 服务器上执行（即使它们都在同一台机器上）。

So, I would say this is not quite possible -- at least not simply.

所以，我想说这不太可能——至少不是简单的。

Still, considering this entry from the MySQL FAQ on Triggers:

不过，考虑一下 MySQL FAQ on Triggers 中的这个条目：

23.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec()UDF available here: https://github.com/mysqludf/lib_mysqludf_sys#readme

23.5.11: 触发器可以通过 UDF 调用外部应用程序吗？

是的。例如，触发器可以调用sys_exec()此处可用的UDF：https: //github.com/mysqludf/lib_mysqludf_sys#readme

So, there might be a way via an UDF function that would launch the php executable/script. Not that easy, but seems possible. ;-)

因此，可能有一种方法可以通过 UDF 函数启动 php 可执行文件/脚本。不是那么容易，但似乎是可能的。;-)

回答by Mike E.

A friend and I have figured out how to call Bernardo Damele's sys_eval UDF, but the solution isn't as elegant as I'd like. Here's what we did:

我和一个朋友已经想出了如何调用 Bernardo Damele 的 sys_eval UDF，但解决方案并不像我想要的那么优雅。这是我们所做的：

1. Since we're using Windows, we had to compile the UDF library for Windows using Roland Bouman's instructionsand install them on our MySQL server.
2. We created a stored procedure that calls sys_eval.
3. We created a trigger that calls the stored procedure.

1. 由于我们使用的是 Windows，我们必须使用Roland Bouman 的说明为 Windows 编译 UDF 库并将它们安装在我们的 MySQL 服务器上。
2. 我们创建了一个调用 sys_eval 的存储过程。
3. 我们创建了一个调用存储过程的触发器。

Stored Procedure code:

存储过程代码：

DELIMITER $$
CREATE PROCEDURE udfwrapper_sp
(p1   DOUBLE,
 p2   DOUBLE,
 p3 BIGINT)
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result CHAR(255);
 SET cmd = CONCAT('C:/xampp/php/php.exe -f "C:/xampp/htdocs/phpFile.php" ', p1, ' ', p2, ' ', p3);
 SET result = sys_eval(cmd);
END$$;

Trigger code:

触发代码：

CREATE TRIGGER udfwrapper_trigger AFTER INSERT ON sometable
FOR EACH ROW
CALL udfwrapper_sp(NEW.Column1, NEW.Column2, NEW.Column3);

I'm not thrilled about having the stored procedure, and I don't know if it creates extra overhead, but it does work. Each time a row is added to sometable, the trigger fires.

我对拥有存储过程并不感到兴奋，我不知道它是否会产生额外的开销，但它确实有效。每次向某个表中添加一行时，触发器都会触发。

回答by Vladislav Rastrusny

That should be considered a very bad programming practiceto call PHP code from a database trigger. If you will explain the task you are trying to solve using such "mad" tricks, we might provide a satisfying solution.

从数据库触发器调用 PHP 代码应该被认为是一种非常糟糕的编程实践。如果您使用此类“疯狂”技巧来解释您要解决的任务，我们可能会提供令人满意的解决方案。

ADDED 19.03.2014:

2014 年 3 月 19 日添加：

I should have added some reasoning earlier, but only found time to do this now. Thanks to @cmc for an important remark. So, PHP triggers add the following complexities to your application:

我应该早点添加一些推理，但现在才找到时间做这件事。感谢@cmc 的重要评论。因此，PHP 触发器为您的应用程序增加了以下复杂性：

• Adds a certain degree of security problems to the application (external PHP script calls, permission setup, probably SELinux setup etc) as @Johan says.

• Adds additional level of complexity to your application (to understand how database works you now need to know both SQL and PHP, not only SQL) and you will have to debug PHP also, not only SQL.

• Adds additional point of failure to your application (PHP misconfiguration for example), which needs to be diagnosied also ( I think trigger needs to hold some debug code which will log somwewhere all insuccessful PHP interpreter calls and their reasons).

• Adds additional point of performance analysis. Each PHP call is expensive, since you need to start interpreter, compile script to bytecode, execute it etc. So each query involving this trigger will execute slower. And sometimes it will be difficult to isolate query performance problems since EXPLAIN doesn't tell you anything about query being slower because of trigger routine performance. And I'm not sure how trigger time is dumped into slow query log.

• Adds some problems to application testing. SQL can be tested pretty easily. But to test SQL + PHP triggers, you will have to apply some skill.

• 正如@Johan 所说，给应用程序增加了一定程度的安全问题（外部 PHP 脚本调用、权限设置、可能是 SELinux 设置等）。

• 为您的应用程序增加了额外的复杂性（要了解数据库的工作原理，您现在需要同时了解 SQL 和 PHP，而不仅仅是 SQL）并且您还必须调试 PHP，而不仅仅是 SQL。

• 为您的应用程序添加额外的故障点（例如 PHP 配置错误），这也需要诊断（我认为触发器需要保存一些调试代码，这些代码将在某些地方记录所有不成功的 PHP 解释器调用及其原因）。

• 添加额外的性能分析点。每个 PHP 调用都是昂贵的，因为您需要启动解释器、将脚本编译为字节码、执行它等等。所以每个涉及这个触发器的查询都会执行得更慢。有时很难隔​​离查询性能问题，因为 EXPLAIN 没有告诉您任何有关由于触发器例程性能而导致查询变慢的信息​​。而且我不确定如何将触发时间转储到慢查询日志中。

• 给应用测试增加了一些问题。SQL 可以很容易地测试。但是要测试 SQL + PHP 触发器，您必须应用一些技巧。

回答by Lance Rushing

I found this:

我找到了这个：

http://forums.mysql.com/read.php?99,170973,257815#msg-257815

http://forums.mysql.com/read.php?99,170973,257815#msg-257815

DELIMITER $$
CREATE TRIGGER tg1 AFTER INSERT ON `test`
FOR EACH ROW
BEGIN
\! echo "php /foo.php" >> /tmp/yourlog.txt
END $$
DELIMITER ;

回答by phazei

I was thinking about this exact issue for a case with long polling where I didn't want the php script to have to continually poll the db. Polling would need to be done somewhere, memory would probably be best. So if somehow the trigger could put the info into something like memcache, then php could poll that would would be much less intensive overall. Just need a method for mysql to use memcache. Perhaps into a predefined variable with a specific user id. Once the data is retrieved php could reset the var until the db sets it again. Not sure about timing issues though. Perhaps a second variable to store the previous key selected.

对于长时间轮询的情况，我正在考虑这个确切的问题，我不希望 php 脚本必须不断轮询数据库。轮询需要在某个地方进行，内存可能是最好的。因此，如果触发器可以以某种方式将信息放入诸如 memcache 之类的东西中，那么 php 可以轮询，这将大大减少总体上的密集度。只需要一个mysql使用memcache的方法。也许进入具有特定用户 ID 的预定义变量。检索到数据后，php 可以重置 var，直到 db 再次设置它。虽然不确定时间问题。也许是第二个变量来存储选择的前一个键。

回答by Kaippally

In order to get a notification from the database I wrote a command line script using websocket to check for the latest updated timestamp every second. This ran as an infinite loop on the server. If there is a change all connected clients will can be sent a notification.

为了从数据库中获取通知，我使用 websocket 编写了一个命令行脚本，以每秒检查最新更新的时间戳。这在服务器上作为无限循环运行。如果发生更改，所有连接的客户端都会收到通知。

回答by Mohammad

If you have transaction logs in you MySQL, you can create a trigger for purpose of a log instance creation. A cronjob could monitor this log and based on events created by your trigger it could invoke a php script. That is if you absolutely have no control over you insertion.

如果您的 MySQL 中有事务日志，您可以创建一个触发器来创建日志实例。cronjob 可以监视此日志，并根据触发器创建的事件调用 php 脚本。也就是说，如果您完全无法控制您的插入。

回答by StackOverflowed

I don't know if it's possible but I always pictured myself being able to do this with the CSV storage engine in MySQL. I don't know the details of this engine: http://dev.mysql.com/doc/refman/5.7/en/csv-storage-engine.htmlbut you can look into it and have a file watcher in your operating system that triggers a PHP call if the file is modified.

我不知道这是否可能，但我总是想象自己能够使用 MySQL 中的 CSV 存储引擎来做到这一点。我不知道这个引擎的细节：http: //dev.mysql.com/doc/refman/5.7/en/csv-storage-engine.html但你可以查看它并在你的操作中有一个文件观察器如果文件被修改，则触发 PHP 调用的系统。

回答by tutor

A cronjob could monitor this log and based on events created by your trigger it could invoke a php script. That is if you absolutely have no control over you insertion.. If you have transaction logs in you MySQL, you can create a trigger for purpose of a log instance creation.

cronjob 可以监视此日志，并根据触发器创建的事件调用 php 脚本。也就是说，如果您完全无法控制您的插入。如果您的 MySQL 中有事务日志，您可以创建一个触发器来创建日志实例。

回答by Mário de Sá Vera

Run away from store procedures as much as possible. They are pretty hard to maintain and are VERY OLD STUFF ;)

尽可能远离存储过程。它们很难维护，而且是非常旧的东西；)