DROP TABLE IF EXISTS mall_member;
|
CREATE TABLE mall_member(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
name VARCHAR(32) COMMENT '名称' ,
|
phone VARCHAR(32) COMMENT '手机号' ,
|
email VARCHAR(90) COMMENT '邮箱' ,
|
password VARCHAR(500) COMMENT '密码' ,
|
sex VARCHAR(2) COMMENT '性别' ,
|
invite_id VARCHAR(8) COMMENT '邀请码' ,
|
referrer_id VARCHAR(8) COMMENT '推荐人邀请码' ,
|
referrer_ids TEXT COMMENT '推荐人线路' ,
|
level VARCHAR(255) COMMENT '代理层级' ,
|
account_status INT COMMENT '账户状态;1-正常 2-禁用' ,
|
account_type INT COMMENT '账户类型;1-正常用户' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商城用户表';
|
|
|
DROP TABLE IF EXISTS mall_goods_category;
|
CREATE TABLE mall_goods_category(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
name VARCHAR(32) COMMENT '名称' ,
|
parent_id BIGINT COMMENT '父级ID' ,
|
parent_ids VARCHAR(900) COMMENT '父级ids' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商品分类';
|
|
|
DROP TABLE IF EXISTS mall_goods;
|
CREATE TABLE mall_goods(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
goods_no VARCHAR(32) COMMENT '商品编号' ,
|
goods_name VARCHAR(255) COMMENT '商品名称' ,
|
goods_introdution TEXT(255) COMMENT '商品介绍' ,
|
unit VARCHAR(10) COMMENT '单位' ,
|
thumb VARCHAR(500) COMMENT '缩略图' ,
|
goods_details TEXT COMMENT '商品详情' ,
|
is_sale INT COMMENT '是否上架;1-上架 2-下架' ,
|
original_price VARCHAR(90) COMMENT '原价' ,
|
present_price VARCHAR(90) COMMENT '现价' ,
|
category_id BIGINT COMMENT '分类ID' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商品信息表';
|
|
|
DROP TABLE IF EXISTS mall_goods_images;
|
CREATE TABLE mall_goods_images(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
image_url VARCHAR(900) COMMENT '主图链接' ,
|
seq INT COMMENT '排序' ,
|
goods_id BIGINT COMMENT '' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商品主图';
|
|
|
DROP TABLE IF EXISTS mall_goods_style;
|
CREATE TABLE mall_goods_style(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
goods_id BIGINT COMMENT '商品ID' ,
|
name VARCHAR(90) COMMENT '样式名称' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商品样式';
|
|
|
DROP TABLE IF EXISTS mall_goods_sku;
|
CREATE TABLE mall_goods_sku(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
sku_name VARCHAR(90) COMMENT 'sku名称' ,
|
sku_image VARCHAR(900) COMMENT 'sku图片' ,
|
stock INT COMMENT '库存' ,
|
sku_volume INT COMMENT '销售数量' ,
|
original_price DECIMAL(20,2) COMMENT '原价' ,
|
present_price DECIMAL(20,2) COMMENT '现价' ,
|
style_id BIGINT COMMENT '样式ID' ,
|
goods_id BIGINT COMMENT '商品ID' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '商品sku';
|
|
alter table mall_member add avatar varchar(500) null comment '头像';
|
ALTER TABLE `mall_goods_category` ADD COLUMN `is_recommend` int(11) NULL COMMENT '是否推荐 1：是 2：否' AFTER `parent_ids`;
|
alter table mall_goods add is_hot int default 2 null comment '是否热卖 1-是 2-否';
|
|
DROP TABLE IF EXISTS mall_shopping_cart;
|
CREATE TABLE mall_shopping_cart(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
goods_id BIGINT COMMENT '商品ID' ,
|
style_id BIGINT COMMENT '样式ID' ,
|
sku_id BIGINT COMMENT 'skuId' ,
|
cnt VARCHAR(255) COMMENT '数量' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '购物车';
|
|
DROP TABLE IF EXISTS mall_address_info;
|
CREATE TABLE mall_address_info(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
name VARCHAR(255) COMMENT '姓名' ,
|
phone VARCHAR(255) COMMENT '联系方式' ,
|
area TEXT COMMENT '地区' ,
|
address TEXT COMMENT '地址' ,
|
longitude VARCHAR(255) COMMENT '经度' ,
|
latitude VARCHAR(255) COMMENT '纬度' ,
|
is_default INT COMMENT '是否默认;1-是2-否' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '用户地址';
|
|
DROP TABLE IF EXISTS mall_order_info;
|
CREATE TABLE mall_order_info(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
order_no VARCHAR(32) COMMENT '订单号' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
order_time DATETIME COMMENT '下单时间' ,
|
pay_time DATETIME COMMENT '支付时间' ,
|
amount DECIMAL(20,2) COMMENT '订单金额' ,
|
pay_method VARCHAR(32) COMMENT '支付方式' ,
|
status INT COMMENT '状态;1-待支付2-待发货3-待收货4-已完成5-退款中6-已退款7-已取消' ,
|
cancel_type INT COMMENT '取消类型;1-超时未支付2-主动取消' ,
|
name VARCHAR(32) COMMENT '联系人' ,
|
phone VARCHAR(32) COMMENT '联系方式' ,
|
address TEXT COMMENT '收货地址' ,
|
longitude VARCHAR(255) COMMENT '经度' ,
|
latitude VARCHAR(255) COMMENT '纬度' ,
|
remark TEXT COMMENT '备注' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '订单表';
|
|
DROP TABLE IF EXISTS mall_order_item;
|
CREATE TABLE mall_order_item(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
order_id BIGINT COMMENT '订单ID' ,
|
sku_id BIGINT COMMENT 'skuID' ,
|
goods_id BIGINT COMMENT '商品ID' ,
|
goods_name VARCHAR(255) COMMENT '商品名称' ,
|
style_name VARCHAR(255) COMMENT '样式名称' ,
|
sku_name VARCHAR(255) COMMENT 'sku名称' ,
|
cnt INT COMMENT '数量' ,
|
price DECIMAL(20,2) COMMENT '单价' ,
|
amount DECIMAL(20,2) COMMENT '金额' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '订单明细表';
|
|
DROP TABLE IF EXISTS mall_money_flow;
|
CREATE TABLE mall_money_flow(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
amount DECIMAL(20,2) COMMENT '金额' ,
|
type INT COMMENT '流水类型;1-分红收入2-业绩奖励3-订单支付4-退款' ,
|
order_no VARCHAR(32) COMMENT '订单号' ,
|
description TEXT COMMENT '描述' ,
|
remark TEXT COMMENT '备注' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '资金流水';
|
alter table mall_order_info add del_flag int default 2 null comment '1-已删除2-未删除' after latitude;
|
alter table mall_order_info add pay_order_no varchar(500) null comment '支付订单号' after pay_method;
|
alter table mall_order_info add pay_result int null comment '支付结果 1-成功2-未成功' after pay_order_no;
|
|
alter table mall_member add trade_password varchar(500) null comment '支付密码' after password;
|
|
DROP TABLE IF EXISTS mall_member_wallet;
|
CREATE TABLE mall_member_wallet(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
balance DECIMAL(20,2) COMMENT '余额' ,
|
member_id bigint null comment '用户ID',
|
PRIMARY KEY (ID)
|
) COMMENT = '用户钱包';
|
alter table mall_order_item add sku_image varchar(1000) null comment 'sku图片' after sku_name;
|
|
DROP TABLE IF EXISTS mall_express_info;
|
CREATE TABLE mall_express_info(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
order_id BIGINT COMMENT '订单ID' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
express_no VARCHAR(255) COMMENT '物流单号' ,
|
express_com VARCHAR(255) COMMENT '物流公司' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '物流信息表';
|
|
alter table mall_order_info add receiving_time datetime null comment '收货时间';
|
|
DROP TABLE IF EXISTS mall_order_refund;
|
CREATE TABLE mall_order_refund(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
member_id BIGINT COMMENT '用户ID' ,
|
order_id BIGINT COMMENT '订单ID' ,
|
reason VARCHAR(255) COMMENT '退款原因' ,
|
desp TEXT COMMENT '描述' ,
|
remark TEXT COMMENT '备注' ,
|
status INT COMMENT '状态;1-已申请2-同意退款3-驳回退款4-已提交物流信息5-已退款' ,
|
express_no VARCHAR(255) COMMENT '物流单号' ,
|
express_com VARCHAR(255) COMMENT '物流公司' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '订单退款';
|
|
DROP TABLE IF EXISTS mall_order_refund_operation;
|
CREATE TABLE mall_order_refund_operation(
|
REVISION INT COMMENT '乐观锁' ,
|
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
|
CREATED_TIME DATETIME COMMENT '创建时间' ,
|
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
|
UPDATED_TIME DATETIME COMMENT '更新时间' ,
|
ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
|
refund_id BIGINT COMMENT '退款ID' ,
|
order_id BIGINT COMMENT '订单ID' ,
|
content TEXT COMMENT '操作内容' ,
|
PRIMARY KEY (ID)
|
) COMMENT = '退款操作明细';
|
|
alter table mall_order_refund add type int null comment '1-仅退款不退货2-退款退货';
|
alter table mall_order_refund add before_status int null comment '订单退款前状态';
|
