mysql查询相关联的前n条记录
由errol发表于2023-04-18 09:28:37 | 分类为 编程 | 标签为mysql、关联性查询、查询前N条记录、sql

如题，目前有这样的一个需求：

根据当前文章的标签，来查询相关联的文章记录，并按照关联度倒序排序，然后取前面n条记录。（关联性取决于文章的标签，相同的标签越多，表示文章的关联性越强）

如果标签专门有一张表来存储，文章和标签之间通过中间表建立关系的话，该需求还是比较容易实现的。

通过关联表的方式查询

相关的表的记录如下。

标签表记录

image

文章表记录

由于其他字段不是很重要，只截了id和标题。

image

文章&标签关联表记录

image

要实现这一需求，首先是通过关联表查询出目标文章的标签id。

select t2.tag_id 
from test.article t1 
join test.article_tag t2 on t2.article_id = t1.id
where t1.id = 1;

查询结果如下。

image

再把上一条sql当作子查询语句，来获取文章的id。

select article_id, count(tag_id) count
from test.article_tag
where article_id != 1 and tag_id in (select t2.tag_id from test.article t1 join test.article_tag t2 on t2.article_id = t1.id where t1.id = 1)
group by article_id order by count desc;

其中，count就是id为1的文章与其他文章的相关度量化，查询结果如下。

image

这个结果说明，其他两条文章记录都与id为1的文章有关联（可以翻到上面查看文章标签关联表对比查询结果）。

最后在通过上个查询得到的文章id，便查询出最终的需要的数据。

到此为止可以看出，通过关联表查询的优缺点都很明显，虽然比较简单，但是却非常的繁琐，一共需要使用三条sql语句，才能获取到最终的结果。

但是...

本项目的文章表结构是长这样的... 文章标签并没有使用独立的表来存储，而是使用了字符串格式，多个标签使用逗号分割。

create table article
(
    id                int auto_increment comment '文章id'
        primary key,
    parent_id         int               null comment '父文章id，比方说如果有专题文章的话，旗下就会有子文章',
    title             varchar(200)      null comment '标题',
    category_id       int               null comment '分类id',
    cover             varchar(150)      null comment '封面',
    summary           varchar(300)      null comment '摘要',
    tag               varchar(200)      null comment '标签',
    creation_type     tinyint           null comment '创作类型：0原创，1转载',
    reprint_url       varchar(200)      null comment '转载地址',
    author_id         int               null comment '作者id',
    commentable       tinyint           null comment '是否启用评论功能：0不开启，1开启',
    state             tinyint default 0 null comment '文章状态：0未发布，1已发布，2已删除，3草稿',
    created_at        datetime          null comment '文章创建时间',
    type              tinyint default 0 null comment '文章类型：0普通文章，1专题文章',
    last_updated_at   datetime          null comment '文章最后更新时间',
    last_published_at datetime          null comment '文章最后发布时间，存在两种情况：1、更新和发布同时进行（此时两个时间相同）；2、发布晚于更新；',
    sort              tinyint           null comment '文章排序',
    content           text              null comment '内容',
    category_name     varchar(100)      null
);

因此，想通过关联表查询的方式行不通，而且因为项目已经处于完成状态，也不能改变字段的存储格式，否则整个系统都得大刀阔斧修改，这是程序开发中的禁忌。

于是我只能继续寻找其他的实现方式...

FIND_IN_SET(String p1, String p2)

结果是理所当然的，即使用搜索引擎找了很久，都没有找到符合需求的查询方式，可能是我检索的关键字姿势不正确，以至于找到的大多数都是类似于前面关联表查询的方式，或者有的只是满足了Top N，而没有按关联性排序进行排序，这些不是我想要的。

但作为代替，我找到了一个mysql的函数：find_in_set(p1, p2)，这是一个由mysql提供的专门用于处理字符串的内置函数，可用于判断字符串p1是否包含于字符串p2中，如果包含，就返回p1在p2中对应的下标，不包含则会返回0。（下标从1开始）。

需要注意的是，字符串p2是有格式要求的，必须是以半角逗号相隔的字符串，而本项目文章表的标签字段的格式刚好符合这一要求。

该函数的使用方式如下。

select find_in_set('a', 'a,b,c'); # 'a'存在于'a,b,c'中，而a在位置1，因此返回1
select find_in_set('c', 'a,b,c'); # 'c'存在于'a,b,c'中，而c在位置3，因此返回3
select find_in_set('d', 'a,b,c'); # 'd'不存在于'a,b,c'中，因此返回0

当然，也很容易把函数的执行结果以布尔值的形式返回，在mysql数据库中，用1表示true，用0表示false。

此处我们用1来表示字符串匹配成功，用0表示匹配失败，即字符串p2不包含p1。

select find_in_set('a', 'a,b,c') > 0; # 返回1
select find_in_set('c', 'a,b,c') > 0; # 返回1 
select find_in_set('d', 'a,b,c') > 0; # 返回0

至此，所有的条件已经都准备好了，我们可以把sql写成这样。

select id, title,tag,((find_in_set('vue', tag) > 0) + (find_in_set('vue-router', tag) > 0) + (find_in_set('abc', tag) > 0)) matched
from article where id = 19;

mysql当然可以进行简单的四则运算。

执行的结果为：

image

其中matched就是传入的标签vue、vue-router、abc和id为19的文章标签的匹配个数，这个查询结果证明当前方式是行得通的。

所以，是时候把该sql语句应用于项目的查询了。

假设我想要查询与id为19的文章关联的其他文章，则sql如下。

state not in (2, 3)是为了过滤一些记录而加入的条件，这个是无关紧要的。

select id, title,
       ((find_in_set('vue', tag) > 0) + (find_in_set('提示保存', tag) > 0) + (find_in_set('vue-router', tag) > 0) + (find_in_set('页面刷新', tag) > 0) + (find_in_set('关闭页面', tag) > 0) + (find_in_set('javascript', tag) > 0)) tagMatched
from article
where id != 19 and state not in (2, 3)
order by tagMatched desc;

从查询结果里可以看到，只有一条数据是跟id为19的文章相关联，而且只有一个标签匹配，其他记录都不相关。

image

可以验证一下这一查询结果。

image

但有时候我们很可能并不需要那些不匹配的记录，关于这一点，我们可以在sql语句中加入正则表达式来过滤数据，于是最终的sql出现了。

默认情况下，mysql的正则表达式是大小写不敏感的。

select id, title,
       ((find_in_set('vue', tag) > 0) + (find_in_set('提示保存', tag) > 0) + (find_in_set('vue-router', tag) > 0) + (find_in_set('页面刷新', tag) > 0) + (find_in_set('关闭页面', tag) > 0) + (find_in_set('javascript', tag) > 0)) tagMatched
from article
where id != 19 and state not in (2, 3) and tag regexp '(vue|提示保存|vue-router|页面刷新|关闭页面|javascript)'
order by tagMatched desc limit 5;

结果如下，可以看到，不相关联的记录被过滤掉了。

image

当然，这种方式的缺点也很容易看出：需要动态生成查询语句，而且当标签较多的时候，sql会变得很长，查询速度也会慢下来。

关于动态sql，考虑到现在的持久层框架都是支持这一特性的，所以这并不是什么问题，而对于标签较多的话，解决方法还是有的，可以对标签的长度做限制，减少标签的数量（都不知道这算不算一个方法）。

如我使用mybatis时，该sql的格式为：

<select id="selectRelativeList" resultType="com.errol.easyblog.entity.Article">
        select t1.*,
               <foreach collection="tag.split(',')" item="item" separator="+" open="(" close=")">
                    (find_in_set(#{item}, t1.tag) > 0)
               </foreach> as tagMatched from article t1 
        where id != #{id} and state not in (2, 3) and tag regexp #{regexp} 
        order by tagMatched desc limit 5
</select>

对应的接口如下，其中，参数依次为文章id，文章tag，文章tag构成的正则表达式。

List<Article> selectRelativeList(@Param("id") Integer id, @Param("tag") String tag, @Param("regexp") String regexp);

事实证明，方法还是比困难多一点的，为了实现这一需求，刚开始当我意识到数据库表设计存在的问题时，我原本已经做了最坏的打算：修改数据库设计，但这样一来，项目就会涉及到很多需要修改的地方，这或许是任何一位开发者都不情愿的事情...

好在功夫不负有心人，虽然中途花了不少的时间，但最后终于是找到解决的方法，同时也避免了修改数据库设计（长叹了一口气）。

现在可以得出结论，不管是关联关系存储，还是字符串存储，都是可以实现关联性查询的，相较之下，前者需要使用的sql语句多一些，而后者则需要绕一点弯。

以上就是本篇文章的所有内容了，希望对你有所帮助，还有如果有更好的方式，望告知。

返回