通用站内消息设计

需求

实现站内消息系统，其中功能包括：

• 一对多（公告、组内消息）
• 一对一（私信）
• 消息有三种状态：未读、已读、删除（删除为逻辑删除）

表结构设计

DROP TABLE IF EXISTS `t_message`;
CREATE TABLE t_message (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sender_id` int(11) NOT NULL DEFAULT '0' COMMENT '发送者id',
  `type`  int(1) NOT NULL DEFAULT '0' COMMENT '类型，0 private，1 public，2 global',
  `group_id` int(1) NOT NULL DEFAULT '0' COMMENT '组 id ',
  `content` varchar(200) DEFAULT '' COMMENT '正文',
  `send_time` varchar(20) NOT NULL DEFAULT '' COMMENT '发送时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_message_log`;
CREATE TABLE t_message_log (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `receiver_id` int(11) NOT NULL COMMENT '接收者id',
  `messsage_id` int(11) NOT NULL COMMENT '消息id',
  `status` int(1) NOT NULL DEFAULT '0' COMMENT '0 未读，1 已读，2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

t_message 表主要用来存放消息本身相关内容，id 为sender_id的用户在 send_time 时间发布了一个 type 类型的消息，内容是 content ，其中type及group_id这两个字段主要是限制消息的范围，如果不涉及组消息可以忽略group_id字段。

t_message_log 表主要用来记录某条消息呈现给接收者是什么状态，messsage_id这条信息对于id为receiver_id用户的状态为status

使用

一对一发送时新建消息需要分别写入消息到两个表

用户 a 发送一条消息给 b 时，先将消息写入到t_message 表,将type 设置为 0（private 状态）。

然后将新建消息的message_id及接收用户的idreceiver_id写入到t_message_log，并将status设置为0（未读）。

当 b 用户登录时，需要查询发送给自己的消息

SELECT t_message.*, t_message_log.status FROM t_message_log
LEFT JOIN t_message on t_message_log.messsage_id = t_message.id 
WHERE t_message.type = 0 and receiver_id = `b用户的id`

当 b 用户将消息标记为 已读时，只需要将t_message_log中该条记录的status改为 1（已读）即可。

一对多发送时新建消息需要只需要写一个表

管理员发布一条公告消息时，由于是公告消息所有用户都是接收者，只需要将消息写入到 t_message 表,将type 设置为 2（global 状态）即可

用户 b 初次登录查询公告消息

SELECT t_message.*, t_message_log.status FROM t_message
LEFT JOIN t_message_log ON t_message_log.messsage_id = t_message.id
WHERE t_message.type = 2 and t_message.id not in (select t_message_log.messsage_id FROM t_message_log WHERE receiver_id = `b用户的id`)

当 b 用户将消息标记为已读时，需要在t_message_log中新增一条记录message_id为公告消息的 id ,receiver_id为用户 b 的 id ,status设置为 1（已读）即可。

提示：用户登录查看公告信息的时候不写入t_message_log表，只有当用户标记为已读时才需要写入。好处是节省了t_message_log表的空间，只有真正登录的用户且标记已读的用户才会写入到表中，提升了性能。

列出某用户所有消息

SELECT t_message.*, t_message_log.status FROM t_message_log
LEFT JOIN t_crm_message ON t_message_log.messsage_id = t_message.id WHERE receiver_id = #{用户id}
UNION
select t_crm_message.*, t_message_log.status FROM t_message
LEFT JOIN t_message_log ON t_message_log.messsage_id = t_message.id
WHERE type=2 and t_message.id NOT IN (select t_message_log.messsage_id FROM t_message_log WHERE receiver_id = #{用户id})
ORDER BY send_time DESC
LIMIT #{page.offset}, #{page.pageSize}

问题

目前只能做到逻辑删除信息，如果需要物理删除需要建议在 t_message 表中加入消息过期字段，查询时只过滤未过期消息，这样就可将已过期的消息物理删除。

参考文档

https://www.cnblogs.com/hejiaquan/archive/2012/04/07/2435817.html