在MySQL中使用HAVING和WHERE子句之间的“OR”？

I am trying to fetch records in MySQL using a simple used submitted field. More precisely, the user inputs a name (firstname or lastname or fullname) and the server should return matched rows.

我试图使用一个简单的使用提交字段来获取MySQL中的记录。更确切地说，用户输入名称（名字或姓氏或全名），服务器应返回匹配的行。

What I am doing so far is something like:

到目前为止我所做的是：

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

That works well for now, but that (obviously) won't work when a user submits the fullname. Is there a way to add a OR between the whole 'WHERE type conditions' and the 'HAVING type conditions'? This way I could do something like:

这在目前很有效，但是当用户提交全名时，（显然）不起作用。有没有办法在整个'WHERE类型条件'和'HAVING类型条件'之间添加OR？这样我可以做类似的事情：

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%' OR 
   HAVING fullname LIKE '%user_submitted_data%'

I know I could just split the original string but that has some negative impact since you have to deal with names containing spaces such as 'De Gaule' and stuff like that.

我知道我可以拆分原始字符串，但这有一些负面影响，因为你必须处理包含空格的名称，例如'De Gaule'和类似的东西。

5 个解决方案

#1

Do a subquery:

做一个子查询：

SELECT [some fields]
FROM
  SELECT firstname, lastname, CONCAT(firstname, ' ', lastname) as fullname
  FROM people) AS tmp
WHERE firstname LIKE '%user_submitted_data%'
OR lastname LIKE '%user_submitted_data%'
OR fullname LIKE '%user_submitted_data%'

#2

Just put all conditions into the HAVING clause.

只需将所有条件都放入HAVING子句中。

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
HAVING firstname LIKE '%user_submitted_data%'
OR      lastname LIKE '%user_submitted_data%'
OR      fullname LIKE '%user_submitted_data%

The WHERE clause could discard rows early, but since you cannot discard them until after you have evaluated the condition on the computed column, and that has to wait until HAVING, it buys you nothing to use WHERE.

WHERE子句可以提前丢弃行，但由于在评估计算列上的条件之后才能丢弃它们，并且必须等到HAVING，它才会购买任何东西来使用WHERE。

#3

Let's consider some possible inputs:

让我们考虑一些可能的输入：

John
Smith
John Smith

Your initial sample query is:

您的初始示例查询是：

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

Now, when the user enters the first input, this query will pick all the people whose first name contains 'John'; it will also pick all the people whose last name contains 'John' (for example, all the Johnsons in the database). Similarly, the second input will pick all the people whose first name contains 'Smith'; it will also pick all the people whose last name contains 'Smith' (for example, the Smithsons and Smithers). So far, so good; it isn't perfect because of case-sensitivity issues (I will ignore case-sensitivity from here on, but you probably should not ignore it at all), but it will be OK.

现在，当用户输入第一个输入时，此查询将选择名字中包含“John”的所有人;它还将挑选所有姓氏为“John”的人（例如，数据库中的所有Johnsons）。同样，第二个输入将选择名字中包含'Smith'的所有人;它还将挑选所有姓氏包含'Smith'的人（例如，Smithsons和Smithers）。到现在为止还挺好;由于区分大小写的问题，它不完美（我会从这里忽略区分大小写，但你可能根本不应该忽略它），但它会没问题。

The third input will only pick the people whose first name contains 'John Smith'; it will also pick those people whose last name contains 'John Smith'. However, it is rather likely that there are very few people who meet those criteria - those people called John Smith will have just John in the first name and just Smith in the last name. This is unlikely to be what you had in mind.

第三个输入只会选择名字中包含“John Smith”的人;它还会挑选那些姓氏包含“John Smith”的人。然而，很可能很少有人符合这些标准 - 那些名叫约翰史密斯的人将只有名字中的约翰和姓氏中的史密斯。这不太可能是你的想法。

It is not clear whether you have a column called 'fullname' in the table. If you do, then you can just match against that column instead of matching against the first name and last name separately. If you don't, maybe you can manufacture such a column and then run the query against that.

目前尚不清楚表中是否有一个名为“fullname”的列。如果这样做，那么您可以只匹配该列，而不是分别匹配第一个名称和姓氏。如果你不这样做，也许你可以制造这样一个列，然后针对它运行查询。

SELECT *
  FROM (SELECT firstname || ' ' || lastname AS fullname, ... FROM people) AS t 
 WHERE t.fullname LIKE '%user_submitted_data%'

This works reasonably well.

这工作得相当好。

However, if you are worried about names such as 'Charles De Gaulle' (or 'Charles de Gaulle') or 'Michael van den Berg'), then the matching will fail if someone enters 'Charles Gaulle' or 'Michael Berg', let alone Michael Vandenberg. You would probably need to replace any space characters in the user input with a '%' symbol too. Even then, you face the problem that the words must appear in exactly the sequence given by the user - which may not matter, but you should consciously decide that it doesn't matter. For example, if the input is 'Adam John Smith', then the query won't catch 'John Adam Smith'; if the input is 'Smith, John', then it won't pick up anyone (most likely).

但是，如果你担心“查尔斯*”（或“*”）或“迈克尔范登伯格”这样的名字，那么如果有人进入“查尔斯*”或“迈克尔·伯格”，那么匹配就会失败，更不用说迈克尔范登堡了。您可能还需要用'％'符号替换用户输入中的任何空格字符。即使这样，你也要面对这样的问题：单词必须完全按照用户给出的顺序出现 - 这可能无关紧要，但你应该有意识地决定它并不重要。例如，如果输入是'Adam John Smith'，那么查询将不会捕获'John Adam Smith';如果输入是'史密斯，约翰'，那么它将不会接收任何人（最有可能）。

If you want to manage this, you probably need to tokenize the user's input, and search on the separate words. Beware of someone asking about a sub-string of a word (for example, someone asks about 'de' as a name word) - none of the queries at the moment ensures that the user input words match whole words in the values (John vs Johnson), and doing so with the SQL standard LIKE operator is near enough impossible.

如果要对此进行管理，则可能需要对用户的输入进行标记，并搜索单独的单词。小心有人问一个单词的子字符串（例如，某人询问'de'作为名字） - 目前没有任何查询确保用户输入的单词与值中的整个单词匹配（John vs约翰逊），并且使用SQL标准LIKE运算符这样做是不够的。

#4

You can reference a computed column in the WHERE clause if you define that column in a subquery:

如果在子查询中定义该列，则可以在WHERE子句中引用计算列：

SELECT p.*
FROM (
  SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
  FROM people
) p
WHERE 
   p.firstname LIKE '%user_submitted_data%' OR 
   p.lastname LIKE '%user_submitted_data%' OR 
   p.fullname LIKE '%user_submitted_data%';

But honestly, for the type of search you're doing, LIKE with wildcards is a terrible solution. You should think about using a FULLTEXT index:

但老实说，对于你正在进行的搜索类型，喜欢使用通配符是一个糟糕的解决方案。您应该考虑使用FULLTEXT索引：

CREATE FULLTEXT INDEX people_names ON people(firstname, lastname);

SELECT *
FROM people
WHERE MATCH(firstname, lastname) AGAINST( ? );

PS: FULLTEXT indexes work only with the MyISAM storage engine. Another solution, even more speedy, is to use Sphinx Search for fulltext indexing.

PS：FULLTEXT索引仅适用于MyISAM存储引擎。另一种更快速的解决方案是使用Sphinx Search进行全文索引。

#5

Although using a subquery works well, it will have an impact because you are not hitting any indexes.

尽管使用子查询效果很好，但它会产生影响，因为您没有访问任何索引。

What about adding a computed column (firstname || ' ' || lastname) to the table and an index to it? Surely it would be much faster.

如何向表中添加计算列（firstname ||''|| lastname）及其索引？肯定会快得多。

If you cannot do that I think that querying like

如果你不能这样做，我认为那样的查询

WHERE firstname || ' ' || lastname LIKE '%user_submitted_data%'

should still work faster than two ORs and one subquery.

应该仍然比两个OR和一个子查询更快地工作。

#1