MYSQL If事务中的语句导致错误 [英] MYSQL If statement in transaction causing error

问题描述

我有以下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

要进行测试，请尝试...

For testing, try...

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.)

这篇关于MYSQL If事务中的语句导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！