如何在 MYSQL 中将字符串作为 WHERE 子句传递 [英] How to pass a string as a WHERE clause in MYSQL

问题描述

我创建了以下字符串:

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) 喜欢 UPPER('%order%')

The result of the string: l_full_search_clause is : UPPER(a.project_name) like UPPER('%order%')

我有一个 select 子句，我需要使用我创建的包含此 where 子句的字符串.所以，我有以下几点:从项目中选择 *其中 l_full_search_clause

I have a select clause that i need to use this string that i have created that contains this where clause. So, i have the following : select * from projects where l_full_search_clause

但是，当然，MYSQL 按原样读取 l_full_search_clause 而不是变量.我如何告诉 MYSQL 条件是一个可变字符串?

But, of course, MYSQL reads l_full_search_clause as is and not as a variable. How can i tell MYSQL that the condition is a variable string ?

这个 sql 在一个 MYSQL 存储过程中 .. 所以它只是普通的 sql .. 我从 C#、MVC3 调用这个存储过程

This sql is in a MYSQL stored procedure .. so its just plain sql .. I am calling this stored procedure from C#, MVC3

推荐答案

试试这个:

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.

I believe you have to use the @var syntax for variables. Also, to execute a query which is stored in a string, you have to use PREPARE and EXECUTE.

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

And you don't have to use UPPER on strings. By default, MySQL compares strings case-insensitively. You can ensure that by looking at collation type for you fields. ci_ ones stand for "case insensitive".

这篇关于如何在 MYSQL 中将字符串作为 WHERE 子句传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！