343 lines
16 KiB
SQL
343 lines
16 KiB
SQL
create table check_in_records
|
||
(
|
||
id bigint unsigned auto_increment comment '自增主键ID'
|
||
primary key,
|
||
user_id bigint unsigned not null comment '用户ID',
|
||
check_in_date date not null comment '签到日期',
|
||
created_at timestamp default CURRENT_TIMESTAMP not null comment '记录创建时间',
|
||
constraint uk_user_date
|
||
unique (user_id, check_in_date) comment '用户和日期的唯一索引，防止重复签到'
|
||
)
|
||
comment '用户签到记录表' collate = utf8mb4_unicode_ci;
|
||
|
||
create table goods_list
|
||
(
|
||
id int auto_increment comment '商品ID'
|
||
primary key,
|
||
name varchar(100) not null comment '商品名称',
|
||
description text null comment '商品描述',
|
||
points int not null comment '所需积分',
|
||
stock int default 0 not null comment '库存数量',
|
||
image_url varchar(255) null comment '商品图片URL',
|
||
status tinyint default 1 not null comment '状态：1-上架，0-下架',
|
||
created_at datetime default CURRENT_TIMESTAMP not null comment '创建时间',
|
||
updated_at datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
|
||
);
|
||
|
||
create table host
|
||
(
|
||
id smallint auto_increment
|
||
primary key,
|
||
name varchar(64) not null,
|
||
alias varchar(32) default '' not null,
|
||
port int default 5921 not null,
|
||
remark varchar(100) default '' not null
|
||
)
|
||
charset = utf8mb3;
|
||
|
||
create table login_log
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
username varchar(32) not null,
|
||
ip varchar(15) not null,
|
||
created datetime not null
|
||
)
|
||
charset = utf8mb3;
|
||
|
||
create table setting
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
code varchar(32) not null,
|
||
`key` varchar(64) not null,
|
||
value varchar(4096) default '' not null
|
||
)
|
||
charset = utf8mb3;
|
||
|
||
create table user_info
|
||
(
|
||
uid varchar(40) not null
|
||
primary key,
|
||
telephone varchar(255) not null,
|
||
password varchar(30) null,
|
||
avatar_url varchar(255) default 'https://images.pexels.com/photos/33628135/pexels-photo-33628135.jpeg' null,
|
||
gender int not null,
|
||
`birthdate-date` datetime null,
|
||
createdtime datetime not null,
|
||
updatedtime datetime not null,
|
||
bio varchar(255) null,
|
||
username varchar(30) not null,
|
||
total_points int default 0 not null
|
||
);
|
||
|
||
create table article_list
|
||
(
|
||
articleId bigint auto_increment comment '文评投票唯一 ID（主键）'
|
||
primary key,
|
||
title varchar(255) not null comment '文评投票标题',
|
||
vote_type varchar(50) not null comment '投票类型',
|
||
total_voters_num int not null comment '投票总人数',
|
||
end_time datetime not null comment '投票结束时间',
|
||
is_ended tinyint(1) not null comment '是否已结束（1 = 结束，0 = 未结束）',
|
||
publish_user_id varchar(40) not null comment '发表用户 ID（关联用户表）',
|
||
create_time datetime not null comment '投票创建时间',
|
||
constraint article_list_user_info_uid_fk
|
||
foreign key (publish_user_id) references user_info (uid)
|
||
on delete cascade
|
||
);
|
||
|
||
create table article_comments
|
||
(
|
||
id int auto_increment comment '评论唯一ID'
|
||
primary key,
|
||
articleId bigint not null comment '对应的文评ID',
|
||
user_id varchar(40) not null comment '对应的用户ID',
|
||
parent_id int null comment '关联本表的 id，用于回复功能。 如果为 0 或 NULL，则为顶级评论。',
|
||
content text not null comment '具体评论',
|
||
created_time datetime not null comment '评论创建时间',
|
||
update_time datetime not null comment '更新时间',
|
||
likes_count int default 0 not null comment '点赞数量',
|
||
status tinyint default 1 not null comment '1:正常，2:已删除',
|
||
constraint article_comments_article_comments_id_fk
|
||
foreign key (parent_id) references article_comments (id),
|
||
constraint article_comments_article_list_articleId_fk
|
||
foreign key (articleId) references article_list (articleId)
|
||
on delete cascade,
|
||
constraint article_comments_user_info_uid_fk
|
||
foreign key (user_id) references user_info (uid)
|
||
on delete cascade
|
||
);
|
||
|
||
create table article_options
|
||
(
|
||
id bigint auto_increment comment '选项唯一 ID'
|
||
primary key,
|
||
vote_article_id bigint not null comment '关联文评投票主表 ID',
|
||
option_content varchar(255) not null comment '选项内容',
|
||
option_votes_num int not null comment '该选项得票数',
|
||
sort_order int null comment '选项的排序',
|
||
constraint article_options_article_list_articleId_fk
|
||
foreign key (vote_article_id) references article_list (articleId)
|
||
on delete cascade
|
||
);
|
||
|
||
create index idx_article_options_vote_article_id_sort_order
|
||
on article_options (vote_article_id, sort_order);
|
||
|
||
create table feed_back
|
||
(
|
||
id bigint auto_increment comment '反馈消息ID'
|
||
primary key,
|
||
user_id varchar(40) not null comment '反馈的用户ID',
|
||
feed_back_type tinyint not null comment '反馈类型;1:功能建议，2:问题反馈，3:投诉举报，4.其他',
|
||
feedback_content text not null comment '反馈内容',
|
||
feedback_status tinyint null comment '反馈进度；0:待处理，1:正在处理，2:处理完成',
|
||
handler_id varchar(40) not null comment '处理员ID',
|
||
create_time datetime not null comment '反馈时间',
|
||
handle_time datetime not null comment '处理完成时间',
|
||
handle_remark text null comment '处理备注',
|
||
is_delete tinyint default 0 not null comment '软删除',
|
||
constraint feed_back_user_info_uid_fk
|
||
foreign key (user_id) references user_info (uid)
|
||
);
|
||
|
||
create table orders_list
|
||
(
|
||
id int auto_increment comment '订单ID'
|
||
primary key,
|
||
user_id varchar(40) not null comment '用户ID，关联用户表',
|
||
total_points int not null comment '订单总消耗积分',
|
||
status tinyint default 0 not null comment '订单状态：0-待处理，1-已完成，2-已取消，3-已发货',
|
||
created_at datetime default CURRENT_TIMESTAMP not null comment '创建时间',
|
||
updated_at datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
|
||
constraint orders_list_user_info_uid_fk
|
||
foreign key (user_id) references user_info (uid)
|
||
on delete cascade
|
||
);
|
||
|
||
create table order_consumption
|
||
(
|
||
id int auto_increment comment '记录ID'
|
||
primary key,
|
||
user_id varchar(40) not null comment '用户ID',
|
||
order_id int not null comment '关联的订单ID',
|
||
points int not null comment '消费的积分数量',
|
||
created_at datetime default CURRENT_TIMESTAMP not null comment '消费时间',
|
||
constraint order_consumption_ibfk_1
|
||
foreign key (user_id) references user_info (uid)
|
||
on delete cascade,
|
||
constraint order_consumption_ibfk_2
|
||
foreign key (order_id) references orders_list (id)
|
||
on delete cascade
|
||
);
|
||
|
||
create index order_id
|
||
on order_consumption (order_id);
|
||
|
||
create index user_id
|
||
on order_consumption (user_id);
|
||
|
||
create table order_details
|
||
(
|
||
id int auto_increment comment '明细ID'
|
||
primary key,
|
||
order_id int not null comment '订单ID，关联订单表',
|
||
goods_id int not null comment '商品ID，关联商品表',
|
||
goods_name varchar(100) not null comment '商品名称（冗余存储，避免商品名称修改后订单显示异常）',
|
||
points int not null comment '购买时的积分（冗余存储）',
|
||
quantity int default 1 not null comment '购买数量',
|
||
created_at datetime default CURRENT_TIMESTAMP not null comment '创建时间',
|
||
constraint order_details_ibfk_1
|
||
foreign key (order_id) references orders_list (id)
|
||
on delete cascade,
|
||
constraint order_details_ibfk_2
|
||
foreign key (goods_id) references goods_list (id)
|
||
);
|
||
|
||
create index goods_id
|
||
on order_details (goods_id);
|
||
|
||
create index order_id
|
||
on order_details (order_id);
|
||
|
||
create table referral_codes
|
||
(
|
||
id int auto_increment comment '内推码Id'
|
||
primary key,
|
||
code int not null comment '内推码',
|
||
created_by varchar(40) not null comment '创建者ID',
|
||
valid_until datetime not null comment '有效期',
|
||
uses_num int not null comment '使用次数',
|
||
create_time datetime not null comment '创建时间',
|
||
constraint referral_codes_pk_2
|
||
unique (code),
|
||
constraint referral_codes_user_info_uid_fk
|
||
foreign key (created_by) references user_info (uid)
|
||
)
|
||
comment '内推码表';
|
||
|
||
create table lottery_campaigns
|
||
(
|
||
id int auto_increment comment '活动id'
|
||
primary key,
|
||
name varchar(255) not null comment '活动名称',
|
||
description text null comment '活动描述',
|
||
referral_code_id int not null comment '内推码id',
|
||
start_time datetime not null comment '活动开始时间',
|
||
end_time datetime not null comment '活动结束时间',
|
||
is_ended int not null comment '活动是否结束，1:结束，2：未结束',
|
||
created_time datetime not null comment '创建时间',
|
||
update_time datetime not null comment '更新时间',
|
||
max_lottery_per_user int default 1 not null comment '每个用户的最大抽奖次数',
|
||
constraint lottery_campaigns_referral_codes_id_fk
|
||
foreign key (referral_code_id) references referral_codes (id)
|
||
)
|
||
comment '抽奖活动表';
|
||
|
||
create table lottery_prizes
|
||
(
|
||
id int auto_increment comment '奖项id'
|
||
primary key,
|
||
campaign_id int not null comment '关联活动ID',
|
||
name varchar(255) not null comment '奖项名称',
|
||
image_url varchar(255) not null comment '奖项图片URL',
|
||
probability float not null comment '奖项中奖概率',
|
||
max_wins int not null comment '最大中奖次数',
|
||
current_wins int default 0 not null comment '当前中奖人数',
|
||
create_time datetime not null comment '创建时间',
|
||
update_time datetime not null comment '更新时间',
|
||
constraint lottery_prizes_lottery_campaigns_id_fk
|
||
foreign key (campaign_id) references lottery_campaigns (id)
|
||
)
|
||
comment '抽奖奖项表';
|
||
|
||
create table lottery_records
|
||
(
|
||
id int auto_increment comment '记录ID'
|
||
primary key,
|
||
user_id varchar(40) not null comment '抽奖用户ID',
|
||
campaign_id int not null comment '关联活动ID',
|
||
referral_code_id int not null,
|
||
prize_id int not null comment '中奖选项ID',
|
||
is_win int not null comment '是否中奖，1:中奖，2:未中奖',
|
||
create_time datetime not null comment '抽奖时间',
|
||
constraint lottery_records_lottery_campaigns_id_fk
|
||
foreign key (campaign_id) references lottery_campaigns (id),
|
||
constraint lottery_records_lottery_prizes_id_fk
|
||
foreign key (prize_id) references lottery_prizes (id),
|
||
constraint lottery_records_referral_codes_id_fk
|
||
foreign key (referral_code_id) references referral_codes (id)
|
||
)
|
||
comment '抽奖记录表';
|
||
|
||
create table referral_code_bindings
|
||
(
|
||
id int auto_increment comment '关联记录ID'
|
||
primary key,
|
||
code_id int not null comment '关联内推码ID',
|
||
entity_type varchar(50) not null comment '业务类型',
|
||
entity_id int not null comment '关联实体ID',
|
||
create_time datetime not null comment '创建时间',
|
||
constraint referral_code_bindings_referral_codes_id_fk
|
||
foreign key (code_id) references referral_codes (id)
|
||
)
|
||
comment '内推码业务关联表';
|
||
|
||
create table user_msg
|
||
(
|
||
id bigint auto_increment comment '通知消息ID'
|
||
primary key,
|
||
sender_id varchar(40) not null comment '消息发送者的ID',
|
||
receiver_id varchar(40) not null comment '接收者的ID',
|
||
msg_type varchar(40) not null,
|
||
status tinyint not null comment '通知状态；1正常显示，2：软删除',
|
||
sequence bigint unsigned not null comment '特定用户的消息序列',
|
||
created_at datetime not null,
|
||
content text null comment '消息内容',
|
||
`is-read` tinyint default 0 not null comment '是否已读;1:已读，0:未读',
|
||
target varchar(255) not null comment '跳转目标',
|
||
constraint user_msg_user_info_uid_fk
|
||
foreign key (sender_id) references user_info (uid),
|
||
constraint user_msg_user_info_uid_fk_2
|
||
foreign key (receiver_id) references user_info (uid)
|
||
);
|
||
|
||
create table user_points
|
||
(
|
||
id int auto_increment comment '添加积分操作的id'
|
||
primary key,
|
||
user_id varchar(40) not null comment '添加积分的用户',
|
||
points_change int not null comment '添加的积分',
|
||
source varchar(40) null comment '积分来源',
|
||
create_time datetime not null comment '创建时间',
|
||
constraint user_points_user_info_uid_fk
|
||
foreign key (user_id) references user_info (uid)
|
||
on delete cascade
|
||
);
|
||
|
||
create table user_votes
|
||
(
|
||
id bigint auto_increment comment '投票记录唯一 ID（主键）'
|
||
primary key,
|
||
vote_article_id bigint not null comment '关联文评投票主表 ID',
|
||
user_id varchar(40) null comment '投票用户 ID（关联用户表）',
|
||
option_id bigint not null comment '关联投票选项表 ID（选了哪个选项） ',
|
||
vote_time datetime not null comment '投票时间',
|
||
constraint user_votes_pk
|
||
unique (user_id, option_id, vote_article_id),
|
||
constraint user_votes_article_list_articleId_fk
|
||
foreign key (vote_article_id) references article_list (articleId)
|
||
on delete cascade
|
||
);
|
||
|
||
create index idx_option_id
|
||
on user_votes (option_id);
|
||
|
||
create index idx_user_id
|
||
on user_votes (user_id);
|
||
|
||
create index idx_vote_article_id
|
||
on user_votes (vote_article_id);
|
||
|