在mysql中使用select into局部变量和prepared语句

问题描述

每次调用此存储过程时，都会出现此错误:

Everytime call this stored procedure i get this error:

#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 'NULL' at line 1

问题出在哪里?

这是代码:

DELIMITER $$

DROP PROCEDURE IF EXISTS `singledb`.`TOUR_TRANSFER`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `TOUR_TRANSFER`()
BEGIN

BEGIN -- for rubric table
            DECLARE tblRubric_rubric_id INT;
            SET @qry = concat('SELECT count(*) into ',tblRubric_rubric_id,' FROM zerodb2.tour_template');
            PREPARE statement FROM @qry;
            EXECUTE statement;

            SELECT tblRubric_rubric_id;
END;
END$$

DELIMITER ;

推荐答案

来自 http://bugs.mysql.com/bug.php?id=15263 :

参数标记只能在应显示数据值的地方使用，不适用于SQL关键字，标识符等."

"Parameter markers can be used only where data values should appear, not for SQLkeywords, identifiers, and so forth."

所以，我们不能这样做:

So, we can not do:

PREPARE stmt从'SELECT ID INTO吗?从t1';

PREPARE stmt FROM 'SELECT id INTO ? FROM t1';

我们不能使用?而不是标识符(变量名).

We can not use ? instead of identifier (variable name).

当您使用参数名称时:

PREPARE stmt FROM'SELECT id INTO rid FROM t1';

PREPARE stmt FROM 'SELECT id INTO rid FROM t1';

在准备的字符串中，然后服务器根本不知道rid指的是什么您正在准备的声明.您可以尝试使用相同的方法在SP外部进行准备结果:

in the string being prepared, then server simply do not know what that rid refers to instatement you are trying to prepare. You may try to prepare it outside SP with the sameresult:

mysql>从'从t1中选择id到p中准备stmt';错误1327(42000):未声明的变量:p

mysql> prepare stmt from 'select id into p from t1';ERROR 1327 (42000): Undeclared variable: p

所以我只使用了用户变量，请参见下文:

So I just used user variable, see below:

DROP PROCEDURE IF EXISTS `singledb`.`TOUR_TRANSFER`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TOUR_TRANSFER`()
BEGIN
BEGIN -- for rubric table
            -- DECLARE @tblRubric_rubric_id INT;

            SET @tblRubric_rubric_id := 0;
            SET @qry = 'SELECT count(*) into @tblRubric_rubric_id FROM zerodb2.tour_template';
            PREPARE statement FROM @qry;
            EXECUTE statement;
END;
END$$

这篇关于在mysql中使用select into局部变量和prepared语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！