mysql - MySQL 事务和变量的问题

Question

我正在尝试建立一个流程，让我的团队可以使用变量和基本脚本进行一些常规数据库更改。我只是执行许多操作中的第一个，甚至无法执行它。这个想法是您在脚本顶部分配变量，并说明是否要在 test 或 prod 模式下运行脚本。

test 应该只是模拟更改，向您展示会发生什么并回滚事务，而 prod 将实际提交事务并向您显示更新的行。

当我单独运行这些行时，它们可以工作，但总的来说，它一直说语法错误。我很乐意帮助调试这个问题，但如果有人对这个问题有更好的解决方案，我很愿意听到。

好的，这是起始代码，提前致谢!

#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;

已更新以包含错误消息:

[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

Answer 1

IF、THEN、ELSE 和 END IF 在上下文之外不是有效的 SQL 语句MySQL 存储程序。例如 PROCEDURE 或 TRIGGER 定义。

引用:https://dev.mysql.com/doc/refman/5.7/en/if.html

IF 语句可以在存储程序中使用，但不能用作独立的 SQL 语句。

此外，IF 语句不应与 IF() 函数混淆，后者可以在诸如 等语句的上下文中使用SELECT 或 UPDATE，它们是有效的 SQL 语句。

  SELECT t.foo, IF(t.foo='bar',41,NULL) AS bar, ...   

某些语句包含 IF 关键字作为其语法的一部分，例如:

  DROP TABLE IF EXISTS foo; 

请注意，在此示例中，IF 关键字是 DROP 语句的一部分；这不是一个单独的声明。

<小时/>

跟进

"the end goal was to have one script ... but ... it's looking like I actually am trying to have some stored procedures that can be called via this script."

最初的答案(上面)是解决在 MySQL 存储程序之外使用 IF 语句的问题。如果您需要使用 IF 语句，那么可以在 PROCEDURE 中完成。

但是，IF 语句并不是严格必需的。您可以使用 PREPARE、EXECUTE 和 DEALLOCATE PREPARE 语句执行动态创建的 SQL 来完成几乎相同的事情。

引用:https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

例如，我们可以在 SELECT 语句中的 IF() 函数中对用户定义的变量执行条件测试，并有条件地返回要执行的 SQL 文本。在本例中，为 COMMIT 或 ROLLBACK 语句。

我认为这样的事情会起作用:

 -- 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
 SELECT IFNULL(MAX(`sort_order`)+1,1) 
   FROM question_categories
   INTO @last_sort_order 
 ;

 -- insert the new row
 INSERT INTO question_categories (name, sort_order)
 VALUES (@new_category_name, @last_sort_order)
 ;

 -- conditionally execute a COMMIT or ROLLBACK
 SELECT IF(@prod_or_test='PROD','COMMIT','ROLLBACK') INTO @sql;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

 SELECT *
   FROM question_categories
  WHERE @prod_or_test = 'PROD'
    AND name = @new_category_name ;