474 lines
24 KiB
SQL
474 lines
24 KiB
SQL
-- 聊天消息表
|
||
create database if not exists cpzs;
|
||
|
||
-- 切换库
|
||
use cpzs;
|
||
|
||
-- 创建历史数据表
|
||
CREATE TABLE IF NOT EXISTS `history_all` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
frequencyPercentage FLOAT NULL COMMENT '出现频率百分比',
|
||
averageInterval FLOAT NULL COMMENT '平均隐现期（次）',
|
||
maxHiddenInterval INT NULL COMMENT '最长隐现期（次）',
|
||
maxConsecutiveCount INT NULL COMMENT '最多连出期（次）',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '全部历史数据表';
|
||
|
||
|
||
-- 创建最近100期数据表
|
||
CREATE TABLE IF NOT EXISTS `history_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
averageInterval FLOAT NULL COMMENT '平均隐现期（次）',
|
||
nowInterval INT NULL COMMENT '当前隐现期（次）',
|
||
maxConsecutiveCount INT NULL COMMENT '最多连出期（次）',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '最近100期数据表';
|
||
|
||
-- 创建历史数据排行表
|
||
CREATE TABLE IF NOT EXISTS `history_top` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
no INT NULL COMMENT '排行',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '历史数据排行表';
|
||
|
||
-- 创建100期数据排行表
|
||
CREATE TABLE IF NOT EXISTS `history_top_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
no INT NULL COMMENT '排行',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '100期数据排行表';
|
||
|
||
|
||
-- 创建蓝球历史数据表
|
||
CREATE TABLE IF NOT EXISTS `blue_history_all` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
frequencyPercentage FLOAT NULL COMMENT '出现频率百分比',
|
||
averageInterval FLOAT NULL COMMENT '平均隐现期（次）',
|
||
maxHiddenInterval INT NULL COMMENT '最长隐现期（次）',
|
||
maxConsecutiveCount INT NULL COMMENT '最多连出期（次）',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '蓝球全部历史数据表';
|
||
|
||
|
||
-- 创建蓝球最近100期数据表
|
||
CREATE TABLE IF NOT EXISTS `blue_history_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
averageInterval FLOAT NULL COMMENT '平均隐现期（次）',
|
||
nowInterval INT NULL COMMENT '当前隐现期（次）',
|
||
maxConsecutiveCount INT NULL COMMENT '最多连出期（次）',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '蓝球最近100期数据表';
|
||
|
||
-- 创建蓝球历史数据排行表
|
||
CREATE TABLE IF NOT EXISTS `blue_history_top` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
no INT NULL COMMENT '排行',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '蓝球历史数据排行表';
|
||
|
||
-- 创建蓝球100期数据排行表
|
||
CREATE TABLE IF NOT EXISTS `blue_history_top_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
no INT NULL COMMENT '排行',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
pointCoefficient FLOAT NULL COMMENT '点系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '蓝球100期数据排行表';
|
||
|
||
-- 创建t3表（红球组红球的线系数）
|
||
CREATE TABLE IF NOT EXISTS `t3` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
lineCoefficient FLOAT NULL COMMENT '线系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't3表（红球组红球的线系数）';
|
||
|
||
|
||
-- 创建t4表（蓝球组红球的线系数）
|
||
CREATE TABLE IF NOT EXISTS `t4` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
lineCoefficient FLOAT NULL COMMENT '线系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't4表（蓝球组红球的线系数）';
|
||
|
||
-- 创建t5表（蓝球组蓝球的线系数）
|
||
CREATE TABLE IF NOT EXISTS `t5` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
lineCoefficient FLOAT NULL COMMENT '线系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't5表（蓝球组蓝球的线系数）';
|
||
|
||
-- 创建t6表（红球组蓝球的线系数）
|
||
CREATE TABLE IF NOT EXISTS `t6` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
lineCoefficient FLOAT NULL COMMENT '线系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't6表（红球组蓝球的线系数）';
|
||
|
||
-- 创建t7表（红球组红球的面系数）
|
||
CREATE TABLE IF NOT EXISTS `t7` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
faceCoefficient FLOAT NULL COMMENT '面系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't7表（红球组红球的面系数）';
|
||
|
||
-- 创建t8表（红球组蓝球的面系数）
|
||
CREATE TABLE IF NOT EXISTS `t8` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
faceCoefficient FLOAT NULL COMMENT '面系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't8表（红球组蓝球的面系数）';
|
||
|
||
-- 创建t11表（蓝球组红球的面系数）
|
||
CREATE TABLE IF NOT EXISTS `t11` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
faceCoefficient FLOAT NULL COMMENT '面系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 't11表（蓝球组红球的面系数）';
|
||
|
||
CREATE TABLE IF NOT EXISTS `lottery_draws` (
|
||
`drawId` BIGINT NOT NULL COMMENT '开奖期号' PRIMARY KEY,
|
||
`drawDate` DATE NOT NULL COMMENT '开奖日期',
|
||
`redBall1` INT NOT NULL COMMENT '红1',
|
||
`redBall2` INT NOT NULL COMMENT '红2',
|
||
`redBall3` INT NOT NULL COMMENT '红3',
|
||
`redBall4` INT NOT NULL COMMENT '红4',
|
||
`redBall5` INT NOT NULL COMMENT '红5',
|
||
`redBall6` INT NOT NULL COMMENT '红6',
|
||
`blueBall` INT NOT NULL COMMENT '蓝球',
|
||
`prizePool` BIGINT NULL COMMENT '奖池'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '彩票开奖信息表';
|
||
|
||
-- 用户表
|
||
create table if not exists user
|
||
(
|
||
id bigint auto_increment comment 'id' primary key,
|
||
userName varchar(256) null comment '用户昵称',
|
||
userAccount varchar(256) not null comment '账号',
|
||
phone varchar(11) null comment '手机号',
|
||
userAvatar varchar(1024) null comment '用户头像',
|
||
gender tinyint null comment '性别',
|
||
userRole varchar(256) default 'user' not null comment '用户角色：user / admin',
|
||
userPassword varchar(512) not null comment '密码',
|
||
isVip int default 0 not null comment '是否会员：0-非会员，1-会员',
|
||
vipExpire datetime null comment '会员到期时间',
|
||
vipType varchar(50) default '体验会员' null comment '套餐类别（体验会员/月度会员/年度会员）',
|
||
location varchar(100) null comment '所在省市',
|
||
preference varchar(100) null comment '彩票偏好（双色球/大乐透等）',
|
||
channel varchar(100) null comment '获客渠道',
|
||
# vipNum int not null comment '会员编号',
|
||
`status` tinyint DEFAULT '0' COMMENT '状态0正常1不正常',
|
||
createTime datetime default CURRENT_TIMESTAMP not null comment '创建时间',
|
||
updateTime datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
|
||
isDelete tinyint default 0 not null comment '是否删除',
|
||
constraint uni_userAccount
|
||
unique (userAccount)
|
||
) comment '用户';
|
||
|
||
CREATE TABLE IF NOT EXISTS `predict_record` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`userId` BIGINT NOT NULL COMMENT '用户ID',
|
||
`drawId` BIGINT NOT NULL COMMENT '开奖期号' ,
|
||
`drawDate` DATE NOT NULL COMMENT '开奖日期',
|
||
`redBall1` INT NOT NULL COMMENT '红1',
|
||
`redBall2` INT NOT NULL COMMENT '红2',
|
||
`redBall3` INT NOT NULL COMMENT '红3',
|
||
`redBall4` INT NOT NULL COMMENT '红4',
|
||
`redBall5` INT NOT NULL COMMENT '红5',
|
||
`redBall6` INT NOT NULL COMMENT '红6',
|
||
`blueBall` INT NOT NULL COMMENT '蓝球',
|
||
`type` VARCHAR(100) NOT NULL COMMENT '预测类型(双色球/组六/大乐透)',
|
||
`predictStatus` VARCHAR(100) default '待开奖' NOT NULL COMMENT '预测状态(待开奖/已开奖)',
|
||
`predictResult` VARCHAR(100) default '待开奖' NOT NULL COMMENT '预测结果(未中奖/三等奖/二等奖/一等奖)',
|
||
`predictTime` datetime default CURRENT_TIMESTAMP not null comment '预测时间',
|
||
`bonus` BIGINT default 0 NOT NULL COMMENT '奖金'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '双色球推测记录表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `vip_code` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`code` varchar(36) NOT NULL COMMENT '会员码',
|
||
`vipExpireTime` int not null COMMENT '会员有效月数(1/12)',
|
||
`vipNumber` int not NULL COMMENT '会员编号',
|
||
`isUse` int NOT NULL COMMENT '是否使用',
|
||
createdUserId bigint null comment '创建人',
|
||
createdUserName varchar(36) null comment '创建人名称',
|
||
usedUserId bigint null comment '使用人',
|
||
usedUserName varchar(36) null comment '使用人名称',
|
||
`createTime` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updateTime` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员码表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `vip_exchange_record` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`userId` bigint NOT NULL COMMENT '用户ID',
|
||
`type` varchar(36) NOT NULL COMMENT '月度会员/年度会员',
|
||
`exchangeMode` int not null COMMENT '兑换方式',
|
||
`orderNo` bigint not NULL COMMENT '订单编号',
|
||
`orderAmount` int not NULL COMMENT '订单金额',
|
||
`isUse` int NOT NULL COMMENT '是否兑换（未兑换/已兑换）',
|
||
`exchangeTime` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '兑换时间',
|
||
`updateTime` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员兑换表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `operation_history` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`userId` BIGINT NOT NULL COMMENT '操作用户ID',
|
||
`operationType` VARCHAR(50) NOT NULL COMMENT '操作类型（批量生成会员码/获取可用会员码/Excel导入等）',
|
||
`operationModule` INTEGER NOT NULL COMMENT '操作模块（0-会员码管理/1-Excel导入管理等）',
|
||
`operationResult` VARCHAR(20) NOT NULL COMMENT '操作结果（成功/失败）',
|
||
`resultMessage` TEXT COMMENT '结果消息',
|
||
`operationTime` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '操作时间',
|
||
`updateTime` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作历史记录表';
|
||
|
||
|
||
CREATE TABLE IF NOT EXISTS `chat_message`
|
||
(
|
||
id BIGINT AUTO_INCREMENT COMMENT 'id' PRIMARY KEY,
|
||
`conversationId` varchar(64) NULL COMMENT '会话ID',
|
||
`studentId` varchar(64) NULL COMMENT '用户ID，关联用户表',
|
||
`messageType` varchar(64) NULL COMMENT '消息类型（如： 用户提问、AI回答）',
|
||
`content` varchar(1024) NULL COMMENT '消息内容',
|
||
`createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`isDelete` tinyint NOT NULL DEFAULT 0 COMMENT '是否删除 0-未删除 1-已删除'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT ='聊天消息表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_draw_record` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`drawId` VARCHAR(50) NOT NULL COMMENT '开奖期号',
|
||
`drawDate` DATE NOT NULL COMMENT '开奖日期',
|
||
`frontBall1` INT NOT NULL COMMENT '前区1',
|
||
`frontBall2` INT NOT NULL COMMENT '前区2',
|
||
`frontBall3` INT NOT NULL COMMENT '前区3',
|
||
`frontBall4` INT NOT NULL COMMENT '前区4',
|
||
`frontBall5` INT NOT NULL COMMENT '前区5',
|
||
`backBall1` INT NOT NULL COMMENT '后区1',
|
||
`backBall2` INT NOT NULL COMMENT '后区2',
|
||
`prizePool` BIGINT NULL COMMENT '奖池'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透开奖信息表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_frontend_history_all` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NOT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
frequencyPercentage FLOAT NULL COMMENT '出现频率%',
|
||
averageHiddenAppear INT NULL COMMENT '平均隐现期(次)',
|
||
maxHiddenInterval INT NULL COMMENT '最长隐现期(次)',
|
||
maxConsecutive INT NULL COMMENT '最多连出期(次)',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透前区全部历史数据表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_frontend_history_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NOT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
averageHiddenAppear FLOAT NULL COMMENT '平均隐现期(次)',
|
||
currentHiddenInterval INT NULL COMMENT '当前隐现期',
|
||
maxConsecutive INT NULL COMMENT '最多连出期(次)',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透前区最近100期数据表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_frontend_history_top` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ranking INT NULL COMMENT '排位',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透前区历史数据排行表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_frontend_history_top_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ranking INT NULL COMMENT '排位',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透前区百期数据排行表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_backend_history_all` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NOT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
frequencyPercentage FLOAT NULL COMMENT '出现频率%',
|
||
averageHiddenAppear INT NULL COMMENT '平均隐现期(次)',
|
||
maxHiddenInterval INT NULL COMMENT '最长隐现期(次)',
|
||
maxConsecutive INT NULL COMMENT '最多连出期(次)',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透后区全部历史数据表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_backend_history_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ballNumber INT NOT NULL COMMENT '球号',
|
||
frequencyCount INT NULL COMMENT '出现频次',
|
||
averageHiddenAppear FLOAT NULL COMMENT '平均隐现期(次)',
|
||
currentHiddenInterval INT NULL COMMENT '当前隐现期',
|
||
maxConsecutive INT NULL COMMENT '最多连出期(次)',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透后区最近100期数据表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_backend_history_top` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ranking INT NULL COMMENT '排位',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透后区历史数据排行表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_backend_history_top_100` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
ranking INT NULL COMMENT '排位',
|
||
ballNumber INT NULL COMMENT '球号',
|
||
activeCoefficient FLOAT NULL COMMENT '活跃系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透后区百期数据排行表';
|
||
|
||
|
||
CREATE TABLE IF NOT EXISTS `d5` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd5表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d6` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd6表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d7` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd7表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d8` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd8表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d9` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd9表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d10` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd10表';
|
||
CREATE TABLE IF NOT EXISTS `d11` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd11表';
|
||
|
||
CREATE TABLE IF NOT EXISTS `d12` (
|
||
id BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
masterBallNumber INT NULL COMMENT '主球',
|
||
slaveBallNumber INT NULL COMMENT '从球',
|
||
coefficient FLOAT NULL COMMENT '系数'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = 'd12表';
|
||
|
||
|
||
CREATE TABLE IF NOT EXISTS `dlt_predict_record` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`userId` BIGINT NOT NULL COMMENT '用户ID',
|
||
`drawId` BIGINT NOT NULL COMMENT '开奖期号' ,
|
||
`drawDate` DATE NOT NULL COMMENT '开奖日期',
|
||
`frontendBall1` INT NOT NULL COMMENT '前区1',
|
||
`frontendBall2` INT NOT NULL COMMENT '前区2',
|
||
`frontendBall3` INT NOT NULL COMMENT '前区3',
|
||
`frontendBall4` INT NOT NULL COMMENT '前区4',
|
||
`frontendBall5` INT NOT NULL COMMENT '前区5',
|
||
`backendBall1` INT NOT NULL COMMENT '后区1',
|
||
`backendBall2` INT NOT NULL COMMENT '后区2',
|
||
`predictStatus` VARCHAR(100) default '待开奖' NOT NULL COMMENT '预测状态(待开奖/已开奖)',
|
||
`predictResult` VARCHAR(100) default '待开奖' NOT NULL COMMENT '预测结果(未中奖/三等奖/二等奖/一等奖)',
|
||
`predictTime` datetime default CURRENT_TIMESTAMP not null comment '预测时间',
|
||
`bonus` BIGINT default 0 NOT NULL COMMENT '奖金'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '大乐透推测记录表';
|
||
|
||
|
||
-- 公告管理表
|
||
CREATE TABLE IF NOT EXISTS `announcement` (
|
||
`id` BIGINT AUTO_INCREMENT COMMENT '唯一标识符' PRIMARY KEY,
|
||
`title` VARCHAR(200) NOT NULL COMMENT '公告标题',
|
||
`content` TEXT NOT NULL COMMENT '公告详情内容',
|
||
`publisherId` BIGINT NOT NULL COMMENT '发布人ID',
|
||
`publisherName` VARCHAR(256) NOT NULL COMMENT '发布人名称',
|
||
`status` TINYINT DEFAULT 1 NOT NULL COMMENT '公告状态：0-草稿，1-已发布，2-已下架',
|
||
`priority` TINYINT DEFAULT 0 NOT NULL COMMENT '优先级：0-普通，1-置顶',
|
||
`viewCount` INT DEFAULT 0 NOT NULL COMMENT '浏览次数',
|
||
`createTime` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updateTime` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`isDelete` TINYINT DEFAULT 0 NOT NULL COMMENT '是否删除：0-未删除，1-已删除'
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4 COMMENT = '公告管理表';
|
||
|
||
-- 为已存在的user表添加新字段
|
||
ALTER TABLE `user`
|
||
ADD COLUMN `vipType` VARCHAR(50) DEFAULT '体验会员' NULL COMMENT '套餐类别（体验会员/月度会员/年度会员）' AFTER `vipExpire`,
|
||
ADD COLUMN `location` VARCHAR(100) NULL COMMENT '所在省市' AFTER `vipType`,
|
||
ADD COLUMN `preference` VARCHAR(100) NULL COMMENT '彩票偏好（双色球/大乐透等）' AFTER `location`,
|
||
ADD COLUMN `channel` VARCHAR(100) NULL COMMENT '获客渠道' AFTER `preference`;
|
||
|
||
ALTER TABLE `user`
|
||
ADD COLUMN `vipType` VARCHAR(50) DEFAULT '体验会员' NULL COMMENT '套餐类别（体验会员/月度会员/年度会员）' AFTER `vipExpire`
|