站内信功能设计

功能

站内信内容表

CREATE TABLE `tb_notice` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '站内信内容表',
  `title` char(50) NOT NULL DEFAULT '' COMMENT '标题',
  `user_id` int(11) NOT NULL COMMENT '接收站内信用户id，值为0表示系统站内信',
  `status` smallint(3) NOT NULL COMMENT ' 发布状态（100:未发布  200:发布）',
  `message` text NOT NULL COMMENT '内容',
  `create_by` int(11) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `modify_time` datetime NOT NULL COMMENT '修改时间',
  `publish_time` datetime NOT NULL COMMENT '发布时间',
  `is_read` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已读（发给单独用户记录）',
  PRIMARY KEY (`id`),
  KEY `key_user_id_is_read` (`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

系统消息阅读表

CREATE TABLE `tb_notice_read` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '系统站内信阅读记录表',
  `user_id` int(11) NOT NULL COMMENT '系统站内信阅读用户id',
  `notice_id` int(11) NOT NULL COMMENT '站内信id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `key_user_id` (`user_id`),
  KEY `key_notice_id` (`notice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

未读信息

explain select * from tb_notice where user_id = 1 and is_read = 0
union
select * from tb_notice where user_id = 0 and is_read=0 and id not in (
    select notice_id from tb_notice_read where user_id = 1  #1  如果结果集为空，not in 判断不需要
) 

已读信息

explain select * from tb_notice where user_id = 1 and is_read = 1
union
select * from tb_notice where id in (
    select notice_id from tb_notice_read where user_id = 1  #1 如果结果集为空，not in 判断不需要
)