[tomato.git] / release / src / router / mysql / mysql-test / suite / parts / r / partition_datetime_innodb.result
| blob | 48af3343d9a4457b70fc35784c127922ffd16f9f |
1 create table t1 (a timestamp not null, primary key(a)) engine='InnoDB'
2 partition by key (a) (
3 partition pa1 max_rows=20 min_rows=2,
4 partition pa2 max_rows=30 min_rows=3,
5 partition pa3 max_rows=30 min_rows=4,
6 partition pa4 max_rows=40 min_rows=2);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11 PRIMARY KEY (`a`)
12 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
13 /*!50100 PARTITION BY KEY (a)
14 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
15 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
16 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
17 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
18 insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
19 select * from t1;
20 a
21 1975-01-01 21:21:21
22 1980-10-14 03:03:00
23 2000-06-15 23:59:00
24 2020-12-31 12:10:30
25 select * from t1 where a=19801014030300;
26 a
27 1980-10-14 03:03:00
28 delete from t1 where a=19801014030300;
29 select * from t1;
30 a
31 1975-01-01 21:21:21
32 2000-06-15 23:59:00
33 2020-12-31 12:10:30
34 drop table t1;
35 create table t2 (a timestamp not null, primary key(a)) engine='InnoDB'
36 partition by key (a) partitions 12;
37 show create table t2;
38 Table Create Table
39 t2 CREATE TABLE `t2` (
40 `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
41 PRIMARY KEY (`a`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
43 /*!50100 PARTITION BY KEY (a)
44 PARTITIONS 12 */
45 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
46 select * from t2;
47 a
48 1975-01-01 00:01:01
49 1980-10-14 13:14:15
50 2000-06-15 14:15:16
51 2020-12-31 10:11:12
52 select * from t2 where a='1980-10-14 13:14:15';
53 a
54 1980-10-14 13:14:15
55 delete from t2 where a='1980-10-14 13:14:15';
56 select * from t2;
57 a
58 1975-01-01 00:01:01
59 2000-06-15 14:15:16
60 2020-12-31 10:11:12
61 delete from t2;
62 59 inserts;
63 insert into t2 values (19710101000000+59);
64 insert into t2 values (19710101000000+58);
65 insert into t2 values (19710101000000+57);
66 insert into t2 values (19710101000000+56);
67 insert into t2 values (19710101000000+55);
68 insert into t2 values (19710101000000+54);
69 insert into t2 values (19710101000000+53);
70 insert into t2 values (19710101000000+52);
71 insert into t2 values (19710101000000+51);
72 insert into t2 values (19710101000000+50);
73 insert into t2 values (19710101000000+49);
74 insert into t2 values (19710101000000+48);
75 insert into t2 values (19710101000000+47);
76 insert into t2 values (19710101000000+46);
77 insert into t2 values (19710101000000+45);
78 insert into t2 values (19710101000000+44);
79 insert into t2 values (19710101000000+43);
80 insert into t2 values (19710101000000+42);
81 insert into t2 values (19710101000000+41);
82 insert into t2 values (19710101000000+40);
83 insert into t2 values (19710101000000+39);
84 insert into t2 values (19710101000000+38);
85 insert into t2 values (19710101000000+37);
86 insert into t2 values (19710101000000+36);
87 insert into t2 values (19710101000000+35);
88 insert into t2 values (19710101000000+34);
89 insert into t2 values (19710101000000+33);
90 insert into t2 values (19710101000000+32);
91 insert into t2 values (19710101000000+31);
92 insert into t2 values (19710101000000+30);
93 insert into t2 values (19710101000000+29);
94 insert into t2 values (19710101000000+28);
95 insert into t2 values (19710101000000+27);
96 insert into t2 values (19710101000000+26);
97 insert into t2 values (19710101000000+25);
98 insert into t2 values (19710101000000+24);
99 insert into t2 values (19710101000000+23);
100 insert into t2 values (19710101000000+22);
101 insert into t2 values (19710101000000+21);
102 insert into t2 values (19710101000000+20);
103 insert into t2 values (19710101000000+19);
104 insert into t2 values (19710101000000+18);
105 insert into t2 values (19710101000000+17);
106 insert into t2 values (19710101000000+16);
107 insert into t2 values (19710101000000+15);
108 insert into t2 values (19710101000000+14);
109 insert into t2 values (19710101000000+13);
110 insert into t2 values (19710101000000+12);
111 insert into t2 values (19710101000000+11);
112 insert into t2 values (19710101000000+10);
113 insert into t2 values (19710101000000+9);
114 insert into t2 values (19710101000000+8);
115 insert into t2 values (19710101000000+7);
116 insert into t2 values (19710101000000+6);
117 insert into t2 values (19710101000000+5);
118 insert into t2 values (19710101000000+4);
119 insert into t2 values (19710101000000+3);
120 insert into t2 values (19710101000000+2);
121 insert into t2 values (19710101000000+1);
122 select count(*) from t2;
123 count(*)
124 59
125 select * from t2;
126 a
127 1971-01-01 00:00:01
128 1971-01-01 00:00:02
129 1971-01-01 00:00:03
130 1971-01-01 00:00:04
131 1971-01-01 00:00:05
132 1971-01-01 00:00:06
133 1971-01-01 00:00:07
134 1971-01-01 00:00:08
135 1971-01-01 00:00:09
136 1971-01-01 00:00:10
137 1971-01-01 00:00:11
138 1971-01-01 00:00:12
139 1971-01-01 00:00:13
140 1971-01-01 00:00:14
141 1971-01-01 00:00:15
142 1971-01-01 00:00:16
143 1971-01-01 00:00:17
144 1971-01-01 00:00:18
145 1971-01-01 00:00:19
146 1971-01-01 00:00:20
147 1971-01-01 00:00:21
148 1971-01-01 00:00:22
149 1971-01-01 00:00:23
150 1971-01-01 00:00:24
151 1971-01-01 00:00:25
152 1971-01-01 00:00:26
153 1971-01-01 00:00:27
154 1971-01-01 00:00:28
155 1971-01-01 00:00:29
156 1971-01-01 00:00:30
157 1971-01-01 00:00:31
158 1971-01-01 00:00:32
159 1971-01-01 00:00:33
160 1971-01-01 00:00:34
161 1971-01-01 00:00:35
162 1971-01-01 00:00:36
163 1971-01-01 00:00:37
164 1971-01-01 00:00:38
165 1971-01-01 00:00:39
166 1971-01-01 00:00:40
167 1971-01-01 00:00:41
168 1971-01-01 00:00:42
169 1971-01-01 00:00:43
170 1971-01-01 00:00:44
171 1971-01-01 00:00:45
172 1971-01-01 00:00:46
173 1971-01-01 00:00:47
174 1971-01-01 00:00:48
175 1971-01-01 00:00:49
176 1971-01-01 00:00:50
177 1971-01-01 00:00:51
178 1971-01-01 00:00:52
179 1971-01-01 00:00:53
180 1971-01-01 00:00:54
181 1971-01-01 00:00:55
182 1971-01-01 00:00:56
183 1971-01-01 00:00:57
184 1971-01-01 00:00:58
185 1971-01-01 00:00:59
186 drop table t2;
187 create table t1 (a date not null, primary key(a)) engine='InnoDB'
188 partition by key (a) (
189 partition pa1 max_rows=20 min_rows=2,
190 partition pa2 max_rows=30 min_rows=3,
191 partition pa3 max_rows=30 min_rows=4,
192 partition pa4 max_rows=40 min_rows=2);
193 show create table t1;
194 Table Create Table
195 t1 CREATE TABLE `t1` (
196 `a` date NOT NULL,
197 PRIMARY KEY (`a`)
198 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
199 /*!50100 PARTITION BY KEY (a)
200 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
201 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
202 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
203 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
204 insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
205 select * from t1;
206 a
207 1975-01-01
208 1980-10-14
209 2000-06-15
210 2020-12-31
211 select * from t1 where a=19801014;
212 a
213 1980-10-14
214 delete from t1 where a=19801014;
215 select * from t1;
216 a
217 1975-01-01
218 2000-06-15
219 2020-12-31
220 drop table t1;
221 create table t2 (a date not null, primary key(a)) engine='InnoDB'
222 partition by key (a) partitions 12;
223 show create table t2;
224 Table Create Table
225 t2 CREATE TABLE `t2` (
226 `a` date NOT NULL,
227 PRIMARY KEY (`a`)
228 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
229 /*!50100 PARTITION BY KEY (a)
230 PARTITIONS 12 */
231 insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
232 select * from t2;
233 a
234 1975-01-01
235 1980-10-14
236 2000-06-15
237 2020-12-31
238 select * from t2 where a='1980-10-14';
239 a
240 1980-10-14
241 delete from t2 where a='1980-10-14';
242 select * from t2;
243 a
244 1975-01-01
245 2000-06-15
246 2020-12-31
247 delete from t2;
248 28 inserts;
249 insert into t2 values (19700101+28-1);
250 insert into t2 values (19700201+28-1);
251 insert into t2 values (19700301+28-1);
252 insert into t2 values (19700101+27-1);
253 insert into t2 values (19700201+27-1);
254 insert into t2 values (19700301+27-1);
255 insert into t2 values (19700101+26-1);
256 insert into t2 values (19700201+26-1);
257 insert into t2 values (19700301+26-1);
258 insert into t2 values (19700101+25-1);
259 insert into t2 values (19700201+25-1);
260 insert into t2 values (19700301+25-1);
261 insert into t2 values (19700101+24-1);
262 insert into t2 values (19700201+24-1);
263 insert into t2 values (19700301+24-1);
264 insert into t2 values (19700101+23-1);
265 insert into t2 values (19700201+23-1);
266 insert into t2 values (19700301+23-1);
267 insert into t2 values (19700101+22-1);
268 insert into t2 values (19700201+22-1);
269 insert into t2 values (19700301+22-1);
270 insert into t2 values (19700101+21-1);
271 insert into t2 values (19700201+21-1);
272 insert into t2 values (19700301+21-1);
273 insert into t2 values (19700101+20-1);
274 insert into t2 values (19700201+20-1);
275 insert into t2 values (19700301+20-1);
276 insert into t2 values (19700101+19-1);
277 insert into t2 values (19700201+19-1);
278 insert into t2 values (19700301+19-1);
279 insert into t2 values (19700101+18-1);
280 insert into t2 values (19700201+18-1);
281 insert into t2 values (19700301+18-1);
282 insert into t2 values (19700101+17-1);
283 insert into t2 values (19700201+17-1);
284 insert into t2 values (19700301+17-1);
285 insert into t2 values (19700101+16-1);
286 insert into t2 values (19700201+16-1);
287 insert into t2 values (19700301+16-1);
288 insert into t2 values (19700101+15-1);
289 insert into t2 values (19700201+15-1);
290 insert into t2 values (19700301+15-1);
291 insert into t2 values (19700101+14-1);
292 insert into t2 values (19700201+14-1);
293 insert into t2 values (19700301+14-1);
294 insert into t2 values (19700101+13-1);
295 insert into t2 values (19700201+13-1);
296 insert into t2 values (19700301+13-1);
297 insert into t2 values (19700101+12-1);
298 insert into t2 values (19700201+12-1);
299 insert into t2 values (19700301+12-1);
300 insert into t2 values (19700101+11-1);
301 insert into t2 values (19700201+11-1);
302 insert into t2 values (19700301+11-1);
303 insert into t2 values (19700101+10-1);
304 insert into t2 values (19700201+10-1);
305 insert into t2 values (19700301+10-1);
306 insert into t2 values (19700101+9-1);
307 insert into t2 values (19700201+9-1);
308 insert into t2 values (19700301+9-1);
309 insert into t2 values (19700101+8-1);
310 insert into t2 values (19700201+8-1);
311 insert into t2 values (19700301+8-1);
312 insert into t2 values (19700101+7-1);
313 insert into t2 values (19700201+7-1);
314 insert into t2 values (19700301+7-1);
315 insert into t2 values (19700101+6-1);
316 insert into t2 values (19700201+6-1);
317 insert into t2 values (19700301+6-1);
318 insert into t2 values (19700101+5-1);
319 insert into t2 values (19700201+5-1);
320 insert into t2 values (19700301+5-1);
321 insert into t2 values (19700101+4-1);
322 insert into t2 values (19700201+4-1);
323 insert into t2 values (19700301+4-1);
324 insert into t2 values (19700101+3-1);
325 insert into t2 values (19700201+3-1);
326 insert into t2 values (19700301+3-1);
327 insert into t2 values (19700101+2-1);
328 insert into t2 values (19700201+2-1);
329 insert into t2 values (19700301+2-1);
330 insert into t2 values (19700101+1-1);
331 insert into t2 values (19700201+1-1);
332 insert into t2 values (19700301+1-1);
333 select count(*) from t2;
334 count(*)
335 84
336 select * from t2;
337 a
338 1970-01-01
339 1970-01-02
340 1970-01-03
341 1970-01-04
342 1970-01-05
343 1970-01-06
344 1970-01-07
345 1970-01-08
346 1970-01-09
347 1970-01-10
348 1970-01-11
349 1970-01-12
350 1970-01-13
351 1970-01-14
352 1970-01-15
353 1970-01-16
354 1970-01-17
355 1970-01-18
356 1970-01-19
357 1970-01-20
358 1970-01-21
359 1970-01-22
360 1970-01-23
361 1970-01-24
362 1970-01-25
363 1970-01-26
364 1970-01-27
365 1970-01-28
366 1970-02-01
367 1970-02-02
368 1970-02-03
369 1970-02-04
370 1970-02-05
371 1970-02-06
372 1970-02-07
373 1970-02-08
374 1970-02-09
375 1970-02-10
376 1970-02-11
377 1970-02-12
378 1970-02-13
379 1970-02-14
380 1970-02-15
381 1970-02-16
382 1970-02-17
383 1970-02-18
384 1970-02-19
385 1970-02-20
386 1970-02-21
387 1970-02-22
388 1970-02-23
389 1970-02-24
390 1970-02-25
391 1970-02-26
392 1970-02-27
393 1970-02-28
394 1970-03-01
395 1970-03-02
396 1970-03-03
397 1970-03-04
398 1970-03-05
399 1970-03-06
400 1970-03-07
401 1970-03-08
402 1970-03-09
403 1970-03-10
404 1970-03-11
405 1970-03-12
406 1970-03-13
407 1970-03-14
408 1970-03-15
409 1970-03-16
410 1970-03-17
411 1970-03-18
412 1970-03-19
413 1970-03-20
414 1970-03-21
415 1970-03-22
416 1970-03-23
417 1970-03-24
418 1970-03-25
419 1970-03-26
420 1970-03-27
421 1970-03-28
422 drop table t2;
423 create table t3 (a date not null, primary key(a)) engine='InnoDB'
424 partition by range (month(a)) subpartition by key (a)
425 subpartitions 3 (
426 partition quarter1 values less than (4),
427 partition quarter2 values less than (7),
428 partition quarter3 values less than (10),
429 partition quarter4 values less than (13)
430 );
431 show create table t3;
432 Table Create Table
433 t3 CREATE TABLE `t3` (
434 `a` date NOT NULL,
435 PRIMARY KEY (`a`)
436 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
437 /*!50100 PARTITION BY RANGE (month(a))
438 SUBPARTITION BY KEY (a)
439 SUBPARTITIONS 3
440 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB,
441 PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB,
442 PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB,
443 PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */
444 12 inserts;
445 insert into t3 values (adddate(19700101,interval 12-1 month));
446 insert into t3 values (adddate(19700101,interval 11-1 month));
447 insert into t3 values (adddate(19700101,interval 10-1 month));
448 insert into t3 values (adddate(19700101,interval 9-1 month));
449 insert into t3 values (adddate(19700101,interval 8-1 month));
450 insert into t3 values (adddate(19700101,interval 7-1 month));
451 insert into t3 values (adddate(19700101,interval 6-1 month));
452 insert into t3 values (adddate(19700101,interval 5-1 month));
453 insert into t3 values (adddate(19700101,interval 4-1 month));
454 insert into t3 values (adddate(19700101,interval 3-1 month));
455 insert into t3 values (adddate(19700101,interval 2-1 month));
456 insert into t3 values (adddate(19700101,interval 1-1 month));
457 select count(*) from t3;
458 count(*)
459 12
460 select * from t3;
461 a
462 1970-01-01
463 1970-02-01
464 1970-03-01
465 1970-04-01
466 1970-05-01
467 1970-06-01
468 1970-07-01
469 1970-08-01
470 1970-09-01
471 1970-10-01
472 1970-11-01
473 1970-12-01
474 drop table t3;
475 create table t4 (a date not null, primary key(a)) engine='InnoDB'
476 partition by list (month(a)) subpartition by key (a)
477 subpartitions 3 (
478 partition quarter1 values in (1,2,3),
479 partition quarter2 values in (4,5,6),
480 partition quarter3 values in (7,8,9),
481 partition quarter4 values in (10,11,12)
482 );
483 show create table t4;
484 Table Create Table
485 t4 CREATE TABLE `t4` (
486 `a` date NOT NULL,
487 PRIMARY KEY (`a`)
488 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
489 /*!50100 PARTITION BY LIST (month(a))
490 SUBPARTITION BY KEY (a)
491 SUBPARTITIONS 3
492 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB,
493 PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB,
494 PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB,
495 PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */
496 12 inserts;
497 insert into t4 values (adddate(19700101,interval 12-1 month));
498 insert into t4 values (adddate(19700101,interval 11-1 month));
499 insert into t4 values (adddate(19700101,interval 10-1 month));
500 insert into t4 values (adddate(19700101,interval 9-1 month));
501 insert into t4 values (adddate(19700101,interval 8-1 month));
502 insert into t4 values (adddate(19700101,interval 7-1 month));
503 insert into t4 values (adddate(19700101,interval 6-1 month));
504 insert into t4 values (adddate(19700101,interval 5-1 month));
505 insert into t4 values (adddate(19700101,interval 4-1 month));
506 insert into t4 values (adddate(19700101,interval 3-1 month));
507 insert into t4 values (adddate(19700101,interval 2-1 month));
508 insert into t4 values (adddate(19700101,interval 1-1 month));
509 select count(*) from t4;
510 count(*)
511 12
512 select * from t4;
513 a
514 1970-01-01
515 1970-02-01
516 1970-03-01
517 1970-04-01
518 1970-05-01
519 1970-06-01
520 1970-07-01
521 1970-08-01
522 1970-09-01
523 1970-10-01
524 1970-11-01
525 1970-12-01
526 drop table t4;
527 create table t1 (a time not null, primary key(a)) engine='InnoDB'
528 partition by key (a) (
529 partition pa1 max_rows=20 min_rows=2,
530 partition pa2 max_rows=30 min_rows=3,
531 partition pa3 max_rows=30 min_rows=4,
532 partition pa4 max_rows=40 min_rows=2);
533 show create table t1;
534 Table Create Table
535 t1 CREATE TABLE `t1` (
536 `a` time NOT NULL,
537 PRIMARY KEY (`a`)
538 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
539 /*!50100 PARTITION BY KEY (a)
540 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
541 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
542 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
543 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
544 insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
545 select * from t1;
546 a
547 03:03:03
548 12:10:30
549 21:21:21
550 23:59:00
551 select * from t1 where a=030303;
552 a
553 03:03:03
554 delete from t1 where a=030303;
555 select * from t1;
556 a
557 12:10:30
558 21:21:21
559 23:59:00
560 drop table t1;
561 create table t2 (a time not null, primary key(a)) engine='InnoDB'
562 partition by key (a) partitions 12;
563 show create table t2;
564 Table Create Table
565 t2 CREATE TABLE `t2` (
566 `a` time NOT NULL,
567 PRIMARY KEY (`a`)
568 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
569 /*!50100 PARTITION BY KEY (a)
570 PARTITIONS 12 */
571 insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
572 select * from t2;
573 a
574 00:01:01
575 10:11:12
576 13:14:15
577 14:15:16
578 select * from t2 where a='13:14:15';
579 a
580 13:14:15
581 delete from t2 where a='13:14:15';
582 select * from t2;
583 a
584 00:01:01
585 10:11:12
586 14:15:16
587 delete from t2;
588 59 inserts;
589 insert into t2 values (000100+59);
590 insert into t2 values (000100+58);
591 insert into t2 values (000100+57);
592 insert into t2 values (000100+56);
593 insert into t2 values (000100+55);
594 insert into t2 values (000100+54);
595 insert into t2 values (000100+53);
596 insert into t2 values (000100+52);
597 insert into t2 values (000100+51);
598 insert into t2 values (000100+50);
599 insert into t2 values (000100+49);
600 insert into t2 values (000100+48);
601 insert into t2 values (000100+47);
602 insert into t2 values (000100+46);
603 insert into t2 values (000100+45);
604 insert into t2 values (000100+44);
605 insert into t2 values (000100+43);
606 insert into t2 values (000100+42);
607 insert into t2 values (000100+41);
608 insert into t2 values (000100+40);
609 insert into t2 values (000100+39);
610 insert into t2 values (000100+38);
611 insert into t2 values (000100+37);
612 insert into t2 values (000100+36);
613 insert into t2 values (000100+35);
614 insert into t2 values (000100+34);
615 insert into t2 values (000100+33);
616 insert into t2 values (000100+32);
617 insert into t2 values (000100+31);
618 insert into t2 values (000100+30);
619 insert into t2 values (000100+29);
620 insert into t2 values (000100+28);
621 insert into t2 values (000100+27);
622 insert into t2 values (000100+26);
623 insert into t2 values (000100+25);
624 insert into t2 values (000100+24);
625 insert into t2 values (000100+23);
626 insert into t2 values (000100+22);
627 insert into t2 values (000100+21);
628 insert into t2 values (000100+20);
629 insert into t2 values (000100+19);
630 insert into t2 values (000100+18);
631 insert into t2 values (000100+17);
632 insert into t2 values (000100+16);
633 insert into t2 values (000100+15);
634 insert into t2 values (000100+14);
635 insert into t2 values (000100+13);
636 insert into t2 values (000100+12);
637 insert into t2 values (000100+11);
638 insert into t2 values (000100+10);
639 insert into t2 values (000100+9);
640 insert into t2 values (000100+8);
641 insert into t2 values (000100+7);
642 insert into t2 values (000100+6);
643 insert into t2 values (000100+5);
644 insert into t2 values (000100+4);
645 insert into t2 values (000100+3);
646 insert into t2 values (000100+2);
647 insert into t2 values (000100+1);
648 select count(*) from t2;
649 count(*)
650 59
651 select * from t2;
652 a
653 00:01:01
654 00:01:02
655 00:01:03
656 00:01:04
657 00:01:05
658 00:01:06
659 00:01:07
660 00:01:08
661 00:01:09
662 00:01:10
663 00:01:11
664 00:01:12
665 00:01:13
666 00:01:14
667 00:01:15
668 00:01:16
669 00:01:17
670 00:01:18
671 00:01:19
672 00:01:20
673 00:01:21
674 00:01:22
675 00:01:23
676 00:01:24
677 00:01:25
678 00:01:26
679 00:01:27
680 00:01:28
681 00:01:29
682 00:01:30
683 00:01:31
684 00:01:32
685 00:01:33
686 00:01:34
687 00:01:35
688 00:01:36
689 00:01:37
690 00:01:38
691 00:01:39
692 00:01:40
693 00:01:41
694 00:01:42
695 00:01:43
696 00:01:44
697 00:01:45
698 00:01:46
699 00:01:47
700 00:01:48
701 00:01:49
702 00:01:50
703 00:01:51
704 00:01:52
705 00:01:53
706 00:01:54
707 00:01:55
708 00:01:56
709 00:01:57
710 00:01:58
711 00:01:59
712 drop table t2;
713 create table t3 (a time not null, primary key(a)) engine='InnoDB'
714 partition by range (second(a)) subpartition by key (a)
715 subpartitions 3 (
716 partition quarter1 values less than (16),
717 partition quarter2 values less than (31),
718 partition quarter3 values less than (46),
719 partition quarter4 values less than (61)
720 );
721 show create table t3;
722 Table Create Table
723 t3 CREATE TABLE `t3` (
724 `a` time NOT NULL,
725 PRIMARY KEY (`a`)
726 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
727 /*!50100 PARTITION BY RANGE (second(a))
728 SUBPARTITION BY KEY (a)
729 SUBPARTITIONS 3
730 (PARTITION quarter1 VALUES LESS THAN (16) ENGINE = InnoDB,
731 PARTITION quarter2 VALUES LESS THAN (31) ENGINE = InnoDB,
732 PARTITION quarter3 VALUES LESS THAN (46) ENGINE = InnoDB,
733 PARTITION quarter4 VALUES LESS THAN (61) ENGINE = InnoDB) */
734 59 inserts;
735 insert into t3 values (100000+59);
736 insert into t3 values (100000+58);
737 insert into t3 values (100000+57);
738 insert into t3 values (100000+56);
739 insert into t3 values (100000+55);
740 insert into t3 values (100000+54);
741 insert into t3 values (100000+53);
742 insert into t3 values (100000+52);
743 insert into t3 values (100000+51);
744 insert into t3 values (100000+50);
745 insert into t3 values (100000+49);
746 insert into t3 values (100000+48);
747 insert into t3 values (100000+47);
748 insert into t3 values (100000+46);
749 insert into t3 values (100000+45);
750 insert into t3 values (100000+44);
751 insert into t3 values (100000+43);
752 insert into t3 values (100000+42);
753 insert into t3 values (100000+41);
754 insert into t3 values (100000+40);
755 insert into t3 values (100000+39);
756 insert into t3 values (100000+38);
757 insert into t3 values (100000+37);
758 insert into t3 values (100000+36);
759 insert into t3 values (100000+35);
760 insert into t3 values (100000+34);
761 insert into t3 values (100000+33);
762 insert into t3 values (100000+32);
763 insert into t3 values (100000+31);
764 insert into t3 values (100000+30);
765 insert into t3 values (100000+29);
766 insert into t3 values (100000+28);
767 insert into t3 values (100000+27);
768 insert into t3 values (100000+26);
769 insert into t3 values (100000+25);
770 insert into t3 values (100000+24);
771 insert into t3 values (100000+23);
772 insert into t3 values (100000+22);
773 insert into t3 values (100000+21);
774 insert into t3 values (100000+20);
775 insert into t3 values (100000+19);
776 insert into t3 values (100000+18);
777 insert into t3 values (100000+17);
778 insert into t3 values (100000+16);
779 insert into t3 values (100000+15);
780 insert into t3 values (100000+14);
781 insert into t3 values (100000+13);
782 insert into t3 values (100000+12);
783 insert into t3 values (100000+11);
784 insert into t3 values (100000+10);
785 insert into t3 values (100000+9);
786 insert into t3 values (100000+8);
787 insert into t3 values (100000+7);
788 insert into t3 values (100000+6);
789 insert into t3 values (100000+5);
790 insert into t3 values (100000+4);
791 insert into t3 values (100000+3);
792 insert into t3 values (100000+2);
793 insert into t3 values (100000+1);
794 select count(*) from t3;
795 count(*)
796 59
797 select * from t3;
798 a
799 10:00:01
800 10:00:02
801 10:00:03
802 10:00:04
803 10:00:05
804 10:00:06
805 10:00:07
806 10:00:08
807 10:00:09
808 10:00:10
809 10:00:11
810 10:00:12
811 10:00:13
812 10:00:14
813 10:00:15
814 10:00:16
815 10:00:17
816 10:00:18
817 10:00:19
818 10:00:20
819 10:00:21
820 10:00:22
821 10:00:23
822 10:00:24
823 10:00:25
824 10:00:26
825 10:00:27
826 10:00:28
827 10:00:29
828 10:00:30
829 10:00:31
830 10:00:32
831 10:00:33
832 10:00:34
833 10:00:35
834 10:00:36
835 10:00:37
836 10:00:38
837 10:00:39
838 10:00:40
839 10:00:41
840 10:00:42
841 10:00:43
842 10:00:44
843 10:00:45
844 10:00:46
845 10:00:47
846 10:00:48
847 10:00:49
848 10:00:50
849 10:00:51
850 10:00:52
851 10:00:53
852 10:00:54
853 10:00:55
854 10:00:56
855 10:00:57
856 10:00:58
857 10:00:59
858 drop table t3;
859 create table t4 (a time not null, primary key(a)) engine='InnoDB'
860 partition by list (second(a)) subpartition by key (a)
861 subpartitions 3 (
862 partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
863 partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
864 partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
865 partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
866 );
867 show create table t4;
868 Table Create Table
869 t4 CREATE TABLE `t4` (
870 `a` time NOT NULL,
871 PRIMARY KEY (`a`)
872 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
873 /*!50100 PARTITION BY LIST (second(a))
874 SUBPARTITION BY KEY (a)
875 SUBPARTITIONS 3
876 (PARTITION quarter1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = InnoDB,
877 PARTITION quarter2 VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
878 PARTITION quarter3 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = InnoDB,
879 PARTITION quarter4 VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
880 59 inserts;
881 insert into t4 values (100000+59);
882 insert into t4 values (100000+58);
883 insert into t4 values (100000+57);
884 insert into t4 values (100000+56);
885 insert into t4 values (100000+55);
886 insert into t4 values (100000+54);
887 insert into t4 values (100000+53);
888 insert into t4 values (100000+52);
889 insert into t4 values (100000+51);
890 insert into t4 values (100000+50);
891 insert into t4 values (100000+49);
892 insert into t4 values (100000+48);
893 insert into t4 values (100000+47);
894 insert into t4 values (100000+46);
895 insert into t4 values (100000+45);
896 insert into t4 values (100000+44);
897 insert into t4 values (100000+43);
898 insert into t4 values (100000+42);
899 insert into t4 values (100000+41);
900 insert into t4 values (100000+40);
901 insert into t4 values (100000+39);
902 insert into t4 values (100000+38);
903 insert into t4 values (100000+37);
904 insert into t4 values (100000+36);
905 insert into t4 values (100000+35);
906 insert into t4 values (100000+34);
907 insert into t4 values (100000+33);
908 insert into t4 values (100000+32);
909 insert into t4 values (100000+31);
910 insert into t4 values (100000+30);
911 insert into t4 values (100000+29);
912 insert into t4 values (100000+28);
913 insert into t4 values (100000+27);
914 insert into t4 values (100000+26);
915 insert into t4 values (100000+25);
916 insert into t4 values (100000+24);
917 insert into t4 values (100000+23);
918 insert into t4 values (100000+22);
919 insert into t4 values (100000+21);
920 insert into t4 values (100000+20);
921 insert into t4 values (100000+19);
922 insert into t4 values (100000+18);
923 insert into t4 values (100000+17);
924 insert into t4 values (100000+16);
925 insert into t4 values (100000+15);
926 insert into t4 values (100000+14);
927 insert into t4 values (100000+13);
928 insert into t4 values (100000+12);
929 insert into t4 values (100000+11);
930 insert into t4 values (100000+10);
931 insert into t4 values (100000+9);
932 insert into t4 values (100000+8);
933 insert into t4 values (100000+7);
934 insert into t4 values (100000+6);
935 insert into t4 values (100000+5);
936 insert into t4 values (100000+4);
937 insert into t4 values (100000+3);
938 insert into t4 values (100000+2);
939 insert into t4 values (100000+1);
940 select count(*) from t4;
941 count(*)
942 59
943 select * from t4;
944 a
945 10:00:01
946 10:00:02
947 10:00:03
948 10:00:04
949 10:00:05
950 10:00:06
951 10:00:07
952 10:00:08
953 10:00:09
954 10:00:10
955 10:00:11
956 10:00:12
957 10:00:13
958 10:00:14
959 10:00:15
960 10:00:16
961 10:00:17
962 10:00:18
963 10:00:19
964 10:00:20
965 10:00:21
966 10:00:22
967 10:00:23
968 10:00:24
969 10:00:25
970 10:00:26
971 10:00:27
972 10:00:28
973 10:00:29
974 10:00:30
975 10:00:31
976 10:00:32
977 10:00:33
978 10:00:34
979 10:00:35
980 10:00:36
981 10:00:37
982 10:00:38
983 10:00:39
984 10:00:40
985 10:00:41
986 10:00:42
987 10:00:43
988 10:00:44
989 10:00:45
990 10:00:46
991 10:00:47
992 10:00:48
993 10:00:49
994 10:00:50
995 10:00:51
996 10:00:52
997 10:00:53
998 10:00:54
999 10:00:55
1000 10:00:56
1001 10:00:57
1002 10:00:58
1003 10:00:59
1004 drop table t4;
1005 create table t1 (a datetime not null, primary key(a)) engine='InnoDB'
1006 partition by key (a) (
1007 partition pa1 max_rows=20 min_rows=2,
1008 partition pa2 max_rows=30 min_rows=3,
1009 partition pa3 max_rows=30 min_rows=4,
1010 partition pa4 max_rows=40 min_rows=2);
1011 show create table t1;
1012 Table Create Table
1013 t1 CREATE TABLE `t1` (
1014 `a` datetime NOT NULL,
1015 PRIMARY KEY (`a`)
1016 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1017 /*!50100 PARTITION BY KEY (a)
1018 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
1019 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
1020 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
1021 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
1022 insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
1023 select * from t1;
1024 a
1025 1975-01-01 21:21:21
1026 1980-10-14 03:03:00
1027 2000-06-15 23:59:00
1028 2020-12-31 12:10:30
1029 select * from t1 where a=19801014030300;
1030 a
1031 1980-10-14 03:03:00
1032 delete from t1 where a=19801014030300;
1033 select * from t1;
1034 a
1035 1975-01-01 21:21:21
1036 2000-06-15 23:59:00
1037 2020-12-31 12:10:30
1038 drop table t1;
1039 create table t2 (a datetime not null, primary key(a)) engine='InnoDB'
1040 partition by key (a) partitions 12;
1041 show create table t2;
1042 Table Create Table
1043 t2 CREATE TABLE `t2` (
1044 `a` datetime NOT NULL,
1045 PRIMARY KEY (`a`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1047 /*!50100 PARTITION BY KEY (a)
1048 PARTITIONS 12 */
1049 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
1050 select * from t2;
1051 a
1052 1975-01-01 00:01:01
1053 1980-10-14 13:14:15
1054 2000-06-15 14:15:16
1055 2020-12-31 10:11:12
1056 select * from t2 where a='1980-10-14 13:14:15';
1057 a
1058 1980-10-14 13:14:15
1059 delete from t2 where a='1980-10-14 13:14:15';
1060 select * from t2;
1061 a
1062 1975-01-01 00:01:01
1063 2000-06-15 14:15:16
1064 2020-12-31 10:11:12
1065 delete from t2;
1066 59 inserts;
1067 insert into t2 values (19700101000000+59);
1068 insert into t2 values (19700101000000+58);
1069 insert into t2 values (19700101000000+57);
1070 insert into t2 values (19700101000000+56);
1071 insert into t2 values (19700101000000+55);
1072 insert into t2 values (19700101000000+54);
1073 insert into t2 values (19700101000000+53);
1074 insert into t2 values (19700101000000+52);
1075 insert into t2 values (19700101000000+51);
1076 insert into t2 values (19700101000000+50);
1077 insert into t2 values (19700101000000+49);
1078 insert into t2 values (19700101000000+48);
1079 insert into t2 values (19700101000000+47);
1080 insert into t2 values (19700101000000+46);
1081 insert into t2 values (19700101000000+45);
1082 insert into t2 values (19700101000000+44);
1083 insert into t2 values (19700101000000+43);
1084 insert into t2 values (19700101000000+42);
1085 insert into t2 values (19700101000000+41);
1086 insert into t2 values (19700101000000+40);
1087 insert into t2 values (19700101000000+39);
1088 insert into t2 values (19700101000000+38);
1089 insert into t2 values (19700101000000+37);
1090 insert into t2 values (19700101000000+36);
1091 insert into t2 values (19700101000000+35);
1092 insert into t2 values (19700101000000+34);
1093 insert into t2 values (19700101000000+33);
1094 insert into t2 values (19700101000000+32);
1095 insert into t2 values (19700101000000+31);
1096 insert into t2 values (19700101000000+30);
1097 insert into t2 values (19700101000000+29);
1098 insert into t2 values (19700101000000+28);
1099 insert into t2 values (19700101000000+27);
1100 insert into t2 values (19700101000000+26);
1101 insert into t2 values (19700101000000+25);
1102 insert into t2 values (19700101000000+24);
1103 insert into t2 values (19700101000000+23);
1104 insert into t2 values (19700101000000+22);
1105 insert into t2 values (19700101000000+21);
1106 insert into t2 values (19700101000000+20);
1107 insert into t2 values (19700101000000+19);
1108 insert into t2 values (19700101000000+18);
1109 insert into t2 values (19700101000000+17);
1110 insert into t2 values (19700101000000+16);
1111 insert into t2 values (19700101000000+15);
1112 insert into t2 values (19700101000000+14);
1113 insert into t2 values (19700101000000+13);
1114 insert into t2 values (19700101000000+12);
1115 insert into t2 values (19700101000000+11);
1116 insert into t2 values (19700101000000+10);
1117 insert into t2 values (19700101000000+9);
1118 insert into t2 values (19700101000000+8);
1119 insert into t2 values (19700101000000+7);
1120 insert into t2 values (19700101000000+6);
1121 insert into t2 values (19700101000000+5);
1122 insert into t2 values (19700101000000+4);
1123 insert into t2 values (19700101000000+3);
1124 insert into t2 values (19700101000000+2);
1125 insert into t2 values (19700101000000+1);
1126 select count(*) from t2;
1127 count(*)
1128 59
1129 select * from t2;
1130 a
1131 1970-01-01 00:00:01
1132 1970-01-01 00:00:02
1133 1970-01-01 00:00:03
1134 1970-01-01 00:00:04
1135 1970-01-01 00:00:05
1136 1970-01-01 00:00:06
1137 1970-01-01 00:00:07
1138 1970-01-01 00:00:08
1139 1970-01-01 00:00:09
1140 1970-01-01 00:00:10
1141 1970-01-01 00:00:11
1142 1970-01-01 00:00:12
1143 1970-01-01 00:00:13
1144 1970-01-01 00:00:14
1145 1970-01-01 00:00:15
1146 1970-01-01 00:00:16
1147 1970-01-01 00:00:17
1148 1970-01-01 00:00:18
1149 1970-01-01 00:00:19
1150 1970-01-01 00:00:20
1151 1970-01-01 00:00:21
1152 1970-01-01 00:00:22
1153 1970-01-01 00:00:23
1154 1970-01-01 00:00:24
1155 1970-01-01 00:00:25
1156 1970-01-01 00:00:26
1157 1970-01-01 00:00:27
1158 1970-01-01 00:00:28
1159 1970-01-01 00:00:29
1160 1970-01-01 00:00:30
1161 1970-01-01 00:00:31
1162 1970-01-01 00:00:32
1163 1970-01-01 00:00:33
1164 1970-01-01 00:00:34
1165 1970-01-01 00:00:35
1166 1970-01-01 00:00:36
1167 1970-01-01 00:00:37
1168 1970-01-01 00:00:38
1169 1970-01-01 00:00:39
1170 1970-01-01 00:00:40
1171 1970-01-01 00:00:41
1172 1970-01-01 00:00:42
1173 1970-01-01 00:00:43
1174 1970-01-01 00:00:44
1175 1970-01-01 00:00:45
1176 1970-01-01 00:00:46
1177 1970-01-01 00:00:47
1178 1970-01-01 00:00:48
1179 1970-01-01 00:00:49
1180 1970-01-01 00:00:50
1181 1970-01-01 00:00:51
1182 1970-01-01 00:00:52
1183 1970-01-01 00:00:53
1184 1970-01-01 00:00:54
1185 1970-01-01 00:00:55
1186 1970-01-01 00:00:56
1187 1970-01-01 00:00:57
1188 1970-01-01 00:00:58
1189 1970-01-01 00:00:59
1190 drop table t2;
1191 create table t3 (a datetime not null, primary key(a)) engine='InnoDB'
1192 partition by range (month(a)) subpartition by key (a)
1193 subpartitions 3 (
1194 partition quarter1 values less than (4),
1195 partition quarter2 values less than (7),
1196 partition quarter3 values less than (10),
1197 partition quarter4 values less than (13)
1198 );
1199 show create table t3;
1200 Table Create Table
1201 t3 CREATE TABLE `t3` (
1202 `a` datetime NOT NULL,
1203 PRIMARY KEY (`a`)
1204 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1205 /*!50100 PARTITION BY RANGE (month(a))
1206 SUBPARTITION BY KEY (a)
1207 SUBPARTITIONS 3
1208 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB,
1209 PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB,
1210 PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB,
1211 PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */
1212 12 inserts;
1213 insert into t3 values (adddate(19700101000000,interval 12-1 month));
1214 insert into t3 values (adddate(19700101000000,interval 11-1 month));
1215 insert into t3 values (adddate(19700101000000,interval 10-1 month));
1216 insert into t3 values (adddate(19700101000000,interval 9-1 month));
1217 insert into t3 values (adddate(19700101000000,interval 8-1 month));
1218 insert into t3 values (adddate(19700101000000,interval 7-1 month));
1219 insert into t3 values (adddate(19700101000000,interval 6-1 month));
1220 insert into t3 values (adddate(19700101000000,interval 5-1 month));
1221 insert into t3 values (adddate(19700101000000,interval 4-1 month));
1222 insert into t3 values (adddate(19700101000000,interval 3-1 month));
1223 insert into t3 values (adddate(19700101000000,interval 2-1 month));
1224 insert into t3 values (adddate(19700101000000,interval 1-1 month));
1225 select count(*) from t3;
1226 count(*)
1227 12
1228 select * from t3;
1229 a
1230 1970-01-01 00:00:00
1231 1970-02-01 00:00:00
1232 1970-03-01 00:00:00
1233 1970-04-01 00:00:00
1234 1970-05-01 00:00:00
1235 1970-06-01 00:00:00
1236 1970-07-01 00:00:00
1237 1970-08-01 00:00:00
1238 1970-09-01 00:00:00
1239 1970-10-01 00:00:00
1240 1970-11-01 00:00:00
1241 1970-12-01 00:00:00
1242 drop table t3;
1243 create table t4 (a datetime not null, primary key(a)) engine='InnoDB'
1244 partition by list (month(a)) subpartition by key (a)
1245 subpartitions 3 (
1246 partition quarter1 values in (1,2,3),
1247 partition quarter2 values in (4,5,6),
1248 partition quarter3 values in (7,8,9),
1249 partition quarter4 values in (10,11,12)
1250 );
1251 show create table t4;
1252 Table Create Table
1253 t4 CREATE TABLE `t4` (
1254 `a` datetime NOT NULL,
1255 PRIMARY KEY (`a`)
1256 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1257 /*!50100 PARTITION BY LIST (month(a))
1258 SUBPARTITION BY KEY (a)
1259 SUBPARTITIONS 3
1260 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB,
1261 PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB,
1262 PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB,
1263 PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */
1264 12 inserts;
1265 insert into t4 values (adddate(19700101000000,interval 12-1 month));
1266 insert into t4 values (adddate(19700101000000,interval 11-1 month));
1267 insert into t4 values (adddate(19700101000000,interval 10-1 month));
1268 insert into t4 values (adddate(19700101000000,interval 9-1 month));
1269 insert into t4 values (adddate(19700101000000,interval 8-1 month));
1270 insert into t4 values (adddate(19700101000000,interval 7-1 month));
1271 insert into t4 values (adddate(19700101000000,interval 6-1 month));
1272 insert into t4 values (adddate(19700101000000,interval 5-1 month));
1273 insert into t4 values (adddate(19700101000000,interval 4-1 month));
1274 insert into t4 values (adddate(19700101000000,interval 3-1 month));
1275 insert into t4 values (adddate(19700101000000,interval 2-1 month));
1276 insert into t4 values (adddate(19700101000000,interval 1-1 month));
1277 select count(*) from t4;
1278 count(*)
1279 12
1280 select * from t4;
1281 a
1282 1970-01-01 00:00:00
1283 1970-02-01 00:00:00
1284 1970-03-01 00:00:00
1285 1970-04-01 00:00:00
1286 1970-05-01 00:00:00
1287 1970-06-01 00:00:00
1288 1970-07-01 00:00:00
1289 1970-08-01 00:00:00
1290 1970-09-01 00:00:00
1291 1970-10-01 00:00:00
1292 1970-11-01 00:00:00
1293 1970-12-01 00:00:00
1294 drop table t4;
1295 create table t1 (a year not null, primary key(a)) engine='InnoDB'
1296 partition by key (a) (
1297 partition pa1 max_rows=20 min_rows=2,
1298 partition pa2 max_rows=30 min_rows=3,
1299 partition pa3 max_rows=30 min_rows=4,
1300 partition pa4 max_rows=40 min_rows=2);
1301 show create table t1;
1302 Table Create Table
1303 t1 CREATE TABLE `t1` (
1304 `a` year(4) NOT NULL,
1305 PRIMARY KEY (`a`)
1306 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1307 /*!50100 PARTITION BY KEY (a)
1308 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
1309 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
1310 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
1311 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
1312 insert into t1 values ('1975'), (2020), ('1980'), ('2000');
1313 select * from t1;
1314 a
1315 1975
1316 1980
1317 2000
1318 2020
1319 select * from t1 where a=1980;
1320 a
1321 1980
1322 delete from t1 where a=1980;
1323 select * from t1;
1324 a
1325 1975
1326 2000
1327 2020
1328 drop table t1;
1329 create table t2 (a year not null, primary key(a)) engine='InnoDB'
1330 partition by key (a) partitions 12;
1331 show create table t2;
1332 Table Create Table
1333 t2 CREATE TABLE `t2` (
1334 `a` year(4) NOT NULL,
1335 PRIMARY KEY (`a`)
1336 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1337 /*!50100 PARTITION BY KEY (a)
1338 PARTITIONS 12 */
1339 insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
1340 select * from t2;
1341 a
1342 1975
1343 1980
1344 2000
1345 2020
1346 select * from t2 where a='1980';
1347 a
1348 1980
1349 delete from t2 where a='1980';
1350 select * from t2;
1351 a
1352 1975
1353 2000
1354 2020
1355 delete from t2;
1356 255 inserts;
1357 Warnings:
1358 Warning 1264 Out of range value for column 'a' at row 1
1359 select count(*) from t2;
1360 count(*)
1361 255
1362 select * from t2;
1363 a
1364 0000
1365 1902
1366 1903
1367 1904
1368 1905
1369 1906
1370 1907
1371 1908
1372 1909
1373 1910
1374 1911
1375 1912
1376 1913
1377 1914
1378 1915
1379 1916
1380 1917
1381 1918
1382 1919
1383 1920
1384 1921
1385 1922
1386 1923
1387 1924
1388 1925
1389 1926
1390 1927
1391 1928
1392 1929
1393 1930
1394 1931
1395 1932
1396 1933
1397 1934
1398 1935
1399 1936
1400 1937
1401 1938
1402 1939
1403 1940
1404 1941
1405 1942
1406 1943
1407 1944
1408 1945
1409 1946
1410 1947
1411 1948
1412 1949
1413 1950
1414 1951
1415 1952
1416 1953
1417 1954
1418 1955
1419 1956
1420 1957
1421 1958
1422 1959
1423 1960
1424 1961
1425 1962
1426 1963
1427 1964
1428 1965
1429 1966
1430 1967
1431 1968
1432 1969
1433 1970
1434 1971
1435 1972
1436 1973
1437 1974
1438 1975
1439 1976
1440 1977
1441 1978
1442 1979
1443 1980
1444 1981
1445 1982
1446 1983
1447 1984
1448 1985
1449 1986
1450 1987
1451 1988
1452 1989
1453 1990
1454 1991
1455 1992
1456 1993
1457 1994
1458 1995
1459 1996
1460 1997
1461 1998
1462 1999
1463 2000
1464 2001
1465 2002
1466 2003
1467 2004
1468 2005
1469 2006
1470 2007
1471 2008
1472 2009
1473 2010
1474 2011
1475 2012
1476 2013
1477 2014
1478 2015
1479 2016
1480 2017
1481 2018
1482 2019
1483 2020
1484 2021
1485 2022
1486 2023
1487 2024
1488 2025
1489 2026
1490 2027
1491 2028
1492 2029
1493 2030
1494 2031
1495 2032
1496 2033
1497 2034
1498 2035
1499 2036
1500 2037
1501 2038
1502 2039
1503 2040
1504 2041
1505 2042
1506 2043
1507 2044
1508 2045
1509 2046
1510 2047
1511 2048
1512 2049
1513 2050
1514 2051
1515 2052
1516 2053
1517 2054
1518 2055
1519 2056
1520 2057
1521 2058
1522 2059
1523 2060
1524 2061
1525 2062
1526 2063
1527 2064
1528 2065
1529 2066
1530 2067
1531 2068
1532 2069
1533 2070
1534 2071
1535 2072
1536 2073
1537 2074
1538 2075
1539 2076
1540 2077
1541 2078
1542 2079
1543 2080
1544 2081
1545 2082
1546 2083
1547 2084
1548 2085
1549 2086
1550 2087
1551 2088
1552 2089
1553 2090
1554 2091
1555 2092
1556 2093
1557 2094
1558 2095
1559 2096
1560 2097
1561 2098
1562 2099
1563 2100
1564 2101
1565 2102
1566 2103
1567 2104
1568 2105
1569 2106
1570 2107
1571 2108
1572 2109
1573 2110
1574 2111
1575 2112
1576 2113
1577 2114
1578 2115
1579 2116
1580 2117
1581 2118
1582 2119
1583 2120
1584 2121
1585 2122
1586 2123
1587 2124
1588 2125
1589 2126
1590 2127
1591 2128
1592 2129
1593 2130
1594 2131
1595 2132
1596 2133
1597 2134
1598 2135
1599 2136
1600 2137
1601 2138
1602 2139
1603 2140
1604 2141
1605 2142
1606 2143
1607 2144
1608 2145
1609 2146
1610 2147
1611 2148
1612 2149
1613 2150
1614 2151
1615 2152
1616 2153
1617 2154
1618 2155
1619 drop table t2;