存储过程中的mysql动态查询

mysql dynamic query in stored procedure(存储过程中的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');

我收到以下错误消息:

错误代码:1054.where 子句"中的未知列SPA"

Error Code: 1054. Unknown column 'SPA' in 'where clause'

我在没有 where 条件的情况下进行了测试并且它工作正常，但是在 where 条件下它不起作用，我尝试使用带有变量名称的 @ 但它仍然不起作用.

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 子句中包含参数 w_team.

You missed to enclose the parameter w_team in WHERE clause.

试试这个:

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

说明:

来自您的代码的查询如下:

Query from your code would be like:

SELECT * FROM Test where team=SPA

它将尝试查找不可用的列 SPA，因此会出现错误.

It will try find a column SPA which is not available, hence the error.

我们将其更改为:

SELECT * FROM Test where team='SPA'

这篇关于存储过程中的mysql动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！