MySQL 错误代码：1422。存储函数或触发器中不允许显式或隐式提交
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16969875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger
提问by bobobobo
Everywhere I look it seems MySQL stored procedures can do transactions. Yet when I declare my stored function
在我看来，MySQL 存储过程似乎可以进行事务处理。然而，当我声明我的存储函数时
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 //
I get
我得到
Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.
错误代码：1422。存储函数或触发器中不允许显式或隐式提交。
回答by bobobobo
Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored proceduresonly.
实际上，您不允许在存储函数内进行交易。只允许在存储过程中进行事务。
create procedure test( a int )
MODIFIES SQL DATA
BEGIN
START TRANSACTION ;
update t set col='some value' where id=a ;
COMMIT ;
END //
To return values from the SP, use output parameters or use the result set from the last select statement in the SP.
要从 SP 返回值，请使用输出参数或使用 SP 中最后一个选择语句的结果集。
