SQL搜索任何顺序的单词

Given this mockup:

鉴于这种模型:

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

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

我想搜索所有包含item_name或desc中的lorem ipsum的产品，另外，lorem和ipsum之间可以出现任何单词，lorem和ipsum可以以任何顺序出现。基本上，这个搜索将返回项目1、4和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')

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?

但是如果我的搜索词更长的话。lorem ipsum dolor sit amet, sacred tetur adipiscing elit)，我觉得如果查询的数量或添加的数量，它可能会变得有点荒谬。有更简单/更有效的方法来处理这个问题吗?

2 个解决方案

#1

this sort of search requirement sounds a good candidate for full text search.

这种搜索需求听起来是全文搜索的一个很好的候选者。

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.

全文搜索(或至少可以是)更像“搜索引擎”(search engine)，而不是传统的sql (sql)搜索(search)。在全文搜索的情况下，搜索词的顺序并不重要，根据RDBMs的不同，一些全文搜索功能允许同义词查找和噪声词过滤。

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:

示例mySql全文搜索语法:

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

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.

全文搜索确实有某些需求(在本文的链接中详细介绍)。我个人还没有使用mysqls的全文搜索，或者我会列出步骤。如果你想往那个方向走，应该足够让你开始。

#2

I think it is simpler to look for each word separately:

我认为分开查找每个单词更简单:

SELECT *
FROM items
WHERE (item_name like '%lorem%' and item_name like '%ipsum%') or
      (`desc` like '%lorem%' and `desc` like '%ipsum%');

This generalizes most easily to more words and to more columns.

这很容易概括为更多的单词和更多的列。

If you like, you can concatenate the values together. However, if you want both values in the same field, then the separate logic is clearer.

如果您愿意，可以将这些值连接在一起。但是，如果您希望两个值都在同一个字段中，那么单独的逻辑就会更清晰。

Also, if you are really looking for words, then full text search is a good option and will have much better performance.

此外，如果你真的在寻找单词，那么全文搜索是一个很好的选择，并且会有更好的性能。

#1