-
Notifications
You must be signed in to change notification settings - Fork 4k
/
Copy pathpartition_decimal_innodb.result
196 lines (196 loc) · 5.65 KB
/
partition_decimal_innodb.result
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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
create table t1 (a decimal(10,4) not null, primary key(a)) engine='InnoDB'
partition by key (a) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` decimal(10,4) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567);
select * from t1;
a
-1234.5670
123456.7899
-123456.7899
0.0000
-999999.9999
-1.5000
-1.0000
1.0000
1234.5670
999999.9999
1.5000
select * from t1 where a=1234.567;
a
1234.5670
delete from t1 where a=1234.567;
select * from t1;
a
-1234.5670
123456.7899
-123456.7899
0.0000
-999999.9999
-1.5000
-1.0000
1.0000
999999.9999
1.5000
drop table t1;
create table t2 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
partition by key (a) partitions 10;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` decimal(18,9) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
PARTITIONS 10 */
insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567);
select * from t2;
a
-1.000000000
1.500000000
-999999999.999999999
-1.500000000
1234.567000000
999999999.999999999
-1234.567000000
0.000000000
select * from t2 where a=1234.567;
a
1234.567000000
delete from t2 where a=1234.567;
select * from t2;
a
-1.000000000
1.500000000
-999999999.999999999
-1.500000000
999999999.999999999
-1234.567000000
0.000000000
delete from t2;
1024*3 inserts;
select count(*) from t2;
count(*)
3072
drop table t2;
create table t3 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
partition by range (floor(a)) subpartition by key (a) subpartitions 2 (
partition pa2 values less than (2),
partition pa4 values less than (4),
partition pa6 values less than (6),
partition pa8 values less than (8),
partition pa10 values less than (10)
);
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` decimal(18,9) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (floor(`a`))
SUBPARTITION BY KEY (a)
SUBPARTITIONS 2
(PARTITION pa2 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION pa4 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION pa6 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION pa8 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION pa10 VALUES LESS THAN (10) ENGINE = InnoDB) */
9*3 inserts;
insert into t3 values (9);
insert into t3 values (9+0.333333333);
insert into t3 values (9+0.755555555);
insert into t3 values (8);
insert into t3 values (8+0.333333333);
insert into t3 values (8+0.755555555);
insert into t3 values (7);
insert into t3 values (7+0.333333333);
insert into t3 values (7+0.755555555);
insert into t3 values (6);
insert into t3 values (6+0.333333333);
insert into t3 values (6+0.755555555);
insert into t3 values (5);
insert into t3 values (5+0.333333333);
insert into t3 values (5+0.755555555);
insert into t3 values (4);
insert into t3 values (4+0.333333333);
insert into t3 values (4+0.755555555);
insert into t3 values (3);
insert into t3 values (3+0.333333333);
insert into t3 values (3+0.755555555);
insert into t3 values (2);
insert into t3 values (2+0.333333333);
insert into t3 values (2+0.755555555);
insert into t3 values (1);
insert into t3 values (1+0.333333333);
insert into t3 values (1+0.755555555);
select count(*) from t3;
count(*)
27
drop table t3;
create table t4 (a decimal(18,9) not null, primary key(a)) engine='InnoDB'
partition by list (ceiling(a)) subpartition by key (a) subpartitions 2 (
partition pa2 values in (1,2),
partition pa4 values in (3,4),
partition pa6 values in (5,6),
partition pa8 values in (7,8),
partition pa10 values in (9,10)
);
show create table t4;
Table Create Table
t4 CREATE TABLE `t4` (
`a` decimal(18,9) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (ceiling(`a`))
SUBPARTITION BY KEY (a)
SUBPARTITIONS 2
(PARTITION pa2 VALUES IN (1,2) ENGINE = InnoDB,
PARTITION pa4 VALUES IN (3,4) ENGINE = InnoDB,
PARTITION pa6 VALUES IN (5,6) ENGINE = InnoDB,
PARTITION pa8 VALUES IN (7,8) ENGINE = InnoDB,
PARTITION pa10 VALUES IN (9,10) ENGINE = InnoDB) */
9*3 inserts;
insert into t4 values (9);
insert into t4 values (9+0.333333333);
insert into t4 values (9+0.755555555);
insert into t4 values (8);
insert into t4 values (8+0.333333333);
insert into t4 values (8+0.755555555);
insert into t4 values (7);
insert into t4 values (7+0.333333333);
insert into t4 values (7+0.755555555);
insert into t4 values (6);
insert into t4 values (6+0.333333333);
insert into t4 values (6+0.755555555);
insert into t4 values (5);
insert into t4 values (5+0.333333333);
insert into t4 values (5+0.755555555);
insert into t4 values (4);
insert into t4 values (4+0.333333333);
insert into t4 values (4+0.755555555);
insert into t4 values (3);
insert into t4 values (3+0.333333333);
insert into t4 values (3+0.755555555);
insert into t4 values (2);
insert into t4 values (2+0.333333333);
insert into t4 values (2+0.755555555);
insert into t4 values (1);
insert into t4 values (1+0.333333333);
insert into t4 values (1+0.755555555);
select count(*) from t4;
count(*)
27
drop table t4;