create.sql

CREATE TABLE `home_page` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `user_id` varchar(64) NOT NULL COMMENT '用户id',
  `head_img` varchar(256) NOT NULL COMMENT '头像url',
  `nick_name` varchar(32) NOT NULL COMMENT '昵称',
  `introduction` varchar(256) NOT NULL COMMENT '个人简介',
  `sex` int(3) COMMENT '性别',
  `location` varchar(256) COMMENT '地址',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  UNIQUE KEY `uk_userId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='个人主页';


CREATE TABLE `follow_rel` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `follow_id` varchar(64) NOT NULL COMMENT '被关注的人id',
  `follower_id` varchar(64) NOT NULL COMMENT '粉丝id',
  `follow_time` datetime COMMENT '关注时间',
  `unfollow_time` datetime COMMENT '取消关注时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  UNIQUE KEY `uk_idol_follower` (`follow_id`, `follower_id`),
  INDEX `idx_follower` (`follower_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='粉丝关系';


CREATE TABLE `theme` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `theme_id` varchar(64) NOT NULL COMMENT 'UUID',
  `title` varchar(64) NOT NULL COMMENT '标题',
  `theme_type` int(4) NOT NULL COMMENT '类型',
  `content` text COMMENT '文本内容',
  `attach_type` int(4) NOT NULL COMMENT '附件类型',
  `attachment` text NOT NULL COMMENT '附件内容',
  `author_id` varchar(64) NOT NULL COMMENT '作者id',
  `former_theme_id` varchar(64) COMMENT '被转发的主题',
  `topic_id` varchar(64) NOT NULL COMMENT '所属的话题',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  INDEX `idx_thmee_id` (`theme_id`),
  INDEX `idx_author` (`author_id`),
  INDEX `idx_former` (`former_theme_id`),
  INDEX `idx_topic` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主题内容';

CREATE TABLE `theme_attachment` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `theme_id` varchar(64) NOT NULL COMMENT '主题ID',
  `attach_type` int(4) NOT NULL COMMENT '附件类型：1:产品 2：直播 3：短视频 4：课程 5:图片',
  `attach_id` varchar(64) NOT NULL COMMENT '附件对应的ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `theme_id` (`theme_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主题附件';

CREATE TABLE `comment` (
  `id` int(32) auto_increment PRIMARY KEY NOT NULL COMMENT 'id',
  `comment_id` varchar(64) NOT NULL COMMENT 'uuid',
  `comment_type` int(4) NOT NULL COMMENT '类型',
  `content` varchar(1024) DEFAULT NULL COMMENT '文本内容',
  `author_id` varchar(64) NOT NULL COMMENT '作者id',
  `theme_id` varchar(64) DEFAULT NULL COMMENT '主题id',
  `parent_id` varchar(64) DEFAULT NULL COMMENT '一级评论id',
  `reply_id` varchar(64) DEFAULT NULL COMMENT '回复评论id',
  `is_block` int(4) NOT NULL DEFAULT '0' COMMENT '是否屏蔽',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_author` (`author_id`) USING BTREE,
  KEY `idx_target` (`reply_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论';


CREATE TABLE `collection` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `collection_type` int(4) NOT NULL COMMENT '类型,类型 1:点赞主题，2：收藏主题 3:点赞评论',
  `user_id` varchar(64) NOT NULL COMMENT '作者id',
  `target_id` varchar(64) NOT NULL COMMENT '评论的目标id',
  `collection_time` datetime COMMENT '收藏时间',
  `uncollection_time` datetime COMMENT '取消收藏时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  INDEX `idx_author` (`user_id`),
  INDEX `idx_target` (`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收藏/点赞';


CREATE TABLE `topic` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `topic_id` varchar(64) NOT NULL COMMENT 'uuid',
  `topic_title` varchar(64) NOT NULL COMMENT '话题名称',
  `is_top` int(4) NOT NULL COMMENT '是否置顶',
  `is_conceal` int(4) NOT NULL COMMENT '是否隐藏',
  `view_cnt_adjust` bigint NOT NULL DEFAULT 0 COMMENT '浏览量调整',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  UNIQUE KEY `uk_title` (`topic_title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='话题';


CREATE TABLE `file_record` (
  `id` int(32) auto_increment NOT NULL COMMENT 'id',
  `file_id` varchar(64) NOT NULL COMMENT 'UUID',
  `file_type` int(4) NOT NULL COMMENT '类型：1：图片',
  `file_name` varchar(64) NOT NULL COMMENT '文件名称',
  `file_oss_key` varchar(64) NOT NULL COMMENT '阿里云key',
  `preview_url` varchar(512) DEFAULT NULL COMMENT '预览url',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_logic` (`file_oss_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='上传文件';


CREATE TABLE `black_list` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `blocker` varchar(64) NOT NULL COMMENT '屏蔽发起人',
  `blocked_type` int(4) NOT NULL COMMENT '屏蔽类型，1：用户，2：内容',
  `blocked_id` varchar(64) NOT NULL COMMENT '被屏蔽的',
  `create_by` varchar(64) DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  INDEX `uk_blocker` (`blocker`),
  INDEX `uk_blocked` (`blocked_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='黑名单';


CREATE TABLE `black_list` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `blocker` varchar(64) NOT NULL COMMENT '屏蔽发起人',
  `blocked_type` int(4) NOT NULL COMMENT '屏蔽类型',
  `blocked_id` varchar(64) NOT NULL COMMENT '被屏蔽的',
  `create_by` varchar(64) DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  INDEX `uk_blocker` (`blocker`),
  INDEX `uk_blocked` (`blocked_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='黑名单';


CREATE TABLE `visit_summary` (
  `id` int(32) auto_increment PRIMARY KEY COMMENT 'id',
  `session_id` varchar(64) NOT NULL COMMENT 'session_id',
  `visitor_id` varchar(64) NOT NULL COMMENT '浏览者id',
  `author_id` varchar(64) NOT NULL COMMENT '作者id',
  `ref_id` varchar(64) NOT NULL COMMENT '关联目标ID',
  `ref_type` int(8) NOT NULL COMMENT '关联目标类型',
  `duration` int(16) NOT NULL COMMENT '浏览时间 单位秒',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_tag` int(3) NOT NULL DEFAULT '0',
  INDEX `idx_session` (`session_id`),
  INDEX `idx_ref` (`ref_id`),
  INDEX `idx_visitor` (`visitor_id`),
  INDEX `idx_author` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='浏览记录';