-- 创建表 userinfo
CREATE TABLE userinfo (
userEmail varchar(100) NOT NULL,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
avatar varchar(256) NOT NULL,
userId int NOT NULL AUTO_INCREMENT,
describeBySelf varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
registrationTime varchar(20) NOT NULL,
lover varchar(100) NOT NULL,
score int DEFAULT '1000' COMMENT '积分',
PRIMARY KEY (userId)
);
-- 创建表 tasklist
CREATE TABLE tasklist (
taskId int NOT NULL AUTO_INCREMENT COMMENT '任务ID',
taskName varchar(100) NOT NULL COMMENT '任务名',
taskDetail varchar(1000) NOT NULL COMMENT '任务详情',
taskImage varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '发布任务图片',
taskReward varchar(1000) NOT NULL COMMENT '任务奖励',
taskScore int NOT NULL DEFAULT '0' COMMENT '任务分数',
creationTime varchar(20) NOT NULL COMMENT '任务创建时间',
publisherName varchar(100) NOT NULL,
publisherEmail varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '任务发布人邮箱',
receiverEmail varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' COMMENT '任务接收人邮箱',
acceptanceTime varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' COMMENT '任务接受时间',
completionTime varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' COMMENT '任务完成时间',
completeRemarks varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '' COMMENT '任务完成备注',
taskStatus char(10) NOT NULL DEFAULT '未开始',
PRIMARY KEY (taskId)
);
-- 创建表 whisper_list
CREATE TABLE whisper_list (
whisperId int NOT NULL AUTO_INCREMENT COMMENT '留言ID',
title varchar(100) NOT NULL COMMENT '留言标题',
content varchar(10000) NOT NULL COMMENT '留言内容',
creationTime varchar(20) NOT NULL COMMENT '创建时间',
publisherEmail varchar(50) NOT NULL COMMENT '发布者邮箱',
PRIMARY KEY (whisperId)
);
-- 创建表 gift_list
CREATE TABLE gift_list (
publisherEmail varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
giftImg varchar(500) NOT NULL COMMENT '图片地址',
giftName varchar(100) NOT NULL COMMENT 'gift名称',
giftId int NOT NULL AUTO_INCREMENT COMMENT 'id',
giftDetail varchar(100) NOT NULL COMMENT 'gift详情',
needScore int NOT NULL DEFAULT '0' COMMENT '兑换需要的分数',
remained int NOT NULL DEFAULT '0' COMMENT '还留有的数量',
redeemed int DEFAULT '0' COMMENT '被兑换的数量',
used int DEFAULT '0' COMMENT '被使用的数量',
isShow tinyint(1) DEFAULT '0' COMMENT '货架是否展示',
PRIMARY KEY (giftId)
);
-- 创建表 favourite_list
CREATE TABLE favourite_list (
favId int NOT NULL AUTO_INCREMENT COMMENT '收藏记录ID',
userEmail varchar(20) NOT NULL COMMENT '收藏者',
collectionId int NOT NULL COMMENT '收藏目标字段id',
collectionType varchar(10) NOT NULL COMMENT '收藏类型',
creationTime varchar(20) NOT NULL,
PRIMARY KEY (favId)
);