大家好,之前我们设计了购物商城的商品表结构(原文链接),现在我们来具体实现表的DDL。对于建表规范,可以参考本系列第一篇文章:前期准备
- 下载地址:前往下载
新建一个数据库,名字叫mall
,基字符集:utf-8
,:数据库排序规则:utf-8_general_ci
对于商品模块,统一使用goods_
做前缀。
首先是两个比较简单的表:分类表和品牌表
- 分类表:
CREATE TABLE `goods_category` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`category_name` varchar(50) NOT NULL COMMENT '分类名称',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分类表';
- 品牌表:
CREATE TABLE `goods_brand` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`brand_name` varchar(50) NOT NULL COMMENT '品牌名称',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='品牌表';
接下来是SPU表:
CREATE TABLE `goods_spu` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`spu_no` varchar(50) NOT NULL COMMENT '商品编号,唯一',
`goods_name` varchar(50) NOT NULL COMMENT '商品名称',
`low_price` decimal(9,2) NOT NULL COMMENT '最低售价',
`category_id` bigint(20) NOT NULL COMMENT '分类id',
`brand_id` bigint(20) NOT NULL COMMENT '品牌id',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_spu_no` (`spu_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='spu表';
然后是SPU规格相关的表:
- 规格表:
CREATE TABLE `goods_spec` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`spec_no` varchar(50) NOT NULL COMMENT '规格编号',
`spec_name` varchar(50) NOT NULL COMMENT '规格名称',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格表';
- 规格值表:
CREATE TABLE `goods_spec_value` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`spec_id` bigint(20) NOT NULL COMMENT '规格id',
`spec_value` varchar(50) NOT NULL COMMENT '规格值',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格值表';
- SPU规格表
CREATE TABLE `goods_spu_spec` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`spu_id` bigint(20) NOT NULL COMMENT 'spu_id',
`spec_id` bigint(20) NOT NULL COMMENT 'spec_id',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='spu规格表';
接下来是SKU相关的表:
- SKU表:
CREATE TABLE `goods_sku` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`sku_no` varchar(50) NOT NULL COMMENT 'sku编号,唯一',
`sku_name` varchar(50) NOT NULL COMMENT 'sku名称(冗余spu_name)',
`price` decimal(9,2) NOT NULL COMMENT '售价',
`stock` int(11) NOT NULL COMMENT '库存',
`shop_id` bigint(20) NOT NULL COMMENT '商铺id,为0表示自营',
`spu_id` bigint(20) NOT NULL COMMENT 'spu_id',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku表';
- 商铺表:
CREATE TABLE `shop_info` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`shop_name` varchar(50) NOT NULL COMMENT '店铺名称',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='店铺表';
- 增值保障表:
CREATE TABLE `goods_safeguard` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`safeguard_name` varchar(50) NOT NULL COMMENT '保障名称',
`price` decimal(9,2) NOT NULL COMMENT '保障价格',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='增值保障';
- SKU增值保障
CREATE TABLE `goods_sku_safeguard` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`sku_id` bigint(20) NOT NULL COMMENT 'sku_id',
`safeguard_id` bigint(20) NOT NULL COMMENT 'safeguard_id',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku增值保障';
- SKU规格值表
CREATE TABLE `goods_sku_spec_value` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`sku_id` bigint(20) NOT NULL COMMENT 'sku_id',
`spec_value_id` bigint(20) NOT NULL COMMENT '规格值id',
`gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku规格值';