@Zjmainstay
2017-03-06T17:07:00.000000Z
字数 1310
阅读 3342
功能
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 判断不需要
)