如何实时检查是否在MySQL表中添加了新行 [英] How to check in real-time if new row was added to MySQL table

问题描述

我们有一个自动车牌读取器，可以记录进入公司的车牌.我的同事问我是否可以立即获得即将到来的汽车的车牌号.该软件使用MySQL，而我只有数据库访问权限.无法访问/编辑PHP代码.

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.

我的提议是定期使用查询进行检查.例如10秒.但是通过这种方式，有可能错过5秒之内即将到来的汽车.然后，减少间隔会增加请求/响应计数，这意味着服务器的额外负载. 我不希望脚本始终运行.它应该只运行添加的新数据库行.它显示了板块并退出.

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.

如何在插入后立即从数据库获取最后记录的行?我的意思是插入后应该有运行我的PHP脚本的触发器.但是我不知道.

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.

我想要的是MySQL可以在新记录后运行我的PHP脚本.

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

推荐答案

如果您的表是MyISAM，我会坚持您的最初想法.从MyISAM表获取行数是即时的. MyISAM始终保持行数，因此只读取一个值即可.

If your table is MyISAM, I would stick to your initial idea. Getting the row count from a MyISAM table is instant. It only takes the reading of one single value as MyISAM maintains the row count at all times.

对于InnoDB，这种方法仍然可以接受.假设car_table.id是主键，则SELECT COUNT(id) FROM car_table仅需要索引扫描，这非常快.您可以通过向表中添加另一个索引的布尔列来改进此想法:

With InnoDB, this approach can still be acceptable. Assuming car_table.id is primary key, SELECT COUNT(id) FROM car_table only requires an index scan, which is very fast. You can improve on this idea by adding another indexed boolean column to your table:

ALTER car_table ADD COLUMN checked BOOLEAN NOT NULL DEFAULT 0, ADD INDEX (checked);

默认值可确保在不修改插入语句的情况下将此标志设置为0插入新车.然后:

The default value ensures new cars will be inserted with this flag set to 0 without modifying the inserting statement. Then:

BEGIN TRANSACTION; -- make sure nobody interferes
SELECT COUNT(checked) FROM car_table WHERE checked = FALSE FOR UPDATE; -- this gets you the number of new, unchecked cars
UPDATE car_table SET checked = TRUE WHERE checked = FALSE; -- mark these cars as checked
COMMIT;

这样，您每次轮询仅扫描很少数量的索引条目.

This way, you only scan a very small number of index entries at each polling.

一种更高级的方法是通过触发器将新创建的汽车ID添加到边桌中.在不锁定主表且不更改其结构的情况下，不时扫描此边桌.每次轮询后只需TRUNCATE此边桌即可.

A more advanced approach consists in adding newly created cars ID's into a side table, through a trigger. This side table is scanned every now and then, without locking the main table, and without altering its structure. Simply TRUNCATE this side table after each polling.

最后，正如Panagiotis所建议的那样，可以选择触发UDF，但这在大多数情况下似乎是过大的选择.

Finally, there is the option of triggering a UDF, as suggested by Panagiotis, but this seems to be an overkill in most situations.

这篇关于如何实时检查是否在MySQL表中添加了新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！