baqing-shop/sql/biz_goods.sql

-- 商品主表
CREATE TABLE IF NOT EXISTS `biz_goods` (
  `goods_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `shop_id` bigint(20) NOT NULL COMMENT '店铺ID',
  `category_id` bigint(20) NOT NULL COMMENT '平台端二级分类ID',
  `shop_category_id` bigint(20) DEFAULT NULL COMMENT '店铺商品分类ID（本店二级）',
  `attr_template_id` bigint(20) DEFAULT NULL COMMENT '属性模版ID',
  `goods_sn` varchar(32) NOT NULL COMMENT '商品编号',
  `goods_name` varchar(200) NOT NULL COMMENT '商品名称',
  `main_pic` varchar(512) NOT NULL COMMENT '主图',
  `detail_content` text COMMENT '详情',
  `sale_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '销售价',
  `stock` int(11) NOT NULL DEFAULT '0' COMMENT '库存',
  `sales_count` int(11) NOT NULL DEFAULT '0' COMMENT '销量',
  `goods_status` char(1) NOT NULL DEFAULT '0' COMMENT '0未上架1待审核2出售中3审核失败4已下架',
  `reject_reason` varchar(500) DEFAULT NULL COMMENT '驳回原因',
  `submit_time` datetime DEFAULT NULL COMMENT '首次提交上架时间',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `off_shelf_time` datetime DEFAULT NULL COMMENT '下架时间',
  `del_flag` char(1) NOT NULL DEFAULT '0',
  `create_by` varchar(64) DEFAULT '',
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(64) DEFAULT '',
  `update_time` datetime DEFAULT NULL,
  `remark` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `uk_goods_sn` (`goods_sn`),
  KEY `idx_shop_status` (`shop_id`,`goods_status`,`del_flag`),
  KEY `idx_category_id` (`category_id`,`del_flag`),
  KEY `idx_shop_category_id` (`shop_category_id`,`del_flag`),
  KEY `idx_attr_template_id` (`attr_template_id`,`del_flag`),
  KEY `idx_submit_time` (`submit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品';

-- 商品服务展示快照
CREATE TABLE IF NOT EXISTS `biz_goods_service_snapshot` (
  `snapshot_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_id` bigint(20) NOT NULL,
  `service_id` bigint(20) NOT NULL COMMENT '来源目录ID',
  `service_name` varchar(64) NOT NULL,
  `service_intro` varchar(500) NOT NULL DEFAULT '',
  `service_icon` varchar(512) NOT NULL,
  `sort_no` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`snapshot_id`),
  KEY `idx_goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品服务展示快照';

-- 商品属性/规格快照
CREATE TABLE IF NOT EXISTS `biz_goods_attr` (
  `attr_id`    bigint(20)  NOT NULL AUTO_INCREMENT,
  `goods_id`   bigint(20)  NOT NULL COMMENT '商品ID',
  `attr_type`  char(1)     NOT NULL COMMENT '1属性2规格',
  `item_name`  varchar(64) NOT NULL COMMENT '属性项/规格项名称',
  `value_text` varchar(128) NOT NULL COMMENT '属性值/规格值',
  `sort_no`    int(11)     NOT NULL DEFAULT '0' COMMENT '展示顺序',
  PRIMARY KEY (`attr_id`),
  KEY `idx_goods_id` (`goods_id`,`attr_type`,`sort_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品属性规格快照';