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

Using 'OR' between HAVING and WHERE clause in MySQL?

提问by Jimmy

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' 之类的东西。

采纳答案by Zed

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%'

回答by Aristotle Pagaltzis

Just put all conditions into the HAVINGclause.

只需将所有条件放入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 WHEREclause could discard rows early, but since you cannot discard them until afteryou 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。

回答by Jonathan Leffler

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”的所有人员（例如，数据库中的所有 Johnson）。同样，第二个输入将选择所有名字包含“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.

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

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).

但是，如果您担心诸如“Charles De Gaulle”（或“Charles de Gaulle”）或“Michael van den Berg”）之类的名字，那么如果有人输入“Charles Gaulle”或“Michael Berg”，则匹配将失败，更不用说迈克尔范登堡了。您可能还需要用“%”符号替换用户输入中的任何空格字符。即便如此，您也面临着单词必须完全按照用户给出的顺序出现的问题——这可能无关紧要，但您应该有意识地决定这无关紧要。例如，如果输入是“Adam John Smith”，那么查询将不会捕获“John Adam Smith”；如果输入是“Smith, John”，则它不会接听任何人（很可能）。

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 Johnson），并且使用 SQL 标准 LIKE 运算符这样做几乎是不可能的。

回答by Bill Karwin

You can reference a computed column in the WHEREclause 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, LIKEwith wildcards is a terrible solution. You should think about using a FULLTEXTindex:

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

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

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

PS: FULLTEXTindexes work only with the MyISAM storage engine. Another solution, even more speedy, is to use Sphinx Searchfor fulltext indexing.

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

回答by Sklivvz

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.

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