如何在MySQL表中添加新行时实时检查

Question

We have an automatic car plate reader which records plates of the cars enter to firm. My colleague asked me if we can instantly get the plate number of the car coming. The software uses MySQL and I have only database access. Cannot reach/edit PHP codes.

My offer is to check using a query periodically. For example for 10 seconds. But in this way it is possible to miss the cars coming in 5 seconds. Then decreasing interval increases request/response count which means extra load for the server. I do not want the script to run always. It should run only a new db row added. It shows the plate and exits.

How can I get last recorded row from the db right after inserting? I mean there should be trigger which runs my PHP script after insertion. But I do not know.

What I want is MySQL could run my PHP script after a new record.

Answer 1

Although this is not the greatest of designs and I have not implemented it, there is way to call an external script through sys_exec() UDF using a trigger as mentioned here:

B.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec() UDF.

http://dev.mysql.com/doc/refman/5.1/en/faqs-triggers.html#qandaitem-B-5-1-11

Also have a look on this thread which is similar to your needs.

Invoking a PHP script from a MySQL trigger