被遗忘的SQL

公司平台有个系统公告模块,展示的是管理员发布的通知和公告,数据库有公告和公告阅读记录这两张表,后者存有公告 ID 和用户 ID。现在前端需要两个接口:1、当前登录用户未读公告;2、当前登录用户下所有公告,并返回已读、未读标识。需求很简单,在此记录一下。

表结构

两表结构分别如下

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `sys_notice` (
`notice_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告ID',
`notice_title` varchar(50) NOT NULL COMMENT '公告标题',
`notice_type` char(1) NOT NULL COMMENT '公告类型(1通知 2公告)',
`notice_content` longtext COMMENT '通知公告内容',
`status` char(1) DEFAULT '0' COMMENT '公告状态(0正常 1关闭)',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`notice_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='通知公告表';
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `sys_notice_read` (
`read_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` int(11) NOT NULL COMMENT '用户ID',
`notice_id` int(11) NOT NULL COMMENT '公告ID',
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`read_id`) USING BTREE,
KEY `read_id` (`read_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8;

刚开始想的很简单,两表数据都查出来,然后 for 循环遍历判断:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 公告列表
List<SysNoticeDto> noticeList = noticeService.getAllNoticeList();

// 公告阅读列表
List<SysNoticeRead> noticeReadList = noticeReadService.getReadNoticeByUserId(userId);

for(SysNoticeDto noticeDto : noticeList){
for(SysNoticeRead noticeRead : noticeReadList){
if(noticeDto.getNoticeId.equals(noticeRead.getNoticeId)){
noticeDto.setIsRead(true); // 已读
}else{
noticeDto.setIsRead(false); // 未读
}
}
}

......

双重循环,代码不太优雅,遂捣鼓着用 SQL 来实现

接口一

查询当前登录用户未读公告:

1
2
3
4
5
6
7
8
9
SELECT
notice_id,
notice_title,
notice_content,
remark
FROM
sys_notice n
WHERE
NOT EXISTS ( SELECT r.notice_id FROM sys_notice_read r WHERE r.notice_id = n.notice_id AND r.user_id = #{userId} )

使用 NOT EXISTS,并在子查询中用 userId 过滤

接口二

当前登录用户所有公告,并标记已读、未读的状态

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
n.notice_id,
n.notice_title,
n.notice_content,
n.remark,
CASE
WHEN r.notice_id IS NULL
THEN 'false' ELSE 'true'
END AS isRead
FROM
sys_notice n
LEFT JOIN ( SELECT notice_id FROM sys_notice_read WHERE user_id = #{userId} ) r ON n.notice_id = r.notice_id

先在子查询中用 userId 过滤已读公告,再用公告表 LEFT JOIN,然后 CASE WHEN 得到 isRead 标识字段。最终得到的记录数应该与公告表的记录数一致,写 SQL 时切记不要少了数据!

写在最后

这样写完后 Controller 代码瞬间简洁多了,直接返回查询结果。再不写写 SQL,自己都快忘了,要求高的话还可以把这 SQL 优化一下,不过目前的版本先这样了。年前几天,公司同事陆续回家了,我买的是 1 月 20 号晚 9 点的票,北京西,20 小时,硬座。。这应该是猪年发的最后一篇文章了,提前祝大家鼠年快乐!!!

点击查看

本文标题:被遗忘的SQL

文章作者:北宸

发布时间:2020年01月19日 - 20:20:20

最后更新:2020年03月18日 - 11:30:24

原始链接:https://www.liaofuzhan.com/posts/3973006348.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

-------------------本文结束 感谢您的阅读-------------------
🌞