使用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屋！