mysql存储过程从表中设置值

Question

我有一个简单的表格如下:

mysql> select * from version;
+----+---------+
| id | version |
+----+---------+
|  1 | 1       |
+----+---------+
1 row in set (0.00 sec)

我需要创建一个存储过程，它会根据该表的值(准确地说，该表的唯一行)执行某些操作(或不执行任何操作)。

DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INTEGER, script TEXT)
BEGIN
  DECLARE version INT(11);
  SET version = (SELECT `version` FROM `version` WHERE `id` = 1 LIMIT 1);
  SELECT version;
  IF version = current_version + 1 THEN
    PREPARE upgrade_stmt FROM script;
    EXECUTE upgrade_stmt;
    UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
  ENDIF;
END $$
DELIMITER ;

在以下时间调用:

CALL upgrade_version(1,'ALTER TABLE ...');

语法不正确，mysql返回:

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 'script;
    EXECUTE upgrade_stmt;
    UPDATE `version` SET `version` = `version`' at line 7 

怎么了？

Answer 1

我不明白你为什么选择那个“SELECT 版本”，如果你想选择你必须将它检索到 varialbe 中的数据，当你直接在终端上使用它时，该语句工作正常。你确定你的脚本中有 prepared_stmt ..!？可能的解决方法是:

1. 将变量名version改为version_v

2. 代替这个:

   SET version = (SELECT version FROM version WHERE id = 1 LIMIT 1);
   选择版本；

试试这个:

SELECT `version` INTO Version_v FROM `version` WHERE `id` = 1 LIMIT 1);