php 在 WHERE 子句中使用 mysql concat()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/303679/
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
Using mysql concat() in WHERE clause?
提问by kevtrout
I would like to search my table having a column of first names and a column of last names. I currently accept a search term from a field and compare it against both columns, one at a time with
我想搜索我的表，其中有一列名字和一列姓氏。我目前接受一个字段中的搜索词并将其与两列进行比较，一次一个
select * from table where first_name like '%$search_term%' or
last_name like '%$search_term%';
This works fine with single word search terms but the result set includes everyone with the name "Larry". But if someone enters a first name then a space, then a last name, I want a narrower search result. I've tried the following without success.
这适用于单字搜索词，但结果集包括名称为“Larry”的每个人。但是，如果有人输入名字，然后是空格，然后是姓氏，我想要更窄的搜索结果。我尝试了以下但没有成功。
select * from table where first_name like '%$search_term%' or last_name
like '%$search_term%' or concat_ws(' ',first_name,last_name)
like '%$search_term%';
Any advice?
有什么建议吗？
EDIT:The name I'm testing with is "Larry Smith". The db stores "Larry" in the "first_name" column, and "Smith" in the "last_name" column. The data is clean, no extra spaces and the search term is trimmed left and right.
编辑：我正在测试的名字是“拉里史密斯”。数据库在“first_name”列中存储“Larry”，在“last_name”列中存储“Smith”。数据干净，没有多余的空格，搜索词被左右修剪。
EDIT 2:I tried Robert Gamble's answer out this morning. His is very similar to what I was running last night. I can't explain it, but this morning it works. The only difference I can think of is that last night I ran the concat function as the third "or" segment of my search query (after looking through first_name and last_name). This morning I ran it as the last segment after looking through the above as well as addresses and business names.
编辑 2：今天早上我尝试了 Robert Gamble 的回答。他和我昨晚跑步的很相似。我无法解释，但今天早上它有效。我能想到的唯一区别是昨晚我将 concat 函数作为搜索查询的第三个“或”段运行（在查看 first_name 和 last_name 之后）。今天早上，在浏览了上述内容以及地址和公司名称后，我将它作为最后一部分运行。
Does running a mysql function at the end of a query work better than in the middle?
在查询结束时运行 mysql 函数是否比在中间运行更好？
回答by Robert Gamble
What you have should work but can be reduced to:
你所拥有的应该有效，但可以减少到：
select * from table where concat_ws(' ',first_name,last_name)
like '%$search_term%';
Can you provide an example name and search term where this doesn't work?
您能否提供一个不起作用的示例名称和搜索词？
回答by Luc
Note that the search query is now case sensitive.
请注意，搜索查询现在区分大小写。
When using
使用时
SELECT * FROM table WHERE `first_name` LIKE '%$search_term%'
It will match both "Larry" and "larry". With this concat_ws, it will suddenly become case sensitive!
它将匹配“Larry”和“larry”。有了这个concat_ws，它会突然变得区分大小写！
This can be fixed by using the following query:
这可以通过使用以下查询来修复：
SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', `first_name`, `last_name`) LIKE UPPER('%$search_term%')
Edit: Note that this only works on non-binary elements. See also mynameispaulie's answer.
编辑：请注意，这仅适用于非二进制元素。另请参阅mynameispaulie 的回答。
回答by mynameispaulie
To Luc:
致吕克：
I agree with your answer, although I would like to add that UPPER only works on non-binary elements. If you are working with say an AGE column (or anything numeric) you will need to perform a CAST conversion to make the UPPER function work correctly.
我同意您的回答，但我想补充一点，UPPER 仅适用于非二进制元素。如果您正在使用 AGE 列（或任何数字），您将需要执行 CAST 转换以使 UPPER 函数正常工作。
SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', first_name, last_name, CAST(age AS CHAR)) LIKE UPPER('%$search_term%');
Forgive me for not responding to Luc's answer directly but for the life of me I could not figure out how to do that. If an admin can move my post, please do so.
请原谅我没有直接回应 Luc 的回答，但对于我的生活，我不知道该怎么做。如果管理员可以移动我的帖子，请这样做。
回答by Hyman
SELECT *,concat_ws(' ',first_name,last_name) AS whole_name FROM users HAVING whole_name LIKE '%$search_term%'
...is probably what you want.
......可能是你想要的。
回答by benlumley
There's a few things that could get in the way - is your data clean?
有一些事情可能会妨碍您 - 您的数据干净吗？
It could be that you have spaces at the end of the first name field, which then means you have two spaces between the firstname and lastname when you concat them? Using trim(first_name)/trim(last_name) will fix this - although the real fix is to update your data.
可能是您在名字字段的末尾有空格，这意味着当您连接它们时，名字和姓氏之间有两个空格？使用 trim(first_name)/trim(last_name) 将解决这个问题 - 尽管真正的解决方法是更新您的数据。
You could also this to match where two words both occur but not necessarily together (assuming you are in php - which the $search_term variable suggests you are)
你也可以用这个来匹配两个词都出现但不一定在一起的地方（假设你在 php 中 - $search_term 变量表明你是）
$whereclauses=array();
$terms = explode(' ', $search_term);
foreach ($terms as $term) {
$term = mysql_real_escape_string($term);
$whereclauses[] = "CONCAT(first_name, ' ', last_name) LIKE '%$term%'";
}
$sql = "select * from table where";
$sql .= implode(' and ', $whereclauses);
回答by zelyan
you can do that (work in mysql) probably other SQL too.. just try this:
你也可以这样做（在 mysql 中工作）可能也可以使用其他 SQL.. 试试这个：
select * from table where concat(' ',first_name,last_name)
like '%$search_term%';
回答by juanchobar
You can try this:
你可以试试这个：
select * FROM table where (concat(first_name, ' ', last_name)) = $search_term;
