问题描述

我在开发个人网站时，关于搜索功能的实现本打算使用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版本的问题，不过没有找到官方的相关说明资料。