MySQL START TRANSACTION 内部 BEGIN ... END 上下文或外部和 LOOP 语法

START TRANSACTION inside BEGIN ... END context or outside and LOOP syntax

提问by Green

I have two questions about Compound-Statement and Transactions in MySQL.

我有两个关于 MySQL 中的复合语句和事务的问题。

FIRST:

第一的：

There are two notes in MySQL Manual:

MySQL手册中有两个注释：

Note

Within all stored programs, the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. To begin a transaction in this context, use START TRANSACTION instead.

Note

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.

笔记

在所有存储的程序中，解析器将 BEGIN [WORK] 视为 BEGIN ... END 块的开始。要在此上下文中开始事务，请改用 START TRANSACTION。

笔记

在所有存储程序（存储过程和函数、触发器和事件）中，解析器将 BEGIN [WORK] 视为 BEGIN ... END 块的开始。在此上下文中使用 START TRANSACTION 开始事务。

I can't understand what exactly is meant. They mean that I have to put START TRANSACTIONinstead of BEGINor right after BEGIN?

我无法理解到底是什么意思。他们的意思是我必须把START TRANSACTION而不是BEGIN或紧随其后BEGIN？

// 1st variant:

BEGIN
   START TRANSACTION
   COMMIT
END


// 2nd variant:

START TRANSACTION
COMMIT
END

Which one is the right way, 1st variant or 2nd variant?

哪个是正确的方法，第一种变体还是第二种变体？

SECOND:

第二：

I don't want to create a Stored Procedure or Function. I just want to create a Compound-Statement Block with a loop inside it in the general flow, like this:

我不想创建存储过程或函数。我只想在一般流程中创建一个带有循环的复合语句块，如下所示：

USE 'someDb';
START TRANSACTION
   ... create table statement
   ... insert statement

// now I want to implement some insert/select statements using loop, I do as follows:

DELIMITER $
BEGIN
  SET @n = 1, @m = 2;
  lab1: LOOP

   ... some insert, select statements here

   END LOOP lab1;
END $
DELIMITER ;

END

COMMIT

Is it possible such kind of structure? Because I have an error thrown:

这种结构可能吗？因为我抛出了一个错误：

Query: BEGIN SET @n = 1, @m = 2; lab1: LOOP SELECT ...
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @n = 1, @m = 2;
lab1: LOOP SELECT ...

My questions are:

我的问题是：

1. Is it allowed to use BEGIN...ENDjust in general flow without creating and using Stored Procedures or Functions?

2. Is it allowed to use BEGIN...ENDinside of START TRANSACTION...COMMITor I have to put START TRANSACTION...COMMITinside of BEGIN...END?

   BEGIN
      START TRANSACTION
      COMMIT
   END
   
   // vs.
   
   START TRANSACTION
      BEGIN
      END
   COMMIT
   

3. Do I by all means have to use BEGIN...ENDif I want to use only LOOP? May I just use LOOPsyntax without starting BEGIN...END? The only example in the manual for LOOPis this:

     CREATE PROCEDURE doiterate(p1 INT)
        BEGIN
          label1: LOOP
            ... 
   

1. 是否允许BEGIN...END仅在一般流程中使用而不创建和使用存储过程或函数？

2. 可以在BEGIN...END里面使用START TRANSACTION...COMMIT还是必须放在START TRANSACTION...COMMIT里面BEGIN...END？

   BEGIN
      START TRANSACTION
      COMMIT
   END
   
   // vs.
   
   START TRANSACTION
      BEGIN
      END
   COMMIT
   

3. BEGIN...END如果我只想使用，我一定要使用LOOP吗？我可以只使用LOOP语法而不开始BEGIN...END吗？手册中的唯一示例LOOP是：

     CREATE PROCEDURE doiterate(p1 INT)
        BEGIN
          label1: LOOP
            ... 
   

回答by eggyal

1. Is it allowed to use BEGIN...END just in general flow without creating and using Stored Procedures or Functions?

   No: compound statements can only be used within the body of stored programs.

2. Is it allowed to use BEGIN...ENDinside of START TRANSACTION...COMMITor I have to put START TRANSACTION...COMMITinside of BEGIN...END?

   START TRANSACTION;and COMMIT;are separate statements. If you want the body of a stored program to contain multiple statements, it will need to enclose those statements in some sort of compound statement block such as BEGIN ... END(which is similar to enclosing a block of statements in braces { ... }within a C-like language).

   That said, you couldhave a stored program which contains only the single-statement START TRANSACTION;or COMMIT;—such a program would not require any compound statement block and would merely commence a new / commit the current transaction respectively.

   Outside of a stored program, where compound statement blocks are not permitted, you can issue START TRANSACTION;and COMMIT;statements as & when required.

3. Do I by all means have to use BEGIN...ENDif I want to use only LOOP? May I just use LOOPsyntax without starting BEGIN...END?

   LOOPis also a compound statement block, which is only valid within a stored procedure. It is not necessaryto enclose a LOOPblock within a BEGIN ... ENDblock, although it is usual (as otherwise it is difficult to perform any required loop initialisation).

1. 是否允许仅在一般流程中使用 BEGIN...END 而无需创建和使用存储过程或函数？

   否：复合语句只能在存储程序的主体内使用。

2. 可以在BEGIN...END里面使用START TRANSACTION...COMMIT还是必须放在START TRANSACTION...COMMIT里面BEGIN...END？

   START TRANSACTION;和COMMIT;是单独的陈述。如果您希望存储程序的主体包含多个语句，则需要将这些语句括在某种复合语句块中，例如BEGIN ... END（类似于{ ... }在类 C 语言中将语句块括在大括号中）。

   也就是说，你可以有一个只包含单语句的存储程序，START TRANSACTION;或者COMMIT;——这样的程序不需要任何复合语句块，而只会分别开始一个新的/提交当前事务。

   在不允许使用复合语句块的存储程序之外，您可以在需要时根据 &发出START TRANSACTION;和COMMIT;语句。

3. BEGIN...END如果我只想使用，我一定要使用LOOP吗？我可以只使用LOOP语法而不开始BEGIN...END吗？

   LOOP也是一个复合语句块，只在存储过程中有效。没有必要将一个LOOP块包含在一个BEGIN ... END块中，尽管这很常见（否则很难执行任何所需的循环初始化）。

In your case, where you apparently want to insert data into a table from a looping construct, you will either need to:

在您的情况下，您显然想从循环构造将数据插入表中，您需要：

• define a stored program in which you use LOOP;

• iterate a loop in an external program that executes database queries on each iteration; or

• redefine your logic in terms of sets upon which SQL can directly operate.

• 定义您使用的存储程序LOOP；

• 在每次迭代时执行数据库查询的外部程序中迭代一个循环；或者

• 根据 SQL 可以直接操作的集合重新定义您的逻辑。