带有声明的MySQL动态查询

Question

我想问一下，是否有可能在动态查询中使用DECLARE变量。

即：

CREATE PROCEDURE `storedProcedureName`()
    DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    SET @i = 0;

    PREPARE dynamicQuery FROM 'DECLARE x INT(11);';
    EXECUTE dynamicQuery;

    PREPARE dynamicQuery FROM 'SET x := ?;';
    EXECUTE dynamicQuery USING @i;

    DEALLOCATE PREPARE dynamicQuery;

    /* More Queries Here */

END

但是我得到了语法错误：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 'DECLARE x INT(11)' at line 1

谢谢

Answer 1

• First - DECLARE只允许在BEGIN ...END compound statement.
• Second -并非所有状态都可以在PREPARE语句中使用，请在此处查看更多详细信息- Prepared Statements.

但你可以用这样的东西-

SET @text = 'something';
SET @var = NULL;
SET @query = 'SELECT ? INTO @var';
PREPARE s FROM @query;
EXECUTE s USING @text;
DEALLOCATE PREPARE s;

SELECT @var;
+-----------+
| @var      |
+-----------+
| something |
+-----------+