设计已读和未读的公告

需求

需求是这样的，需要在小程序端展示公告，可根据管理后台的排序显示公告列表，如果当前用户未阅读公告，则有未读的红点提示。

想法

提供管理后台的CRUD接口，提供小程序端公告列表接口且接口数据结构中包含是否已阅标识，还需提供一个已阅公告接口。

表设计

思考

表如何设计呢？要有一张公告表notice，这是肯定的，但是如何判定用户是否已读这个公告呢？可以采用一张用户与公告关系记录表notice_read，只要用户点击了公告，总之由前端调用已阅公告接口时，就往notice_read插一条记录。

设计

CREATE TABLE notice
(
    id            BIGINT(20) AUTO_INCREMENT COMMENT '公告主键ID',
    name          VARCHAR(225)                                  NOT NULL DEFAULT '' COMMENT '公告名称',
    content       VARCHAR(512)                                  NOT NULL DEFAULT '' COMMENT '公告内容',
    sort          tinyint(1) unsigned default 0                 not null comment '排序',
    is_deleted    tinyint(1) unsigned default 0                 not null comment '是否删除，0-否，1-是',
    creator_id    bigint unsigned     default 0                 not null comment '创建人ID',
    creator_name  varchar(64)         default ''                not null comment '创建人',
    gmt_create    datetime            default CURRENT_TIMESTAMP not null comment '创建时间',
    modified_id   bigint unsigned     default 0                 not null comment '修改人ID',
    gmt_modified  datetime            default CURRENT_TIMESTAMP not null comment '更新时间',
    modified_name varchar(64)         default ''                not null comment '最后变更人',
    PRIMARY KEY `id` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='公告表';


CREATE TABLE notice_read
(
    id         BIGINT(20) AUTO_INCREMENT COMMENT '主键',
    notice_id  BIGINT(20)                NOT NULL DEFAULT 0 COMMENT '公告ID',
    user_id    BIGINT(20)                NOT NULL DEFAULT 0 COMMENT '用户ID',
    gmt_create DATETIME                  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY `id` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='用户公告已读表';

初始化数据

mysql> insert into notice(name, content) values('中奖啦','恭喜中1000积分'),('重要通知','特别的重要....');
Query OK, 1 row affected (0.01 sec)

mysql> select * from notice;
+----+--------------+---------------------+------+------------+------------+--------------+---------------------+-------------+---------------------+---------------+
| id | name         | content             | sort | is_deleted | creator_id | creator_name | gmt_create          | modified_id | gmt_modified        | modified_name |
+----+--------------+---------------------+------+------------+------------+--------------+---------------------+-------------+---------------------+---------------+
|  1 | 中奖啦       | 恭喜中1000积分      |    0 |          0 |          0 |              | 2020-12-16 20:52:14 |           0 | 2020-12-16 20:52:14 |               |
|  2 | 重要通知     | 特别的重要....      |    0 |          0 |          0 |              | 2020-12-16 20:56:50 |           0 | 2020-12-16 20:56:50 |               |
+----+--------------+---------------------+------+------------+------------+--------------+---------------------+-------------+---------------------+---------------+
2 rows in set (0.00 sec)

假如有一个user_id为1的用户阅读了id为1的公告

∫mysql> insert into notice_read(notice_id, user_id) values(1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from notice_read;
+----+-----------+---------+---------------------+
| id | notice_id | user_id | gmt_create          |
+----+-----------+---------+---------------------+
|  1 |         1 |       1 | 2020-12-16 20:54:16 |
+----+-----------+---------+---------------------+
1 row in set (0.00 sec)

查询用户已阅和未阅公告

查询用户公告以公告表为主表，并且要返回公告是否已阅状态，假如该字段为isRead，0-标识为已阅，1-未阅。

mysql> SELECT  n.id, n.name,n.content, n.id IN (SELECT `notice_id` FROM `notice_read` WHERE `user_id` = 1) AS isRead FROM `notice` n
    -> ;
+----+--------------+---------------------+--------+
| id | name         | content             | isRead |
+----+--------------+---------------------+--------+
|  1 | 中奖啦       | 恭喜中1000积分      |      1 |
|  2 | 重要通知     | 特别的重要....      |      0 |
+----+--------------+---------------------+--------+
2 rows in set (0.00 sec)

恭喜，思路正确，公告id为1的记录，isRead为1，公告id为0，isRead为0，达到假设结果。

但是，这样设计真的好吗，如果数据量很大时，上面的语句效率会很低，in里面最好放确定的数据。我们改造一下，在notice_read表增加一个is_read字段。

ALTER TABLE notice_read ADD COLUMN is_read tinyint(1) unsigned not null default 0 comment '是否已阅，0-否，1-是';

当我们每次往notice_read插入一条数据时，is_read为1。

mysql> select * from notice_read;
+----+-----------+---------+---------------------+---------+
| id | notice_id | user_id | gmt_create          | is_read |
+----+-----------+---------+---------------------+---------+
|  1 |         1 |       1 | 2020-12-16 20:54:16 |       1 |
+----+-----------+---------+---------------------+---------+
1 row in set (0.01 sec)

这时候我们可以这样写

mysql> SELECT  n.id, n.name,n.content,ifnull(r.is_read, 0)  AS isRead FROM `notice` n left join `notice_read` r on n.id = r.notice_id and r.user_id = 1;
+----+--------------+---------------------+--------+
| id | name         | content             | isRead |
+----+--------------+---------------------+--------+
|  1 | 中奖啦       | 恭喜中1000积分      |      1 |
|  2 | 重要通知     | 特别的重要....      |      0 |
+----+--------------+---------------------+--------+
2 rows in set (0.00 sec)

 

OK啦，就这样。