当添加数据库中的新行时，必须调用外部命令行程序 [英] When a new row in database is added, an external command line program must be invoked

问题描述

当数据库中的一个表中添加新行时，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语法中找到更多信息参考。

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正在等待您的可执行文件完成，那么插入行可能会很慢。另外，如果乍得桦木是正确的，那么就必须重新编译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调用可执行文件，我将使用触发器记录一行获得INSERTED或UPDATED：记录数据库中的信息，或者使用现有表格中的新列或称为 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 与字段日期， 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. 删除它。 li>
   
4. 重复1-3，直到 database_changes 为空。

1. Select the first row in the database_changes table.
2. Process it.
3. Remove it.
4. Repeat 1-3 until database_changes is empty.

通过这种方法，您可以更好地控制数据的处理时间和方式，您可以轻松检查数据是否已被实际处理（只需检查 database_changes table is empty）。

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).

这篇关于当添加数据库中的新行时，必须调用外部命令行程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！