5.1sql語句
gulimall-oms.sql
drop table if exists oms_order;
drop table if exists oms_order_item;
drop table if exists oms_order_operate_history;
drop table if exists oms_order_return_apply;
drop table if exists oms_order_return_reason;
drop table if exists oms_order_setting;
drop table if exists oms_payment_info;
drop table if exists oms_refund_info;
/*==============================================================*/
/* Table: oms_order */
/*==============================================================*/
create table oms_order
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
order_sn char(32) comment '訂單號',
coupon_id bigint comment '使用的優(yōu)惠券',
create_time datetime comment 'create_time',
member_username varchar(200) comment '用戶名',
total_amount decimal(18,4) comment '訂單總額',
pay_amount decimal(18,4) comment '應(yīng)付總額',
freight_amount decimal(18,4) comment '運費金額',
promotion_amount decimal(18,4) comment '促銷優(yōu)化金額（促銷價、滿減、階梯價）',
integration_amount decimal(18,4) comment '積分抵扣金額',
coupon_amount decimal(18,4) comment '優(yōu)惠券抵扣金額',
discount_amount decimal(18,4) comment '后臺調(diào)整訂單使用的折扣金額',
pay_type tinyint comment '支付方式【1->支付寶；2->微信；3->銀聯(lián)； 4->貨到付款；】',
source_type tinyint comment '訂單來源[0->PC訂單；1->app訂單]',
status tinyint comment '訂單狀態(tài)【0->待付款；1->待發(fā)貨；2->已發(fā)貨；3->已完成；4->已關(guān)閉；5->無效訂單】',
delivery_company varchar(64) comment '物流公司(配送方式)',
delivery_sn varchar(64) comment '物流單號',
auto_confirm_day int comment '自動確認(rèn)時間（天）',
integration int comment '可以獲得的積分',
growth int comment '可以獲得的成長值',
bill_type tinyint comment '發(fā)票類型[0->不開發(fā)票；1->電子發(fā)票；2->紙質(zhì)發(fā)票]',
bill_header varchar(255) comment '發(fā)票抬頭',
bill_content varchar(255) comment '發(fā)票內(nèi)容',
bill_receiver_phone varchar(32) comment '收票人電話',
bill_receiver_email varchar(64) comment '收票人郵箱',
receiver_name varchar(100) comment '收貨人姓名',
receiver_phone varchar(32) comment '收貨人電話',
receiver_post_code varchar(32) comment '收貨人郵編',
receiver_province varchar(32) comment '省份/直轄市',
receiver_city varchar(32) comment '城市',
receiver_region varchar(32) comment '區(qū)',
receiver_detail_address varchar(200) comment '詳細(xì)地址',
note varchar(500) comment '訂單備注',
confirm_status tinyint comment '確認(rèn)收貨狀態(tài)[0->未確認(rèn)；1->已確認(rèn)]',
delete_status tinyint comment '刪除狀態(tài)【0->未刪除；1->已刪除】',
use_integration int comment '下單時使用的積分',
payment_time datetime comment '支付時間',
delivery_time datetime comment '發(fā)貨時間',
receive_time datetime comment '確認(rèn)收貨時間',
comment_time datetime comment '評價時間',
modify_time datetime comment '修改時間',
primary key (id)
);
alter table oms_order comment '訂單';
/*==============================================================*/
/* Table: oms_order_item */
/*==============================================================*/
create table oms_order_item
(
id bigint not null auto_increment comment 'id',
order_id bigint comment 'order_id',
order_sn char(32) comment 'order_sn',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment 'spu_name',
spu_pic varchar(500) comment 'spu_pic',
spu_brand varchar(200) comment '品牌',
category_id bigint comment '商品分類id',
sku_id bigint comment '商品sku編號',
sku_name varchar(255) comment '商品sku名字',
sku_pic varchar(500) comment '商品sku圖片',
sku_price decimal(18,4) comment '商品sku價格',
sku_quantity int comment '商品購買的數(shù)量',
sku_attrs_vals varchar(500) comment '商品銷售屬性組合（JSON）',
promotion_amount decimal(18,4) comment '商品促銷分解金額',
coupon_amount decimal(18,4) comment '優(yōu)惠券優(yōu)惠分解金額',
integration_amount decimal(18,4) comment '積分優(yōu)惠分解金額',
real_amount decimal(18,4) comment '該商品經(jīng)過優(yōu)惠后的分解金額',
gift_integration int comment '贈送積分',
gift_growth int comment '贈送成長值',
primary key (id)
);
alter table oms_order_item comment '訂單項信息';
/*==============================================================*/
/* Table: oms_order_operate_history */
/*==============================================================*/
create table oms_order_operate_history
(
id bigint not null auto_increment comment 'id',
order_id bigint comment '訂單id',
operate_man varchar(100) comment '操作人[用戶；系統(tǒng)；后臺管理員]',
create_time datetime comment '操作時間',
order_status tinyint comment '訂單狀態(tài)【0->待付款；1->待發(fā)貨；2->已發(fā)貨；3->已完成；4->已關(guān)閉；5->無效訂單】',
note varchar(500) comment '備注',
primary key (id)
);
alter table oms_order_operate_history comment '訂單操作歷史記錄';
/*==============================================================*/
/* Table: oms_order_return_apply */
/*==============================================================*/
create table oms_order_return_apply
(
id bigint not null auto_increment comment 'id',
order_id bigint comment 'order_id',
sku_id bigint comment '退貨商品id',
order_sn char(32) comment '訂單編號',
create_time datetime comment '申請時間',
member_username varchar(64) comment '會員用戶名',
return_amount decimal(18,4) comment '退款金額',
return_name varchar(100) comment '退貨人姓名',
return_phone varchar(20) comment '退貨人電話',
status tinyint(1) comment '申請狀態(tài)[0->待處理；1->退貨中；2->已完成；3->已拒絕]',
handle_time datetime comment '處理時間',
sku_img varchar(500) comment '商品圖片',
sku_name varchar(200) comment '商品名稱',
sku_brand varchar(200) comment '商品品牌',
sku_attrs_vals varchar(500) comment '商品銷售屬性(JSON)',
sku_count int comment '退貨數(shù)量',
sku_price decimal(18,4) comment '商品單價',
sku_real_price decimal(18,4) comment '商品實際支付單價',
reason varchar(200) comment '原因',
description述 varchar(500) comment '描述',
desc_pics varchar(2000) comment '憑證圖片，以逗號隔開',
handle_note varchar(500) comment '處理備注',
handle_man varchar(200) comment '處理人員',
receive_man varchar(100) comment '收貨人',
receive_time datetime comment '收貨時間',
receive_note varchar(500) comment '收貨備注',
receive_phone varchar(20) comment '收貨電話',
company_address varchar(500) comment '公司收貨地址',
primary key (id)
);
alter table oms_order_return_apply comment '訂單退貨申請';
/*==============================================================*/
/* Table: oms_order_return_reason */
/*==============================================================*/
create table oms_order_return_reason
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '退貨原因名',
sort int comment '排序',
status tinyint(1) comment '啟用狀態(tài)',
create_time datetime comment 'create_time',
primary key (id)
);
alter table oms_order_return_reason comment '退貨原因';
/*==============================================================*/
/* Table: oms_order_setting */
/*==============================================================*/
create table oms_order_setting
(
id bigint not null auto_increment comment 'id',
flash_order_overtime int comment '秒殺訂單超時關(guān)閉時間(分)',
normal_order_overtime int comment '正常訂單超時時間(分)',
confirm_overtime int comment '發(fā)貨后自動確認(rèn)收貨時間（天）',
finish_overtime int comment '自動完成交易時間，不能申請退貨（天）',
comment_overtime int comment '訂單完成后自動好評時間（天）',
member_level tinyint(2) comment '會員等級【0-不限會員等級，全部通用；其他-對應(yīng)的其他會員等級】',
primary key (id)
);
alter table oms_order_setting comment '訂單配置信息';
/*==============================================================*/
/* Table: oms_payment_info */
/*==============================================================*/
create table oms_payment_info
(
id bigint not null auto_increment comment 'id',
order_sn char(32) comment '訂單號（對外業(yè)務(wù)號）',
order_id bigint comment '訂單id',
alipay_trade_no varchar(50) comment '支付寶交易流水號',
total_amount decimal(18,4) comment '支付總金額',
subject varchar(200) comment '交易內(nèi)容',
payment_status varchar(20) comment '支付狀態(tài)',
create_time datetime comment '創(chuàng)建時間',
confirm_time datetime comment '確認(rèn)時間',
callback_content varchar(4000) comment '回調(diào)內(nèi)容',
callback_time datetime comment '回調(diào)時間',
primary key (id)
);
alter table oms_payment_info comment '支付信息表';
/*==============================================================*/
/* Table: oms_refund_info */
/*==============================================================*/
create table oms_refund_info
(
id bigint not null auto_increment comment 'id',
order_return_id bigint comment '退款的訂單',
refund decimal(18,4) comment '退款金額',
refund_sn varchar(64) comment '退款交易流水號',
refund_status tinyint(1) comment '退款狀態(tài)',
refund_channel tinyint comment '退款渠道[1-支付寶，2-微信，3-銀聯(lián)，4-匯款]',
refund_content varchar(5000),
primary key (id)
);
alter table oms_refund_info comment '退款信息';
gulimall-pms.sql
drop table if exists pms_attr;
drop table if exists pms_attr_attrgroup_relation;
drop table if exists pms_attr_group;
drop table if exists pms_brand;
drop table if exists pms_category;
drop table if exists pms_category_brand_relation;
drop table if exists pms_comment_replay;
drop table if exists pms_product_attr_value;
drop table if exists pms_sku_images;
drop table if exists pms_sku_info;
drop table if exists pms_sku_sale_attr_value;
drop table if exists pms_spu_comment;
drop table if exists pms_spu_images;
drop table if exists pms_spu_info;
drop table if exists pms_spu_info_desc;
/*==============================================================*/
/* Table: pms_attr */
/*==============================================================*/
create table pms_attr
(
attr_id bigint not null auto_increment comment '屬性id',
attr_name char(30) comment '屬性名',
search_type tinyint comment '是否需要檢索[0-不需要，1-需要]',
icon varchar(255) comment '屬性圖標(biāo)',
value_select char(255) comment '可選值列表[用逗號分隔]',
attr_type tinyint comment '屬性類型[0-銷售屬性，1-基本屬性，2-既是銷售屬性又是基本屬性]',
enable bigint comment '啟用狀態(tài)[0 - 禁用，1 - 啟用]',
catelog_id bigint comment '所屬分類',
show_desc tinyint comment '快速展示【是否展示在介紹上；0-否 1-是】，在sku中仍然可以調(diào)整',
primary key (attr_id)
);
alter table pms_attr comment '商品屬性';
/*==============================================================*/
/* Table: pms_attr_attrgroup_relation */
/*==============================================================*/
create table pms_attr_attrgroup_relation
(
id bigint not null auto_increment comment 'id',
attr_id bigint comment '屬性id',
attr_group_id bigint comment '屬性分組id',
attr_sort int comment '屬性組內(nèi)排序',
primary key (id)
);
alter table pms_attr_attrgroup_relation comment '屬性&屬性分組關(guān)聯(lián)';
/*==============================================================*/
/* Table: pms_attr_group */
/*==============================================================*/
create table pms_attr_group
(
attr_group_id bigint not null auto_increment comment '分組id',
attr_group_name char(20) comment '組名',
sort int comment '排序',
descript varchar(255) comment '描述',
icon varchar(255) comment '組圖標(biāo)',
catelog_id bigint comment '所屬分類id',
primary key (attr_group_id)
);
alter table pms_attr_group comment '屬性分組';
/*==============================================================*/
/* Table: pms_brand */
/*==============================================================*/
create table pms_brand
(
brand_id bigint not null auto_increment comment '品牌id',
name char(50) comment '品牌名',
logo varchar(2000) comment '品牌logo地址',
descript longtext comment '介紹',
show_status tinyint comment '顯示狀態(tài)[0-不顯示；1-顯示]',
first_letter char(1) comment '檢索首字母',
sort int comment '排序',
primary key (brand_id)
);
alter table pms_brand comment '品牌';
/*==============================================================*/
/* Table: pms_category */
/*==============================================================*/
create table pms_category
(
cat_id bigint not null auto_increment comment '分類id',
name char(50) comment '分類名稱',
parent_cid bigint comment '父分類id',
cat_level int comment '層級',
show_status tinyint comment '是否顯示[0-不顯示，1顯示]',
sort int comment '排序',
icon char(255) comment '圖標(biāo)地址',
product_unit char(50) comment '計量單位',
product_count int comment '商品數(shù)量',
primary key (cat_id)
);
alter table pms_category comment '商品三級分類';
/*==============================================================*/
/* Table: pms_category_brand_relation */
/*==============================================================*/
create table pms_category_brand_relation
(
id bigint not null auto_increment,
brand_id bigint comment '品牌id',
catelog_id bigint comment '分類id',
brand_name varchar(255),
catelog_name varchar(255),
primary key (id)
);
alter table pms_category_brand_relation comment '品牌分類關(guān)聯(lián)';
/*==============================================================*/
/* Table: pms_comment_replay */
/*==============================================================*/
create table pms_comment_replay
(
id bigint not null auto_increment comment 'id',
comment_id bigint comment '評論id',
reply_id bigint comment '回復(fù)id',
primary key (id)
);
alter table pms_comment_replay comment '商品評價回復(fù)關(guān)系';
/*==============================================================*/
/* Table: pms_product_attr_value */
/*==============================================================*/
create table pms_product_attr_value
(
id bigint not null auto_increment comment 'id',
spu_id bigint comment '商品id',
attr_id bigint comment '屬性id',
attr_name varchar(200) comment '屬性名',
attr_value varchar(200) comment '屬性值',
attr_sort int comment '順序',
quick_show tinyint comment '快速展示【是否展示在介紹上；0-否 1-是】',
primary key (id)
);
alter table pms_product_attr_value comment 'spu屬性值';
/*==============================================================*/
/* Table: pms_sku_images */
/*==============================================================*/
create table pms_sku_images
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
img_url varchar(255) comment '圖片地址',
img_sort int comment '排序',
default_img int comment '默認(rèn)圖[0 - 不是默認(rèn)圖，1 - 是默認(rèn)圖]',
primary key (id)
);
alter table pms_sku_images comment 'sku圖片';
/*==============================================================*/
/* Table: pms_sku_info */
/*==============================================================*/
create table pms_sku_info
(
sku_id bigint not null auto_increment comment 'skuId',
spu_id bigint comment 'spuId',
sku_name varchar(255) comment 'sku名稱',
sku_desc varchar(2000) comment 'sku介紹描述',
catalog_id bigint comment '所屬分類id',
brand_id bigint comment '品牌id',
sku_default_img varchar(255) comment '默認(rèn)圖片',
sku_title varchar(255) comment '標(biāo)題',
sku_subtitle varchar(2000) comment '副標(biāo)題',
price decimal(18,4) comment '價格',
sale_count bigint comment '銷量',
primary key (sku_id)
);
alter table pms_sku_info comment 'sku信息';
/*==============================================================*/
/* Table: pms_sku_sale_attr_value */
/*==============================================================*/
create table pms_sku_sale_attr_value
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
attr_id bigint comment 'attr_id',
attr_name varchar(200) comment '銷售屬性名',
attr_value varchar(200) comment '銷售屬性值',
attr_sort int comment '順序',
primary key (id)
);
alter table pms_sku_sale_attr_value comment 'sku銷售屬性&值';
/*==============================================================*/
/* Table: pms_spu_comment */
/*==============================================================*/
create table pms_spu_comment
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment '商品名字',
member_nick_name varchar(255) comment '會員昵稱',
star tinyint(1) comment '星級',
member_ip varchar(64) comment '會員ip',
create_time datetime comment '創(chuàng)建時間',
show_status tinyint(1) comment '顯示狀態(tài)[0-不顯示，1-顯示]',
spu_attributes varchar(255) comment '購買時屬性組合',
likes_count int comment '點贊數(shù)',
reply_count int comment '回復(fù)數(shù)',
resources varchar(1000) comment '評論圖片/視頻[json數(shù)據(jù)；[{type:文件類型,url:資源路徑}]]',
content text comment '內(nèi)容',
member_icon varchar(255) comment '用戶頭像',
comment_type tinyint comment '評論類型[0 - 對商品的直接評論，1 - 對評論的回復(fù)]',
primary key (id)
);
alter table pms_spu_comment comment '商品評價';
/*==============================================================*/
/* Table: pms_spu_images */
/*==============================================================*/
create table pms_spu_images
(
id bigint not null auto_increment comment 'id',
spu_id bigint comment 'spu_id',
img_name varchar(200) comment '圖片名',
img_url varchar(255) comment '圖片地址',
img_sort int comment '順序',
default_img tinyint comment '是否默認(rèn)圖',
primary key (id)
);
alter table pms_spu_images comment 'spu圖片';
/*==============================================================*/
/* Table: pms_spu_info */
/*==============================================================*/
create table pms_spu_info
(
id bigint not null auto_increment comment '商品id',
spu_name varchar(200) comment '商品名稱',
spu_description varchar(1000) comment '商品描述',
catalog_id bigint comment '所屬分類id',
brand_id bigint comment '品牌id',
weight decimal(18,4),
publish_status tinyint comment '上架狀態(tài)[0 - 下架，1 - 上架]',
create_time datetime,
update_time datetime,
primary key (id)
);
alter table pms_spu_info comment 'spu信息';
/*==============================================================*/
/* Table: pms_spu_info_desc */
/*==============================================================*/
create table pms_spu_info_desc
(
spu_id bigint not null comment '商品id',
decript longtext comment '商品介紹',
primary key (spu_id)
);
alter table pms_spu_info_desc comment 'spu信息介紹';
gulimall_sms.sql
drop table if exists sms_coupon;
drop table if exists sms_coupon_history;
drop table if exists sms_coupon_spu_category_relation;
drop table if exists sms_coupon_spu_relation;
drop table if exists sms_home_adv;
drop table if exists sms_home_subject;
drop table if exists sms_home_subject_spu;
drop table if exists sms_member_price;
drop table if exists sms_seckill_promotion;
drop table if exists sms_seckill_session;
drop table if exists sms_seckill_sku_notice;
drop table if exists sms_seckill_sku_relation;
drop table if exists sms_sku_full_reduction;
drop table if exists sms_sku_ladder;
drop table if exists sms_spu_bounds;
/*==============================================================*/
/* Table: sms_coupon */
/*==============================================================*/
create table sms_coupon
(
id bigint not null auto_increment comment 'id',
coupon_type tinyint(1) comment '優(yōu)惠卷類型[0->全場贈券；1->會員贈券；2->購物贈券；3->注冊贈券]',
coupon_img varchar(2000) comment '優(yōu)惠券圖片',
coupon_name varchar(100) comment '優(yōu)惠卷名字',
num int comment '數(shù)量',
amount decimal(18,4) comment '金額',
per_limit int comment '每人限領(lǐng)張數(shù)',
min_point decimal(18,4) comment '使用門檻',
start_time datetime comment '開始時間',
end_time datetime comment '結(jié)束時間',
use_type tinyint(1) comment '使用類型[0->全場通用；1->指定分類；2->指定商品]',
note varchar(200) comment '備注',
publish_count int(11) comment '發(fā)行數(shù)量',
use_count int(11) comment '已使用數(shù)量',
receive_count int(11) comment '領(lǐng)取數(shù)量',
enable_start_time datetime comment '可以領(lǐng)取的開始日期',
enable_end_time datetime comment '可以領(lǐng)取的結(jié)束日期',
code varchar(64) comment '優(yōu)惠碼',
member_level tinyint(1) comment '可以領(lǐng)取的會員等級[0->不限等級，其他-對應(yīng)等級]',
publish tinyint(1) comment '發(fā)布狀態(tài)[0-未發(fā)布，1-已發(fā)布]',
primary key (id)
);
alter table sms_coupon comment '優(yōu)惠券信息';
/*==============================================================*/
/* Table: sms_coupon_history */
/*==============================================================*/
create table sms_coupon_history
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
member_id bigint comment '會員id',
member_nick_name varchar(64) comment '會員名字',
get_type tinyint(1) comment '獲取方式[0->后臺贈送；1->主動領(lǐng)取]',
create_time datetime comment '創(chuàng)建時間',
use_type tinyint(1) comment '使用狀態(tài)[0->未使用；1->已使用；2->已過期]',
use_time datetime comment '使用時間',
order_id bigint comment '訂單id',
order_sn bigint comment '訂單號',
primary key (id)
);
alter table sms_coupon_history comment '優(yōu)惠券領(lǐng)取歷史記錄';
/*==============================================================*/
/* Table: sms_coupon_spu_category_relation */
/*==============================================================*/
create table sms_coupon_spu_category_relation
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
category_id bigint comment '產(chǎn)品分類id',
category_name varchar(64) comment '產(chǎn)品分類名稱',
primary key (id)
);
alter table sms_coupon_spu_category_relation comment '優(yōu)惠券分類關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_coupon_spu_relation */
/*==============================================================*/
create table sms_coupon_spu_relation
(
id bigint not null auto_increment comment 'id',
coupon_id bigint comment '優(yōu)惠券id',
spu_id bigint comment 'spu_id',
spu_name varchar(255) comment 'spu_name',
primary key (id)
);
alter table sms_coupon_spu_relation comment '優(yōu)惠券與產(chǎn)品關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_home_adv */
/*==============================================================*/
create table sms_home_adv
(
id bigint not null auto_increment comment 'id',
name varchar(100) comment '名字',
pic varchar(500) comment '圖片地址',
start_time datetime comment '開始時間',
end_time datetime comment '結(jié)束時間',
status tinyint(1) comment '狀態(tài)',
click_count int comment '點擊數(shù)',
url varchar(500) comment '廣告詳情連接地址',
note varchar(500) comment '備注',
sort int comment '排序',
publisher_id bigint comment '發(fā)布者',
auth_id bigint comment '審核者',
primary key (id)
);
alter table sms_home_adv comment '首頁輪播廣告';
/*==============================================================*/
/* Table: sms_home_subject */
/*==============================================================*/
create table sms_home_subject
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '專題名字',
title varchar(255) comment '專題標(biāo)題',
sub_title varchar(255) comment '專題副標(biāo)題',
status tinyint(1) comment '顯示狀態(tài)',
url varchar(500) comment '詳情連接',
sort int comment '排序',
img varchar(500) comment '專題圖片地址',
primary key (id)
);
alter table sms_home_subject comment '首頁專題表【jd首頁下面很多專題，每個專題鏈接新的頁面，展示專題商品信息】';
/*==============================================================*/
/* Table: sms_home_subject_spu */
/*==============================================================*/
create table sms_home_subject_spu
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '專題名字',
subject_id bigint comment '專題id',
spu_id bigint comment 'spu_id',
sort int comment '排序',
primary key (id)
);
alter table sms_home_subject_spu comment '專題商品';
/*==============================================================*/
/* Table: sms_member_price */
/*==============================================================*/
create table sms_member_price
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'sku_id',
member_level_id bigint comment '會員等級id',
member_level_name varchar(100) comment '會員等級名',
member_price decimal(18,4) comment '會員對應(yīng)價格',
add_other tinyint(1) comment '可否疊加其他優(yōu)惠[0-不可疊加優(yōu)惠，1-可疊加]',
primary key (id)
);
alter table sms_member_price comment '商品會員價格';
/*==============================================================*/
/* Table: sms_seckill_promotion */
/*==============================================================*/
create table sms_seckill_promotion
(
id bigint not null auto_increment comment 'id',
title varchar(255) comment '活動標(biāo)題',
start_time datetime comment '開始日期',
end_time datetime comment '結(jié)束日期',
status tinyint comment '上下線狀態(tài)',
create_time datetime comment '創(chuàng)建時間',
user_id bigint comment '創(chuàng)建人',
primary key (id)
);
alter table sms_seckill_promotion comment '秒殺活動';
/*==============================================================*/
/* Table: sms_seckill_session */
/*==============================================================*/
create table sms_seckill_session
(
id bigint not null auto_increment comment 'id',
name varchar(200) comment '場次名稱',
start_time datetime comment '每日開始時間',
end_time datetime comment '每日結(jié)束時間',
status tinyint(1) comment '啟用狀態(tài)',
create_time datetime comment '創(chuàng)建時間',
primary key (id)
);
alter table sms_seckill_session comment '秒殺活動場次';
/*==============================================================*/
/* Table: sms_seckill_sku_notice */
/*==============================================================*/
create table sms_seckill_sku_notice
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
sku_id bigint comment 'sku_id',
session_id bigint comment '活動場次id',
subcribe_time datetime comment '訂閱時間',
send_time datetime comment '發(fā)送時間',
notice_type tinyint(1) comment '通知方式[0-短信，1-郵件]',
primary key (id)
);
alter table sms_seckill_sku_notice comment '秒殺商品通知訂閱';
/*==============================================================*/
/* Table: sms_seckill_sku_relation */
/*==============================================================*/
create table sms_seckill_sku_relation
(
id bigint not null auto_increment comment 'id',
promotion_id bigint comment '活動id',
promotion_session_id bigint comment '活動場次id',
sku_id bigint comment '商品id',
seckill_price decimal comment '秒殺價格',
seckill_count decimal comment '秒殺總量',
seckill_limit decimal comment '每人限購數(shù)量',
seckill_sort int comment '排序',
primary key (id)
);
alter table sms_seckill_sku_relation comment '秒殺活動商品關(guān)聯(lián)';
/*==============================================================*/
/* Table: sms_sku_full_reduction */
/*==============================================================*/
create table sms_sku_full_reduction
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'spu_id',
full_price decimal(18,4) comment '滿多少',
reduce_price decimal(18,4) comment '減多少',
add_other tinyint(1) comment '是否參與其他優(yōu)惠',
primary key (id)
);
alter table sms_sku_full_reduction comment '商品滿減信息';
/*==============================================================*/
/* Table: sms_sku_ladder */
/*==============================================================*/
create table sms_sku_ladder
(
id bigint not null auto_increment comment 'id',
sku_id bigint comment 'spu_id',
full_count int comment '滿幾件',
discount decimal(4,2) comment '打幾折',
price decimal(18,4) comment '折后價',
add_other tinyint(1) comment '是否疊加其他優(yōu)惠[0-不可疊加，1-可疊加]',
primary key (id)
);
alter table sms_sku_ladder comment '商品階梯價格';
/*==============================================================*/
/* Table: sms_spu_bounds */
/*==============================================================*/
create table sms_spu_bounds
(
id bigint not null auto_increment comment 'id',
spu_id bigint,
grow_bounds decimal(18,4) comment '成長積分',
buy_bounds decimal(18,4) comment '購物積分',
work tinyint(1) comment '優(yōu)惠生效情況[1111（四個狀態(tài)位，從右到左）;0 - 無優(yōu)惠，成長積分是否贈送;1 - 無優(yōu)惠，購物積分是否贈送;2 - 有優(yōu)惠，成長積分是否贈送;3 - 有優(yōu)惠，購物積分是否贈送【狀態(tài)位0：不贈送，1：贈送】]',
primary key (id)
);
alter table sms_spu_bounds comment '商品spu積分設(shè)置';
gulimall_ums.sql
drop table if exists ums_growth_change_history;
drop table if exists ums_integration_change_history;
drop table if exists ums_member;
drop table if exists ums_member_collect_spu;
drop table if exists ums_member_collect_subject;
drop table if exists ums_member_level;
drop table if exists ums_member_login_log;
drop table if exists ums_member_receive_address;
drop table if exists ums_member_statistics_info;
/*==============================================================*/
/* Table: ums_growth_change_history */
/*==============================================================*/
create table ums_growth_change_history
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment 'create_time',
change_count int comment '改變的值（正負(fù)計數(shù)）',
note varchar(0) comment '備注',
source_type tinyint comment '積分來源[0-購物，1-管理員修改]',
primary key (id)
);
alter table ums_growth_change_history comment '成長值變化歷史記錄';
/*==============================================================*/
/* Table: ums_integration_change_history */
/*==============================================================*/
create table ums_integration_change_history
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment 'create_time',
change_count int comment '變化的值',
note varchar(255) comment '備注',
source_tyoe tinyint comment '來源[0->購物；1->管理員修改;2->活動]',
primary key (id)
);
alter table ums_integration_change_history comment '積分變化歷史記錄';
/*==============================================================*/
/* Table: ums_member */
/*==============================================================*/
create table ums_member
(
id bigint not null auto_increment comment 'id',
level_id bigint comment '會員等級id',
username char(64) comment '用戶名',
password varchar(64) comment '密碼',
nickname varchar(64) comment '昵稱',
mobile varchar(20) comment '手機號碼',
email varchar(64) comment '郵箱',
header varchar(500) comment '頭像',
gender tinyint comment '性別',
birth date comment '生日',
city varchar(500) comment '所在城市',
job varchar(255) comment '職業(yè)',
sign varchar(255) comment '個性簽名',
source_type tinyint comment '用戶來源',
integration int comment '積分',
growth int comment '成長值',
status tinyint comment '啟用狀態(tài)',
create_time datetime comment '注冊時間',
primary key (id)
);
alter table ums_member comment '會員';
/*==============================================================*/
/* Table: ums_member_collect_spu */
/*==============================================================*/
create table ums_member_collect_spu
(
id bigint not null comment 'id',
member_id bigint comment '會員id',
spu_id bigint comment 'spu_id',
spu_name varchar(500) comment 'spu_name',
spu_img varchar(500) comment 'spu_img',
create_time datetime comment 'create_time',
primary key (id)
);
alter table ums_member_collect_spu comment '會員收藏的商品';
/*==============================================================*/
/* Table: ums_member_collect_subject */
/*==============================================================*/
create table ums_member_collect_subject
(
id bigint not null auto_increment comment 'id',
subject_id bigint comment 'subject_id',
subject_name varchar(255) comment 'subject_name',
subject_img varchar(500) comment 'subject_img',
subject_urll varchar(500) comment '活動url',
primary key (id)
);
alter table ums_member_collect_subject comment '會員收藏的專題活動';
/*==============================================================*/
/* Table: ums_member_level */
/*==============================================================*/
create table ums_member_level
(
id bigint not null auto_increment comment 'id',
name varchar(100) comment '等級名稱',
growth_point int comment '等級需要的成長值',
default_status tinyint comment '是否為默認(rèn)等級[0->不是；1->是]',
free_freight_point decimal(18,4) comment '免運費標(biāo)準(zhǔn)',
comment_growth_point int comment '每次評價獲取的成長值',
priviledge_free_freight tinyint comment '是否有免郵特權(quán)',
priviledge_member_price tinyint comment '是否有會員價格特權(quán)',
priviledge_birthday tinyint comment '是否有生日特權(quán)',
note varchar(255) comment '備注',
primary key (id)
);
alter table ums_member_level comment '會員等級';
/*==============================================================*/
/* Table: ums_member_login_log */
/*==============================================================*/
create table ums_member_login_log
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
create_time datetime comment '創(chuàng)建時間',
ip varchar(64) comment 'ip',
city varchar(64) comment 'city',
login_type tinyint(1) comment '登錄類型[1-web，2-app]',
primary key (id)
);
alter table ums_member_login_log comment '會員登錄記錄';
/*==============================================================*/
/* Table: ums_member_receive_address */
/*==============================================================*/
create table ums_member_receive_address
(
id bigint not null auto_increment comment 'id',
member_id bigint comment 'member_id',
name varchar(255) comment '收貨人姓名',
phone varchar(64) comment '電話',
post_code varchar(64) comment '郵政編碼',
province varchar(100) comment '省份/直轄市',
city varchar(100) comment '城市',
region varchar(100) comment '區(qū)',
detail_address varchar(255) comment '詳細(xì)地址(街道)',
areacode varchar(15) comment '省市區(qū)代碼',
default_status tinyint(1) comment '是否默認(rèn)',
primary key (id)
);
alter table ums_member_receive_address comment '會員收貨地址';
/*==============================================================*/
/* Table: ums_member_statistics_info */
/*==============================================================*/
create table ums_member_statistics_info
(
id bigint not null auto_increment comment 'id',
member_id bigint comment '會員id',
consume_amount decimal(18,4) comment '累計消費金額',
coupon_amount decimal(18,4) comment '累計優(yōu)惠金額',
order_count int comment '訂單數(shù)量',
coupon_count int comment '優(yōu)惠券數(shù)量',
comment_count int comment '評價數(shù)',
return_order_count int comment '退貨數(shù)量',
login_count int comment '登錄次數(shù)',
attend_count int comment '關(guān)注數(shù)量',
fans_count int comment '粉絲數(shù)量',
collect_product_count int comment '收藏的商品數(shù)量',
collect_subject_count int comment '收藏的專題活動數(shù)量',
collect_comment_count int comment '收藏的評論數(shù)量',
invite_friend_count int comment '邀請的朋友數(shù)量',
primary key (id)
);
alter table ums_member_statistics_info comment '會員統(tǒng)計信息';
gulimall_wms.sql
/*
SQLyog Ultimate v11.25 (64 bit)
MySQL - 5.7.27 : Database - gulimall_wms
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gulimall_wms` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `gulimall_wms`;
/*Table structure for table `undo_log` */
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`branch_id` bigint(20) NOT NULL,
`xid` varchar(100) NOT NULL,
`context` varchar(128) NOT NULL,
`rollback_info` longblob NOT NULL,
`log_status` int(11) NOT NULL,
`log_created` datetime NOT NULL,
`log_modified` datetime NOT NULL,
`ext` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `undo_log` */
/*Table structure for table `wms_purchase` */
DROP TABLE IF EXISTS `wms_purchase`;
CREATE TABLE `wms_purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`assignee_id` bigint(20) DEFAULT NULL,
`assignee_name` varchar(255) DEFAULT NULL,
`phone` char(13) DEFAULT NULL,
`priority` int(4) DEFAULT NULL,
`status` int(4) DEFAULT NULL,
`ware_id` bigint(20) DEFAULT NULL,
`amount` decimal(18,4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采購信息';
/*Data for the table `wms_purchase` */
/*Table structure for table `wms_purchase_detail` */
DROP TABLE IF EXISTS `wms_purchase_detail`;
CREATE TABLE `wms_purchase_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`purchase_id` bigint(20) DEFAULT NULL COMMENT '采購單id',
`sku_id` bigint(20) DEFAULT NULL COMMENT '采購商品id',
`sku_num` int(11) DEFAULT NULL COMMENT '采購數(shù)量',
`sku_price` decimal(18,4) DEFAULT NULL COMMENT '采購金額',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉庫id',
`status` int(11) DEFAULT NULL COMMENT '狀態(tài)[0新建，1已分配，2正在采購，3已完成，4采購失敗]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*Data for the table `wms_purchase_detail` */
/*Table structure for table `wms_ware_info` */
DROP TABLE IF EXISTS `wms_ware_info`;
CREATE TABLE `wms_ware_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(255) DEFAULT NULL COMMENT '倉庫名',
`address` varchar(255) DEFAULT NULL COMMENT '倉庫地址',
`areacode` varchar(20) DEFAULT NULL COMMENT '區(qū)域編碼',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='倉庫信息';
/*Data for the table `wms_ware_info` */
/*Table structure for table `wms_ware_order_task` */
DROP TABLE IF EXISTS `wms_ware_order_task`;
CREATE TABLE `wms_ware_order_task` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`order_id` bigint(20) DEFAULT NULL COMMENT 'order_id',
`order_sn` varchar(255) DEFAULT NULL COMMENT 'order_sn',
`consignee` varchar(100) DEFAULT NULL COMMENT '收貨人',
`consignee_tel` char(15) DEFAULT NULL COMMENT '收貨人電話',
`delivery_address` varchar(500) DEFAULT NULL COMMENT '配送地址',
`order_comment` varchar(200) DEFAULT NULL COMMENT '訂單備注',
`payment_way` tinyint(1) DEFAULT NULL COMMENT '付款方式【 1:在線付款 2:貨到付款】',
`task_status` tinyint(2) DEFAULT NULL COMMENT '任務(wù)狀態(tài)',
`order_body` varchar(255) DEFAULT NULL COMMENT '訂單描述',
`tracking_no` char(30) DEFAULT NULL COMMENT '物流單號',
`create_time` datetime DEFAULT NULL COMMENT 'create_time',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉庫id',
`task_comment` varchar(500) DEFAULT NULL COMMENT '工作單備注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='庫存工作單';
/*Data for the table `wms_ware_order_task` */
/*Table structure for table `wms_ware_order_task_detail` */
DROP TABLE IF EXISTS `wms_ware_order_task_detail`;
CREATE TABLE `wms_ware_order_task_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
`sku_name` varchar(255) DEFAULT NULL COMMENT 'sku_name',
`sku_num` int(11) DEFAULT NULL COMMENT '購買個數(shù)',
`task_id` bigint(20) DEFAULT NULL COMMENT '工作單id',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉庫id',
`lock_status` int(1) DEFAULT NULL COMMENT '1-已鎖定 2-已解鎖 3-扣減',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='庫存工作單';
/*Data for the table `wms_ware_order_task_detail` */
/*Table structure for table `wms_ware_sku` */
DROP TABLE IF EXISTS `wms_ware_sku`;
CREATE TABLE `wms_ware_sku` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
`ware_id` bigint(20) DEFAULT NULL COMMENT '倉庫id',
`stock` int(11) DEFAULT NULL COMMENT '庫存數(shù)',
`sku_name` varchar(200) DEFAULT NULL COMMENT 'sku_name',
`stock_locked` int(11) DEFAULT '0' COMMENT '鎖定庫存',
PRIMARY KEY (`id`),
KEY `sku_id` (`sku_id`) USING BTREE,
KEY `ware_id` (`ware_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品庫存';
/*Data for the table `wms_ware_sku` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;