站内消息设计

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

登录用户只显示未读的消息。用户查看未读消息后，将消息插入消息回收站表标记为已读，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,'[email protected]','用户i','aaaaaaaaaaaa',1394421299);  

insert into `msg` values (3,'[email protected]','用户公告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-删除',  

) engine=myisam auto_increment=8 default charset=utf8;  

lock tables `msgrecycle` write;  

insert into `msgrecycle` values (1,'[email protected]',2,1,0);  

insert into `msgrecycle` values (2,'[email protected]',3,0,1);//删除的未读消息  

insert into `msgrecycle` values (3,'[email protected]', 1, 1, 0); //[email protected]用户已读的system消息  

unlock tables;  

显示用户已读消息mr.uid='[email protected]'

2.2)

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

2.1)

select * from msgrecycle where uid='[email protected]' and isread=1 and isdelete=0  

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

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

select m.* from msg m left join msgrecycle mr on m.id=mr.msgid where m.mail='[email protected]' 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='[email protected]' and isread=1 and isdelete=0)