mysql 事务 执行错误,mysql事务错误处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

BEGIN

ROLLBACK;

END;

START TRANSACTION;

UPDATE tbl_order SET TransactionID="abc" WHERE OrderID=1;

UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;

UPDATE tbl_order SET TransactionID="zzz" WHERE OrderID=13;

COMMIT;

for some reason order 1 and 13 are filled without rollback and i get syntax error for the exit hadler.

Query: DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN ROLLBACK

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 'DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

BEGIN

ROLLBACK' at line 1

can someone help me figure out what i'm doing wrong?

Thanks in advance

EDIT

UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;

is intentional

解决方案

I believe exit handlers can only be used in stored procedures. The documentation doesn't explicitly state this, but alludes to

Conditions may arise during stored program execution that require special handling