一个成熟的系统，都会具备一个消息功能。用户可以在自己的主页查看到属于自己的信息。那么今天，就来分享一个简单的设计思路。如下示意图：
通过上面的截图，我们来分析一下大致的功能点：
- 每一个消息都具备消息类型，用户可以根据消息类型进行数据筛选。
- 消息有消息名称、消息内容和消息接收时间等基本字段。
- 消息分为已读和未读状态。
- 用户勾选了阅读消息，则消息会被设置为已阅读。
基础设计
要设计一个可用性高的软件，很难一步就直接能够实现。接下来我们，先一步一步的实现，最后在一点一点的完善。首先我们明确消息的几个字段。我们可以分入如下几个字段；
代码语言：javascript复制CREATE TABLE message (
id INT(11) NOT NULL AUTO_INCREMENT, -- 消息ID，自增长主键
sender VARCHAR(50) NOT NULL, -- 发送者，不能为空
receiver VARCHAR(50) NOT NULL, -- 接收者，不能为空
type INT(11) NOT NULL, -- 消息类型，不能为空[外键]
content VARCHAR(255) NOT NULL, -- 消息内容，不能为空
status INT(11) NOT NULL, -- 消息状态，不能为空[1已读2未读]
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认为当前时间戳
send_time TIMESTAMP NULL DEFAULT NULL, -- 发送时间，默认为空
update_time TIMESTAMP NULL DEFAULT NULL, -- 更新时间，默认为空
PRIMARY KEY (id),
INDEX message_receiver_index (receiver), -- 接收者索引
INDEX message_type_index (type) -- 消息类型索引
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
由于消息有分类，也就是上面表的type 字段值。这里我们创建一个消息分类表：
CREATE TABLE message_type (
id INT(11) NOT NULL AUTO_INCREMENT, -- 消息类型ID，自增长主键
title VARCHAR(50) NOT NULL, -- 消息类型名称
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认为当前时间戳
update_time TIMESTAMP NULL DEFAULT NULL, -- 更新时间，默认为空
PRIMARY KEY (id),
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
接下来就可以往 message 表中插入一条数据。
INSERT INTO message (sender, receiver, type, content, status, create_time) VALUES
('alice', 'bob', 1, '你好，这是一条私信消息！', 1, '2019-01-01 10:00:00');
用户查询消息时，就可以使用下面的SQL进行查询。
代码语言：javascript复制# 查询某种指定类型
SELECT * FROM message WHERE receiver = "alice" AND type = 1;
# 查询所有的消息
当用户阅读某条信息之后，就将该信息设置为已读状态.
代码语言：javascript复制update message set status = 1 where id = xxx and receiver = "alice";
进阶设计
在日常办公过程中，用户信息并非都是业务触发的场景，有可能平台也会发送消息。如何用上面的表结构来进行设计，就不好实现这样的场景。接下来，对该表进行一次升级。
代码语言：javascript复制CREATE TABLE message (
id INT(11) NOT NULL AUTO_INCREMENT, -- 消息ID，自增长主键
sender VARCHAR(50) NOT NULL, -- 发送者，不能为空
receiver VARCHAR(50) NOT NULL, -- 接收者，不能为空
type INT(11) NOT NULL, -- 消息类型，不能为空
content VARCHAR(255) NOT NULL, -- 消息内容，不能为空
status INT(11) NOT NULL, -- 消息状态，不能为空
is_global BIT(1) NOT NULL DEFAULT 0, -- 是否全局消息，默认为 0
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认为当前时间戳
send_time TIMESTAMP NULL DEFAULT NULL, -- 发送时间，默认为空
update_time TIMESTAMP NULL DEFAULT NULL, -- 更新时间，默认为空
PRIMARY KEY (id),
INDEX message_receiver_index (receiver), -- 接收者索引
INDEX message_type_index (type) -- 消息类型索引
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
如果用户需要查询信息时，既要查询到属于个人的信息，同时也要查询到全局的信息。可以使用 UNION 操作符将两个查询语句的结果合并在一起，一个查询获取当前用户的消息，另一个查询获取全局消息，并在最终结果中合并。
代码语言：javascript复制SELECT * FROM (
SELECT * FROM message WHERE receiver = "alice" -- 查询当前用户消息
UNION
SELECT * FROM message WHERE is_global = 1 -- 查询全局消息
) AS messages
ORDER BY create_time DESC; -- 按创建时间倒序排序
上述的SQL语句能够直接查询出用户的所有信息，但存在一个问题。当要判断对消息的阅读张图，全局消息的阅读状态就没法直观的判断。这时候就需要考虑到额外的一张表来进行处理。
代码语言：javascript复制CREATE TABLE user_global_message (
id INT(11) NOT NULL AUTO_INCREMENT,
user_id VARCHAR(50) NOT NULL,
message_id INT(11) NOT NULL,
PRIMARY KEY (id),
INDEX user_global_message_user_index (user_id), -- 用户 ID 索引
INDEX user_global_message_message_index (message_id) -- 全局消息 ID 索引
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
当用户对全局消息进行阅读之后，就往 user_global_message 表中插入一条数据。如果没有阅读的就表示，用户还未阅读该消息。
INSERT INTO user_global_message (user_id, message_id) VALUES
(1, 1, '2019-01-02 10:00:00');
用户在读取消息时，使用下面的SQL语句合并查询得到所有的消息。
代码语言：javascript复制SELECT
message.*,
IFNULL(user_global_message.is_read, 0) AS is_read
FROM message
LEFT JOIN user_global_message ON message.id = user_global_message.message_id AND user_global_message.user_id = "alice"
WHERE message.is_global = 1
ORDER BY message.create_time DESC;
优化版本
在实际的项目中，用户可以对属于自己的消息进行删除。如果不是全局消息，非常的简单，直接将message 表中插入一条数据，并将数据状态设置为已删除。针对这种情况，就需要把user_global_message表进行进一步的优化。
CREATE TABLE user_global_message (
id INT(11) NOT NULL AUTO_INCREMENT,
user_id VARCHAR(50) NOT NULL,
message_id INT(11) NOT NULL,
status tinyint(3) NOT NULL default 2, -- 1保留2删除
PRIMARY KEY (id),
INDEX user_global_message_user_index (user_id), -- 用户 ID 索引
INDEX user_global_message_message_index (message_id) -- 全局消息 ID 索引
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
在查询消息时，需要对 user_global_message 表进行联合查询，并过滤掉已经被删除的消息。
SELECT
message.*,
IFNULL(user_global_message.is_read, 0) AS is_read
FROM message
LEFT JOIN user_global_message ON message.id = user_global_message.message_id AND user_global_message.user_id = "alice"
WHERE message.is_global = 1 and user_global_message.status = 1
ORDER BY message.create_time DESC;
