165 lines
7.4 KiB
SQL
165 lines
7.4 KiB
SQL
-- MySQL 8.0 建表脚本
|
||
-- 根据代码中的entity和domain生成
|
||
|
||
-- 设置字符集和排序规则
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ------------------------------
|
||
-- keXuePay模块表结构
|
||
-- ------------------------------
|
||
|
||
-- 1. 产品表 t_product
|
||
CREATE TABLE `t_product` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`title` varchar(255) NOT NULL COMMENT '商品名称',
|
||
`price` int NOT NULL COMMENT '价格（分）',
|
||
`get_beans` int NOT NULL COMMENT '得到的可学豆',
|
||
`add_beans` int NOT NULL COMMENT '加送的可学豆',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='产品表';
|
||
|
||
-- 2. 订单信息表 t_order_info
|
||
CREATE TABLE `t_order_info` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`title` varchar(255) NOT NULL COMMENT '订单标题',
|
||
`order_no` varchar(64) NOT NULL COMMENT '商户订单编号',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`product_id` bigint NOT NULL COMMENT '支付产品id',
|
||
`total_fee` int NOT NULL COMMENT '订单金额(分)',
|
||
`code_url` varchar(512) DEFAULT NULL COMMENT '订单二维码连接',
|
||
`order_status` varchar(32) NOT NULL COMMENT '订单状态',
|
||
`payment_type` varchar(32) NOT NULL COMMENT '支付方式',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_order_no` (`order_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_product_id` (`product_id`),
|
||
KEY `idx_order_status` (`order_status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单信息表';
|
||
|
||
-- 3. 支付信息表 t_payment_info
|
||
CREATE TABLE `t_payment_info` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_no` varchar(64) NOT NULL COMMENT '商品订单编号',
|
||
`transaction_id` varchar(64) DEFAULT NULL COMMENT '支付系统交易编号',
|
||
`payment_type` varchar(32) NOT NULL COMMENT '支付类型',
|
||
`trade_type` varchar(32) DEFAULT NULL COMMENT '交易类型',
|
||
`trade_state` varchar(32) DEFAULT NULL COMMENT '交易状态',
|
||
`payer_total` int DEFAULT NULL COMMENT '支付金额(分)',
|
||
`content` text COMMENT '通知参数',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_transaction_id` (`transaction_id`),
|
||
KEY `idx_payment_type` (`payment_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付信息表';
|
||
|
||
-- 4. 退款信息表 t_refund_info
|
||
CREATE TABLE `t_refund_info` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_no` varchar(64) NOT NULL COMMENT '商品订单编号',
|
||
`refund_no` varchar(64) NOT NULL COMMENT '退款单编号',
|
||
`refund_id` varchar(64) DEFAULT NULL COMMENT '支付系统退款单号',
|
||
`total_fee` int NOT NULL COMMENT '原订单金额(分)',
|
||
`refund` int NOT NULL COMMENT '退款金额(分)',
|
||
`reason` varchar(255) NOT NULL COMMENT '退款原因',
|
||
`refund_status` varchar(32) NOT NULL COMMENT '退款单状态',
|
||
`content_return` text COMMENT '申请退款返回参数',
|
||
`content_notify` text COMMENT '退款结果通知参数',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_refund_no` (`refund_no`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_refund_status` (`refund_status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='退款信息表';
|
||
|
||
-- ------------------------------
|
||
-- keXueUser模块表结构
|
||
-- ------------------------------
|
||
|
||
-- 1. 用户表 user
|
||
CREATE TABLE `user` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`user_name` varchar(64) NOT NULL COMMENT '用户名',
|
||
`password` varchar(255) NOT NULL COMMENT '密码',
|
||
`phone` varchar(11) DEFAULT NULL COMMENT '手机号',
|
||
`school` varchar(128) DEFAULT NULL COMMENT '学校',
|
||
`major` varchar(128) DEFAULT NULL COMMENT '专业',
|
||
`sys_beans` int DEFAULT '0' COMMENT '系统赠送豆',
|
||
`pay_beans` int DEFAULT '0' COMMENT '购买豆',
|
||
`invite_beans` int DEFAULT '0' COMMENT '邀请豆',
|
||
`invite_code` varchar(32) DEFAULT NULL COMMENT '自己的邀请码',
|
||
`inviter_id` int DEFAULT NULL COMMENT '邀请人的id',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_name` (`user_name`),
|
||
UNIQUE KEY `uk_phone` (`phone`),
|
||
UNIQUE KEY `uk_invite_code` (`invite_code`),
|
||
KEY `idx_inviter_id` (`inviter_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
|
||
|
||
-- 2. 支付记录表 d_pay_record
|
||
CREATE TABLE `d_pay_record` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`user_name` varchar(64) NOT NULL COMMENT '用户名',
|
||
`drawer_id` varchar(64) NOT NULL COMMENT '设备id',
|
||
`update_time` datetime NOT NULL COMMENT '更新时间',
|
||
`count` int NOT NULL COMMENT '数量',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_name` (`user_name`),
|
||
KEY `idx_drawer_id` (`drawer_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付记录表';
|
||
|
||
-- 3. 令牌表 d_token
|
||
CREATE TABLE `d_token` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`user_name` varchar(64) NOT NULL COMMENT '用户名',
|
||
`draw_token` varchar(255) NOT NULL COMMENT '令牌',
|
||
`update_time` datetime NOT NULL COMMENT '更新时间',
|
||
`drawer_id` varchar(64) NOT NULL COMMENT '设备id',
|
||
`last_login_time` datetime NOT NULL COMMENT '最后登录时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_name` (`user_name`),
|
||
KEY `idx_drawer_id` (`drawer_id`),
|
||
KEY `idx_draw_token` (`draw_token`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='令牌表';
|
||
|
||
-- 4. 新闻表 news
|
||
CREATE TABLE `news` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`title` varchar(255) NOT NULL COMMENT '标题',
|
||
`content` text NOT NULL COMMENT '内容',
|
||
`url` varchar(512) DEFAULT NULL COMMENT '链接',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新闻表';
|
||
|
||
-- 5. 通知表 notices
|
||
CREATE TABLE `notices` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`title` varchar(255) NOT NULL COMMENT '标题',
|
||
`content` text NOT NULL COMMENT '内容',
|
||
`publish_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_publish_time` (`publish_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通知表';
|
||
|
||
-- 6. 短信表 sms
|
||
CREATE TABLE `sms` (
|
||
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`phone` varchar(11) NOT NULL COMMENT '手机号',
|
||
`code` varchar(6) NOT NULL COMMENT '验证码',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`state` tinyint DEFAULT '0' COMMENT '状态：0-未使用，1-已使用',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_phone` (`phone`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_state` (`state`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='短信表';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1; |