mysql存储过程设置值_表中的mysql存储过程设置值

I've got a following, simple table:

mysql> select * from version;

+----+---------+

| id | version |

+----+---------+

| 1 | 1 |

+----+---------+

1 row in set (0.00 sec)

I need to create a stored procedure that will do something (or nothing) depending on the value of this table (precisely, the only row of this table).

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 ;

To be called within:

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

It is syntactically incorrect, mysql returns:

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

What is wrong?

解决方案

Try this:

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 ;