-
Notifications
You must be signed in to change notification settings - Fork 4k
/
Copy pathpartition_reorganize_innodb.result
356 lines (356 loc) · 15.3 KB
/
partition_reorganize_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
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
SET default_storage_engine=InnoDB;
SET SESSION innodb_strict_mode = ON;
SET GLOBAL innodb_file_per_table=ON;
#
# CREATE a table with SUBPARTITIONS
#
CREATE TABLE emp (
id INT NOT NULL,
store_name VARCHAR(30),
parts VARCHAR(30),
store_id INT
) engine InnoDB
PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
(
PARTITION northeast VALUES LESS THAN (50)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
(SUBPARTITION ne0, SUBPARTITION ne1),
PARTITION southwest VALUES LESS THAN (100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
(SUBPARTITION sw2, SUBPARTITION sw3)
);
INSERT INTO emp values(1,'Oracle','NUT',10);
INSERT INTO emp values(2,'SAP','BOLT',40);
INSERT INTO emp values(3,'IBM','NAIL',60);
INSERT INTO emp values(4,'SUN','SCREW',90);
SELECT * FROM emp;
id store_name parts store_id
1 Oracle NUT 10
2 SAP BOLT 40
3 IBM NAIL 60
4 SUN SCREW 90
SHOW CREATE TABLE emp;
Table Create Table
emp CREATE TABLE `emp` (
`id` int NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`store_id`)
SUBPARTITION BY HASH (`store_id`)
(PARTITION northeast VALUES LESS THAN (50)
(SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast/' ENGINE = InnoDB,
SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast/' ENGINE = InnoDB),
PARTITION southwest VALUES LESS THAN (100)
(SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest/' ENGINE = InnoDB,
SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest/' ENGINE = InnoDB)) */
=== information_schema.innodb_tables and innodb_tablespaces ===
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
test/emp#p#northeast#sp#ne0 test/emp#p#northeast#sp#ne0 97 7 Dynamic 0 Single
test/emp#p#northeast#sp#ne1 test/emp#p#northeast#sp#ne1 97 7 Dynamic 0 Single
test/emp#p#southwest#sp#sw2 test/emp#p#southwest#sp#sw2 97 7 Dynamic 0 Single
test/emp#p#southwest#sp#sw3 test/emp#p#southwest#sp#sw3 97 7 Dynamic 0 Single
=== information_schema.innodb_tablespaces and innodb_datafiles ===
Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
test/emp#p#northeast#sp#ne0 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne0.ibd
test/emp#p#northeast#sp#ne1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne1.ibd
test/emp#p#southwest#sp#sw2 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw2.ibd
test/emp#p#southwest#sp#sw3 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw3.ibd
=== information_schema.files ===
Space_Name File_Type Engine Status Tablespace_Name Path
mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
test/emp#p#northeast#sp#ne0 TABLESPACE InnoDB NORMAL test/emp#p#northeast#sp#ne0 MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne0.ibd
test/emp#p#northeast#sp#ne1 TABLESPACE InnoDB NORMAL test/emp#p#northeast#sp#ne1 MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne1.ibd
test/emp#p#southwest#sp#sw2 TABLESPACE InnoDB NORMAL test/emp#p#southwest#sp#sw2 MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw2.ibd
test/emp#p#southwest#sp#sw3 TABLESPACE InnoDB NORMAL test/emp#p#southwest#sp#sw3 MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw3.ibd
---- MYSQLD_DATADIR/test
---- MYSQL_TMP_DIR/alt_dir_northeast/test
emp#p#northeast#sp#ne0.ibd
emp#p#northeast#sp#ne1.ibd
---- MYSQL_TMP_DIR/alt_dir_southwest/test
emp#p#southwest#sp#sw2.ibd
emp#p#southwest#sp#sw3.ibd
#
# REORGANIZE the PARTITIONS and SUBPARTITIONS
#
ALTER TABLE emp REORGANIZE PARTITION northeast INTO
(
PARTITION east VALUES LESS THAN (25)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
(SUBPARTITION e0, SUBPARTITION e1),
PARTITION north VALUES LESS THAN (50)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
(SUBPARTITION n0, SUBPARTITION n1)
);
ALTER TABLE emp REORGANIZE PARTITION southwest INTO
(
PARTITION west VALUES LESS THAN (75)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
(SUBPARTITION w0, SUBPARTITION w1),
PARTITION south VALUES LESS THAN (100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
(SUBPARTITION s0, SUBPARTITION s1)
);
SELECT * FROM emp;
id store_name parts store_id
1 Oracle NUT 10
2 SAP BOLT 40
3 IBM NAIL 60
4 SUN SCREW 90
SHOW CREATE TABLE emp;
Table Create Table
emp CREATE TABLE `emp` (
`id` int NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`store_id`)
SUBPARTITION BY HASH (`store_id`)
(PARTITION east VALUES LESS THAN (25)
(SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB),
PARTITION north VALUES LESS THAN (50)
(SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB),
PARTITION west VALUES LESS THAN (75)
(SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB,
SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB),
PARTITION south VALUES LESS THAN (100)
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = InnoDB,
SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = InnoDB)) */
=== information_schema.innodb_tables and innodb_tablespaces ===
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
test/emp#p#east#sp#e0 test/emp#p#east#sp#e0 97 7 Dynamic 0 Single
test/emp#p#east#sp#e1 test/emp#p#east#sp#e1 97 7 Dynamic 0 Single
test/emp#p#north#sp#n0 test/emp#p#north#sp#n0 97 7 Dynamic 0 Single
test/emp#p#north#sp#n1 test/emp#p#north#sp#n1 97 7 Dynamic 0 Single
test/emp#p#south#sp#s0 test/emp#p#south#sp#s0 97 7 Dynamic 0 Single
test/emp#p#south#sp#s1 test/emp#p#south#sp#s1 97 7 Dynamic 0 Single
test/emp#p#west#sp#w0 test/emp#p#west#sp#w0 97 7 Dynamic 0 Single
test/emp#p#west#sp#w1 test/emp#p#west#sp#w1 97 7 Dynamic 0 Single
=== information_schema.innodb_tablespaces and innodb_datafiles ===
Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
test/emp#p#east#sp#e0 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e0.ibd
test/emp#p#east#sp#e1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e1.ibd
test/emp#p#north#sp#n0 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n0.ibd
test/emp#p#north#sp#n1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n1.ibd
test/emp#p#south#sp#s0 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s0.ibd
test/emp#p#south#sp#s1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s1.ibd
test/emp#p#west#sp#w0 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w0.ibd
test/emp#p#west#sp#w1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w1.ibd
=== information_schema.files ===
Space_Name File_Type Engine Status Tablespace_Name Path
mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
test/emp#p#east#sp#e0 TABLESPACE InnoDB NORMAL test/emp#p#east#sp#e0 MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e0.ibd
test/emp#p#east#sp#e1 TABLESPACE InnoDB NORMAL test/emp#p#east#sp#e1 MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e1.ibd
test/emp#p#north#sp#n0 TABLESPACE InnoDB NORMAL test/emp#p#north#sp#n0 MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n0.ibd
test/emp#p#north#sp#n1 TABLESPACE InnoDB NORMAL test/emp#p#north#sp#n1 MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n1.ibd
test/emp#p#south#sp#s0 TABLESPACE InnoDB NORMAL test/emp#p#south#sp#s0 MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s0.ibd
test/emp#p#south#sp#s1 TABLESPACE InnoDB NORMAL test/emp#p#south#sp#s1 MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s1.ibd
test/emp#p#west#sp#w0 TABLESPACE InnoDB NORMAL test/emp#p#west#sp#w0 MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w0.ibd
test/emp#p#west#sp#w1 TABLESPACE InnoDB NORMAL test/emp#p#west#sp#w1 MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w1.ibd
---- MYSQLD_DATADIR/test
---- MYSQL_TMP_DIR/alt_dir_northeast/test
---- MYSQL_TMP_DIR/alt_dir_southwest/test
---- MYSQL_TMP_DIR/alt_dir_east/test
emp#p#east#sp#e0.ibd
emp#p#east#sp#e1.ibd
---- MYSQL_TMP_DIR/alt_dir_north/test
emp#p#north#sp#n0.ibd
emp#p#north#sp#n1.ibd
---- MYSQL_TMP_DIR/alt_dir_west/test
emp#p#west#sp#w0.ibd
emp#p#west#sp#w1.ibd
---- MYSQL_TMP_DIR/alt_dir_south/test
emp#p#south#sp#s0.ibd
emp#p#south#sp#s1.ibd
DROP TABLE emp;
#
# Cleanup
#
CREATE TABLE t1 (a INT) ENGINE = InnoDB
PARTITION BY RANGE (a)
(partition p0 values less than MAXVALUE);
INSERT INTO t1 VALUES (1), (11), (21), (33);
SELECT * FROM t1;
a
1
11
21
33
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
---- MYSQLD_DATADIR/test
t1#p#p0.ibd
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p10 VALUES LESS THAN MAXVALUE);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SELECT * FROM t1;
a
1
11
21
33
---- MYSQLD_DATADIR/test
t1#p#p0.ibd
t1#p#p10.ibd
DROP TABLE t1;
CREATE TABLE tsp (
a INT,
b VARCHAR(55),
PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY RANGE (a)
SUBPARTITION BY HASH(a)
(PARTITION p0 VALUES LESS THAN (10)
(SUBPARTITION sp00,
SUBPARTITION sp01,
SUBPARTITION sp02,
SUBPARTITION sp03,
SUBPARTITION sp04),
PARTITION p1 VALUES LESS THAN (20)
(SUBPARTITION sp10,
SUBPARTITION sp11,
SUBPARTITION sp12,
SUBPARTITION sp13,
SUBPARTITION sp14),
PARTITION p2 VALUES LESS THAN (100)
(SUBPARTITION sp20,
SUBPARTITION sp21,
SUBPARTITION sp22,
SUBPARTITION sp23,
SUBPARTITION sp24));
INSERT INTO tsp VALUES(1, 'one'), (5, 'five'), (15, 'fifteen'), (30, 'thirty'), (40, 'forty'), (61, 'sixty one'), (75, 'seventy five'), (79, 'seventy nine'), (81, 'eighty one'), (83, 'eighty three'), (99, 'ninety nine');
SELECT * FROM tsp PARTITION(p2);
a b
30 thirty
40 forty
75 seventy five
61 sixty one
81 eighty one
83 eighty three
79 seventy nine
99 ninety nine
---- MYSQLD_DATADIR/test
tsp#p#p0#sp#sp00.ibd
tsp#p#p0#sp#sp01.ibd
tsp#p#p0#sp#sp02.ibd
tsp#p#p0#sp#sp03.ibd
tsp#p#p0#sp#sp04.ibd
tsp#p#p1#sp#sp10.ibd
tsp#p#p1#sp#sp11.ibd
tsp#p#p1#sp#sp12.ibd
tsp#p#p1#sp#sp13.ibd
tsp#p#p1#sp#sp14.ibd
tsp#p#p2#sp#sp20.ibd
tsp#p#p2#sp#sp21.ibd
tsp#p#p2#sp#sp22.ibd
tsp#p#p2#sp#sp23.ibd
tsp#p#p2#sp#sp24.ibd
ALTER TABLE tsp REORGANIZE PARTITION p2 INTO(
PARTITION p20 VALUES LESS THAN(60) (SUBPARTITION sp200, SUBPARTITION sp201, SUBPARTITION sp202, SUBPARTITION sp203, SUBPARTITION sp204),
PARTITION p2 VALUES LESS THAN(100) (SUBPARTITION sp20, SUBPARTITION sp211, SUBPARTITION sp22, SUBPARTITION sp213, SUBPARTITION sp24));
SHOW CREATE TABLE tsp;
Table Create Table
tsp CREATE TABLE `tsp` (
`a` int NOT NULL,
`b` varchar(55) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
SUBPARTITION BY HASH (`a`)
(PARTITION p0 VALUES LESS THAN (10)
(SUBPARTITION sp00 ENGINE = InnoDB,
SUBPARTITION sp01 ENGINE = InnoDB,
SUBPARTITION sp02 ENGINE = InnoDB,
SUBPARTITION sp03 ENGINE = InnoDB,
SUBPARTITION sp04 ENGINE = InnoDB),
PARTITION p1 VALUES LESS THAN (20)
(SUBPARTITION sp10 ENGINE = InnoDB,
SUBPARTITION sp11 ENGINE = InnoDB,
SUBPARTITION sp12 ENGINE = InnoDB,
SUBPARTITION sp13 ENGINE = InnoDB,
SUBPARTITION sp14 ENGINE = InnoDB),
PARTITION p20 VALUES LESS THAN (60)
(SUBPARTITION sp200 ENGINE = InnoDB,
SUBPARTITION sp201 ENGINE = InnoDB,
SUBPARTITION sp202 ENGINE = InnoDB,
SUBPARTITION sp203 ENGINE = InnoDB,
SUBPARTITION sp204 ENGINE = InnoDB),
PARTITION p2 VALUES LESS THAN (100)
(SUBPARTITION sp20 ENGINE = InnoDB,
SUBPARTITION sp211 ENGINE = InnoDB,
SUBPARTITION sp22 ENGINE = InnoDB,
SUBPARTITION sp213 ENGINE = InnoDB,
SUBPARTITION sp24 ENGINE = InnoDB)) */
---- MYSQLD_DATADIR/test
tsp#p#p0#sp#sp00.ibd
tsp#p#p0#sp#sp01.ibd
tsp#p#p0#sp#sp02.ibd
tsp#p#p0#sp#sp03.ibd
tsp#p#p0#sp#sp04.ibd
tsp#p#p1#sp#sp10.ibd
tsp#p#p1#sp#sp11.ibd
tsp#p#p1#sp#sp12.ibd
tsp#p#p1#sp#sp13.ibd
tsp#p#p1#sp#sp14.ibd
tsp#p#p2#sp#sp20.ibd
tsp#p#p2#sp#sp211.ibd
tsp#p#p2#sp#sp213.ibd
tsp#p#p2#sp#sp22.ibd
tsp#p#p2#sp#sp24.ibd
tsp#p#p20#sp#sp200.ibd
tsp#p#p20#sp#sp201.ibd
tsp#p#p20#sp#sp202.ibd
tsp#p#p20#sp#sp203.ibd
tsp#p#p20#sp#sp204.ibd
SELECT * FROM tsp PARTITION(p2);
a b
75 seventy five
61 sixty one
81 eighty one
83 eighty three
79 seventy nine
99 ninety nine
SELECT * FROM tsp PARTITION(p20);
a b
30 thirty
40 forty
DROP TABLE tsp;