执行流程，事务和MySQL存储过程

I want to know if stored procedure in MySQL is something more than sequence of SQL statements with some restrictions (http://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html).

我想知道MySQL中的存储过程是否超出了具有一些限制的SQL语句序列(http://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html)。

Is there something special with transactions, DML on InnoDB tables etc.?

有关事务的特殊事项,InnoDB表上的DML等吗?

As far as I know it isn't, because I failed to find out opposite.

据我所知,事实并非如此,因为我没有发现相反的情况。

Edit:

Execution flow is also an interesting question.

执行流程也是一个有趣的问题。

Assume I have simple following procedure and autocommit is off:

假设我有简单的以下程序,并且自动提交已关闭:

CREATE PROCEDURE someproc ()
    -> BEGIN
    ->   -- exec stmt1
    ->   -- exec stmt2  <--- assume it fails!
    ->   -- exec stmt3
    -> END//

What happens if stmt2 fails during execution for some reason?

如果stmt2由于某种原因在执行期间失败会发生什么?

In my opinion stmt2 changes will be rollbacked, but stmt1 changes are not and execution flow of procedure will be stopped so stmt3 won't be execute.

在我看来,stmt2更改将被回滚,但stmt1更改不会,并且程序的执行流程将被停止,因此stmt3将不会执行。

2 个解决方案

#1

A MySQL stored program supports syntax beyond SQL statements, such as declaring variables, conditional branching, loops, cursors, dynamic SQL, etc. In that sense, it is more than a sequence of SQL statements.

MySQL存储程序支持SQL语句之外的语法,例如声明变量,条件分支,循环,游标,动态SQL等。从这个意义上讲,它不仅仅是一系列SQL语句。

There's nothing "special" about DML statements on InnoDB tables or transactions in a MySQL stored program. A PROCEDURE is the most lenient in terms of allowed oprations. There are some restrictions on FUNCTIONS and TRIGGERS (as as you have found). The restrictions mostly stem from how functions get called (as an expression within a statement, or how triggers get fired (when a DML statement is executing.)

关于InnoDB表上的DML语句或MySQL存储程序中的事务没有什么“特殊”。就允许的操作而言,程序是最宽松的。对FUNCTIONS和TRIGGERS有一些限制(正如你所发现的那样)。这些限制主要源于如何调用函数(作为语句中的表达式,或触发器如何被触发(执行DML语句时)。

EDIT

Unless a HANDLER is declared to catch an exception/error, when an error is encountered within a MySQL stored PROCEDURE, the execution of the procedure stops and the error is returned to the caller.

除非声明HANDLER捕获异常/错误,否则在MySQL存储的PROCEDURE中遇到错误时,过程的执行将停止,并且错误将返回给调用者。

As far was what DML changes are committed or rolled back, that depends on whether the tables are MyISAM or not, and whether auto commit is enabled. or on whether a COMMIT or ROLLBACK is issued for the transaction. It doesn't matter whether the DML statement is executed within a stored procedure, or outside of a procedure.

到目前为止,DML更改是提交还是回滚的,这取决于表是否为MyISAM,以及是否启用了自动提交。或者是否为交易发出COMMIT或ROLLBACK。 DML语句是在存储过程中执行还是在过程外执行并不重要。

#2

A stored procedure can be seen as a group of SQL statements to be executed as one single transaction.

存储过程可以看作是一组要作为单个事务执行的SQL语句。

The transaction nature is important when dealing with concurrency.

处理并发时,事务性质很重要。

Another benefit of having a stored procedure is to apply access control to the stored procedure.

具有存储过程的另一个好处是将访问控制应用于存储过程。

#1