如何创建用于搜索部分匹配项的 sql 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7172947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create sql query for searching partial matches?
提问by Damon Julian
I have a set of items in db .Each itemhas a name and a description.I need to implement a searchfacility which takes a number of keywords and returns distinct items which have at least one of the keywords matching a word in the name ordescription.
我在 db 中有一组项目。每个项目item都有一个name and a description.I 需要实现一个search工具，它接受多个关键字并返回不同的项目，这些项目至少有一个与名称or描述中的单词匹配的关键字。
for example I have in the db ,three items
例如我在数据库中有三个项目
1.item1 :
name : magic marker
description: a writing device which makes erasable marks on whiteboard
2.item2:
name: pall mall cigarettes
description: cigarette named after a street in london
3.item3:
name: XPigment Liner
description: for writing and drawing
A search using keyword 'writing' should return magic marker and XPigment Liner
使用关键字“writing”进行搜索应返回魔术标记和 XPigment Liner
A search using keyword 'mall' should return the second item
使用关键字“商场”的搜索应返回第二个项目
I tried using the LIKEkeyword and INkeyword separately ,..
For INkeyword to work,the query has to be
我尝试分别使用LIKE关键字和IN关键字，.. 要使IN关键字起作用，查询必须是
SELECT DISTINCT FROM mytable WHERE name IN ('pall mall cigarettes')
but
但
SELECT DISTINCT FROM mytable WHERE name IN ('mall')
will return 0 rows
将返回 0 行
I couldn't figure out how to make a query that accommodates both the name and description columns and allows partial word match..
我无法弄清楚如何进行同时包含名称和描述列并允许部分单词匹配的查询。
Can somebody help?
有人可以帮忙吗？
update:
更新：
I created the table through hibernate and for the description field, used javax.persistence @Lob annotation.Using psql when I examined the table,It is shown
我通过休眠创建了表，对于描述字段，使用 javax.persistence @Lob annotation.Using psql 当我检查表时，显示
...
id | bigint | not null
description | text |
name | character varying(255) |
...
One of the records in the table is like,
表中的记录之一是这样的，
id | description | name
21 | 133414 | magic marker
回答by stracktracer
First of all, this approach won't scale in the large, you'll need a separate index from words to item (like an inverted index).
首先，这种方法不会大规模扩展，您需要从单词到项目的单独索引（如倒排索引）。
If your data is not large, you can do
如果你的数据不是很大，你可以这样做
SELECT DISTINCT(name) FROM mytable WHERE name LIKE '%mall%' OR description LIKE '%mall%'
using ORif you have multiple keywords.
使用OR，如果你有多个关键字。
回答by Narnian
This may work as well.
这也可能有效。
SELECT *
FROM myTable
WHERE CHARINDEX('mall', name) > 0
OR CHARINDEX('mall', description) > 0
