多字段、多关键字模糊查询实现

一、需求说明

文本描述：

1. 搜索框只有一个文本输入框
2. 支持多字段的关键字模糊查询，如输入一个字符，同时搜索商品名称和商品编码中是否存在该字符
3. 搜索框内支持逗号分隔输入，逗号不区分全角和半角（一般中文逗号为全角，英文逗号为半角）
4. 搜索框内容为全逗号或为空，不将该内容作为过滤条件进行查询

例子：

输入	结果
苹果，，，,榴莲,	模糊匹配查出商品名称或编码中存在“苹果”或“榴莲”的内容
,，，,，	相当于没有进行输入，不将商品名称和编码作为过滤条件进行查询

二、方案选择

1. 使用like和or进行查询

   select id,code,name from trace_business_variety_info tbvi where name like '%苹果%' or name like '%榴莲%' or code like '%苹果%' or code like '%榴莲%';
   

2. 使用concat(str1,str2,...)函数进行查询

   select id,code,name from trace_business_variety_info tbvi where concat(name,code) like '%苹果%' or concat(name,code) like '%榴莲%';
   

3. 使用or和MySQL的正则表达式（regexp）进行查询

   select id,code,name from trace_business_variety_info tbvi where name regexp '苹果|榴莲' or code regexp'苹果|榴莲';
   

三、方案分析

1. 方案1只使用like和or进行查询会导致生成的查询SQL语句过长，且like使用'%%'进行查找并未使用到索引

2. 方案2存在一个问题，那就是concat函数将多个字符串参数连接成一个字符串时，只要其中一个字符串的值为NULL，concat函数将返回NULL，这会将部分数据遗漏掉，如下所示：

   原始表数据：

   

   执行方案2的SQL查询结果(遗漏掉了泰国榴莲这条数据)：

   

3. 方案3使用or和regexp进行查询，regexp同样不走索引，但相对于方案1和方案2而言，方案3生成的SQL查询语句相对较短，且不会遗漏数据结果

​ 综上所述，使用方案3为最优解

四、具体实现

由于涉及到公司自研框架代码，不方便公布具体代码