存储过程中的mysql动态查询

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.

谢谢你的帮助。

3 个解决方案

#1

Try like this:

试试这样:

SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");

Explanation:

previous dynamic query will be like:

以前的动态查询将如下:

SELECT * FROM Test where team=SPA

And we changed it to:

我们将其改为:

SELECT * FROM Test where team='SPA'

#2

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_team variable..

你错过了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 @t1 will print the statment that build dynamically..

select @ t1将打印动态构建的语句。

#3

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.

怎么克服这个?将Prepared Statements与动态输入值一起使用。

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.

你可以使用占位符吗?在存储过程中也使用动态输入值与预准备语句一起使用。在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;