556 lines
41 KiB
SQL
556 lines
41 KiB
SQL
-- ============================================================
|
||
-- 零食商城 数据库初始化脚本
|
||
-- 适用 MySQL 8.0+
|
||
-- 字符集：utf8mb4 / 排序规则：utf8mb4_unicode_ci
|
||
-- 全部表与字段均已添加 COMMENT 注释
|
||
-- ============================================================
|
||
|
||
-- 删除并重建数据库
|
||
DROP DATABASE IF EXISTS `snack_mall`;
|
||
CREATE DATABASE `snack_mall`
|
||
DEFAULT CHARACTER SET utf8mb4
|
||
DEFAULT COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE `snack_mall`;
|
||
|
||
|
||
-- ============================================================
|
||
-- ① 用户表
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `user`;
|
||
CREATE TABLE `user` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`username` VARCHAR(50) NOT NULL COMMENT '登录用户名（唯一）',
|
||
`password` VARCHAR(100) NOT NULL COMMENT '加密密码（BCrypt 强度 10）',
|
||
`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
|
||
`nickname` VARCHAR(50) DEFAULT NULL COMMENT '昵称',
|
||
`avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像 URL',
|
||
`gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别：0-未知 1-男 2-女',
|
||
`birthday` DATE DEFAULT NULL COMMENT '生日',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0-禁用 1-启用',
|
||
`last_login_time` DATETIME DEFAULT NULL COMMENT '最近登录时间',
|
||
`last_login_ip` VARCHAR(50) DEFAULT NULL COMMENT '最近登录 IP',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_username` (`username`),
|
||
KEY `idx_phone` (`phone`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='前台用户表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ② 管理员表
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `admin`;
|
||
CREATE TABLE `admin` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`username` VARCHAR(50) NOT NULL COMMENT '登录用户名（唯一）',
|
||
`password` VARCHAR(100) NOT NULL COMMENT 'BCrypt 加密密码',
|
||
`nickname` VARCHAR(50) DEFAULT NULL COMMENT '昵称',
|
||
`avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像 URL',
|
||
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
|
||
`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
|
||
`role` VARCHAR(50) NOT NULL DEFAULT 'admin' COMMENT '角色标识：super_admin / admin / operator',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0-禁用 1-启用',
|
||
`last_login_time` DATETIME DEFAULT NULL COMMENT '最近登录时间',
|
||
`last_login_ip` VARCHAR(50) DEFAULT NULL COMMENT '最近登录 IP',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_username` (`username`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_role` (`role`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='后台管理员表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ③ 商品分类表（支持多级树形）
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `category`;
|
||
CREATE TABLE `category` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`name` VARCHAR(50) NOT NULL COMMENT '分类名称',
|
||
`parent_id` BIGINT NOT NULL DEFAULT 0 COMMENT '父分类 ID，0 表示一级分类',
|
||
`level` TINYINT NOT NULL DEFAULT 1 COMMENT '层级：1-一级 2-二级 3-三级',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序号（升序展示）',
|
||
`icon` VARCHAR(255) DEFAULT NULL COMMENT '分类图标 URL',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_parent_id` (`parent_id`),
|
||
KEY `idx_level` (`level`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='商品分类表（支持多级树形结构）';
|
||
|
||
|
||
-- ============================================================
|
||
-- ④ 商品 SPU 表
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `product`;
|
||
CREATE TABLE `product` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID（SPU）',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '商品名称',
|
||
`category_id` BIGINT NOT NULL COMMENT '所属分类 ID',
|
||
`brand` VARCHAR(100) DEFAULT NULL COMMENT '品牌',
|
||
`main_image` VARCHAR(255) DEFAULT NULL COMMENT '主图 URL',
|
||
`sub_images` TEXT DEFAULT NULL COMMENT '副图列表（JSON 数组）',
|
||
`detail` LONGTEXT DEFAULT NULL COMMENT '富文本详情（HTML）',
|
||
`origin_price` DECIMAL(10,2) DEFAULT NULL COMMENT '原价/划线价',
|
||
`sales` INT NOT NULL DEFAULT 0 COMMENT '累计销量',
|
||
`view_count` INT NOT NULL DEFAULT 0 COMMENT '浏览量',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-下架 1-上架',
|
||
`is_hot` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热门：0-否 1-是',
|
||
`is_new` TINYINT NOT NULL DEFAULT 0 COMMENT '是否新品：0-否 1-是',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_category_id` (`category_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_sales` (`sales`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='商品 SPU 表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑤ 商品 SKU 表
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `product_sku`;
|
||
CREATE TABLE `product_sku` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID（SKU）',
|
||
`product_id` BIGINT NOT NULL COMMENT '所属 SPU ID',
|
||
`sku_name` VARCHAR(100) NOT NULL COMMENT '规格名称，如：原味/500g',
|
||
`image` VARCHAR(255) DEFAULT NULL COMMENT '规格图片 URL',
|
||
`price` DECIMAL(10,2) NOT NULL COMMENT '售价',
|
||
`stock` INT NOT NULL DEFAULT 0 COMMENT '库存数量',
|
||
`sales` INT NOT NULL DEFAULT 0 COMMENT '累计销量',
|
||
`weight` INT DEFAULT NULL COMMENT '重量（克）',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_product_id` (`product_id`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='商品 SKU 表（具体规格）';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑥ 收货地址
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `address`;
|
||
CREATE TABLE `address` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '所属用户 ID',
|
||
`receiver` VARCHAR(50) NOT NULL COMMENT '收货人姓名',
|
||
`phone` VARCHAR(20) NOT NULL COMMENT '收货人手机号',
|
||
`province` VARCHAR(50) DEFAULT NULL COMMENT '省',
|
||
`city` VARCHAR(50) DEFAULT NULL COMMENT '市',
|
||
`district` VARCHAR(50) DEFAULT NULL COMMENT '区/县',
|
||
`detail` VARCHAR(255) NOT NULL COMMENT '详细地址',
|
||
`tag` VARCHAR(20) DEFAULT NULL COMMENT '标签：家/公司/学校',
|
||
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认地址：0-否 1-是',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_is_default` (`user_id`, `is_default`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='收货地址表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑦ 购物车
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `cart`;
|
||
CREATE TABLE `cart` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户 ID',
|
||
`product_id` BIGINT NOT NULL COMMENT '商品 SPU ID',
|
||
`sku_id` BIGINT NOT NULL COMMENT '商品 SKU ID',
|
||
`quantity` INT NOT NULL DEFAULT 1 COMMENT '数量',
|
||
`selected` TINYINT NOT NULL DEFAULT 1 COMMENT '选中状态：0-未选 1-已选',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_sku` (`user_id`, `sku_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='购物车表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑧ 订单主表
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `orders`;
|
||
CREATE TABLE `orders` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`order_no` VARCHAR(32) NOT NULL COMMENT '订单号（业务唯一）',
|
||
`user_id` BIGINT NOT NULL COMMENT '下单用户 ID',
|
||
`total_amount` DECIMAL(10,2) NOT NULL COMMENT '商品总金额（未优惠）',
|
||
`freight_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '运费',
|
||
`discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '优惠金额（满减/折扣）',
|
||
`coupon_id` BIGINT DEFAULT NULL COMMENT '使用的优惠券 ID',
|
||
`coupon_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '优惠券抵扣金额',
|
||
`pay_amount` DECIMAL(10,2) NOT NULL COMMENT '实付金额',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0-待付款 1-待发货 2-待收货 3-已完成 4-已取消 5-已退款',
|
||
`receiver_name` VARCHAR(50) NOT NULL COMMENT '收货人姓名',
|
||
`receiver_phone` VARCHAR(20) NOT NULL COMMENT '收货人手机号',
|
||
`receiver_address` VARCHAR(255) NOT NULL COMMENT '完整收货地址',
|
||
`remark` VARCHAR(500) DEFAULT NULL COMMENT '订单备注',
|
||
`tracking_company` VARCHAR(50) DEFAULT NULL COMMENT '物流公司',
|
||
`tracking_no` VARCHAR(50) DEFAULT NULL COMMENT '物流单号',
|
||
`pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
|
||
`deliver_time` DATETIME DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` DATETIME DEFAULT NULL COMMENT '收货时间',
|
||
`cancel_time` DATETIME DEFAULT NULL COMMENT '取消时间',
|
||
`finish_time` DATETIME DEFAULT NULL COMMENT '完成时间',
|
||
`pay_channel` VARCHAR(20) DEFAULT NULL COMMENT '支付渠道：alipay / wechat / balance',
|
||
`pay_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方支付平台交易号（支付宝/微信返回）',
|
||
`refund_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '退款交易号（支付宝/微信返回）',
|
||
`refund_time` DATETIME DEFAULT NULL COMMENT '退款时间',
|
||
`refund_reason` VARCHAR(255) DEFAULT NULL COMMENT '退款原因',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_order_no` (`order_no`),
|
||
UNIQUE KEY `uk_pay_trade_no` (`pay_trade_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_user_status` (`user_id`, `status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='订单主表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑨ 订单商品明细
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `order_item`;
|
||
CREATE TABLE `order_item` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`order_id` BIGINT NOT NULL COMMENT '订单 ID',
|
||
`order_no` VARCHAR(32) NOT NULL COMMENT '订单号（冗余）',
|
||
`product_id` BIGINT NOT NULL COMMENT '商品 SPU ID',
|
||
`product_name` VARCHAR(200) NOT NULL COMMENT '商品名（冗余防商品改名）',
|
||
`product_image` VARCHAR(255) DEFAULT NULL COMMENT '商品主图（冗余）',
|
||
`sku_id` BIGINT NOT NULL COMMENT 'SKU ID',
|
||
`sku_name` VARCHAR(100) NOT NULL COMMENT 'SKU 规格（冗余）',
|
||
`price` DECIMAL(10,2) NOT NULL COMMENT '成交单价',
|
||
`quantity` INT NOT NULL COMMENT '购买数量',
|
||
`total_amount` DECIMAL(10,2) NOT NULL COMMENT '小计金额（price*quantity）',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_product_id` (`product_id`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='订单商品明细表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑩ 收藏
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `favorite`;
|
||
CREATE TABLE `favorite` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户 ID',
|
||
`product_id` BIGINT NOT NULL COMMENT '商品 SPU ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '收藏时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_product` (`user_id`, `product_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_product_id` (`product_id`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='用户收藏表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑪ 轮播图
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `banner`;
|
||
CREATE TABLE `banner` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`title` VARCHAR(100) DEFAULT NULL COMMENT '标题',
|
||
`image` VARCHAR(255) NOT NULL COMMENT '图片 URL',
|
||
`link_type` TINYINT NOT NULL DEFAULT 0 COMMENT '跳转类型：0-不跳转 1-商品 2-分类 3-外链',
|
||
`link_value` VARCHAR(255) DEFAULT NULL COMMENT '跳转目标值（商品 ID / 分类 ID / URL）',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序号（升序）',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-下线 1-上线',
|
||
`start_time` DATETIME DEFAULT NULL COMMENT '生效时间',
|
||
`end_time` DATETIME DEFAULT NULL COMMENT '失效时间',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status_sort` (`status`, `sort`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_end_time` (`end_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='首页轮播图表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑫ 客服会话表（WebSocket 长连接）
|
||
-- 设计要点：
|
||
-- - 客户端通过 WebSocket 建立长连接，服务端实时转发消息
|
||
-- - session_no 是对外展示/查询的编号
|
||
-- - user_seq / admin_seq 记录各端最大已确认序号，用于断线补发
|
||
-- - status 区分会话生命周期
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `chat_session`;
|
||
CREATE TABLE `chat_session` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`session_no` VARCHAR(32) NOT NULL COMMENT '会话编号（业务唯一）',
|
||
`user_id` BIGINT NOT NULL COMMENT '发起用户 ID',
|
||
`admin_id` BIGINT DEFAULT NULL COMMENT '接待客服 ID，NULL=待分配',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '会话状态：0-待处理 1-处理中 2-已解决 3-已关闭',
|
||
`user_unread` INT NOT NULL DEFAULT 0 COMMENT '用户未读消息数',
|
||
`admin_unread` INT NOT NULL DEFAULT 0 COMMENT '客服未读消息数',
|
||
`last_message` VARCHAR(500) DEFAULT NULL COMMENT '最后一条消息摘要',
|
||
`last_time` DATETIME DEFAULT NULL COMMENT '最后一条消息时间',
|
||
`user_seq` BIGINT NOT NULL DEFAULT 0 COMMENT '用户端最大已确认消息序号（用于断线补发）',
|
||
`admin_seq` BIGINT NOT NULL DEFAULT 0 COMMENT '客服端最大已确认消息序号（用于断线补发）',
|
||
`close_time` DATETIME DEFAULT NULL COMMENT '关闭时间',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_session_no` (`session_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_admin_id` (`admin_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_last_time` (`last_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='客服会话表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑬ 客服消息表（WebSocket 消息持久化）
|
||
-- 设计要点：
|
||
-- - 每条消息全局递增 seq 编号（同 session 内）
|
||
-- - 客户端通过 last_ack_seq 拉取差量消息
|
||
-- - type 支持 text / image / file / system / product（商品卡片）
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `chat_message`;
|
||
CREATE TABLE `chat_message` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`session_id` BIGINT NOT NULL COMMENT '所属会话 ID',
|
||
`seq` BIGINT NOT NULL DEFAULT 0 COMMENT '消息在会话内的递增序号（用于断线补发和排序）',
|
||
`sender_id` BIGINT NOT NULL COMMENT '发送者 ID（用户/客服）',
|
||
`sender_type` TINYINT NOT NULL COMMENT '发送者类型：0-用户 1-客服 2-系统',
|
||
`type` VARCHAR(10) NOT NULL DEFAULT 'text' COMMENT '消息类型：text / image / file / product / system',
|
||
`content` TEXT NOT NULL COMMENT '消息内容（文字 / JSON / 图片 URL）',
|
||
`extra` JSON DEFAULT NULL COMMENT '扩展字段（商品卡片等结构化数据）',
|
||
`is_recalled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否撤回：0-否 1-是',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_session_seq` (`session_id`, `seq`),
|
||
KEY `idx_session_id` (`session_id`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_session_time` (`session_id`, `create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='客服消息表（WebSocket 持久化）';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑭ 客服快捷回复模板
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `quick_reply`;
|
||
CREATE TABLE `quick_reply` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`title` VARCHAR(100) NOT NULL COMMENT '模板标题（用于搜索）',
|
||
`content` VARCHAR(500) NOT NULL COMMENT '模板内容',
|
||
`category` VARCHAR(50) DEFAULT NULL COMMENT '分类：问候/物流/售后/活动等',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
`use_count` INT NOT NULL DEFAULT 0 COMMENT '使用次数（统计）',
|
||
`create_by` BIGINT DEFAULT NULL COMMENT '创建人（管理员 ID）',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status_sort` (`status`, `sort`),
|
||
KEY `idx_category` (`category`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='客服快捷回复模板表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑮ 系统公告
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `notice`;
|
||
CREATE TABLE `notice` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`title` VARCHAR(200) NOT NULL COMMENT '公告标题',
|
||
`content` LONGTEXT NOT NULL COMMENT '公告内容（富文本 HTML）',
|
||
`type` TINYINT NOT NULL DEFAULT 0 COMMENT '公告类型：0-普通 1-重要 2-活动',
|
||
`is_top` TINYINT NOT NULL DEFAULT 0 COMMENT '是否置顶：0-否 1-是',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-下线 1-上线',
|
||
`start_time` DATETIME DEFAULT NULL COMMENT '生效时间',
|
||
`end_time` DATETIME DEFAULT NULL COMMENT '失效时间',
|
||
`view_count` INT NOT NULL DEFAULT 0 COMMENT '浏览量',
|
||
`publisher_id` BIGINT DEFAULT NULL COMMENT '发布人（管理员 ID）',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_type` (`type`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_top_status` (`is_top`, `status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='系统公告表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑯ 优惠券模板
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `coupon`;
|
||
CREATE TABLE `coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '优惠券名称',
|
||
`type` TINYINT NOT NULL COMMENT '类型：0-满减券 1-折扣券 2-无门槛券',
|
||
`amount` DECIMAL(10,2) NOT NULL COMMENT '满减面值 / 折扣率（0.85 表示 8.5 折）',
|
||
`min_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '最低使用金额（0 表示无门槛）',
|
||
`max_discount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '折扣券最高抵扣金额',
|
||
`total` INT NOT NULL COMMENT '发放总量（-1 表示不限量）',
|
||
`remain` INT NOT NULL COMMENT '剩余可领取数量',
|
||
`per_limit` INT NOT NULL DEFAULT 1 COMMENT '每人限领数量',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-未上线 1-已上线 2-已结束',
|
||
`start_time` DATETIME NOT NULL COMMENT '领取开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '领取截止时间',
|
||
`valid_days` INT DEFAULT NULL COMMENT '领取后有效天数（NULL 时使用固定截止日期）',
|
||
`description` VARCHAR(500) DEFAULT NULL COMMENT '使用说明',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_end_time` (`end_time`),
|
||
KEY `idx_type` (`type`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='优惠券模板表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑰ 用户优惠券（领取记录）
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `user_coupon`;
|
||
CREATE TABLE `user_coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户 ID',
|
||
`coupon_id` BIGINT NOT NULL COMMENT '优惠券模板 ID',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-未使用 1-已使用 2-已过期 3-已作废',
|
||
`order_id` BIGINT DEFAULT NULL COMMENT '使用时关联的订单 ID',
|
||
`receive_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
||
`use_time` DATETIME DEFAULT NULL COMMENT '使用时间',
|
||
`expire_time` DATETIME NOT NULL COMMENT '过期时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_coupon_id` (`coupon_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire` (`expire_time`),
|
||
KEY `idx_user_status` (`user_id`, `status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='用户优惠券领取记录表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑱ 限时抢购活动
|
||
-- 设计要点（与 Redis 配合）：
|
||
-- - 活动开启时，把 seckill_product 的 seckill_stock 同步到 Redis
|
||
-- key: seckill:stock:{activityId}:{productId}
|
||
-- - 用户抢购时 Redis DECR 原子扣减，避免超卖
|
||
-- - 抢购成功后再异步写库（消费 MQ 消息）
|
||
-- - seckill_order 唯一索引 (user_id, activity_id, product_id) 兜底"一人一单"
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `seckill_activity`;
|
||
CREATE TABLE `seckill_activity` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '活动名称',
|
||
`cover` VARCHAR(255) DEFAULT NULL COMMENT '活动封面图',
|
||
`start_time` DATETIME NOT NULL COMMENT '活动开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '活动结束时间',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-未开始 1-进行中 2-已结束',
|
||
`description` VARCHAR(500) DEFAULT NULL COMMENT '活动说明',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_start_time` (`start_time`),
|
||
KEY `idx_end_time` (`end_time`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='限时抢购活动表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑲ 抢购活动商品
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `seckill_product`;
|
||
CREATE TABLE `seckill_product` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`activity_id` BIGINT NOT NULL COMMENT '所属活动 ID',
|
||
`product_id` BIGINT NOT NULL COMMENT '商品 SPU ID',
|
||
`sku_id` BIGINT NOT NULL COMMENT '商品 SKU ID',
|
||
`seckill_price` DECIMAL(10,2) NOT NULL COMMENT '抢购价',
|
||
`origin_price` DECIMAL(10,2) NOT NULL COMMENT '原价（展示用）',
|
||
`seckill_stock` INT NOT NULL COMMENT '总库存（同步到 Redis）',
|
||
`remain_stock` INT NOT NULL COMMENT '剩余库存（兜底用，正常情况下以 Redis 为准）',
|
||
`per_limit` INT NOT NULL DEFAULT 1 COMMENT '每人限购数量',
|
||
`sales` INT NOT NULL DEFAULT 0 COMMENT '已售数量（异步消费 MQ 后累加）',
|
||
`sort` INT NOT NULL DEFAULT 0 COMMENT '活动内排序',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_activity_sku` (`activity_id`, `sku_id`),
|
||
KEY `idx_product_id` (`product_id`),
|
||
KEY `idx_activity_id` (`activity_id`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='抢购活动商品表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ⑳ 抢购订单（仅作为"成功下单"的最终记录）
|
||
-- 设计要点：
|
||
-- - 抢购请求先通过 Redis 原子扣库存 + Lua 限流
|
||
-- - 抢到后消息进入 MQ，由消费者线程消费：
|
||
-- ① 写 seckill_order（唯一索引防一人多单）
|
||
-- ② 生成主订单
|
||
-- ③ 异步扣 MySQL 库存（兜底）
|
||
-- - 此表只保留"已成功抢购"的记录，不存失败的请求
|
||
-- - (user_id, activity_id, product_id) 唯一索引：兜底防一人多单
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `seckill_order`;
|
||
CREATE TABLE `seckill_order` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户 ID',
|
||
`activity_id` BIGINT NOT NULL COMMENT '活动 ID',
|
||
`product_id` BIGINT NOT NULL COMMENT '商品 SPU ID',
|
||
`sku_id` BIGINT NOT NULL COMMENT 'SKU ID',
|
||
`quantity` INT NOT NULL COMMENT '抢购数量',
|
||
`seckill_price` DECIMAL(10,2) NOT NULL COMMENT '成交单价',
|
||
`order_id` BIGINT DEFAULT NULL COMMENT '关联主订单 ID（支付/取消后回填）',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-待支付 1-已支付 2-已取消 3-已退款',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '抢购时间',
|
||
`pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
|
||
PRIMARY KEY (`id`),
|
||
-- 唯一索引：防止同一用户在同一活动中对同一商品重复抢购
|
||
UNIQUE KEY `uk_user_activity_sku` (`user_id`, `activity_id`, `sku_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_activity_id` (`activity_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='抢购成功记录表';
|
||
|
||
|
||
-- ============================================================
|
||
-- ㉑ 文件上传记录
|
||
-- ============================================================
|
||
DROP TABLE IF EXISTS `upload_file`;
|
||
CREATE TABLE `upload_file` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
|
||
`name` VARCHAR(255) NOT NULL COMMENT '原始文件名',
|
||
`path` VARCHAR(255) NOT NULL COMMENT '存储路径（相对 uploads/）',
|
||
`url` VARCHAR(255) NOT NULL COMMENT '访问 URL',
|
||
`size` BIGINT NOT NULL DEFAULT 0 COMMENT '文件大小（字节）',
|
||
`content_type` VARCHAR(100) DEFAULT NULL COMMENT 'MIME 类型',
|
||
`storage_type` VARCHAR(20) NOT NULL DEFAULT 'local' COMMENT '存储类型：local / qiniu / aliyun / tencent / minio',
|
||
`biz_type` VARCHAR(50) DEFAULT NULL COMMENT '业务类型：avatar / product / banner / chat',
|
||
`upload_by` BIGINT DEFAULT NULL COMMENT '上传人（用户/管理员 ID）',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_upload_by` (`upload_by`),
|
||
KEY `idx_biz_type` (`biz_type`),
|
||
KEY `idx_create_time`(`create_time`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='文件上传记录表';
|
||
|
||
|
||
-- ============================================================
|
||
-- 完成
|
||
-- ============================================================
|
||
SELECT '✅ 数据库初始化完成，共 21 张表' AS message;
|