如何创建用于查询部分匹配项的sql查询? [英] How do I create sql query for searching partial matches?

问题描述

我在db中有一组项目.每个item有一个name and a description.我需要实现一个search工具，该工具接受多个关键字并返回具有至少一个与之匹配的关键字的不同项目or描述中的单词.

例如 我在数据库中有三个项目

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

使用关键字文字"进行的搜索应返回魔术标记和XPigment衬纸

使用关键字购物中心"进行的搜索应返回第二项

我尝试分别使用LIKE关键字和IN关键字.. 为了使IN关键字有效，查询必须为

SELECT DISTINCT FROM mytable WHERE name IN ('pall mall cigarettes')

但是

SELECT DISTINCT FROM mytable WHERE name IN ('mall')

将返回0行

我不知道如何进行查询，以容纳名称和描述列，并允许部分单词匹配.

有人可以帮忙吗?

更新:

我通过休眠创建了该表，并在description字段中使用了javax.persistence @Lob批注.检查表时使用psql，如图所示

...
 id           | bigint                      | not null
 description  | text                        |  
 name         | character varying(255)      | 
...

表中的一条记录是

id | description | name 
21 | 133414      | magic marker

解决方案

首先，这种方法无法大范围扩展，您需要从单词到项目的单独索引(如倒排索引). /p>

如果您的数据不大，可以这样做

SELECT DISTINCT(name) FROM mytable WHERE name LIKE '%mall%' OR description LIKE '%mall%'

如果您有多个关键字，请使用OR.

I have a set of items in db .Each item has a name and a description.I need to implement a search facility which takes a number of keywords and returns distinct items which have at least one of the keywords matching a word in the name or description.

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

A search using keyword 'mall' should return the second item

I tried using the LIKE keyword and IN keyword separately ,.. For IN keyword to work,the query has to be

SELECT DISTINCT FROM mytable WHERE name IN ('pall mall cigarettes')

but

SELECT DISTINCT FROM mytable WHERE name IN ('mall')

will return 0 rows

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

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

解决方案

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 OR if you have multiple keywords.

这篇关于如何创建用于查询部分匹配项的sql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！