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)) */