站内信设计(群发+私信)实践

一：数据库设计

DROP TABLE IF EXISTS message_read;
CREATE TABLE message_read (
id int(11) NOT NULL AUTO_INCREMENT,
wxAccountId int(11) DEFAULT NULL COMMENT ‘公众号id’,
messageId int(11) DEFAULT NULL COMMENT ‘消息id’,
delete_flag int(11) DEFAULT NULL COMMENT ‘删除标志：0未删除 1：已删除’,
PRIMARY KEY (id),
KEY wxAccountId (wxAccountId),
KEY messageId (messageId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS message_text;
CREATE TABLE message_text (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(50) DEFAULT NULL COMMENT ‘标题’,
content varchar(1024) DEFAULT NULL COMMENT ‘内容’,
created_at datetime DEFAULT NULL COMMENT ‘创建时间’,
status int(11) DEFAULT ‘0’ COMMENT ‘0:未发送 1：已发送 ‘,
type int(1) DEFAULT ‘0’ COMMENT ‘0/全局1/私有’,
from_id int(11) DEFAULT NULL COMMENT ‘发送者account_id’,
to_id int(11) DEFAULT NULL COMMENT ‘接收者account_id’,
PRIMARY KEY (id),
KEY from_id (from_id),
KEY to_id (to_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二：查询已读和未读的列表
SELECT *,(
SELECT count(*)
FROM message_read
WHERE t.id = message_read.messageId
AND message_read.wxAccountId = #wxAccountId#
) AS readStatus
FROM message_text t
where t.id not in(select messageId from message_read where wxAccountId=#wxAccountId# and delete_flag=1) and t.status = 1 and t.type = 0 or t.to_id = #wxAccountId#
order by readStatus asc
​

三：查询未读的邮件数目

select count(*) from message_text t where t.id not in(select messageId from message_read where wxAccountId = #wxAccountId#) and t.status = 1 and (t.to_id = #wxAccountId# or t.type = 0)​

当阅读【未读】的站内信时，在message_read中插入一条阅读记录
当删除【已读】的站内信时，将message_read的delete_flag置为1