mysql-prize.sql
6.73 KB
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
/*
Navicat MySQL Data Transfer
Source Server : sdk test
Source Server Version : 50616
Source Host : rm-m5eg27hoe205fc0x2.mysql.rds.aliyuncs.com:3306
Source Database : csair_dp
Target Server Type : MYSQL
Target Server Version : 50616
File Encoding : 65001
Date: 2017-03-23 17:43:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `t_prize_info`
-- ----------------------------
DROP TABLE IF EXISTS `t_prize_info`;
CREATE TABLE `t_prize_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`prize_pool_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用ID',
`name` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '奖品的名称',
`number` int(11) NOT NULL DEFAULT '0' COMMENT '奖品总量',
`draw_number` int(11) NOT NULL DEFAULT '0' COMMENT '已经抽取的数量',
`prize_type` int(11) NOT NULL DEFAULT '1' COMMENT '奖品类型：1、实物；2、积分；',
`prize_image` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '图片地址',
`ext_information` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '扩展信息，存储相应奖品的个性信息。',
`probability` double NOT NULL COMMENT '中奖概率',
`lottery_draw_amount` int(11) DEFAULT NULL COMMENT '每周发放上限',
`positions` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '位置信息，针对大转盘设置。多个位置用“,”分割。',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_PRIZE_POOL_ID` (`prize_pool_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of t_prize_info
-- ----------------------------
-- ----------------------------
-- Table structure for `t_prize_lottery_record`
-- ----------------------------
DROP TABLE IF EXISTS `t_prize_lottery_record`;
CREATE TABLE `t_prize_lottery_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`prize_pool_id` varchar(32) COLLATE utf8mb4_bin NOT NULL,
`phone_number` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`lottery_platform` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
`lottery_day` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '抽奖日期',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_LO_PR_PH` (`lottery_platform`,`prize_pool_id`,`phone_number`),
KEY `IDX_LO_LO_PR_PH` (`lottery_platform`,`lottery_day`,`prize_pool_id`,`phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of t_prize_lottery_record
-- ----------------------------
-- ----------------------------
-- Table structure for `t_prize_pool`
-- ----------------------------
DROP TABLE IF EXISTS `t_prize_pool`;
CREATE TABLE `t_prize_pool` (
`id` varchar(32) COLLATE utf8mb4_bin NOT NULL,
`name` varchar(45) COLLATE utf8mb4_bin NOT NULL COMMENT '活动名称',
`desscription` text CHARACTER SET utf8mb4 COMMENT '活动描述',
`winning_rate` int(11) NOT NULL DEFAULT '1' COMMENT '1 表示 万分之一 中奖率，非负， 0<= r <= 10000，在这个范围之外取其极限。',
`deleted` tinyint(4) NOT NULL DEFAULT '1' COMMENT '互动的状态，1、进行中；0、已结束。',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_lottery_platform` tinyint(4) DEFAULT '2' COMMENT '1 全平台,2 pc/手机端平台',
`lottery_frequency` tinyint(4) NOT NULL COMMENT '参与间隔：1、整个活动；2、每天',
`lottery_times` int(11) NOT NULL COMMENT '抽奖次数',
`draw_times` int(11) DEFAULT NULL COMMENT '总中奖次数',
`draw_frequency` int(11) DEFAULT NULL COMMENT '中奖间隔：1、整个活动；2、每天；3、每周',
`draw_frequency_times` int(11) DEFAULT NULL COMMENT '中奖间隔内的中奖次数',
`is_white_rand` tinyint(4) DEFAULT '1' COMMENT '适用用户: 1 通用，2 白名单用户',
PRIMARY KEY (`id`),
KEY `IDX_IS_WHITE_RAND` (`is_white_rand`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of t_prize_pool
-- ----------------------------
-- ----------------------------
-- Table structure for `t_prize_white_info`
-- ----------------------------
DROP TABLE IF EXISTS `t_prize_white_info`;
CREATE TABLE `t_prize_white_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`prize_pool_id` varchar(32) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`online_time` timestamp NULL DEFAULT NULL COMMENT '上线时间',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `IDX_PHONE_NUMBER_ONLINE_TIME` (`phone_number`,`online_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_prize_white_info
-- ----------------------------
-- ----------------------------
-- Table structure for `t_prize_winning_record`
-- ----------------------------
DROP TABLE IF EXISTS `t_prize_winning_record`;
CREATE TABLE `t_prize_winning_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`prize_pool_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`phone_number` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`lottery_platform` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '抽奖平台',
`prize_id` int(11) NOT NULL,
`prize_name` varchar(45) COLLATE utf8mb4_bin NOT NULL COMMENT '奖品的名称',
`prize_type` int(11) NOT NULL DEFAULT '1' COMMENT '奖品类型：1、实物；2、积分；',
`prize_extends` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '扩展信息，存储相应奖品的个性信息。',
`prize_image` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`prize_no` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '奖品的编号，20位数字',
`winning_day` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '中奖日期',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_PR_PR_WI` (`prize_id`,`prize_pool_id`,`winning_day`),
KEY `IDX_PH_PR_WI` (`phone_number`,`prize_pool_id`,`winning_day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of t_prize_winning_record
-- ----------------------------