// 树形品类维护表
CREATE TABLE `category` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned NOT NULL COMMENT '父Id',
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '分类名称',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_ctm` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=693 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品分类表'
// 品类属性KEY
CREATE TABLE `attribute_key` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) unsigned NOT NULL COMMENT '分类Id',
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '规格名称',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_ctd_name` (`category_id`,`name`),
KEY `idx_ctm` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=298 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类之规格表组key'
// 属性具体VALUE枚举表
CREATE TABLE `attribute_value` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`attribute_id` int(11) unsigned NOT NULL COMMENT '规格Id',
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_atd` (`attribute_id`),
KEY `idx_ctm` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类之规格组内属性'
// 商品类目表
CREATE TABLE `product` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '商品名称',
`product_desc` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '商品描述',
`supplier_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '供应商Id',
`category_id` int(11) unsigned NOT NULL COMMENT '分类Id',
`status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '0 无效 1 正常',
`source` int(11) NOT NULL DEFAULT '1' COMMENT '来源 1 运营录入',
`attribute_list` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '商品规格详情json ',
`body` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '商品详细展示结构,json图文介绍',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_spd` (`supplier_id`),
KEY `idx_cid` (`category_id`),
KEY `idx_ctm` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=714 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品-spu'
// 商品类目表
CREATE TABLE `sku` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) unsigned NOT NULL COMMENT '商品Id',
`sku_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'sku单品名称',
`sku_code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'sku单品code 按规则排序存储 ', // 600张65*65mm
`sku_price` int(11) NOT NULL DEFAULT '1' COMMENT '单品价格',
`sku_stock` int(11) NOT NULL DEFAULT '10000000' COMMENT '单品库存',
`attribute_list` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', // 比如 {"period":92,"firstBuyPrice":2200} 比如 {"颜色":"500张65*65mm"}
`status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '0 下架 1 正常 ',
`source` int(11) NOT NULL DEFAULT '1' COMMENT '来源 1 运营录入',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_pid_sku` (`product_id`,`sku_code`),
KEY `idx_ctm` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1008 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='单品sku'
// 订单表
CREATE TABLE `biz_order` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`biz_order_no` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '业务订单ID',
`pay_order_no` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '支付订单ID',
`address` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地址',
`buyer_open_id` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '购买者',
`seller_open_id` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '销售者',
`order_status` int(11) NOT NULL COMMENT '(1,"待支付"), (2,"支付中"), (3,"支付成功/未发货"), (4,"打款成功"), (5,"已退款")(6,"待打款,运营填完快递信息的状态")',
`from_open_id` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邀请人',
`from_biz_id` bigint(11) DEFAULT NULL COMMENT '来源信息',
`from_biz_type` int(11) DEFAULT NULL COMMENT '来源类型1:app,2:小城,
`total_amount` int(11) NOT NULL COMMENT '购买总金额',
`item_title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '商品名称',
`item_amount` int(11) NOT NULL COMMENT '单价',
`total_count` int(11) NOT NULL COMMENT '购买数量',
`actual_amount` int(11) NOT NULL COMMENT '支付金额',
`biz_id` bigint(11) DEFAULT NULL COMMENT '关联业务',
`biz_product_id` bigint(11) unsigned DEFAULT '0' COMMENT '关联业务产品id',
`biz_type` int(11) DEFAULT NULL COMMENT '关联业务类型',
`coupon_id` int(11) DEFAULT NULL COMMENT '优惠券信息',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_order_no` (`biz_order_no`)
) ENGINE=InnoDB AUTO_INCREMENT=6850 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci