Mybatis-Plus——分页+模糊查询

建表： 

create table pms_brand
(
   brand_id             bigint not null auto_increment comment '品牌id',
   name                 char(50) comment '品牌名',
   logo                 varchar(2000) comment '品牌logo地址',
   descript             longtext comment '介绍',
   show_status          tinyint comment '显示状态[0-不显示；1-显示]',
   first_letter         char(1) comment '检索首字母',
   sort                 int comment '排序',
   primary key (brand_id)
);

alter table pms_brand comment '品牌';

查看问题：

SELECT * from pms_brand where name like '%孔%' LIMIT 0,3;

 explain语句可以看到当前语句走了全表扫描，从中选取3条作为结果：

我们要查找的满足name='孔'的结果在第三行（按主键brand_id排序），假如我们只取两条结果，则在当前页面拿取不到我们要的结果；

现在我们希望不论如何情况都能在第一页取得最精确的结果，即'孔'出现在第一行。

方案1

把分页查询接口的数据，拆分成两部分：

1. 精确查询

2. 模糊查询

在代码中做处理的时候，先根据关键字精确查询，即sql中使用name='孔'，这种方式查询一次数据。

如果没查出数据，则再直接用like '%孔'进行模糊查询。

如果查出了一条数据，则把它放在返回结果集合中的第一位置。接下来，使用like '%孔'进行模糊查询的时候，再加上条件 name!='孔'。将查出的结果，从第二个位置往后放。

这样可以拼接出你想要的集合。

但有个缺点，就是代码耦合性太大了。

方案2

假如，我们有这样一种排序：

1. 全匹配显示在最前面，比如：孔。

2. 数据左半部分匹配，右边按字母排序，比如：孔1、孔2、孔技术。

3. 从中间开始匹配，比如：1孔、2孔。

4. 第2步和第3步，还要根据字符长度排序，字符短的排在前面，比如：孔1、2孔、1孔技术1

说起来容易，做起来难。

难道要先全匹配：name='孔'，再有匹配：name like '孔%'，再左匹配：name like '%孔'，把查询三次的结果组装起来？

不行，查询次数太多，臃肿。

其实，我们可以换一种思路，根据字符的长度排序。

mysql给我们提供了很多非常有用的函数，比如：char_length。

通过该函数就能获取字符长度。

sql调整如下：

select * from pms_brand where name like '%孔%' 
ORDER BY CHAR_LENGTH(name) asc LIMIT 0,3;

name字段使用关键字模糊查询之后，再使用char_length函数，获取name字段的字符长度，然后按长度升序。

搞定需求了：

我们所期待的：苏三，终于排在第一个了。同时由于该sql做了分页的，即使name字段在查询时丢失了索引，执行效率也不会太低。

业务上的需求搞定了。

但追求完美的我们，好奇，想看看第二页是什么情况：

select * from pms_brand where name like '%孔%' 
ORDER BY CHAR_LENGTH(name) asc LIMIT 3,3;

执行结果：

并没有按照我们设想的剧本进行下去，我们之前假设的3条排序中，第2条和第3条都没有满足。

这时该怎么办？

答：可以使用mysql中的locate函数，通过它可以匹配的关键字，在字符串中的位置。

LOCATE(substr,str), LOCATE(substr,str,pos)

第一个语法返回substr在字符串str 的第一个出现的位置。第二个语法返回子符串 substr 在字符串str，从pos处开始的第一次出现的位置。如果substr 不在str 中，则返回值为0 。 

使用locate函数改造之后sql如下：

select * from pms_brand where name like '%孔%' 
ORDER BY CHAR_LENGTH(name) asc,
LOCATE('孔',name) asc LIMIT 0,5;

执行结果：

除此之外，还可以使用：instr和position函数，它们的功能跟locate函数类似

mybatis中的xml

<select id="queryLikeByName" resultType="com.hcx.product.entity.Brand">
        select brand_id, name, logo, descript, show_status, first_letter, sort
        from pms_brand
        where name like concat('%',#{name},'%')
        order by char_length(name) ,
        locate(#{name},name)
        limit #{current},#{size};
 </select>
// 在语句中加入<if test=…… ></if> 标签导致locate函数中#{}占位符未生效，使用'${}'替代

mapper接口

@Mapper
public interface BrandMapper extends BaseMapper<Brand> {

    /**
     * 分页+模糊查询
     * @param name
     * @param current
     * @param size
     * @return
     */
    List<Brand> queryLikeByName(String name, long current, long size);
    
    //可以传入Page参数,分页插件拦截并返回Page结果
    Page<Brand> queryLikeByName(Page page,String name, long current, long size);
}

Service层 

@Service
public class BrandServiceImpl extends ServiceImpl<BrandMapper, Brand> implements BrandService {
    @Autowired
    private BrandMapper brandMapper;

    /**
     * 分页+模糊查询
     *
     * @param params
     * @return
     */
    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        QueryWrapper<Brand> queryWrapper = new QueryWrapper<>();
        //1、获取key
        String key = (String) params.get("key");
        IPage<Brand> page = new Page<>();
        //如果传过来的数据不是空的，就进行name模糊查询
        if (!StringUtils.isEmpty(key)) {
            long pageSize = Long.parseLong((String) params.get(Constant.LIMIT));
            long pageNumber = Long.parseLong((String) params.get(Constant.PAGE));

            List<Brand> brands = brandMapper.queryLikeByName(key,
                    (pageNumber - 1) * pageSize, pageSize);

            page.setRecords(brands);
            page.setSize(pageSize);
            page.setCurrent(pageNumber);

        } else {
            page = this.page(new Query<Brand>().getPage(params));
        }

        return new PageUtils(page);
    }

}