ER模型

ER图.png

建表SQL

-- 配置信息 
CREATE TABLE `config` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` varchar(20) COLLATE utf8_bin NOT NULL,
  `value` varchar(2000) COLLATE utf8_bin NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT '2019-01-01 00:00:00',
  `update_time` datetime NOT NULL DEFAULT '2019-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 订阅信息
CREATE TABLE `subscribe` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `openid` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `work_id` int(10) unsigned NOT NULL DEFAULT '0',
  `state` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '0、取消，1、正常',
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_openid_wid` (`openid`,`work_id`),
  KEY `idx_wid` (`work_id`,`state`)
) ENGINE=InnoDB ;

-- 用户信息
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `openid` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `union_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `token` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '访问令牌',
  `session_key` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `nick_name` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `avatar_url` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `gender` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0、女，1、男',
  `province` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `city` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `country` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_openid` (`openid`),
  KEY `idx_token` (`token`)
) ENGINE=InnoDB ;

-- 视频信息
CREATE TABLE `video` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sno_str` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
  `sno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '剧集序号',
  `url` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '视频地址',
  `create_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  PRIMARY KEY (`id`),
  KEY `idx_ct` (`create_time`),
  KEY `idx_wid` (`work_id`),
  KEY `idx_sno` (`sno`)
) ENGINE=InnoDB;

-- 剧集，作品信息
CREATE TABLE `work` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
  `wirter` varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '编剧',
  `director` varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '',
  `actor` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '演员',
  `total_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '总集数',
  `release_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '已经更新的集数',
  `year` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年代',
  `release_desc` varchar(45) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '更新描述(例如:每天发布一集)',
  `release_date` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '发布日期',
  `description` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '剧集描述',
  `img_url` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '封面图片地址',
  `douban_id` int(11) NOT NULL DEFAULT '0' COMMENT '豆瓣id',
  `douban_point` float NOT NULL DEFAULT '0' COMMENT '豆瓣评分',
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `finished` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0、未完结，1、完结',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '类型:0、韩剧，1、美剧',
  `extra` varchar(1000) COLLATE utf8_bin NOT NULL COMMENT '扩展字段',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_year` (`year`),
  KEY `idx_db_id` (`douban_id`),
  KEY `idx_db_point` (`douban_point`),
  KEY `idx_ct` (`create_time`),
  KEY `idx_ut` (`update_time`)
) ENGINE=InnoDB ;

CREATE TABLE `work_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL DEFAULT '0',
  `category` varchar(5) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '类别',
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_wid` (`work_id`)
) ENGINE=InnoDB ;