MySQL 将 SQL 中的两列合并为 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10325170/
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
Combine two columns in SQL for WHERE clause
提问by Dmytro Shevchenko
In my SQL, I am using the WHEREand LIKEclauses to perform a search. However, I need to perform the search on a combined value of two columns - first_nameand last_name:
在我的 SQL 中，我使用WHEREandLIKE子句来执行搜索。但是，我需要对两列的组合值执行搜索 -first_name和last_name：
WHERE customers.first_name + customers.last_name LIKE '%John Smith%'
WHERE customers.first_name + customers.last_name LIKE '%John Smith%'
This doesn't work, but I wondered how I could do something along these lines?
这行不通，但我想知道如何按照这些方式做一些事情？
I have tried to do seperate the search by the two columns, like so:
我试图按两列分开搜索，如下所示：
WHERE customers.first_name LIKE '%John Smith%' OR customers.last_name LIKE '%John Smith%'
WHERE customers.first_name LIKE '%John Smith%' OR customers.last_name LIKE '%John Smith%'
But obviously that will not work, because the search query is the combined value of these two columns.
但显然这行不通，因为搜索查询是这两列的组合值。
回答by Dmytro Shevchenko
Use the following:
使用以下内容：
WHERE CONCAT(customers.first_name, ' ', customers.last_name) LIKE '%John Smith%'
Note that in order for this to work as intended, first name and last name should be trimmed, i.e. they should not contain leading or trailing whitespaces. It's better to trim strings in PHP, before inserting to the database. But you can also incorporate trimming into your query like this:
请注意，为了使其按预期工作，应该修剪名字和姓氏，即它们不应包含前导或尾随空格。在插入到数据库之前，最好在 PHP 中修剪字符串。但是您也可以像这样将修剪合并到您的查询中：
WHERE CONCAT(TRIM(customers.first_name), ' ', TRIM(customers.last_name)) LIKE '%John Smith%'
回答by scott
I would start with something like this:
我会从这样的事情开始：
WHERE customers.first_name LIKE 'John%' AND customers.last_name LIKE 'Smith%'
This would return results like: John Smith, Johnny Smithy, Johnson Smithison, because the percentage sign is only at the end of the LIKEclause. Unlike '%John%'which could return results like: aaaaJohnaaaa, aaaSmithaaa.
这将返回如下结果：John Smith, Johnny Smithy, Johnson Smithison, 因为百分号只在LIKE子句的末尾。不像'%John%'它可以返回这样的结果：aaaaJohnaaaa, aaaSmithaaa。
回答by victor
try this:
尝试这个：
SELECT *
FROM customers
WHERE concat(first_name,' ',last_name) like '%John Smith%';
reference:
MySQL string functions
参考：
MySQL 字符串函数
