-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathshop.sql
166 lines (135 loc) · 7.81 KB
/
shop.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
CREATE DATABASE `shop` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
USE `shop`;
DROP TABLE IF EXISTS `money_logs`;
DROP TABLE IF EXISTS `pays`;
DROP TABLE IF EXISTS `mq_consumer_logs`;
DROP TABLE IF EXISTS `mq_producer_logs`;
DROP TABLE IF EXISTS `order_goods`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `user_coupons`;
DROP TABLE IF EXISTS `coupons`;
DROP TABLE IF EXISTS `goods`;
DROP TABLE IF EXISTS `users`;
-- 用户表
CREATE TABLE `users` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '用户ID',
`name` VARCHAR(100) UNIQUE NOT NULL COMMENT '用户姓名',
`password` CHAR(64) NOT NULL NULL COMMENT '用户密码',
`mobile` CHAR(13) UNIQUE NOT NULL COMMENT '手机号',
`score` INT UNSIGNED DEFAULT NULL COMMENT '积分',
`money` DECIMAL(10,2) DEFAULT NULL COMMENT '用户余额',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 商品表
CREATE TABLE `goods` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '商品id',
`name` VARCHAR(100) NOT NULL COMMENT '商品名称',
`quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品库存',
`price` DECIMAL(10,2) DEFAULT 0 COMMENT '商品价格',
`desc` VARCHAR(255) DEFAULT NULL COMMENT '商品描述',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 优惠券表
CREATE TABLE `coupons` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '优惠券ID',
`price` DECIMAL(10,2) NOT NULL COMMENT '优惠券金额',
`condition` DECIMAL(10, 2) NOT NULL COMMENT '优惠券使用条件',
`count` INT UNSIGNED NOT NULL COMMENT '优惠券数量',
`expired` TIMESTAMP NULL DEFAULT NULL COMMENT '过期时间 NULL永久有效',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户优惠券表
CREATE TABLE `user_coupons` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'ID',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
`coupons_id` BIGINT UNSIGNED NOT NULL COMMENT '优惠券id',
`used` TIMESTAMP NULL DEFAULT NULL COMMENT '使用时间 NULL 未使用，已使用写入时间',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT user_coupon_user_key FOREIGN KEY(`user_id`) REFERENCES users(`id`),
CONSTRAINT user_coupon_counpon_key FOREIGN KEY(`coupons_id`) REFERENCES coupons(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单表
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '订单ID',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
`status` TINYINT UNSIGNED DEFAULT 0 COMMENT '订单状态 0未确认 1已确认 2已取消 3无效 4退款',
`pay_status` TINYINT UNSIGNED DEFAULT 0 COMMENT '支付状态 0未支付 1支付中 2已支付',
`shipping_status` TINYINT UNSIGNED DEFAULT 0 COMMENT '发货状态 0未发货 1已发货 2已收货',
`address` VARCHAR(255) DEFAULT NULL COMMENT '收货地址',
`consignee` VARCHAR(100) DEFAULT NULL COMMENT '收货人',
`order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单价格',
`shipping_fee` DECIMAL(10,2) DEFAULT 10 COMMENT '运费',
`coupon_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '优惠券ID',
`coupon_paid` DECIMAL(10,2) DEFAULT NULL COMMENT '优惠金额',
`pay_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '支付金额',
`confirmed` TIMESTAMP NULL DEFAULT NULL COMMENT '订单确认时间',
`pay_time` TIMESTAMP NULL DEFAULT NULL COMMENT '支付时间',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT order_user_key FOREIGN KEY(`user_id`) REFERENCES users(`id`),
CONSTRAINT order_coupon_key FOREIGN KEY(`coupon_id`) REFERENCES coupons(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单商品表
CREATE TABLE `order_goods` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '商品订单id',
`goods_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
`order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
`count` INT UNSIGNED DEFAULT NULL COMMENT '购买数量',
`price` DECIMAL(10,2) DEFAULT NULL COMMENT '商品价格',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT order_goods_good_key FOREIGN KEY(`goods_id`) REFERENCES goods(`id`),
CONSTRAINT order_goods_order_key FOREIGN KEY(`order_id`) REFERENCES orders(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `mq_consumer_logs` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '消费者日志id',
`msg_id` CHAR(50) DEFAULT NULL,
`group_name` CHAR(100) NOT NULL,
`msg_tag` CHAR(100) NOT NULL,
`msg_key` CHAR(100) NOT NULL,
`msg_body` VARCHAR(500) DEFAULT NULL,
`consumer_status` TINYINT UNSIGNED DEFAULT NULL COMMENT '0:正在处理;1:处理成功;2:处理失败',
`consumer_count` TINYINT UNSIGNED DEFAULT NULL COMMENT '消费次数',
`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `mq_producer_logs` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '生产者日志id',
`msg_id` CHAR(100) NOT NULL,
`group_name` CHAR(100) DEFAULT NULL,
`msg_topic` CHAR(100) DEFAULT NULL,
`msg_tag` CHAR(100) DEFAULT NULL,
`msg_key` CHAR(100) DEFAULT NULL,
`msg_body` VARCHAR(500) DEFAULT NULL,
`msg_status` TINYINT UNSIGNED DEFAULT NULL COMMENT '0:未处理;1:已经处理',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单支付表
CREATE TABLE `pays` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '支付id',
`sequence` CHAR(64) NOT NULL COMMENT '支付编号',
`order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '订单id',
`amount` DECIMAL(10,2) DEFAULT NULL COMMENT '支付金额',
`status` TINYINT UNSIGNED DEFAULT 0 COMMENT '是否已支付 0否 1是',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT pay_order_key FOREIGN KEY(`order_id`) REFERENCES orders(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户余额日志
CREATE TABLE `money_logs` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '主键id',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
`order_id` bigint UNSIGNED NOT NULL COMMENT '订单ID',
`type` TINYINT UNSIGNED NOT NULL COMMENT '日志类型 1订单付款 2 订单退款',
`amount` decimal(10,2) NOT NULL COMMENT '操作金额',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT money_log_user_key FOREIGN KEY(`user_id`) REFERENCES users(`id`),
CONSTRAINT money_log_order_key FOREIGN KEY(`order_id`) REFERENCES orders(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;