事务中的MYSQL If语句导致错误

2022-01-06 00:00:00 variables transactions mysql

我有以下 MYSQL 查询:

I have the following MYSQL query:

START TRANSACTION;

SELECT sport_id INTO @a FROM sports WHERE sport_id = 2 FOR UPDATE;

UPDATE sports SET sport_name = 'Table Tennis' WHERE sport_id = @a;

if (@a > 1) then
    COMMIT;
ELSE
    ROLLBACK;
END IF;

问题在于它在 if 语句处返回错误:

The problem is that it returns an error at the if statement:

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的if (@a > 1) then COMMIT"附近使用的正确语法

#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 'if (@a > 1) then COMMIT' at line 1

我查看了堆栈溢出，并且有一个显示类似查询的答案，其编写方式几乎相同方式，但他们使用的变量没有 @ 符号.删除查询的 @ 并不能解决问题.

I've looked on stack overflow and there is an answer showing a similar query, written in pretty much the same way, but they are using the variable without an @ symbol. Removing the @ for my query does not resolve the issue.

这只是一个使用 MYSQL 尝试一些事务的测试查询，因此为什么查询看起来有点毫无意义.我有点卡住了.

This is just a test query to try out some transactions using MYSQL, hence why the query seems a little pointless. I'm a little stuck.

推荐答案

MySQL 无法将以关键字 IF 开头的语句识别为有效的 SQL 语句.

MySQL doesn't recognize a statement beginning with the keyword IF as as a valid SQL statement.

IF 语句仅适用于复合语句的上下文(即，包含在 BEGIN 和 END 之间的语句块.目前，复合语句仅在存储程序(存储过程、函数或触发器)的上下文中支持.

The IF statement works only in the context of a compound statement (that is, a block of statements enclosed between BEGIN and END. Currently, the compound statement is only supported in the context of a stored program (stored procedure, function or trigger.)

http://dev.mysql.com/doc/refman/5.5/en/begin-end.html

为了测试，尝试...

DELIMITER //

CREATE PROCEDURE usp_test_transaction()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;

  -- whatever DML operations and SELECT statements you want to perform go here

  IF (1=1) THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END//

DELIMITER ;

CALL usp_test_transaction;

(注意:我不是在这里提倡在存储过程中处理事务.我个人的偏好是不这样做，而是在更高级别处理事务.但上面的例子应该可行；我相信 MySQL 确实支持在存储过程的上下文中调用 START TRANSACTION、COMMIT 和 ROLLBACK.)

(NOTE: I am not advocating here that transactions be handled inside of stored procedure. My personal preference is to NOT do this, and instead handle transactions at a higher level. But the example above should work; I believe MySQL does support calling START TRANSACTION, COMMIT and ROLLBACK within the context of a stored procedure.)

相关文章