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 

出了什么问题？

- ducin

---

你是在过程定义时还是在尝试调用它时收到这条消息的？ - berty

定义过程时出现了这个错误。 - ducin

Answer 1

我不明白为什么你选择了那个“SELECT version”，如果想要选择数据，你必须将其检索到变量中，当你直接在终端使用该语句时，它可以正常工作。你确定你的脚本中有 prepared_stmt 吗？可能的修复方法是：

1. 将变量名 version 更改为 version_v。 2. 不要使用下面这个语句： SET version = (SELECT version FROM version WHERE id = 1 LIMIT 1); SELECT version; 而是使用以下语句：

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

- Chella

Answer 2

试试这个：

DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INT, script TEXT)
BEGIN
  SELECT `version` into @ver FROM `version` WHERE `id` = 1 LIMIT 1;  
  SET @s = script;
  IF @ver = current_version + 1 THEN
    PREPARE upgrade_stmt FROM @s;
    EXECUTE upgrade_stmt;
    DEALLOCATE PREPARE upgrade_stmt;
    UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
  END IF;
END $$
DELIMITER ;

- Saharsh Shah

Answer 3

设置变量的值

       DELIMITER $$
       create procedure Testing()
           begin
            declare Regione int;   
            set Regione=(select  id from users
            where id=1) ;
            select Regione ;
           end $$
         DELIMITER;

- Hari Lakkakula