一：数据库设计
DROP TABLE IF EXISTS `message_text`;CREATE TABLE `message_text` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(50) DEFAULT NULL COMMENT '标题',`content` varchar(1024) DEFAULT NULL COMMENT '内容',`created_at` datetime DEFAULT NULL COMMENT '创建时间',`status` int(11) DEFAULT '0' COMMENT '0:未发送 1：已发送 ',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;INSERT INTO `message_text` VALUES ('1', '智朗素材推送通知1', '智朗推送了1条素材，请注意查看!', '-10-15 16:49:17', '1');INSERT INTO `message_text` VALUES ('2', '智朗素材推送通知2', '智朗推送了2条素材，请注意查看!', '-10-15 17:31:00', '1');INSERT INTO `message_text` VALUES ('3', '智朗素材推送通知3', '智朗推送了3条素材，请注意查看!', '-10-15 17:31:22', '1');INSERT INTO `message_text` VALUES ('4', '智朗素材推送通知4', '智朗推送了3条素材，请注意查看!', '-10-15 18:09:01', '1');DROP TABLE IF EXISTS `message_read`;CREATE TABLE `message_read` (`id` int(11) NOT NULL AUTO_INCREMENT,`wxAccountId` int(11) DEFAULT NULL COMMENT '公众号id',`messageId` int(11) DEFAULT NULL COMMENT '消息id',`delete_flag` int(11) DEFAULT NULL COMMENT '删除标志：0未删除 1：已删除',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO `message_read` VALUES ('1', '1', '1', '0');INSERT INTO `message_read` VALUES ('2', '1', '2', '0');INSERT INTO `message_read` VALUES ('3', '1', '3', '1');
二：sql查询
/未读和已读站内信/
SELECT *,(SELECTcount(*)FROMmessage_readWHEREt.id = message_read.messageIdAND message_read.wxAccountId = 1) AS readStatusFROMmessage_text twhere t.id not in(select id from message_read where wxAccountId=1 and delete_flag=1)order by readStatus desc
/已删除的站内信/
SELECTt.*FROMmessage_text tLEFT JOIN message_read ron t.id = r.messageIdwhere r.delete_flag = 1 and r.wxAccountId = 1
当阅读【未读】的站内信时，在message_read中插入一条阅读记录
当删除【已读】的站内信时，将message_read的delete_flag置为1
