错误代码:1422.在存储的函数或触发器中不允许显式或隐式提交 [英] Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger

问题描述

我看上去到处都可以看到MySQL存储过程可以完成事务.但是当我声明我的存储函数时

Everywhere I look it seems MySQL stored procedures can do transactions. Yet when I declare my stored function

create function test( a int )
returns int
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
  return 0 ;
END //

我知道

错误代码:1422.存储的函数或触发器中不允许显式或隐式提交.

Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.

推荐答案

实际上，不允许您在存储的函数中进行事务处理.仅允许您在存储的 过程 中进行交易.

Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored procedures only.

create procedure test( a int )
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
END //

要从SP返回值，请使用输出参数或使用SP中最后一个select语句的结果集.

To return values from the SP, use output parameters or use the result set from the last select statement in the SP.

这篇关于错误代码:1422.在存储的函数或触发器中不允许显式或隐式提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！