MySQL 在mysql中使用select into局部变量和准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7917345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using select into local variable and prepared statement in mysql
提问by kaz
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
What seems to be the problem?
似乎是什么问题？
Here is the code:
这是代码：
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 ;
回答by kaz
From http://bugs.mysql.com/bug.php?id=15263:
从http://bugs.mysql.com/bug.php?id=15263：
"Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth."
“参数标记只能用于数据值应该出现的地方，不能用于 SQL 关键字、标识符等。”
So, we can not do:
所以，我们不能这样做：
PREPARE stmt FROM 'SELECT id INTO ? FROM t1';
准备 stmt FROM 'SELECT id INTO ? 从 t1';
We can not use ? instead of identifier (variable name).
我们不能用？而不是标识符（变量名）。
And when you use parameter name:
当您使用参数名称时：
PREPARE stmt FROM 'SELECT id INTO rid FROM t1';
PREPARE stmt FROM 'SELECT id INTO rid FROM t1';
in the string being prepared, then server simply do not know what that rid refers to in statement you are trying to prepare. You may try to prepare it outside SP with the same result:
在正在准备的字符串中，服务器根本不知道在您尝试准备的语句中该rid指的是什么。您可以尝试在 SP 之外准备它，结果相同：
mysql> prepare stmt from 'select id into p from t1'; ERROR 1327 (42000): Undeclared variable: p
mysql> 准备 stmt from 'select id into p from t1'; 错误 1327 (42000)：未声明的变量：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$$
回答by Ray
You had a lot of syntax errors I think. This is usually how I write my stored procedures and they work. Try this so far. Basically there is no need for you to select the count into that temporary variable if you're just going to select from that temporary variable in the very next statement.
我认为你有很多语法错误。这通常是我编写存储过程和它们工作的方式。到目前为止试试这个。基本上，如果您只是要在下一个语句中从该临时变量中进行选择，则无需将计数选择到该临时变量中。
DELIMITER $$
DROP PROCEDURE IF EXISTS TOUR_TRANSFER;
CREATE PROCEDURE TOUR_TRANSFER()
BEGIN # for rubric table
DECLARE tblRubric_rubric_id INT;
SET @qry := CONCAT('SELECT count(*) FROM zerodb2.tour_template');
PREPARE statement FROM @qry;
EXECUTE statement;
#SELECT tblRubric_rubric_id; # i don't think this will work
END$$
DELIMITER ;
