问题描述

我在开发个人网站时,关于搜索功能的实现本打算使用Elasticsearch,但是奈何机器配置太低,跑不起来,于是退而求其次,使用了SQLAlchemy的contains方法,如下:

posts = Post.query.filter(or_(Post.title.contains(g.search_form.q.data),
                   Post.content.contains(g.search_form.q.data))).order_by(
                   Post.pub_date.desc()).paginate(
                   page, current_app.config['POSTS_PER_PAGE'], False)

在上线后,我有一篇博文包含“Kafka”这个字段,而不包含“kafka”这个字段。(注意这里有大小写之分)

我搜索“kafka”这个字段,没有返回任何结果,搜索“Kafka”返回结果包含上面提到的博文。

我的目标是搜索“kafka”返回结果要包含上面提到的博文,也就是说搜索时不区分大小写。

调试

于是我开始调试代码,发现我的本地调试环境可以达到预期结果,代码是同一套代码,查询结果不一致,那么很可能是数据库环境不一致导致的,我的数据库使用的是mysql,经过对比之后,我发现我的本地mysql是8.0.12,而线上mysql是5.7.24,所以,我判断是mysql版本不一致导致查询结果不一致。

解决

模糊查询除了使用contains方法,还可以使用ilike方法。

所以我有两个选择,第一,使用ilike代替contains试试;第二,升级线上数据库。

我的最优选是使用ilike代替contains,因为这样降低和数据库的耦合性。于是,我调试代码如下:

posts = Post.query.filter(or_(Post.title.ilike("%{}%".format(g.search_form.q.data)),
                   Post.content.ilike("%{}%".format(g.search_form.q.data)))).order_by(
                   Post.pub_date.desc()).paginate(
                   page, current_app.config['POSTS_PER_PAGE'], False)

返回结果符合预期,于是我决定使用ilike代替contains。使用ilike,也可能引发其他的问题,不过现在满足我的需求,先使用起来。

探索

经过查询SQLAlchemy的资料,找到了两个方法的描述。根据我的理解,这两个方法都与LIKE表达式有关。

区别如下表:

方法

使用示例

对应的SQL表达式

contains

Post.content.contains(“foobar”)

content LIKE ‘%’ || foobar || ‘%’

ilike

Post.content.ilike("%foobar%")

lower(content) LIKE lower(foobar) 或者

content ILIKE foobar

这里的contains貌似不是对SQL中的contains的实现。


新的问题

根据上面的信息,可以发现contains貌似是对like进行了一次封装,使用时不需要用通配符包裹关键字,其查询结果应该和like一致。

注意本篇文章开始提到的,根据查询结果对比,在mysql5.7中,使用contains方法进行查询是区分大小写的,而在mysql8.0中不区分大小写,而如果contains和like的关系如上所述,那么可以有如下猜想:

mysql8.0相对于mysql5.7,like的实现发生了变化。

验证猜想

测试准备:

在mysql5.7和mysql8.0中分别创建相同的表格post,并插入相同的数据,该数据中包含’Kafka’,不包含’kafka’。

使用相同的模糊查询语句分别执行查询,返回结果如下:

sql

mysql5.7

mysql8.0

select title from post where content like “%kafka%”;


预期数据

select title from post where content like “%Kafka%”;

预期数据

预期数据

经过测试,确实发现是mysql版本的问题,不过没有找到官方的相关说明资料。