如何在MYSQL中将字符串作为WHERE子句传递

Question

我创建了以下字符串：

set l_project_name = concat("UPPER(a.project_name) like UPPER ('%",projectName,"%')");
set l_project_type = concat(" OR UPPER(a.project_type) like UPPER('%",projectType,"%')");
set l_project_description = concat(" OR UPPER(a.project_description) like UPPER('%",projectDescription,"%')");

set l_full_search_clause = concat(l_project_name ,l_project_type, l_project_description); 

字符串: l_full_search_clause的结果是: UPPER(a.project_name) like UPPER('%order%')

我有一个select子句，需要使用我创建的包含where子句的字符串。因此，我有以下内容: select * from projects where l_full_search_clause

但是，当然，MYSQL按原样读取l_full_search_clause，而不是将其作为变量。我如何告诉MYSQL条件是一个变量字符串？

此sql位于MYSQL存储过程中。所以它只是普通的sql ..我将从C#、MVC3调用此存储过程

Answer 1

试试这个：

SET @query = "SELECT * FROM projects WHERE ";

SET @l_project_name = CONCAT("a.project_name LIKE '%", projectName, "%'");
SET @l_project_type = CONCAT(" OR a.project_type LIKE '%", projectType, "%'");
SET @l_project_description = CONCAT(" OR a.project_description LIKE '%", projectDescription, "%'");

SET @l_full_search_clause = CONCAT(@l_project_name, @l_project_type, @l_project_description); 

SET @query = CONCAT(@query, @l_full_search_clause);

PREPARE stmt FROM @query;
EXECUTE stmt;

我相信您必须对变量使用@var语法。此外，要执行存储在字符串中的查询，必须使用PREPARE和EXECUTE。

而且您不必在字符串上使用UPPER。默认情况下，MySQL比较字符串时不区分大小写。您可以通过查看字段的排序规则类型来确保这一点。ci_ ones代表“不区分大小写”。