使用OR操作符使用全文搜索(FTS)和多个标记进行多列搜索 [英] Searching in multiple columns using Full Text Search(FTS) with multiple tokens using OR operator
问题描述
我使用FTS查询数据库以提高搜索速度,因为我还需要在文本描述中进行搜索,
当我试图使用单列进行查询时它的工作很好,如下所示:
select * from productsearch where productsearch match('prod_name:panasonic * tw *')
此外,
select * from productsearch where productsearch match('prod_short_desc:samsung * s5 *')
以上两个查询给我预期的结果,但当我尝试使用 OR或
运算符来结合两个查询时,它不会给我任何结果
select * from productsearch where productsearch match('prod_name:panasonic * tw * or
prod_short_desc:samsung * s5 *')
所以,我想知道在使用 OR
操作符时我在这里做了什么错误用于搜索多列
更新
下面的查询工作正常,不符合我的要求,
select * from productsearch where productsearch match('prod_name:panasonic * or
prod_short_desc: samsung *')
您可以看到,如果我删除了多个标记, c $ c> OR 运算符也是如此。
而不是单独搜索所有列,我在数据库中创建了一列,其中包含要搜索的所需列的数据,
示例
我需要在 prod_name
& prod_short_desc
列,所以我在数据库中创建了一个名为 data
的列,并添加了 prod_name
& prod_short_desc
然后查找 data
字段,就像一个魅力一样工作
prod_name | prod_short_desc
samsung | samsung s5
所以,现在我将两列数据合并为一个空格作为分隔符
数据
samsung s5
>然后用下面的查询搜索速度非常快,
select * from搜索产品搜索匹配('samsung * s5 *')
I am using FTS for querying my database for increasing the search speed as I need to search in text description also,
When I am trying to query using single column its working fine like below
select * from productsearch where productsearch match ('prod_name:panasonic*tw*')
And also,
select * from productsearch where productsearch match ('prod_short_desc:samsung*s5*')
So, above both queries give me expected result but when I try to combine both queries using OR
operator its not giving me any result
select * from productsearch where productsearch match ('prod_name:panasonic*tw* OR
prod_short_desc:samsung*s5*')
So, I want to know if I am doing anything wrong in here while using OR
operator for searching multiple columns
UPDATE
Below query is working fine but that doesn't fulfill my requirement,
select * from productsearch where productsearch match ('prod_name:panasonic* OR
prod_short_desc:samsung*')
You can see that if I remove the multiple tokens then its working fine with OR
operator too.
So, I found the solution finally,
instead of searching from all the columns individually, I created a single column in the database which contains data for required columns to be searched into,
Example
I need to search in prod_name
& prod_short_desc
columns, so I created a column named data
in database and appended the values of prod_name
& prod_short_desc
then looking up into only data
field worked like a charm
prod_name | prod_short_desc
samsung | samsung s5
So, now I merged the data of both the columns into one with space as a seperator
data
samsung samsung s5
And then search was very fast indeed with below query,
select * from productsearch where productsearch match ('samsung*s5*')
这篇关于使用OR操作符使用全文搜索(FTS)和多个标记进行多列搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!