错误 1305 (42000): SAVEPOINT ...不存在 [英] ERROR 1305 (42000): SAVEPOINT ... does not exist

问题描述

我的 MYSQL 数据库中有这个 SQL(sproc 为空，所以我猜没有隐式提交?).

I have this SQL in my MYSQL DB (sproc with empty body so I guess no implicit commits ?).

DROP PROCEDURE IF EXISTS doOrder;

DELIMITER $$

CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40))
  BEGIN
    SAVEPOINT sp_doOrder;

    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_doOrder;

      -- doing my updates and selects here...

    END;

    RELEASE SAVEPOINT sp_doOrder;
  END $$

DELIMITER ;

当我

call doOrder('some-unique-id');

我得到:错误 1305 (42000):SAVEPOINT sp_doOrder 不存在.

我可能会忽略一些东西...有什么想法吗?

I might overlook something... Any idea?

推荐答案

您必须使用 START TRANSACTION 而不是 BEGIN 来启动存储过程中的事务.

You have to use START TRANSACTION instead of BEGIN to start a transaction in a stored procedure.

此外，您可能需要将 SAVEPOINT 语句移到 DECLARE 之后(取决于您放置 START TRANSACTION 的位置)

Also, you may need to move the SAVEPOINT statement to be after the DECLARE (depending upon where you put the START TRANSACTION)

注意

在所有存储程序(存储过程和函数、触发器、和事件)，解析器将 BEGIN [WORK] 视为一个BEGIN ... END 块.在此上下文中使用 START 开始事务改为交易.

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

参考:http://dev.mysql.com/doc/refman/5.6/en/commit.html

这篇关于错误 1305 (42000): SAVEPOINT ...不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！