插入表后在mysql触发器中发送http请求

2022-01-01 00:00:00 http php mysql triggers

我在 PHP 中工作.我必须创建一个 mysql 触发器，它会在插入表后触发 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 FUNCTION emg.json_object 不存在

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

消息:SQLSTATE[42000]:语法错误或访问冲突:1305 FUNCTION 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..

推荐答案

虽然技术上可行，但出于以下几个原因，我强烈建议您不要走这条路:

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.

强烈推荐阅读:

  • 触发器的问题

现在很可能你需要的是一个工作队列，例如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.

相关文章