1
DROP TABLE IF EXISTS t1;
2
CREATE TABLE t1 (c1 TINYINT,name VARCHAR(30), purchased DATE)
3
PARTITION BY RANGE( YEAR(purchased) )
4
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
5
PARTITION p0 VALUES LESS THAN (1990) (
9
PARTITION p1 VALUES LESS THAN (2000) (
13
PARTITION p2 VALUES LESS THAN MAXVALUE (
18
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
19
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
20
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
21
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
22
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
23
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
24
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
25
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
26
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
27
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
28
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
29
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
30
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
31
SELECT * FROM t1 ORDER BY c1;
51
t1 CREATE TABLE `t1` (
52
`c1` tinyint(4) DEFAULT NULL,
53
`name` varchar(30) DEFAULT NULL,
54
`purchased` date DEFAULT NULL
55
) ENGINE=ENGINE DEFAULT CHARSET=latin1
56
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
57
SUBPARTITION BY HASH ( TO_DAYS(purchased))
58
(PARTITION p0 VALUES LESS THAN (1990)
59
(SUBPARTITION s0 ENGINE = ENGINE,
60
SUBPARTITION s1 ENGINE = ENGINE),
61
PARTITION p1 VALUES LESS THAN (2000)
62
(SUBPARTITION s2 ENGINE = ENGINE,
63
SUBPARTITION s3 ENGINE = ENGINE),
64
PARTITION p2 VALUES LESS THAN MAXVALUE
65
(SUBPARTITION s4 ENGINE = ENGINE,
66
SUBPARTITION s5 ENGINE = ENGINE)) */
70
CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
71
PARTITION BY RANGE( YEAR(purchased) )
72
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
73
PARTITION p0 VALUES LESS THAN (1990) (
77
PARTITION p1 VALUES LESS THAN (2000) (
81
PARTITION p2 VALUES LESS THAN MAXVALUE (
86
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
87
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
88
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
89
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
90
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
91
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
92
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
93
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
94
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
95
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
96
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
97
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
98
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
99
SELECT * FROM t1 ORDER BY c1;
117
SHOW CREATE TABLE t1;
119
t1 CREATE TABLE `t1` (
120
`c1` smallint(6) DEFAULT NULL,
121
`name` varchar(30) DEFAULT NULL,
122
`purchased` date DEFAULT NULL
123
) ENGINE=ENGINE DEFAULT CHARSET=latin1
124
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
125
SUBPARTITION BY HASH ( TO_DAYS(purchased))
126
(PARTITION p0 VALUES LESS THAN (1990)
127
(SUBPARTITION s0 ENGINE = ENGINE,
128
SUBPARTITION s1 ENGINE = ENGINE),
129
PARTITION p1 VALUES LESS THAN (2000)
130
(SUBPARTITION s2 ENGINE = ENGINE,
131
SUBPARTITION s3 ENGINE = ENGINE),
132
PARTITION p2 VALUES LESS THAN MAXVALUE
133
(SUBPARTITION s4 ENGINE = ENGINE,
134
SUBPARTITION s5 ENGINE = ENGINE)) */
138
CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
139
PARTITION BY RANGE( YEAR(purchased) )
140
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
141
PARTITION p0 VALUES LESS THAN (1990) (
145
PARTITION p1 VALUES LESS THAN (2000) (
149
PARTITION p2 VALUES LESS THAN MAXVALUE (
154
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
155
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
156
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
157
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
158
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
159
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
160
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
161
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
162
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
163
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
164
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
165
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
166
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
167
SELECT * FROM t1 ORDER BY c1;
185
SHOW CREATE TABLE t1;
187
t1 CREATE TABLE `t1` (
188
`c1` mediumint(9) DEFAULT NULL,
189
`name` varchar(30) DEFAULT NULL,
190
`purchased` date DEFAULT NULL
191
) ENGINE=ENGINE DEFAULT CHARSET=latin1
192
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
193
SUBPARTITION BY HASH ( TO_DAYS(purchased))
194
(PARTITION p0 VALUES LESS THAN (1990)
195
(SUBPARTITION s0 ENGINE = ENGINE,
196
SUBPARTITION s1 ENGINE = ENGINE),
197
PARTITION p1 VALUES LESS THAN (2000)
198
(SUBPARTITION s2 ENGINE = ENGINE,
199
SUBPARTITION s3 ENGINE = ENGINE),
200
PARTITION p2 VALUES LESS THAN MAXVALUE
201
(SUBPARTITION s4 ENGINE = ENGINE,
202
SUBPARTITION s5 ENGINE = ENGINE)) */
206
CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
207
PARTITION BY RANGE( YEAR(purchased) )
208
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
209
PARTITION p0 VALUES LESS THAN (1990) (
213
PARTITION p1 VALUES LESS THAN (2000) (
217
PARTITION p2 VALUES LESS THAN MAXVALUE (
222
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
223
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
224
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
225
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
226
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
227
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
228
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
229
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
230
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
231
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
232
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
233
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
234
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
235
SELECT * FROM t1 ORDER BY c1;
253
SHOW CREATE TABLE t1;
255
t1 CREATE TABLE `t1` (
256
`c1` int(11) DEFAULT NULL,
257
`name` varchar(30) DEFAULT NULL,
258
`purchased` date DEFAULT NULL
259
) ENGINE=ENGINE DEFAULT CHARSET=latin1
260
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
261
SUBPARTITION BY HASH ( TO_DAYS(purchased))
262
(PARTITION p0 VALUES LESS THAN (1990)
263
(SUBPARTITION s0 ENGINE = ENGINE,
264
SUBPARTITION s1 ENGINE = ENGINE),
265
PARTITION p1 VALUES LESS THAN (2000)
266
(SUBPARTITION s2 ENGINE = ENGINE,
267
SUBPARTITION s3 ENGINE = ENGINE),
268
PARTITION p2 VALUES LESS THAN MAXVALUE
269
(SUBPARTITION s4 ENGINE = ENGINE,
270
SUBPARTITION s5 ENGINE = ENGINE)) */
274
CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
275
PARTITION BY RANGE( YEAR(purchased) )
276
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
277
PARTITION p0 VALUES LESS THAN (1990) (
281
PARTITION p1 VALUES LESS THAN (2000) (
285
PARTITION p2 VALUES LESS THAN MAXVALUE (
290
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
291
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
292
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
293
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
294
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
295
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
296
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
297
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
298
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
299
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
300
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
301
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
302
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
303
SELECT * FROM t1 ORDER BY c1;
321
SHOW CREATE TABLE t1;
323
t1 CREATE TABLE `t1` (
324
`c1` int(11) DEFAULT NULL,
325
`name` varchar(30) DEFAULT NULL,
326
`purchased` date DEFAULT NULL
327
) ENGINE=ENGINE DEFAULT CHARSET=latin1
328
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
329
SUBPARTITION BY HASH ( TO_DAYS(purchased))
330
(PARTITION p0 VALUES LESS THAN (1990)
331
(SUBPARTITION s0 ENGINE = ENGINE,
332
SUBPARTITION s1 ENGINE = ENGINE),
333
PARTITION p1 VALUES LESS THAN (2000)
334
(SUBPARTITION s2 ENGINE = ENGINE,
335
SUBPARTITION s3 ENGINE = ENGINE),
336
PARTITION p2 VALUES LESS THAN MAXVALUE
337
(SUBPARTITION s4 ENGINE = ENGINE,
338
SUBPARTITION s5 ENGINE = ENGINE)) */
342
CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
343
PARTITION BY RANGE( YEAR(purchased) )
344
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
345
PARTITION p0 VALUES LESS THAN (1990) (
349
PARTITION p1 VALUES LESS THAN (2000) (
353
PARTITION p2 VALUES LESS THAN MAXVALUE (
358
INSERT INTO t1 VALUES(1,'abc','1994-01-01');
359
INSERT INTO t1 VALUES(2,'abc','1995-01-01');
360
INSERT INTO t1 VALUES(3,'abc','1996-01-01');
361
INSERT INTO t1 VALUES(4,'abc','1997-01-01');
362
INSERT INTO t1 VALUES(5,'abc','1998-01-01');
363
INSERT INTO t1 VALUES(6,'abc','1999-01-01');
364
INSERT INTO t1 VALUES(7,'abc','2000-01-01');
365
INSERT INTO t1 VALUES(8,'abc','2001-01-01');
366
INSERT INTO t1 VALUES(9,'abc','2002-01-01');
367
INSERT INTO t1 VALUES(10,'abc','2003-01-01');
368
INSERT INTO t1 VALUES(11,'abc','2004-01-01');
369
INSERT INTO t1 VALUES(12,'abc','2005-01-01');
370
INSERT INTO t1 VALUES(13,'abc','2006-01-01');
371
SELECT * FROM t1 ORDER BY c1;
389
SHOW CREATE TABLE t1;
391
t1 CREATE TABLE `t1` (
392
`c1` bigint(20) DEFAULT NULL,
393
`name` varchar(30) DEFAULT NULL,
394
`purchased` date DEFAULT NULL
395
) ENGINE=ENGINE DEFAULT CHARSET=latin1
396
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
397
SUBPARTITION BY HASH ( TO_DAYS(purchased))
398
(PARTITION p0 VALUES LESS THAN (1990)
399
(SUBPARTITION s0 ENGINE = ENGINE,
400
SUBPARTITION s1 ENGINE = ENGINE),
401
PARTITION p1 VALUES LESS THAN (2000)
402
(SUBPARTITION s2 ENGINE = ENGINE,
403
SUBPARTITION s3 ENGINE = ENGINE),
404
PARTITION p2 VALUES LESS THAN MAXVALUE
405
(SUBPARTITION s4 ENGINE = ENGINE,
406
SUBPARTITION s5 ENGINE = ENGINE)) */