567 lines
27 KiB
SQL
567 lines
27 KiB
SQL
-- ----------------------------
|
|
-- 商品表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_product`;
|
|
CREATE TABLE `mall_product` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '商品ID',
|
|
`name` varchar(128) NOT NULL COMMENT '商品名称',
|
|
`category_id` bigint DEFAULT NULL COMMENT '分类ID',
|
|
`brand_id` bigint DEFAULT NULL COMMENT '品牌ID',
|
|
`cover_url` varchar(512) DEFAULT NULL COMMENT '封面图片',
|
|
`images` text COMMENT '商品图片(JSON数组)',
|
|
`description` text COMMENT '商品描述',
|
|
`detail` text COMMENT '商品详情',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0上架 1下架)',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`sales_count` int DEFAULT '0' COMMENT '销量',
|
|
`view_count` int DEFAULT '0' COMMENT '浏览量',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' 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_brand_id` (`brand_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='商品表';
|
|
|
|
-- ----------------------------
|
|
-- SKU表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_sku`;
|
|
CREATE TABLE `mall_sku` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'SKU ID',
|
|
`product_id` bigint NOT NULL COMMENT '商品ID',
|
|
`sku_code` varchar(64) NOT NULL COMMENT 'SKU编码',
|
|
`name` varchar(128) DEFAULT NULL COMMENT 'SKU名称',
|
|
`price` int NOT NULL COMMENT '价格(分)',
|
|
`original_price` int DEFAULT NULL COMMENT '原价(分)',
|
|
`stock` int NOT NULL DEFAULT '0' COMMENT '库存',
|
|
`image` varchar(512) DEFAULT NULL COMMENT 'SKU图片',
|
|
`specs` text COMMENT '规格属性(JSON)',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_sku_code` (`sku_code`),
|
|
KEY `idx_product_id` (`product_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='SKU表';
|
|
|
|
-- ----------------------------
|
|
-- 购物车表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_cart`;
|
|
CREATE TABLE `mall_cart` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '购物车ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户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_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' 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 COMMENT='购物车表';
|
|
|
|
-- ----------------------------
|
|
-- 订单表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_order`;
|
|
CREATE TABLE `mall_order` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
|
|
`order_no` varchar(32) NOT NULL COMMENT '订单号',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`total_amount` int NOT NULL COMMENT '订单总金额(分)',
|
|
`pay_amount` int NOT NULL COMMENT '实付金额(分)',
|
|
`pay_status` tinyint NOT NULL DEFAULT '0' COMMENT '支付状态(0未支付 1已支付 2已退款)',
|
|
`pay_order_no` varchar(32) DEFAULT NULL COMMENT '支付单号',
|
|
`pay_channel_code` varchar(32) DEFAULT NULL COMMENT '支付渠道编码',
|
|
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
|
|
`order_status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态(0待付款 1待发货 2待收货 3已完成 4已取消)',
|
|
`delivery_status` tinyint NOT NULL DEFAULT '0' COMMENT '发货状态(0未发货 1已发货 2已收货)',
|
|
`receiver_name` varchar(64) NOT NULL COMMENT '收货人姓名',
|
|
`receiver_phone` varchar(32) NOT NULL COMMENT '收货人电话',
|
|
`receiver_address` varchar(512) NOT NULL COMMENT '收货地址',
|
|
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
|
|
`cancel_reason` varchar(256) DEFAULT NULL COMMENT '取消原因',
|
|
`cancel_time` datetime DEFAULT NULL COMMENT '取消时间',
|
|
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
|
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
|
`promotion_type` tinyint DEFAULT NULL COMMENT '活动类型(0普通订单 1拼团 2秒杀 3砍价)',
|
|
`promotion_id` bigint DEFAULT NULL COMMENT '营销活动ID',
|
|
`seckill_id` bigint DEFAULT NULL COMMENT '秒杀活动ID（仅秒杀订单）',
|
|
`group_buy_id` bigint DEFAULT NULL COMMENT '拼团活动ID（仅拼团订单）',
|
|
`bargain_id` bigint DEFAULT NULL COMMENT '砍价活动ID（仅砍价订单）',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_order_no` (`order_no`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_pay_order_no` (`pay_order_no`),
|
|
KEY `idx_order_status` (`order_status`),
|
|
KEY `idx_pay_status` (`pay_status`),
|
|
KEY `idx_promotion_type` (`promotion_type`),
|
|
KEY `idx_promotion_id` (`promotion_id`),
|
|
KEY `idx_seckill_id` (`seckill_id`)
|
|
) ENGINE=InnoDB COMMENT='订单表';
|
|
|
|
-- ----------------------------
|
|
-- 订单明细表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_order_item`;
|
|
CREATE TABLE `mall_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 '商品ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`product_name` varchar(128) NOT NULL COMMENT '商品名称',
|
|
`sku_name` varchar(128) DEFAULT NULL COMMENT 'SKU名称',
|
|
`sku_image` varchar(512) DEFAULT NULL COMMENT 'SKU图片',
|
|
`price` int NOT NULL COMMENT '单价(分)',
|
|
`quantity` int NOT NULL COMMENT '数量',
|
|
`total_amount` int NOT NULL COMMENT '小计金额(分)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_order_no` (`order_no`),
|
|
KEY `idx_product_id` (`product_id`),
|
|
KEY `idx_sku_id` (`sku_id`)
|
|
) ENGINE=InnoDB COMMENT='订单明细表';
|
|
|
|
-- ----------------------------
|
|
-- 商品分类表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_category`;
|
|
CREATE TABLE `mall_category` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '分类ID',
|
|
`name` varchar(64) NOT NULL COMMENT '分类名称',
|
|
`parent_id` bigint DEFAULT '0' COMMENT '父分类ID(0表示顶级)',
|
|
`level` tinyint DEFAULT '1' COMMENT '分类级别(1一级 2二级 3三级)',
|
|
`icon` varchar(512) DEFAULT NULL COMMENT '分类图标',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' 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_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='商品分类表';
|
|
|
|
-- ----------------------------
|
|
-- 品牌表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_brand`;
|
|
CREATE TABLE `mall_brand` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '品牌ID',
|
|
`name` varchar(64) NOT NULL COMMENT '品牌名称',
|
|
`logo` varchar(512) DEFAULT NULL COMMENT '品牌Logo',
|
|
`description` varchar(512) DEFAULT NULL COMMENT '品牌描述',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='品牌表';
|
|
|
|
-- ----------------------------
|
|
-- 优惠券表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_coupon`;
|
|
CREATE TABLE `mall_coupon` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '优惠券ID',
|
|
`name` varchar(128) NOT NULL COMMENT '优惠券名称',
|
|
`type` tinyint NOT NULL COMMENT '优惠券类型(0满减 1折扣 2现金)',
|
|
`discount_amount` int DEFAULT NULL COMMENT '优惠金额(分)',
|
|
`discount_rate` decimal(5,2) DEFAULT NULL COMMENT '折扣率(%)',
|
|
`min_amount` int NOT NULL DEFAULT '0' COMMENT '使用门槛(分)',
|
|
`total_count` int DEFAULT NULL COMMENT '发放总数(null表示不限)',
|
|
`used_count` int DEFAULT '0' COMMENT '已使用数量',
|
|
`per_limit` int DEFAULT '1' COMMENT '每人限领数量',
|
|
`valid_start_time` datetime NOT NULL COMMENT '有效开始时间',
|
|
`valid_end_time` datetime NOT NULL COMMENT '有效结束时间',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_valid_time` (`valid_start_time`, `valid_end_time`)
|
|
) ENGINE=InnoDB COMMENT='优惠券表';
|
|
|
|
-- ----------------------------
|
|
-- 用户优惠券表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_coupon_user`;
|
|
CREATE TABLE `mall_coupon_user` (
|
|
`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已过期)',
|
|
`used_time` datetime DEFAULT NULL COMMENT '使用时间',
|
|
`order_id` bigint DEFAULT NULL COMMENT '使用的订单ID',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_coupon_id` (`coupon_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='用户优惠券表';
|
|
|
|
-- ----------------------------
|
|
-- 营销活动表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_promotion`;
|
|
CREATE TABLE `mall_promotion` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '活动ID',
|
|
`name` varchar(128) NOT NULL COMMENT '活动名称',
|
|
`type` tinyint NOT NULL COMMENT '活动类型(0普通活动 1拼团 2秒杀 3砍价)',
|
|
`start_time` datetime NOT NULL COMMENT '开始时间',
|
|
`end_time` datetime NOT NULL COMMENT '结束时间',
|
|
`rules` text COMMENT '活动规则(JSON)',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_type` (`type`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_time` (`start_time`, `end_time`)
|
|
) ENGINE=InnoDB COMMENT='营销活动表';
|
|
|
|
-- ----------------------------
|
|
-- 拼团表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_group_buy`;
|
|
CREATE TABLE `mall_group_buy` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '拼团ID',
|
|
`promotion_id` bigint NOT NULL COMMENT '活动ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`group_price` int NOT NULL COMMENT '拼团价(分)',
|
|
`group_size` int NOT NULL COMMENT '成团人数',
|
|
`duration_hours` int DEFAULT '24' COMMENT '拼团有效期(小时)',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_promotion_id` (`promotion_id`),
|
|
KEY `idx_sku_id` (`sku_id`)
|
|
) ENGINE=InnoDB COMMENT='拼团表';
|
|
|
|
-- ----------------------------
|
|
-- 拼团订单表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_group_buy_order`;
|
|
CREATE TABLE `mall_group_buy_order` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|
`group_buy_id` bigint NOT NULL COMMENT '拼团ID',
|
|
`group_no` varchar(32) NOT NULL COMMENT '拼团编号',
|
|
`order_id` bigint NOT NULL COMMENT '订单ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0拼团中 1已成团 2已失败)',
|
|
`success_time` datetime DEFAULT NULL COMMENT '成团时间',
|
|
`expire_time` datetime NOT NULL COMMENT '过期时间',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_group_buy_id` (`group_buy_id`),
|
|
KEY `idx_group_no` (`group_no`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='拼团订单表';
|
|
|
|
-- ----------------------------
|
|
-- 秒杀表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_seckill`;
|
|
CREATE TABLE `mall_seckill` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '秒杀ID',
|
|
`promotion_id` bigint NOT NULL COMMENT '活动ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`seckill_price` int NOT NULL COMMENT '秒杀价(分)',
|
|
`seckill_stock` int NOT NULL COMMENT '秒杀库存',
|
|
`start_time` datetime NOT NULL COMMENT '开始时间',
|
|
`end_time` datetime NOT NULL COMMENT '结束时间',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_promotion_id` (`promotion_id`),
|
|
KEY `idx_sku_id` (`sku_id`),
|
|
KEY `idx_time` (`start_time`, `end_time`)
|
|
) ENGINE=InnoDB COMMENT='秒杀表';
|
|
|
|
-- ----------------------------
|
|
-- 砍价活动表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_bargain`;
|
|
CREATE TABLE `mall_bargain` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '砍价ID',
|
|
`promotion_id` bigint NOT NULL COMMENT '活动ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`bargain_price` int NOT NULL COMMENT '砍后价(分)',
|
|
`original_price` int NOT NULL COMMENT '原价(分)',
|
|
`bargain_stock` int NOT NULL COMMENT '砍价库存',
|
|
`min_price` int NOT NULL COMMENT '最低价(分)',
|
|
`help_count` int NOT NULL DEFAULT '0' COMMENT '需要助力人数',
|
|
`duration_hours` int DEFAULT '24' COMMENT '砍价有效期(小时)',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_promotion_id` (`promotion_id`),
|
|
KEY `idx_sku_id` (`sku_id`)
|
|
) ENGINE=InnoDB COMMENT='砍价活动表';
|
|
|
|
-- ----------------------------
|
|
-- 砍价记录表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_bargain_record`;
|
|
CREATE TABLE `mall_bargain_record` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|
`bargain_id` bigint NOT NULL COMMENT '砍价活动ID',
|
|
`bargain_no` varchar(32) NOT NULL COMMENT '砍价编号',
|
|
`user_id` bigint NOT NULL COMMENT '发起用户ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`current_price` int NOT NULL COMMENT '当前价格(分)',
|
|
`bargain_price` int NOT NULL COMMENT '已砍金额(分)',
|
|
`help_count` int NOT NULL DEFAULT '0' COMMENT '已助力人数',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0进行中 1已成功 2已失败 3已下单)',
|
|
`order_id` bigint DEFAULT NULL COMMENT '订单ID',
|
|
`expire_time` datetime NOT NULL COMMENT '过期时间',
|
|
`success_time` datetime DEFAULT NULL COMMENT '成功时间',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_bargain_id` (`bargain_id`),
|
|
KEY `idx_bargain_no` (`bargain_no`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='砍价记录表';
|
|
|
|
-- ----------------------------
|
|
-- 砍价助力记录表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_bargain_help`;
|
|
CREATE TABLE `mall_bargain_help` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|
`bargain_record_id` bigint NOT NULL COMMENT '砍价记录ID',
|
|
`bargain_no` varchar(32) NOT NULL COMMENT '砍价编号',
|
|
`user_id` bigint NOT NULL COMMENT '助力用户ID',
|
|
`help_price` int NOT NULL COMMENT '助力金额(分)',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '助力时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_bargain_record_id` (`bargain_record_id`),
|
|
KEY `idx_bargain_no` (`bargain_no`),
|
|
KEY `idx_user_id` (`user_id`)
|
|
) ENGINE=InnoDB COMMENT='砍价助力记录表';
|
|
|
|
-- ----------------------------
|
|
-- 订单评价表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_order_evaluate`;
|
|
CREATE TABLE `mall_order_evaluate` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '评价ID',
|
|
`order_id` bigint NOT NULL COMMENT '订单ID',
|
|
`order_no` varchar(32) NOT NULL COMMENT '订单号',
|
|
`order_item_id` bigint NOT NULL COMMENT '订单明细ID',
|
|
`product_id` bigint NOT NULL COMMENT '商品ID',
|
|
`sku_id` bigint NOT NULL COMMENT 'SKU ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`score` tinyint NOT NULL COMMENT '评分(1-5)',
|
|
`content` varchar(512) DEFAULT NULL COMMENT '评价内容',
|
|
`images` text COMMENT '评价图片(JSON数组)',
|
|
`reply_content` varchar(512) DEFAULT NULL COMMENT '回复内容',
|
|
`reply_time` datetime DEFAULT NULL COMMENT '回复时间',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0待审核 1已通过 2已驳回)',
|
|
`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_order_id` (`order_id`),
|
|
KEY `idx_order_no` (`order_no`),
|
|
KEY `idx_order_item_id` (`order_item_id`),
|
|
KEY `idx_product_id` (`product_id`),
|
|
KEY `idx_user_id` (`user_id`)
|
|
) ENGINE=InnoDB COMMENT='订单评价表';
|
|
|
|
-- ----------------------------
|
|
-- 售后申请表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_after_sale`;
|
|
CREATE TABLE `mall_after_sale` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '售后ID',
|
|
`after_sale_no` varchar(32) NOT NULL COMMENT '售后单号',
|
|
`order_id` bigint NOT NULL COMMENT '订单ID',
|
|
`order_no` varchar(32) NOT NULL COMMENT '订单号',
|
|
`order_item_id` bigint NOT NULL COMMENT '订单明细ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`type` tinyint NOT NULL COMMENT '售后类型(0退款 1退货 2换货)',
|
|
`reason` varchar(256) NOT NULL COMMENT '申请原因',
|
|
`description` varchar(512) DEFAULT NULL COMMENT '问题描述',
|
|
`images` text COMMENT '凭证图片(JSON数组)',
|
|
`refund_amount` int DEFAULT NULL COMMENT '退款金额(分)',
|
|
`quantity` int NOT NULL DEFAULT '1' COMMENT '数量',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0待审核 1已同意 2已拒绝 3待退货 4待收货 5已完成 6已取消)',
|
|
`refuse_reason` varchar(256) DEFAULT NULL COMMENT '拒绝原因',
|
|
`refund_order_no` varchar(32) DEFAULT NULL COMMENT '退款单号',
|
|
`logistics_company` varchar(64) DEFAULT NULL COMMENT '物流公司',
|
|
`logistics_no` varchar(64) DEFAULT NULL COMMENT '物流单号',
|
|
`apply_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
|
`audit_time` datetime DEFAULT NULL COMMENT '审核时间',
|
|
`return_time` datetime DEFAULT NULL COMMENT '退货时间',
|
|
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
|
`refund_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_after_sale_no` (`after_sale_no`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_order_no` (`order_no`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='售后申请表';
|
|
|
|
-- ----------------------------
|
|
-- 订单优惠券使用记录表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_order_coupon`;
|
|
CREATE TABLE `mall_order_coupon` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|
`order_id` bigint NOT NULL COMMENT '订单ID',
|
|
`order_no` varchar(32) NOT NULL COMMENT '订单号',
|
|
`coupon_id` bigint NOT NULL COMMENT '优惠券ID',
|
|
`coupon_user_id` bigint NOT NULL COMMENT '用户优惠券ID',
|
|
`discount_amount` int NOT NULL COMMENT '优惠金额(分)',
|
|
`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_coupon_id` (`coupon_id`),
|
|
KEY `idx_coupon_user_id` (`coupon_user_id`)
|
|
) ENGINE=InnoDB COMMENT='订单优惠券使用记录表';
|
|
|
|
-- ----------------------------
|
|
-- 收货地址表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_address`;
|
|
CREATE TABLE `mall_address` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '地址ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`receiver_name` varchar(64) NOT NULL COMMENT '收货人姓名',
|
|
`receiver_phone` varchar(32) NOT NULL COMMENT '收货人电话',
|
|
`province` varchar(64) DEFAULT NULL COMMENT '省份',
|
|
`city` varchar(64) DEFAULT NULL COMMENT '城市',
|
|
`district` varchar(64) DEFAULT NULL COMMENT '区/县',
|
|
`address` varchar(512) NOT 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` (`is_default`)
|
|
) ENGINE=InnoDB COMMENT='收货地址表';
|
|
|
|
-- ----------------------------
|
|
-- 属性模板表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_attribute_template`;
|
|
CREATE TABLE `mall_attribute_template` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '模板ID',
|
|
`name` varchar(64) NOT NULL COMMENT '模板名称',
|
|
`category_id` bigint DEFAULT NULL COMMENT '分类ID(可选，绑定到分类)',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' 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`)
|
|
) ENGINE=InnoDB COMMENT='属性模板表';
|
|
|
|
-- ----------------------------
|
|
-- 属性表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_attribute`;
|
|
CREATE TABLE `mall_attribute` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '属性ID',
|
|
`template_id` bigint DEFAULT NULL COMMENT '模板ID(可选，绑定到模板)',
|
|
`name` varchar(64) NOT NULL COMMENT '属性名称',
|
|
`type` tinyint NOT NULL DEFAULT '0' COMMENT '属性类型(0文本 1数字 2日期 3枚举)',
|
|
`required` tinyint NOT NULL DEFAULT '0' COMMENT '是否必填(0否 1是)',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_template_id` (`template_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='属性表';
|
|
|
|
-- ----------------------------
|
|
-- 属性值表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_attribute_value`;
|
|
CREATE TABLE `mall_attribute_value` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '属性值ID',
|
|
`attribute_id` bigint NOT NULL COMMENT '属性ID',
|
|
`value` varchar(128) NOT NULL COMMENT '属性值',
|
|
`sort` int DEFAULT '0' COMMENT '排序',
|
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0启用 1禁用)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_attribute_id` (`attribute_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB COMMENT='属性值表';
|
|
|
|
-- ----------------------------
|
|
-- 商品属性关联表
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `mall_product_attribute`;
|
|
CREATE TABLE `mall_product_attribute` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
|
`product_id` bigint NOT NULL COMMENT '商品ID',
|
|
`attribute_id` bigint NOT NULL COMMENT '属性ID',
|
|
`attribute_value_id` bigint DEFAULT NULL COMMENT '属性值ID(当属性类型为枚举时使用)',
|
|
`value` varchar(512) DEFAULT NULL COMMENT '属性值(当属性类型为文本/数字/日期时使用，或自定义值)',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' 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_attribute_id` (`attribute_id`),
|
|
KEY `idx_attribute_value_id` (`attribute_value_id`)
|
|
) ENGINE=InnoDB COMMENT='商品属性关联表';
|