设计优点，发系统消息给全站用户只需一条记录，不需要每个用户都插入，解决用户量大的问题
登录用户只显示未读的消息。用户查看未读消息后，将消息插入消息回收站表标记为已读，uid与该用户绑定，因此回收站表没有uid=system的记录。
2)已读消息列表：实现方法有2种取其一即可
2.1)插入消息回收站表并删除用户的未读消息表对应记录（非system）
2.2)插入消息回收站表
删除的消息列表：只插入消息回收站表，
未读消息：当前用户没有读过的消息
未读消息/消息表
Java代码
- 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,'test@163.com','用户i','aaaaaaaaaaaa',1394421299);
- INSERT INTO `msg` VALUES (3,'danny@qq.com','用户公告heihei','aaaaaaa',1394421299);
- INSERT INTO `msg` VALUES (4,'system','系统公告2','第二条消息',1394421260);
已读消息或删除消息
Java代码
- 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,'test@163.com',2,1,0);
- INSERT INTO `msgrecycle` VALUES (2,'danny@qq.com',3,0,1);//删除的未读消息
- INSERT INTO `msgrecycle` VALUES (3,'test@163.com', 1, 1, 0); //test@163.com用户已读的system消息
- UNLOCK TABLES;
显示用户已读消息mr.uid='test@163.com'
2.2)
Java代码
- 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代码
- select * FROM msgrecycle where uid='test@163.com' and isread=1 and isdelete=0
显示用户未读消息 = 消息回收站表没有读过的用户消息记录+消息回收站表没有读过的系统消息记录
消息回收站表没有读过的用户消息记录
Java代码
- 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代码
- 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)
