friendship-parent/doc/ddl/table/V4.2.sql

-- 2020-06-01 用户短视频表
CREATE TABLE `friendship`.`user_video`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `content` varchar(140) NULL COMMENT '文案',
  `user_id` bigint(20) NULL COMMENT '用户id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  `status` tinyint(1) NULL COMMENT '状态',
  `video_cover` varchar(255) NULL COMMENT '视频封面',
  `video_url` varchar(255) NULL COMMENT '视频地址',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 11000011;

-- 2020-06-01 话题标签表
CREATE TABLE `friendship`.`topic_dictionary_info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `topic_id` bigint(20) NULL COMMENT '话题id',
  `tag_id` bigint(20) NULL COMMENT '标签id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10312341 COMMENT = '话题关联字典标签信息表';


-- 2020-06-02 黑名单
CREATE TABLE `friendship`.`user_black`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) NULL COMMENT '用户id',
  `target_user_id` bigint(20) NULL COMMENT '目标用户id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10001000;

-- 2020-06-04 小视频推荐表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `video_recommand`;
CREATE TABLE `video_recommand` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `video_id` bigint(20) DEFAULT NULL COMMENT '视频id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `video_url` varchar(256) DEFAULT NULL COMMENT '视频url',
  `video_cover_image` varchar(256) DEFAULT NULL COMMENT '视频封面',
  `logic_delete` tinyint(2) DEFAULT '0' COMMENT '是否逻辑删除:0:不删除:1:删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11001419 DEFAULT CHARSET=utf8mb4 COMMENT='小视频推荐表';

SET FOREIGN_KEY_CHECKS = 1;

-- 2020-0604 视频点赞
CREATE TABLE `friendship`.`video_like`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) NULL COMMENT '用户id',
  `video_id` bigint(20) NULL COMMENT '视频id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
);

ALTER TABLE `friendship`.`video_like`
ADD INDEX `idx_userid_videoId`(`user_id`, `video_id`) COMMENT 'userId和videoid建立联合索引';


-- 2020-06-07 视频弹幕
CREATE TABLE `friendship`.`video_barrage`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
  `video_id` bigint(20) NULL DEFAULT NULL COMMENT '视频id',
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '内容',
  `create_time` timestamp(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `video_id`(`video_id`) USING BTREE COMMENT '为视频id添加索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1008829 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '视频弹幕/评论表' ROW_FORMAT = Dynamic;


-- 2020-06-12 用户短视频统计
CREATE TABLE `friendship`.`user_video_statistics`  (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `video_id` bigint(20) NULL COMMENT '短视频id',
  `like_num` int(8) NOT NULL DEFAULT 0 COMMENT '点赞次数',
  `reward_num` int(8) NOT NULL DEFAULT 0 COMMENT '打赏次数',
  `comment_num` int(8) NOT NULL DEFAULT 0 COMMENT '评论次数',
  PRIMARY KEY (`id`)
);

ALTER TABLE `friendship`.`user_video_statistics` COMMENT = '短视频统计信息';

ALTER TABLE `friendship`.`user_video_statistics`
MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键' FIRST;

-- 20200617
CREATE TABLE `friendship`.`topic_user_black_list`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `topic_id` bigint(20) NULL COMMENT '话题id',
  `target_user_id` bigint(20) NULL COMMENT '目标用户id',
  `create_time` timestamp(0) NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10001110;