-
Notifications
You must be signed in to change notification settings - Fork 5
/
init-data(user-orders)-mysql.sql
126 lines (109 loc) · 4.84 KB
/
init-data(user-orders)-mysql.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
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50624
Source Host : localhost:3306
Source Database : bee
Target Server Type : MYSQL
Target Server Version : 50624
File Encoding : 65001
Date: 2020-03-02 16:42:40
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`total` decimal(10,2) NOT NULL,
`createtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`sequence` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`abc` varchar(20) DEFAULT NULL,
`updatetime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100004 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('100001', 'bee', 'Bee(ORM Framework)', '95.01', '2020-03-02 11:29:28', 'test', '12345601', 'test1', '2020-03-02 16:34:19');
INSERT INTO `orders` VALUES ('100002', 'bee', 'Bee(ORM Framework)', '96.01', '2020-03-02 11:29:28', 'test', '12345602', 'test2', '2020-03-02 16:34:19');
INSERT INTO `orders` VALUES ('100003', 'bee', 'Bee', '97.01', '2020-03-02 11:29:28', 'test', '12345603', 'test3', '2020-03-02 16:34:19');
-- ----------------------------
-- Table structure for `test_user`
-- ----------------------------
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`last_name` varchar(60) COLLATE utf8_bin DEFAULT NULL,
`name` varchar(60) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(60) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(60) COLLATE utf8_bin NOT NULL,
`createtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=800003 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of test_user
-- ----------------------------
INSERT INTO `test_user` VALUES ('800001', 'beeUser@163.com', null, 'Bee', 'bee', 'bee', null);
INSERT INTO `test_user` VALUES ('800002', 'honey@163.com', null, 'Honey', 'honey', 'honey', '2020-03-02 16:41:33');
-- ----------------------------
-- Table structure for `leaf_alloc`
-- ----------------------------
DROP TABLE IF EXISTS `leaf_alloc`;
CREATE TABLE `leaf_alloc` (
`id` bigint(20) NOT NULL,
`biz_tag` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`max_id` bigint(20) NOT NULL DEFAULT '1',
`step` int(11) NOT NULL,
`description` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`version` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index_biz_tag` (`biz_tag`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of leaf_alloc
-- id(456537470402562) has global unique featrue.
-- see: org.teasoft.honey.distribution.SerialUniqueId
-- ----------------------------
INSERT INTO `leaf_alloc` VALUES ('456537470402562', 'test', '100', '100', 'test segment', '2020-07-04 22:42:09', '1');
INSERT INTO `leaf_alloc` VALUES ('456537470402563', 'order', '100', '100', 'order segment', '2020-07-04 22:42:10', '1');
DROP TABLE IF EXISTS `orders_202007`;
DROP TABLE IF EXISTS `leaf_alloc2`;
create table orders_202007 like orders;
create table leaf_alloc2 like leaf_alloc;
DROP TABLE IF EXISTS `test_primary_key`;
CREATE TABLE `test_primary_key` (
`myid` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`remark` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`remark2` varchar(100) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`myid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Procedure structure for `selectOrders`
-- ----------------------------
DROP PROCEDURE IF EXISTS `selectOrders`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectOrders`(in in_remark varchar(50))
BEGIN
select * from orders where remark=in_remark ;
END
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for `updateOrders`
-- ----------------------------
DROP PROCEDURE IF EXISTS `updateOrders`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateOrders`(in in_Id bigint(20))
BEGIN
UPDATE orders SET updatetime=now(),remark='change via CallableSql' where id=in_Id ;
END
;;
DELIMITER ;