mysql事务变量_MySQL事务和变量的问题(Issues with MySQL transactions and variables)

I'm trying to set up a process where my team can do some routine DB changes using variables and a basic script. I'm only on the first of many operations and can't even get it to execute. The idea is you assign the variables at the top of the script and say if you want to run the script in test or prod mode.

test should just mock the changes, show you what would've happened and roll the transaction back while prod will actually commit the transaction and show you the updated rows.

When I run these lines individually they work, but altogether it keeps saying syntax error. I would love help debugging this but also if anyone has a better solution to this problem I'm wide open to hearing it.

Ok here's the starting code, thanks in advance!

#ADD A NEW QUESTION CATEGORY

SET @new_category_name = 'NEW CATEGORY NAME HERE';

SET @prod_or_test = 'PROD';

START TRANSACTION;

#SET IT'S SORT_ORDER TO BE AT THE END OF THE LIST

SET @last_sort_order = ((SELECT MAX(`sort_order`) FROM question_categories)+1);

#INSERT THE NEW ROW

INSERT INTO question_categories (name, sort_order) VALUES (@new_category_name, @last_sort_order);

IF (@prod_or_test = 'PROD') THEN

COMMIT;

SELECT * FROM question_categories WHERE name = @new_category_name;

ELSE

ROLLBACK;

END IF;

Updated to include the error messages:

[ERROR in query 6] 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 (@prod_or_test = 'PROD') THEN

COMMIT' at line 1

[ERROR in query 8] 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 'ELSE

ROLLBACK' at line 1

[ERROR in query 9] 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 'END IF' at line 1