SQL以任何顺序搜索单词 [英] SQL search for words in any order

问题描述

给出这个模型:

+-----------+-------------+---------------------------+
|  item_id  |  item_name  |  desc                     |
+-----------+-------------+---------------------------+
|  1        |  Product 1  |  lorem ipsum dolor...     |
|  2        |  Product 2  |  lorem mauris eu...       |
|  3        |  Product 3  |  scelerisque sagittis...  |
|  4        |  Product 4  |  lorem dolor ipsum...     |
|  5        |  Product 5  |  ipsum dolor lorem...     |
+-----------+-------------+---------------------------+

我想搜索在item_name或desc中包含单词lorem ipsum的所有产品.此外，任何单词都可以出现在lorem和ipsum之间，并且lorem和ipsum可以以任何顺序出现.基本上，此搜索将返回项目1，4和5

And I want to search all of the products that contain the words lorem ipsum in either item_name or desc. Additionally, any words can appear between lorem and ipsum, and lorem and ipsum can appear in any order. Basically, this search would return items 1, 4, and 5

现在，我知道我可以做到:

Now, I know I could accomplish this with:

SELECT * FROM items
WHERE (item_name LIKE 'lorem%ipsum'
       OR desc LIKE 'lorem%ipsum')
OR (item_name LIKE 'ipsum%lorem'
    OR desc LIKE 'ipsum%lorem')

但是，如果我的搜索词较长(例如，lorem ipsum dolor坐在amet处，有礼貌地拥护高级人士)，我觉得随着向查询中添加OR的数量，它可能会变得有点荒谬.有没有更简单/更有效的方式来处理此问题?

But if my search term is longer (ie. lorem ipsum dolor sit amet, consectetur adipiscing elit), I feel like it could become a bit ridiculous with the number of OR added to the query. Is there an easier/more efficient way to handle this?

推荐答案

这种搜索要求对于全文搜索是(或至少可以是)类似于搜索的搜索引擎"，而不是传统的sql like搜索.对于全文搜索，搜索单词的顺序无关紧要，并且取决于RDBM，某些全文搜索功能允许同义词查找以及噪声单词过滤.

Full text search is (or at least can be) more of a "search engine" like search as opposed to the traditional sql like searches. With full text searching, the order of the words being searched for does not matter, and depending on the RDBMs some full text searching capabilities allow for synonym lookup, as well as noise word filtering.

在大多数情况下(我相信)，全文搜索比like搜索快得多. 此处是有关开始使用全文搜索的文章mysql.

In (i believe) most cases, full text searching is significantly faster than a like search. Here is an article on getting started with full text search in mysql.

示例mySql全文搜索语法:

Example mySql full text search syntax:

select *
from items
where match(item_name) against ('+lorem +ipsum' in boolean mode)

全文搜索确实有某些要求(在本文的链接中进行了详细介绍).我没有亲自进行mysql的全文搜索，否则我将列出这些步骤.如果您想朝那个方向走，应该足以让您入门.

Full text searching does have certain requirements (which are gone into detail in the links in the article). I've not personally worked with mysqls full text search, or I'd list out the steps. Should be enough to get you started though if you wanted to go in that direction.

这篇关于SQL以任何顺序搜索单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！