schema.md

建表语句

create database video_management;
create table user(
    id bigint unsigned primary key AUTO_INCREMENT,
    username varchar(50) not null unique ,
    password varchar(100) comment "密码哈希",
    phone varchar(18) not null unique ,
    image varchar(255) comment "用户头像url",
    role tinyint unsigned default 0 comment "0-普通用户 1-管理员",
    create_time timestamp default current_timestamp,
    update_time timestamp default current_timestamp on update current_timestamp
);
create table video(
    id bigint unsigned primary key AUTO_INCREMENT,
    video varchar(2048) unique,
    video_cover varchar(2048) comment "视频封面",
    user_id bigint unsigned not null,
    video_name varchar(255) not null,
    video_duration int unsigned not null default 0,
    liked_count bigint unsigned default 0,
    comments_count bigint unsigned default 0,
    status tinyint unsigned default 0 comment "0-审核中，1-已发布，2-已下架",
    play_count bigint unsigned default 0,
    collection_count bigint unsigned default 0,
    forward_count bigint unsigned default 0,
    video_size bigint unsigned not null default 0 comment "视频大小，单位为字节",
    create_time timestamp default current_timestamp,
    update_time timestamp default current_timestamp on update current_timestamp
);
create table comment(
    id bigint unsigned primary key AUTO_INCREMENT,
    video_id bigint unsigned not null,
    user_id bigint unsigned not null,
    content varchar(500) not null,
    parent_id bigint unsigned default null,
    liked_count bigint unsigned default 0,
    create_time timestamp default current_timestamp,
    index idx_video (video_id),
    index idx_user (user_id)
);
create table collection(
    id bigint unsigned primary key AUTO_INCREMENT,
    video_id bigint unsigned not null,
    user_id bigint unsigned not null,
    create_time timestamp default current_timestamp,
    unique key uk_video_user (video_id, user_id)
);
create table follow(
    id bigint unsigned primary key AUTO_INCREMENT,
    user_id bigint unsigned not null,
    target_user_id bigint unsigned not null,
    create_time timestamp default current_timestamp,
    unique key uk_user_target (user_id, target_user_id),
    key idx_target_user (target_user_id)
);
create table coupon(
    id bigint unsigned primary key AUTO_INCREMENT,
    name varchar(100) not null,
    discount decimal(10,2) not null,
    discount_type varchar(20) not null,
    min_amount decimal(10,2) default 0.00,
    total_stock int not null default 0,
    remaining_stock int not null default 0,
    video_id bigint default null,
    expire_time timestamp not null,
    create_time timestamp default current_timestamp,
    key idx_video (video_id)
);
create table user_coupon(
    id bigint unsigned primary key AUTO_INCREMENT,
    user_id bigint unsigned not null,
    coupon_id bigint unsigned not null,
    status tinyint not null default 0 comment "0-未使用, 1-已使用, 2-已过期",
    grab_time timestamp default current_timestamp,
    use_time timestamp default null,
    key idx_user_status (user_id, status),
    key idx_coupon (coupon_id)
);