如何在 MySQL LIKE 子句中使用用户变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8537516/
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
How to use an user variables in MySQL LIKE clause?
提问by Chris
I am trying to setup a few simple SQL scripts to help with some short term DB administration. As such, I'm setting up variables to try to make it easier to reuse these scripts.
我正在尝试设置一些简单的 SQL 脚本来帮助进行一些短期的数据库管理。因此，我正在设置变量以尝试更轻松地重用这些脚本。
The problem I'm having is specifically with the LIKE clause.
我遇到的问题特别是 LIKE 子句。
SET @email = '[email protected]';
SELECT email from `user` WHERE email LIKE '%@email%';
So I want to have it finding results based on the email SET in the variable. The query works if I manually enter the email into the LIKE clause.
所以我想让它根据变量中的电子邮件 SET 查找结果。如果我手动将电子邮件输入 LIKE 子句，则查询有效。
How can I get the LIKE clause to work with the user variable?
如何让 LIKE 子句与用户变量一起使用？
UPDATE: @dems's answer works for this simple case, but I'm having trouble with a more complex query.
更新：@dems 的答案适用于这个简单的案例，但我在处理更复杂的查询时遇到了麻烦。
SET @email = '[email protected]';
SELECT project.projectno, project.projectname, login.username,
CONCAT(login.firstname, ' ', login.lastname), projectuser.title
FROM projectuser
INNER JOIN login ON projectuser.uid = login.uid
LEFT JOIN project ON projectuser.pid = project.pid
WHERE login.username LIKE CONCAT ('%', @email, '%')
Gives me the error "FUNCTION mydb.CONCAT does not exist"
给我错误“FUNCTION mydb.CONCAT 不存在”
The query works without the CONCAT():
查询在没有 CONCAT() 的情况下工作：
SET @email = '[email protected]';
SELECT project.projectno, project.projectname, login.username,
CONCAT(login.firstname, ' ', login.lastname), projectuser.title
FROM projectuser
INNER JOIN login ON projectuser.uid = login.uid
LEFT JOIN project ON projectuser.pid = project.pid
WHERE login.username LIKE @email
回答by MatBailie
SET @email = '[email protected]';
SELECT email from `user` WHERE email LIKE CONCAT('%', @email, '%');
回答by Irina
Works without concat():
无需 concat() 即可工作：
LIKE '%{$var}%'
(Mysql version 5.+)
(Mysql 5.+ 版本)
回答by Anand Maurya
No need to CONCAT just use '%'+ Variable +'%':
无需 CONCAT 只需使用 '%'+ 变量 +'%'：
SET @email = '[email protected]';
SELECT email from `user` WHERE email LIKE '%' + @email + '%';
回答by Amey Vartak
BEGIN
SET @emailid = CONCAT('%', '[email protected]' ,'%');
SET @t1 =CONCAT('SELECT * FROM user WHERE email LIKE ''', @emailid, '''');
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
回答by Yevgeniy Afanasyev
You may have error
你可能有错误
Error Code: 1267. Illegal mix of collations for operation 'like' 0.016 sec
In this case you need to specify the same collation as used for your table like that:
在这种情况下，您需要指定与用于您的表相同的排序规则，如下所示：
SET @email = '[email protected]' COLLATE utf8_unicode_ci;
回答by Angel Alvarez Carvajal
I resolved using the CONCAT function before using LIKE statement:
我在使用 LIKE 语句之前使用 CONCAT 函数解决了：
SET @email = '[email protected]';
set @email = CONCAT('%',@email,'%');
SELECT email from `user` WHERE email LIKE @email;
It works fine for me
这对我来说可以
回答by Sergio Castillo
Using same syntax as oracle seems to work:
使用与 oracle 相同的语法似乎有效：
SELECT email from user WHERE email LIKE '%' || @email || '%';
SELECT email from user WHERE email LIKE '%' || @email || '%';
