在数据库中添加新行时，必须调用外部命令行程序

2021-12-26 00:00:00 mysql database-design

当新行添加到数据库中的一个表时，MySQL 数据库是否可以调用外部 exe 文件?

Is it possible for MySQL database to invoke an external exe file when a new row is added to one of the tables in the database?

我需要监控数据库中的变化，所以当发生相关的变化时，我需要在数据库外做一些批处理作业.

I need to monitor the changes in the database, so when a relevant change is made, I need to do some batch jobs outside the database.

推荐答案

Chad Birch 有一个使用 MySQL 触发器和用户定义的函数.您可以在 MySQL CREATE TRIGGER Syntax 中找到更多信息参考.

Chad Birch has a good idea with using MySQL triggers and a user-defined function. You can find out more in the MySQL CREATE TRIGGER Syntax reference.

但是您确定需要在插入行时立即调用可执行文件吗?该方法似乎很容易失败，因为 MySQL 可能会同时生成多个可执行文件实例.如果您的可执行文件失败，那么将没有记录哪些行已经被处理，哪些还没有.如果 MySQL 正在等待您的可执行文件完成，那么插入行可能会很慢.另外，如果Chad Birch是对的，那么就必须重新编译MySQL，所以听起来很困难.

But are you sure that you need to call an executable right away when the row is inserted? It seems like that method will be prone to failure, because MySQL might spawn multiple instances of the executable at the same time. If your executable fails, then there will be no record of which rows have been processed yet and which have not. If MySQL is waiting for your executable to finish, then inserting rows might be very slow. Also, if Chad Birch is right, then will have to recompile MySQL, so it sounds difficult.

我不会直接从 MySQL 调用可执行文件，而是使用触发器来简单地记录一行被插入或更新的事实:在数据库中记录该信息，在现有表中使用新列或使用全新表称为 database_changes.然后制作一个外部程序，定期从数据库中读取信息，对其进行处理，并将其标记为完成.

Instead of calling the executable directly from MySQL, I would use triggers to simply record the fact that a row got INSERTED or UPDATED: record that information in the database, either with new columns in your existing tables or with a brand new table called say database_changes. Then make an external program that regularly reads the information from the database, processes it, and marks it as done.

您的具体解决方案将取决于外部程序实际需要的参数.

Your specific solution will depend on what parameters the external program actually needs.

如果您的外部程序需要知道插入了哪一行，那么您的解决方案可能是这样的:创建一个名为 database_changes 的新表，其中包含字段 date, table_name 和 row_id，以及所有其他表，像这样创建触发器:

If your external program needs to know which row was inserted, then your solution could be like this: Make a new table called database_changes with fields date, table_name, and row_id, and for all the other tables, make a trigger like this:

CREATE TRIGGER `my_trigger`
AFTER INSERT ON `table_name`
FOR EACH ROW BEGIN
  INSERT INTO `database_changes` (`date`, `table_name`, `row_id`)
  VALUES (NOW(), "table_name", NEW.id)
END;

然后你的批处理脚本可以做这样的事情:

Then your batch script can do something like this:

  1. 选择 database_changes 表中的第一行.
  2. 处理它.
  3. 删除它.
  4. 重复 1-3 直到 database_changes 为空.

使用这种方法，您可以更好地控制处理数据的时间和方式，并且可以轻松检查数据是否实际得到处理(只需检查 database_changes 表为空).

With this approach, you can have more control over when and how the data gets processed, and you can easily check to see whether the data actually got processed (just check to see if the database_changes table is empty).

相关文章