站内消息设计

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

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

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

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

  2.2)插入消息回收站表

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

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

 

未读消息/消息表

Java代码  

1. DROP TABLE IF EXISTS `msg`;  
2. CREATE TABLE `msg` (  
3.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
4.   `mail` varchar(60) NOT NULL DEFAULT 'system' COMMENT '邮箱:默认为system表示发给所有用户',  
5.   `title` varchar(50) DEFAULT NULL COMMENT '标题',  
6.   `content` text NOT NULL COMMENT '内容',  
7.   `createtime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',  
8.   PRIMARY KEY (`id`)  
9. ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='公告表';  
10.   
11. INSERT INTO `msg` VALUES (1,'system','系统公告','今天天气不错',1394421260);  
12. INSERT INTO `msg` VALUES (2,'test@163.com','用户i','aaaaaaaaaaaa',1394421299);  
13. INSERT INTO `msg` VALUES (3,'danny@qq.com','用户公告heihei','aaaaaaa',1394421299);  
14. INSERT INTO `msg` VALUES (4,'system','系统公告2','第二条消息',1394421260);  

 已读消息或删除消息

Java代码  

1. DROP TABLE IF EXISTS `msgrecycle`;  
2. CREATE TABLE `msgrecycle` (  
3.   `id` int(11) NOT NULL AUTO_INCREMENT,  
4.   `uid` varchar(200) NOT NULL COMMENT '用户id',  
5.   `msgid` int(11) NOT NULL COMMENT '消息id',  
6.   `isread` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '0-未读  1-已读',  
7.   `isdelete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-删除',  
8.   PRIMARY KEY (`id`)  
9. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;  
10.   
11. LOCK TABLES `msgrecycle` WRITE;  
12. INSERT INTO `msgrecycle` VALUES (1,'test@163.com',2,1,0);  
13. INSERT INTO `msgrecycle` VALUES (2,'danny@qq.com',3,0,1);//删除的未读消息  
14. INSERT INTO `msgrecycle` VALUES (3,'test@163.com', 1, 1, 0); //test@163.com用户已读的system消息  
15. UNLOCK TABLES;  

 

显示用户已读消息mr.uid='test@163.com'

2.2)

Java代码  

1. select * FROM msg m left join msgrecycle mr on m.id=mr.msgid where (m.mail='system' or m.mail='test@163.com') and mr.uid='test@163.com' and isread=1 and isdelete=0  

2.1)

Java代码  

1. select * FROM msgrecycle where uid='test@163.com' and isread=1 and isdelete=0  

 

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

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

Java代码  

1. select m.* FROM msg m left join msgrecycle mr on m.id=mr.msgid where m.mail='test@163.com' and mr.id is null   

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

Java代码  

1. 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='test@163.com' and isread=1 and isdelete=0)