站内消息设计

设计优点，发系统消息给全站用户只需一条记录，不需要每个用户都插入，解决用户量大的问题

登录用户只显示未读的消息。用户查看未读消息后，将消息插入消息回收站表标记为已读，uid与该用户绑定，因此回收站表没有uid=system的记录。

2)已读消息列表：实现方法有2种取其一即可

  2.1)插入消息回收站表并删除用户的未读消息表对应记录（非system）

  2.2)插入消息回收站表

删除的消息列表：只插入消息回收站表，

未读消息：当前用户没有读过的消息

 

未读消息/消息表

DROP TABLE IF EXISTS `msg`;
CREATE TABLE `msg` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mail` varchar(60) NOT NULL DEFAULT 'system' COMMENT '邮箱:默认为system表示发给所有用户',
  `title` varchar(50) DEFAULT NULL COMMENT '标题',
  `content` text NOT NULL COMMENT '内容',
  `createtime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='公告表';

INSERT INTO `msg` VALUES (1,'system','系统公告','今天天气不错',1394421260);
INSERT INTO `msg` VALUES (2,'[e-mail]','用户i','aaaaaaaaaaaa',1394421299);
INSERT INTO `msg` VALUES (3,'[e-mail]','用户公告heihei','aaaaaaa',1394421299);
INSERT INTO `msg` VALUES (4,'system','系统公告2','第二条消息',1394421260);

 已读消息或删除消息

DROP TABLE IF EXISTS `msgrecycle`;
CREATE TABLE `msgrecycle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(200) NOT NULL COMMENT '用户id',
  `msgid` int(11) NOT NULL COMMENT '消息id',
  `isread` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '0-未读  1-已读',
  `isdelete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-删除',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

LOCK TABLES `msgrecycle` WRITE;
INSERT INTO `msgrecycle` VALUES (1,'[e-mail]',2,1,0);
INSERT INTO `msgrecycle` VALUES (2,'[e-mail]',3,0,1);//删除的未读消息
INSERT INTO `msgrecycle` VALUES (3,'[e-mail]', 1, 1, 0); //[e-mail]用户已读的system消息
UNLOCK TABLES;

 

显示用户已读消息mr.uid='[e-mail]'

2.2)

select * FROM msg m left join msgrecycle mr on m.id=mr.msgid where (m.mail='system' or m.mail='[e-mail]') and mr.uid='[e-mail]' and isread=1 and isdelete=0

2.1)

select * FROM msgrecycle where uid='[e-mail]' and isread=1 and isdelete=0

 

显示用户未读消息 = 消息回收站表没有读过的用户消息记录+消息回收站表没有读过的系统消息记录

消息回收站表没有读过的用户消息记录

select m.* FROM msg m left join msgrecycle mr on m.id=mr.msgid where m.mail='[e-mail]' and mr.id is null 

 消息回收站表没有读过的系统消息记录

select * FROM msg where mail='system' AND id not in (select m.id FROM msg m left join msgrecycle mr on m.id=mr.msgid where m.mail='system' and mr.uid='[e-mail]' and isread=1 and isdelete=0)