问题描述
我有以下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:
我看过堆栈溢出,有一个答案显示了类似的查询,写法几乎相同方式,但他们使用的变量没有@
符号.删除查询的@
不能解决问题.
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事务中的语句导致错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!