插入表后，在mysql触发器中发送http请求 [英] Send an http request in mysql trigger after insertion in a table

问题描述

我在PHP工作。我必须创建一个mysql触发器，在插入table.Below之后触发一个http请求。代码。

I am working in PHP.I have to created a mysql trigger which fires an http request after insertion on table.Below is the code.

DELIMITER @@
CREATE TRIGGER Test_Trigger
AFTER INSERT ON insertsms
FOR EACH ROW
BEGIN
  SET @tt_json = (SELECT json_object(id,addtime,title) 
                  FROM insertsms WHERE id = NEW.id LIMIT 1);
  SET @tt_resu = (SELECT http_put(CONCAT('--url localhost--')));
END;
@@
DELIMITER ;

但我收到的错误如

消息：SQLSTATE [42000]：语法错误或访问冲突：1305功能emg.json_object不存在

Message: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION emg.json_object does not exist

消息：SQLSTATE [42000]：语法错误或访问冲突：1305功能emg.http_put不存在

Message: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION emg.http_put does not exist

如何删除此错误？我无法下载包含这些功能的支持文件。我已在localhost中测试过。有没有其他方法可以实现我的要求？请任何人帮助我..

How to remove this error? I was not able to download the supporting files containing these functions.I have tested in localhost.Is there any other way to achieve my requirement? Please anyone help me..

推荐答案

虽然技术上可行，但我强烈建议您不要这样做，原因如下： / p>

Although it's technically possible I'd strongly discourage you from going this route for several reasons:

1. 使用UDF本身就存在安全风险。所有数据库用户都可以使用UDF - 您无法为它们授予EXECUTE权限。

1. Using UDFs is a security risk on its own. UDFs are available to all database users - you cannot grant EXECUTE privileges for them.

在触发器中执行任何非事务性操作完全错误。 DML语句所做的数据更改（在您的情况下是更新）可以并将在现实世界中回滚。您将无法撤消您的http呼叫。

Doing any non-transactional operations in a trigger is simply wrong. Data changes made by DML statement (in your case it's an update) can and will be rolled back in a real world scenario. You won't be able to undo your http calls.

您正在延长插入事务的时间，可能导致其他更新的锁定等待超时/插入操作。

You're prolonging the time for insert transaction possibly causing lock-wait-timeouts for other update/insert operations.

强烈建议阅读：

• 触发器故障

• The Trouble with Triggers

现在很可能你需要的是工作队列，例如 beanstalked 。使用这种专用中间件要比使用数据库组织队列要好得多。

Now most likely what you need is a work queue e.g. beanstalked. Using such specialized middleware is much better than organizing queues with database.

这篇关于插入表后，在mysql触发器中发送http请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！