新闻娱乐类APP的后端核心逻辑总结
新闻娱乐类APP的后端核心逻辑总结
一.主要功能：
用户：登录、注册（微信账号登录、手机号登录）、修改、审核
内容：发布、审核、分享、点赞、收藏及置顶热推等相关操作
评论：发布、审核、点赞及热评等相关操作
消息推送：站内信如用户修改结果、内容发布结果、评论审核结果等通知，站外信比如短信、微信通知
后台相关：审核、编辑操作（置顶、热评等操作）、统计
二.主要的表结构：
1.用户相关：
（1）用户登录日志：
CREATE TABLE login_log (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,device varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘设备号’,ip varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘登录ip’,version varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘设备号’,dtu varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘0’ COMMENT ‘渠道号 200：WEB、100: iOS、001：安卓’,content text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘json字符串，请求字段的合集’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY member_id (member_id),
KEY device (device)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’登录日志表’;
（2）用户注册信息表：存储用户静态数据
CREATE TABLE member (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id，即用户id’,telephone varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘手机号码’,union_id varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信union_id’,open_id varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信open_id’,origin tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘来源，1-通过手机号码注册，2-通过微信注册’,source tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘用户注册渠道，1：ios注册，2：安卓注册，3-web注册，4-马甲用户’,dtu varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘来源渠道（dtu）’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY telephone (telephone),
KEY union_id (union_id),
KEY create_time (create_time),
KEY update_time (update_time),
KEY is_mp (is_mp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户注册表’;
（3）用户基本信息表：动态数据和冗余静态数据
CREATE TABLE member_info (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘用户id, member.id’,telephone varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘手机号码’,nickname varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘用户昵称’,wx_nickname varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信昵称’,birth varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘生日’,avatar varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘头像’,sex tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘性别，0-未知，1-男，2-女’,age int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘年龄’,level int(11) NOT NULL DEFAULT ‘1’ COMMENT ‘用户等级’,freeze_time timestamp NULL DEFAULT NULL COMMENT ‘冻结时间’,follow_num bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘关注数’,fan_num bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘粉丝数’,version varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘当前版本’,device varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘设备号’,last_active_time timestamp NULL DEFAULT NULL COMMENT ‘最近一次活跃时间’,signature varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘签名’,is_realname tinyint(2) DEFAULT ‘0’ COMMENT ‘是否实名认证,0-否，1-是’,experience bigint(20) NOT NULL DEFAULT ‘1’ COMMENT ‘经验值’,irregularity_content_num bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘违规内容数量’,irregularity_freeze_to timestamp NULL DEFAULT NULL COMMENT ‘冻结上传截止时间’,position tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘身份：0-普通，1-签约’,status tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘状态，1-正常，2-未激活，3-冻结’,is_mp tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否是自媒体’,is_original tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否原创,0-否，1-是’,ext text COLLATE utf8mb4_unicode_ci COMMENT ‘扩展信息’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY telephone (telephone),
KEY create_time (create_time),
KEY update_time (update_time),
KEY position (position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户基本信息表’;
（4）用户微信信息表
CREATE TABLE member_wx_info (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,member_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,nickname varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘昵称’,avatar varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘头像’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
UNIQUE KEY member_id (member_id),
KEY create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户微信信息表’;
（5）微信绑定记录表
CREATE TABLE wechat_bind_log (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,union_id varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信union_id’,open_id varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信open_id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘绑定的用户id’,wx_nickname varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信昵称’,sex tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘性别，0-未知，1-男，2-女’,avatar varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信图像’,city varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信用户所在城市’,province varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信用户所在省份’,country varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信用户所在国家’,language varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信language’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY union_id (union_id),
KEY member_id (member_id),
KEY open_id (open_id(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’微信绑定记录表’;
（6）用户审核
CREATE TABLE member_info_audit (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,operator_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘操作者id’,member_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,nickname varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘昵称’,wx_nickname varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘微信昵称’,avatar varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘头像’,signature varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘签名’,status tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘状态，0-待审核，1-审核驳回，2-审核中，3-审核通过’,type tinyint(1) NOT NULL DEFAULT ‘1’ COMMENT ‘类型： 1 微信登录；2 用户修改’,content text COLLATE utf8mb4_unicode_ci COMMENT ‘驳回原因’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY create_time (create_time),
KEY member_status (member_id,status),
KEY uptime_status (update_time,status)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’头像、昵称、签名审核表’;
（7）用户审核队列表
CREATE TABLE member_info_audit_queue (id bigint(20) NOT NULL AUTO_INCREMENT,member_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户member_id’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY index_mem_id (member_id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
（8）正在被审核的用户队列（审核占坑）
CREATE TABLE member_info_audit_queue_jump (id bigint(20) NOT NULL AUTO_INCREMENT,member_info_auidt_queue_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户审核队列id’,audit_member_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘审核者id’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’正在被审核的用户队列（审核占坑）’;
（9）
CREATE TABLE member_msg_push (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id，即用户id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,push_id varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘推送id’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘平台类型，1-友盟，2-个推’,status tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘状态，0-无效，1-有效’,source tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘用户注册渠道，1：ios注册，2：安卓注册，3-web注册\n’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户信息推送表’;
2.评论相关表
（1）内容评论表
CREATE TABLE comment (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,ref_comment_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘引用的顶级comment_id’,rep_comment_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘引用的上一级comment_id’,rep_member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘回复针对的member(上一级)’,comment text COLLATE utf8mb4_unicode_ci COMMENT ‘评论内容’,content text COLLATE utf8mb4_unicode_ci COMMENT ‘内容’,like_num bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘点赞数’,reply_num bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘回复数’,status tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘状态，0-待审核，1-已通过, 2-未通过’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘类型，1-普通，2-优质’,is_top tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否置顶，0-否，1-是’,sort_score bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘排序分数’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,is_hot tinyint(2) DEFAULT NULL COMMENT ‘是否热门评论’,path varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘资源路径’,media_id varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘媒体id’,width int(11) DEFAULT NULL COMMENT ‘宽度’,height int(11) DEFAULT NULL COMMENT ‘高度’,grade tinyint(4) DEFAULT NULL COMMENT ‘等级’,
PRIMARY KEY (id) USING BTREE,
KEY ref_comment_id (ref_comment_id) USING BTREE,
KEY content_id (content_id,create_time) USING BTREE,
KEY member_id (member_id,create_time) USING BTREE,
KEY sort_score (sort_score) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容评论表’;
（2）评论点赞表
CREATE TABLE comment_like (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,comment_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘评论id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘点赞人id’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,comment_member_id int(11) NOT NULL COMMENT ‘评论的所属用户id’,is_cancle tinyint(1) NOT NULL DEFAULT ‘0′ COMMENT ‘是否已取消 ：1是 0否 ‘ ,
PRIMARY KEY (id) USING BTREE,
KEY comment_id (comment_id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’评论点赞表’;
3.内容相关：
（1）内容表：
CREATE TABLE content (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,title varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘标题’,type tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘1-视频, 2-图文, 3-GIF’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘发布者id’,file_id varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,status tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘状态，0-上传完成，1-待审，2-驳回，3-审核中，4-通过，5-删除，6-人工废弃，7-系统废弃，8-云处理失败，9-云处理中，10-下线’,audit_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘审核时间’,audit_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘审核者id’,approved_at timestamp NULL DEFAULT NULL COMMENT ‘通过时间’,category int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘分类’,origin tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘来源，1-安卓录入，2-ios用户录入，3-微视抓取，其他看配置’,is_recommended tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否推荐，0-否，1-是’,recommended_at timestamp NULL DEFAULT NULL COMMENT ‘推荐时间’,is_mp tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否是自媒体视频’,is_original tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否APP拍摄，0-否，1-是’,show_type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘展示类型，0-不在列表展示，1-在列表展示，2-在热推榜’,content varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘内容，json存入，比如审核相关的一些值，cpm等’,reason_id varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘驳回原因id,多个时用逗号分隔’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY member_id (member_id),
KEY update_time (update_time),
KEY create_time (create_time),
KEY approved_at (approved_at),
KEY audit_id (audit_id,audit_at),
KEY audit_at_status (status,audit_at),
KEY member_status (member_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容表’;
（2）内容详情表：
CREATE TABLE content_detail (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘视频id，即video.id’,media_id varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘阿里媒体id’,width int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘宽’,height int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘高’,duration decimal(11,6) NOT NULL DEFAULT ‘0.000000’ COMMENT ‘时长’,path varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘图片路径’,cover varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘0’ COMMENT ‘封面图’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘1-视频截图，2-自己上传’,format varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘文件类型’,size int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘大小，单位b’,fps varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘每秒钟帧数’,bitrate varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘比特率，单位%’,definition varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘清晰度，xld-低清，ld-普清，sd-标清,hd-高清’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY media_id (media_id(191)),
KEY content_id (content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容详情表’;
（3）内容互动（分享、点赞、收藏等）基本数据表
CREATE TABLE content_interactive_info (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘内容id’,like_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘收藏人数’,comment_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘评论人数’,qq_share_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘qq分享数’,wx_share_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘微信分享数’,wxf_share_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘微信朋友圈分享数’,wb_share_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘微博分享数’,qq_share_success_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘成功分享qq’,wx_share_success_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘成功分享微信好友’,wxf_share_success_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘成功分享微信朋友圈’,wb_share_success_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘成功分享到微博’,watch_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘查看量’,hot_title varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘推荐标题’,hot_cover_image varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘推荐封面’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY update_time (update_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容互动（分享、点赞、收藏等）基本数据表’;
（4）用户内容点赞表
CREATE TABLE content_like (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,status tinyint(3) NOT NULL DEFAULT ‘1’ COMMENT ‘0无效 1有效’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY member_content (member_id,content_id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户内容点赞表’;
（5）用户负面反馈表
CREATE TABLE content_negative (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,content_member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容发布者id’,type tinyint(3) NOT NULL DEFAULT ‘1’ COMMENT ‘类型: 1-屏蔽该作者, 2-内容太差, 详细见配置’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY content_id (content_id),
KEY member_id (member_id),
KEY content_member_id (content_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户负面反馈表’;
（6）内容分享记录表
CREATE TABLE content_share (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,member_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’,content_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘分享类型，1-QQ，2-微信，3-微信朋友圈，其他看配置’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
KEY member_type (member_id,type),
KEY content_type (content_id,type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容分享记录表’;
（7）内容审核日志
CREATE TABLE content_audit_log (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘发布者id’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘1-视频, 2-图文, 3-GIF’,category tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘分类id’,origin tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘来源，1-安卓录入，2-ios用户录入，3-PC抓取’,content_source tinyint(2) unsigned NOT NULL DEFAULT ‘1’ COMMENT ‘内容来源，1-用户上传，2-微博抓取，3-百度抓取’,audit_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘审核人id’,audit_status tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘审核状态’,reason_id varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘1’ COMMENT ‘驳回原因id,多个时用逗号分隔’,content varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘审核描述信息’,is_first tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否优先审核，0不是，1是’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
UNIQUE KEY content_id (content_id),
KEY member_id (member_id),
KEY category (category),
KEY create_time (create_time),
KEY origin (origin),
KEY type (type),
KEY content_source (content_source),
KEY audit_id (audit_id),
KEY audit_status (audit_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容审核日志’;
（8）内容待审核队列表
CREATE TABLE content_audit_queue (id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,content_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘内容id’,member_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘发布者id’,type tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘1-视频, 2-图文, 3-GIF’,category tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘分类id’,origin tinyint(2) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘来源，1-安卓录入，2-ios用户录入，3-PC抓取’,content_source tinyint(2) unsigned NOT NULL DEFAULT ‘1’ COMMENT ‘内容来源，1-用户上传，2-微博抓取，3-百度抓取’,is_first tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘是否优先审核，0不是，1是’,publish_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘内容发布时间’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
UNIQUE KEY content_id (content_id),
KEY member_id (member_id),
KEY category (category),
KEY create_time (create_time),
KEY origin (origin),
KEY type (type) USING BTREE,
KEY content_source (content_source) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’内容待审核队列表’;
（9）正在被审核的内容队列（审核人员占坑）表
CREATE TABLE content_audit_queue_jump (id bigint(20) NOT NULL AUTO_INCREMENT,content_audit_queue_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘内容审核队列表的id’,audit_member_id int(10) NOT NULL COMMENT ‘审核人id’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’正在被审核的内容队列（审核人员占坑）表’;
4.敏感词相关
（1）敏感词表
CREATE TABLE sensitive_word (id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,name varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘名称’,status tinyint(2) NOT NULL DEFAULT ‘1’ COMMENT ‘状态，1有效，0无效’,level smallint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘级别’,operation tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘处理方式，1先发后审，2先审后发’,scene varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘应用场景，1:昵称，2:评论，3:签名，4:标题，多个以英文,分割’,admin_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘操作人，admin.id’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘添加时间’,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id),
UNIQUE KEY name (name) USING BTREE,
KEY admin_id (admin_id),
KEY status (status),
KEY time (create_time,update_time) USING BTREE,
KEY scene (scene) USING BTREE,
KEY operation (operation) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’敏感词’;
三.补充说明：
1.基本上核心逻辑就在表的设计中，还有其他附属功能如积分体系、等级体系等需要加表的，扩展起来也容易
2.为了提高请求速度和并发可适当配合ES和redis，如将用户信息保存在redis中，内容、评论等可保存在ES中
3.内容如视频、图片此处用到了阿里的媒体服务，首先会上传到阿里的服务器获得media_id（资源唯一id），这期间的轮转状态比较复杂，需要开发考虑全面。
posted on 2019-01-02 13:44 Ryanyanglibin 阅读(…) 评论(…) 编辑 收藏