从 mysql 中的触发器调用 url

Calling an url from a trigger in mysql

提问by Saikios

I know it's highly unrecommended,

我知道这是极不推荐的，

I know that it's an issue with performance, speed, etc, but it's for an integration, and they only are doing their updates via mysql (I know it's crazy to do that too but I can't change what they do, and they are making a ton of sales so they don't want to change anything).

我知道这是性能、速度等方面的问题，但这是为了集成，他们只通过 mysql 进行更新（我知道这样做也很疯狂，但我无法改变他们所做的，而且他们是进行大量销售，所以他们不想改变任何东西）。

I only need to post to a URL (it can be as simple as http://www.google.com?id=skuid)

我只需要发布到一个 URL（它可以像http://www.google.com?id=skuid一样简单）

I read this blogs and stacks, but they are 2+ years old, would like to know if there are alternatives to using an udf:

我阅读了这个博客和堆栈，但它们已经超过 2 年了，想知道是否有使用 udf 的替代方法：

http://open-bi.blogspot.pe/2012/11/call-restful-web-services-from-mysql.html

http://open-bi.blogspot.pe/2012/11/call-restful-web-services-from-mysql.html

http://www.mooreds.com/wordpress/archives/1497

http://www.mooreds.com/wordpress/archives/1497

Calling a php file by using mysql trigger

使用mysql触发器调用php文件

Thanks a lot for everything!!

非常感谢一切！！

回答by ivan_pozdeev

To trigger an external action, you have to use a UDF - it's the only way for mysqlto tell something to the "outside world". The only alternativeis an external agent polling the DB constantly - which is an inferior solution.

要触发外部操作，您必须使用 UDF - 这mysql是向“外部世界”传达信息的唯一方法。唯一的选择是外部代理不断轮询数据库 - 这是一个较差的解决方案。

As for the choice of a UDF,

至于UDF的选择，

• to minimize load on the DB, it should probably be something that finishes quickly (note that UDFs run synchronously).
• So, unless the installation is sufficiently small-scale, it's going to merely notify an external agent of the event. This also minimizes error handling at the DB side.
  • Otherwise, if you don't (yet) care, you can e.g. just spawn curlfor all it's worth.

• 为了最小化数据库上的负载，它可能应该是快速完成的（注意 UDF 是同步运行的）。
• 因此，除非安装规模足够小，否则它只会将事件通知外部代理。这也最大限度地减少了 DB 端的错误处理。
  • 否则，如果你（还）不关心，你可以例如只curl为它的价值而产生。

Ways that come to mind:

想到的方法：

• spawn a small program - e.g. touchsome file which the agent watches. There's an existing sys_execthat uses system()(with all due considerations).
• IPC (signal is the simplest; with others, you can pass additional information but it requires more setup)

• 生成一个小程序——例如touch代理监视的一些文件。有一个现有的sys_exec使用system()（考虑到所有应有的考虑）。
• IPC（信号是最简单的；对于其他信号，您可以传递附加信息，但需要更多设置）

As the sys_exec's sourceshows, it's not so hard to write a UDF, so you aren't really limited to what's already available (this may explain why lib_mysqludf_sysis so limited: if you need something better, it's sufficiently easy to write a task-specific function). The current docs are at 26.4.2 Adding a New User-Defined Function - MySQL 5.7 Reference Manual.

正如sys_exec源代码所示，编写 UDF 并不难，因此您并不仅限于已经可用的内容（这可以解释为什么lib_mysqludf_sys如此有限：如果您需要更好的东西，编写任务就足够容易了-具体功能）。当前文档位于26.4.2 Add a New User-Defined Function - MySQL 5.7 Reference Manual。

回答by Jan

Here's a solution for a MySQL server 5.6 64bit(!) on Windows platform. I tested it under Win10 64bit. I needed a 64bit .dll version of a plugin which gives you functionality to run a command in a shell, a working one I found here: http://winadmin.blogspot.nl/2011/06/mysql-sysexec-udf-for-64-bit-windows.html

这是 Windows 平台上 MySQL 服务器 5.6 64 位（！）的解决方案。我在Win10 64位下测试过。我需要一个 64 位 .dll 版本的插件，它为您提供在 shell 中运行命令的功能，我在这里找到了一个有效的：http: //winadmin.blogspot.nl/2011/06/mysql-sysexec-udf-for -64 位 windows.html

You could also compile it yourself on Windows see: http://rpbouman.blogspot.nl/2007/09/creating-mysql-udfs-with-microsoft.html

您也可以在 Windows 上自己编译，请参阅：http: //rpbouman.blogspot.nl/2007/09/creating-mysql-udfs-with-microsoft.html

For MySQL 5.1+ you have to put the plugin/dll in a subdir of your MySQL installation root for example C:\wamp\bin\mysql\mysql5.6.17\lib\pluginOr else you get an error:

例如，对于 MySQL 5.1+，您必须将插件/dll 放在 MySQL 安装根目录的子目录中，C:\wamp\bin\mysql\mysql5.6.17\lib\plugin否则会出现错误：

Can not open shared library dll – errorcode 193

无法打开共享库 dll – 错误代码 193

You also need curl.exewhich is called by sys_eval. You need to download the correct one here (be sure to copy both(!) files .exe and .crt to a reachable path from your PATH env. var), I used c:\windows\system32: https://winampplugins.co.uk/curl/

您还需要curl.exewhich 被调用sys_eval。您需要在此处下载正确的（确保将两个（！）文件 .exe 和 .crt 复制到 PATH env.var 中的可访问路径），我使用了c:\windows\system32：https: //winampplugins.co.uk/curl/

Then only code you need is:

那么你需要的唯一代码是：

--one time setup. run inside your database
CREATE FUNCTION sys_eval RETURNS STRING SONAME ‘lib_mysqludf_sys.dll';

--example call to an URL
select CONVERT(sys_eval(CONCAT(‘curl https://randomuser.me/api?results=1‘)) USING UTF8MB4);

回答by amaksr

You can execute external script via "sys_exec" command from your trigger. The trick is to write that script the non-blocking way, so it spawns background process that do the work asynchronously, and the main process finishes right away.

您可以通过触发器中的“sys_exec”命令执行外部脚本。诀窍是以非阻塞方式编写该脚本，因此它会生成异步执行工作的后台进程，并且主进程会立即完成。

For example something like this:

例如这样的事情：

#!/bin/sh
nohup curl(or wget) http://www.example.com ...other_post_parameters... &

You need to make sure though, that you don't create too many simultaneous processes. That could be done in the trigger (for example it may write last execution time to some table, and then check if some amount of time has passed), or in shell script (it can create/delete some flag file that would indicate running proceess).

但是，您需要确保不会创建太多的并发进程。这可以在触发器中完成（例如，它可以将上次执行时间写入某个表，然后检查是否已经过了一段时间），或者在 shell 脚本中（它可以创建/删除一些指示正在运行的进程的标志文件）。