MySQL 存储过程中的mysql动态查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23178816/
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
mysql dynamic query in stored procedure
提问by Shann
i am creating a dynamic query in stored procedure. my stored procedure is as follows:
我正在存储过程中创建一个动态查询。我的存储过程如下：
CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40))
BEGIN
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team=",w_team);
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
when i try to run it with the following call:
当我尝试使用以下调用运行它时：
call test1 ('Test','SPA');
i get the following error message:
我收到以下错误消息：
Error Code: 1054. Unknown column 'SPA' in 'where clause'
错误代码：1054。“where 子句”中的“SPA”列未知
i tested without where condition and it works fine, but with the where condition its not working, i tried using @ with the variable name but it still does not work.
我在没有 where 条件的情况下进行了测试并且它工作正常，但是在 where 条件下它不起作用时，我尝试将 @ 与变量名称一起使用，但它仍然不起作用。
Thanks for your help.
谢谢你的帮助。
采纳答案by Raging Bull
You missed the quotes in WHEREclause.
你错过了WHERE子句中的引号。
Try like this:
像这样尝试：
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");
Explanation:
说明：
Query from your code would be like:
从您的代码查询将是这样的：
SELECT * FROM Test where team=SPA
And we changed it to:
我们将其更改为：
SELECT * FROM Test where team='SPA'
回答by Ravinder Reddy
Error Code: 1054. Unknown column 'SPA' in 'where clause'
错误代码：1054。“where 子句”中的“SPA”列未知
This happens when you do not enclose input string within quotes, and SQL engine tries to identify it as a column in the table being queried. But it fails as it can't find it.
当您没有将输入字符串括在引号中时会发生这种情况，并且 SQL 引擎尝试将其标识为被查询表中的列。但它失败了，因为它找不到它。
But what happens when it finds such column?
It fetches results when it finds some matches on the column values.
Obviously this is not what one was expecting.
但是当它找到这样的列时会发生什么？
当它在列值上找到一些匹配时，它会获取结果。
显然，这不是人们所期望的。
How to overcome this? Use Prepared Statements with dynamic input values.
如何克服这一点？将准备好的语句与动态输入值一起使用。
You can use placeholders like ?in stored procedures too on dynamic input values to use with Prepared Statements. The engine will handle escape characters and other string values when assigned to or compared within SQL expressions.
您也可以像?在存储过程中一样在动态输入值上使用占位符以与Prepared Statements. 当分配给 SQL 表达式或在 SQL 表达式中进行比较时，引擎将处理转义字符和其他字符串值。
You just need to re-assign procedure inputs to one or more session variables, as required.
您只需根据需要将过程输入重新分配给一个或多个会话变量。
Example on your procedure:
您的程序示例：
CREATE PROCEDURE `test1`( IN tab_name VARCHAR(40), IN w_team VARCHAR(40) )
BEGIN
SET @t1 = CONCAT( 'SELECT * FROM ', tab_name, ' where team = ?' ); -- <-- placeholder
SET @w_team := w_team;
PREPARE stmt3 FROM @t1;
EXECUTE stmt3 USING @w_team; -- <-- input for placeholder
DEALLOCATE PREPARE stmt3;
END;
回答by Ronak Shah
Try this..
尝试这个..
CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40))
BEGIN
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
You are missing quotes around w_teamvariable..
您缺少w_team变量周围的引号..
you should print the statement that dynamically build so you can just copy printed statement and try so you can easily find this kind of problem.
您应该打印动态构建的语句，以便您可以复制打印的语句并尝试，以便您可以轻松找到此类问题。
select @t1will print the statment that build dynamically..
select @t1将打印动态构建的语句..
