SQL全文本搜索vs“LIKE” [英] SQL full text search vs "LIKE"

问题描述

假设我有一个相当简单的应用程序，它允许用户在他们自己的DVD上存储信息（标题，演员，年份，描述等），我想允许用户通过这些字段（例如

Let's say I have a fairly simple app that lets users store information on DVDs they own (title, actors, year, description, etc.) and I want to allow users to search their collection by any of these fields (e.g. "Keanu Reeves" or "The Matrix" would be valid search queries).

使用SQL全文搜索的优点是只需用空格分隔查询，然后执行SQL语句中的几个LIKE子句？

What's the advantage of going with SQL full text search vs simply splitting the query up by spaces and doing a few "LIKE" clauses in the SQL statement? Does it simply perform better or will it actually return results that are more accurate?

推荐答案

全文搜索可能会更快，因为它将受益于它将用于查找记录的单词索引，而使用LIKE将需要全表扫描。

Full text search is likely to be quicker since it will benefit from an index of words that it will use to look up the records, whereas using LIKE is going to need to full table scan.

在某些情况下，LIKE会更准确，因为LIKE％％AND LIKE％Matrix将选择矩阵而不是矩阵重新加载，而全文搜索将忽略The并返回两者。这说明两者可能是一个更好的结果。

In some cases LIKE will more accurate since LIKE "%The%" AND LIKE "%Matrix" will pick out "The Matrix" but not "Matrix Reloaded" whereas full text search will ignore "The" and return both. That said both would likely have been a better result.

这篇关于SQL全文本搜索vs“LIKE”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！