Debian Codegraph @

mysql-test/suite/parts/r/partition_datetime_innodb.result

raw
1 create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
43 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 select count(*) from t2;
64 count(*)
65 59
66 select * from t2;
67 a
68 1971-01-01 00:00:01
69 1971-01-01 00:00:02
70 1971-01-01 00:00:03
71 1971-01-01 00:00:04
72 1971-01-01 00:00:05
73 1971-01-01 00:00:06
74 1971-01-01 00:00:07
75 1971-01-01 00:00:08
76 1971-01-01 00:00:09
77 1971-01-01 00:00:10
78 1971-01-01 00:00:11
79 1971-01-01 00:00:12
80 1971-01-01 00:00:13
81 1971-01-01 00:00:14
82 1971-01-01 00:00:15
83 1971-01-01 00:00:16
84 1971-01-01 00:00:17
85 1971-01-01 00:00:18
86 1971-01-01 00:00:19
87 1971-01-01 00:00:20
88 1971-01-01 00:00:21
89 1971-01-01 00:00:22
90 1971-01-01 00:00:23
91 1971-01-01 00:00:24
92 1971-01-01 00:00:25
93 1971-01-01 00:00:26
94 1971-01-01 00:00:27
95 1971-01-01 00:00:28
96 1971-01-01 00:00:29
97 1971-01-01 00:00:30
98 1971-01-01 00:00:31
99 1971-01-01 00:00:32
100 1971-01-01 00:00:33
101 1971-01-01 00:00:34
102 1971-01-01 00:00:35
103 1971-01-01 00:00:36
104 1971-01-01 00:00:37
105 1971-01-01 00:00:38
106 1971-01-01 00:00:39
107 1971-01-01 00:00:40
108 1971-01-01 00:00:41
109 1971-01-01 00:00:42
110 1971-01-01 00:00:43
111 1971-01-01 00:00:44
112 1971-01-01 00:00:45
113 1971-01-01 00:00:46
114 1971-01-01 00:00:47
115 1971-01-01 00:00:48
116 1971-01-01 00:00:49
117 1971-01-01 00:00:50
118 1971-01-01 00:00:51
119 1971-01-01 00:00:52
120 1971-01-01 00:00:53
121 1971-01-01 00:00:54
122 1971-01-01 00:00:55
123 1971-01-01 00:00:56
124 1971-01-01 00:00:57
125 1971-01-01 00:00:58
126 1971-01-01 00:00:59
127 drop table t2;
128 create table t1 (a date not null, primary key(a)) engine='InnoDB'
129 partition by key (a) (
130 partition pa1 max_rows=20 min_rows=2,
131 partition pa2 max_rows=30 min_rows=3,
132 partition pa3 max_rows=30 min_rows=4,
133 partition pa4 max_rows=40 min_rows=2);
134 show create table t1;
135 Table Create Table
136 t1 CREATE TABLE `t1` (
137 `a` date NOT NULL,
138 PRIMARY KEY (`a`)
139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
140 PARTITION BY KEY (`a`)
141 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
142 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
143 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
144 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
145 insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
146 select * from t1;
147 a
148 1980-10-14
149 2020-12-31
150 1975-01-01
151 2000-06-15
152 select * from t1 where a=19801014;
153 a
154 1980-10-14
155 delete from t1 where a=19801014;
156 select * from t1;
157 a
158 2020-12-31
159 1975-01-01
160 2000-06-15
161 drop table t1;
162 create table t2 (a date not null, primary key(a)) engine='InnoDB'
163 partition by key (a) partitions 12;
164 show create table t2;
165 Table Create Table
166 t2 CREATE TABLE `t2` (
167 `a` date NOT NULL,
168 PRIMARY KEY (`a`)
169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
170 PARTITION BY KEY (`a`)
171 PARTITIONS 12
172 insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
173 select * from t2;
174 a
175 1975-01-01
176 1980-10-14
177 2020-12-31
178 2000-06-15
179 select * from t2 where a='1980-10-14';
180 a
181 1980-10-14
182 delete from t2 where a='1980-10-14';
183 select * from t2;
184 a
185 1975-01-01
186 2020-12-31
187 2000-06-15
188 delete from t2;
189 28 inserts;
190 select count(*) from t2;
191 count(*)
192 84
193 select * from t2;
194 a
195 1970-01-04
196 1970-01-12
197 1970-01-14
198 1970-01-24
199 1970-01-28
200 1970-02-02
201 1970-02-04
202 1970-02-10
203 1970-02-14
204 1970-02-16
205 1970-03-04
206 1970-03-06
207 1970-03-12
208 1970-01-13
209 1970-01-27
210 1970-02-03
211 1970-02-09
212 1970-02-13
213 1970-02-21
214 1970-03-05
215 1970-03-07
216 1970-03-09
217 1970-03-15
218 1970-03-23
219 1970-01-06
220 1970-01-08
221 1970-01-16
222 1970-01-18
223 1970-01-22
224 1970-02-06
225 1970-02-12
226 1970-02-20
227 1970-02-22
228 1970-02-28
229 1970-03-02
230 1970-03-08
231 1970-03-16
232 1970-03-20
233 1970-03-22
234 1970-03-24
235 1970-01-05
236 1970-01-07
237 1970-01-11
238 1970-01-15
239 1970-01-21
240 1970-02-01
241 1970-02-11
242 1970-02-15
243 1970-02-19
244 1970-03-03
245 1970-03-11
246 1970-03-13
247 1970-03-17
248 1970-03-19
249 1970-03-25
250 1970-03-27
251 1970-01-02
252 1970-01-10
253 1970-01-20
254 1970-01-26
255 1970-02-08
256 1970-02-18
257 1970-02-24
258 1970-02-26
259 1970-03-10
260 1970-03-14
261 1970-03-18
262 1970-03-26
263 1970-03-28
264 1970-01-01
265 1970-01-03
266 1970-01-09
267 1970-01-17
268 1970-01-19
269 1970-01-23
270 1970-01-25
271 1970-02-05
272 1970-02-07
273 1970-02-17
274 1970-02-23
275 1970-02-25
276 1970-02-27
277 1970-03-01
278 1970-03-21
279 drop table t2;
280 create table t3 (a date not null, primary key(a)) engine='InnoDB'
281 partition by range (month(a)) subpartition by key (a)
282 subpartitions 3 (
283 partition quarter1 values less than (4),
284 partition quarter2 values less than (7),
285 partition quarter3 values less than (10),
286 partition quarter4 values less than (13)
287 );
288 show create table t3;
289 Table Create Table
290 t3 CREATE TABLE `t3` (
291 `a` date NOT NULL,
292 PRIMARY KEY (`a`)
293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
294 PARTITION BY RANGE (month(`a`))
295 SUBPARTITION BY KEY (`a`)
296 SUBPARTITIONS 3
297 (PARTITION `quarter1` VALUES LESS THAN (4) ENGINE = InnoDB,
298 PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = InnoDB,
299 PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = InnoDB,
300 PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = InnoDB)
301 12 inserts;
302 select count(*) from t3;
303 count(*)
304 12
305 select * from t3;
306 a
307 1970-02-01
308 1970-01-01
309 1970-03-01
310 1970-04-01
311 1970-05-01
312 1970-06-01
313 1970-09-01
314 1970-07-01
315 1970-08-01
316 1970-12-01
317 1970-10-01
318 1970-11-01
319 drop table t3;
320 create table t4 (a date not null, primary key(a)) engine='InnoDB'
321 partition by list (month(a)) subpartition by key (a)
322 subpartitions 3 (
323 partition quarter1 values in (1,2,3),
324 partition quarter2 values in (4,5,6),
325 partition quarter3 values in (7,8,9),
326 partition quarter4 values in (10,11,12)
327 );
328 show create table t4;
329 Table Create Table
330 t4 CREATE TABLE `t4` (
331 `a` date NOT NULL,
332 PRIMARY KEY (`a`)
333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
334 PARTITION BY LIST (month(`a`))
335 SUBPARTITION BY KEY (`a`)
336 SUBPARTITIONS 3
337 (PARTITION `quarter1` VALUES IN (1,2,3) ENGINE = InnoDB,
338 PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = InnoDB,
339 PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = InnoDB,
340 PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = InnoDB)
341 12 inserts;
342 select count(*) from t4;
343 count(*)
344 12
345 select * from t4;
346 a
347 1970-02-01
348 1970-01-01
349 1970-03-01
350 1970-04-01
351 1970-05-01
352 1970-06-01
353 1970-09-01
354 1970-07-01
355 1970-08-01
356 1970-12-01
357 1970-10-01
358 1970-11-01
359 drop table t4;
360 create table t1 (a time not null, primary key(a)) engine='InnoDB'
361 partition by key (a) (
362 partition pa1 max_rows=20 min_rows=2,
363 partition pa2 max_rows=30 min_rows=3,
364 partition pa3 max_rows=30 min_rows=4,
365 partition pa4 max_rows=40 min_rows=2);
366 show create table t1;
367 Table Create Table
368 t1 CREATE TABLE `t1` (
369 `a` time NOT NULL,
370 PRIMARY KEY (`a`)
371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
372 PARTITION BY KEY (`a`)
373 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
374 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
375 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
376 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
377 insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
378 select * from t1;
379 a
380 03:03:03
381 21:21:21
382 23:59:00
383 12:10:30
384 select * from t1 where a=030303;
385 a
386 03:03:03
387 delete from t1 where a=030303;
388 select * from t1;
389 a
390 21:21:21
391 23:59:00
392 12:10:30
393 drop table t1;
394 create table t2 (a time not null, primary key(a)) engine='InnoDB'
395 partition by key (a) partitions 12;
396 show create table t2;
397 Table Create Table
398 t2 CREATE TABLE `t2` (
399 `a` time NOT NULL,
400 PRIMARY KEY (`a`)
401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
402 PARTITION BY KEY (`a`)
403 PARTITIONS 12
404 insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
405 select * from t2;
406 a
407 10:11:12
408 13:14:15
409 14:15:16
410 00:01:01
411 select * from t2 where a='13:14:15';
412 a
413 13:14:15
414 delete from t2 where a='13:14:15';
415 select * from t2;
416 a
417 10:11:12
418 14:15:16
419 00:01:01
420 delete from t2;
421 59 inserts;
422 select count(*) from t2;
423 count(*)
424 59
425 select * from t2;
426 a
427 00:01:15
428 00:01:23
429 00:01:39
430 00:01:47
431 00:01:59
432 00:01:16
433 00:01:24
434 00:01:40
435 00:01:48
436 00:01:05
437 00:01:13
438 00:01:21
439 00:01:37
440 00:01:45
441 00:01:57
442 00:01:14
443 00:01:22
444 00:01:38
445 00:01:46
446 00:01:58
447 00:01:03
448 00:01:11
449 00:01:19
450 00:01:35
451 00:01:43
452 00:01:55
453 00:01:04
454 00:01:12
455 00:01:20
456 00:01:36
457 00:01:44
458 00:01:56
459 00:01:01
460 00:01:09
461 00:01:29
462 00:01:33
463 00:01:41
464 00:01:53
465 00:01:02
466 00:01:10
467 00:01:18
468 00:01:34
469 00:01:42
470 00:01:54
471 00:01:07
472 00:01:27
473 00:01:31
474 00:01:51
475 00:01:08
476 00:01:28
477 00:01:32
478 00:01:52
479 00:01:17
480 00:01:25
481 00:01:49
482 00:01:06
483 00:01:26
484 00:01:30
485 00:01:50
486 drop table t2;
487 create table t3 (a time not null, primary key(a)) engine='InnoDB'
488 partition by range (second(a)) subpartition by key (a)
489 subpartitions 3 (
490 partition quarter1 values less than (16),
491 partition quarter2 values less than (31),
492 partition quarter3 values less than (46),
493 partition quarter4 values less than (61)
494 );
495 show create table t3;
496 Table Create Table
497 t3 CREATE TABLE `t3` (
498 `a` time NOT NULL,
499 PRIMARY KEY (`a`)
500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
501 PARTITION BY RANGE (second(`a`))
502 SUBPARTITION BY KEY (`a`)
503 SUBPARTITIONS 3
504 (PARTITION `quarter1` VALUES LESS THAN (16) ENGINE = InnoDB,
505 PARTITION `quarter2` VALUES LESS THAN (31) ENGINE = InnoDB,
506 PARTITION `quarter3` VALUES LESS THAN (46) ENGINE = InnoDB,
507 PARTITION `quarter4` VALUES LESS THAN (61) ENGINE = InnoDB)
508 59 inserts;
509 select count(*) from t3;
510 count(*)
511 59
512 select a, second(a), if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) from t3;
513 a second(a) if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4)))
514 10:00:01 1 1
515 10:00:06 6 1
516 10:00:07 7 1
517 10:00:14 14 1
518 10:00:15 15 1
519 10:00:02 2 1
520 10:00:03 3 1
521 10:00:08 8 1
522 10:00:09 9 1
523 10:00:04 4 1
524 10:00:05 5 1
525 10:00:10 10 1
526 10:00:11 11 1
527 10:00:12 12 1
528 10:00:13 13 1
529 10:00:20 20 2
530 10:00:21 21 2
531 10:00:26 26 2
532 10:00:27 27 2
533 10:00:16 16 2
534 10:00:17 17 2
535 10:00:22 22 2
536 10:00:23 23 2
537 10:00:28 28 2
538 10:00:29 29 2
539 10:00:18 18 2
540 10:00:19 19 2
541 10:00:24 24 2
542 10:00:25 25 2
543 10:00:30 30 2
544 10:00:32 32 3
545 10:00:33 33 3
546 10:00:35 35 3
547 10:00:40 40 3
548 10:00:41 41 3
549 10:00:34 34 3
550 10:00:36 36 3
551 10:00:37 37 3
552 10:00:42 42 3
553 10:00:43 43 3
554 10:00:31 31 3
555 10:00:38 38 3
556 10:00:39 39 3
557 10:00:44 44 3
558 10:00:45 45 4
559 10:00:46 46 4
560 10:00:48 48 4
561 10:00:49 49 4
562 10:00:54 54 4
563 10:00:55 55 4
564 10:00:50 50 4
565 10:00:51 51 4
566 10:00:56 56 4
567 10:00:57 57 4
568 10:00:59 59 4
569 10:00:47 47 4
570 10:00:52 52 4
571 10:00:53 53 4
572 10:00:58 58 4
573 drop table t3;
574 create table t4 (a time not null, primary key(a)) engine='InnoDB'
575 partition by list (second(a)) subpartition by key (a)
576 subpartitions 3 (
577 partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
578 partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
579 partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
580 partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
581 );
582 show create table t4;
583 Table Create Table
584 t4 CREATE TABLE `t4` (
585 `a` time NOT NULL,
586 PRIMARY KEY (`a`)
587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
588 PARTITION BY LIST (second(`a`))
589 SUBPARTITION BY KEY (`a`)
590 SUBPARTITIONS 3
591 (PARTITION `quarter1` VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = InnoDB,
592 PARTITION `quarter2` VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
593 PARTITION `quarter3` VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = InnoDB,
594 PARTITION `quarter4` VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB)
595 59 inserts;
596 select count(*) from t4;
597 count(*)
598 59
599 select * from t4;
600 a
601 10:00:01
602 10:00:06
603 10:00:07
604 10:00:14
605 10:00:15
606 10:00:02
607 10:00:03
608 10:00:08
609 10:00:09
610 10:00:04
611 10:00:05
612 10:00:10
613 10:00:11
614 10:00:12
615 10:00:13
616 10:00:20
617 10:00:21
618 10:00:26
619 10:00:27
620 10:00:16
621 10:00:17
622 10:00:22
623 10:00:23
624 10:00:28
625 10:00:29
626 10:00:18
627 10:00:19
628 10:00:24
629 10:00:25
630 10:00:30
631 10:00:32
632 10:00:33
633 10:00:35
634 10:00:40
635 10:00:41
636 10:00:34
637 10:00:36
638 10:00:37
639 10:00:42
640 10:00:43
641 10:00:31
642 10:00:38
643 10:00:39
644 10:00:44
645 10:00:45
646 10:00:46
647 10:00:48
648 10:00:49
649 10:00:54
650 10:00:55
651 10:00:50
652 10:00:51
653 10:00:56
654 10:00:57
655 10:00:59
656 10:00:47
657 10:00:52
658 10:00:53
659 10:00:58
660 drop table t4;
661 create table t1 (a datetime not null, primary key(a)) engine='InnoDB'
662 partition by key (a) (
663 partition pa1 max_rows=20 min_rows=2,
664 partition pa2 max_rows=30 min_rows=3,
665 partition pa3 max_rows=30 min_rows=4,
666 partition pa4 max_rows=40 min_rows=2);
667 show create table t1;
668 Table Create Table
669 t1 CREATE TABLE `t1` (
670 `a` datetime NOT NULL,
671 PRIMARY KEY (`a`)
672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
673 PARTITION BY KEY (`a`)
674 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
675 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
676 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
677 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
678 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');
679 select * from t1;
680 a
681 1975-01-01 21:21:21
682 2000-06-15 23:59:00
683 2020-12-31 12:10:30
684 1980-10-14 03:03:00
685 select * from t1 where a=19801014030300;
686 a
687 1980-10-14 03:03:00
688 delete from t1 where a=19801014030300;
689 select * from t1;
690 a
691 1975-01-01 21:21:21
692 2000-06-15 23:59:00
693 2020-12-31 12:10:30
694 drop table t1;
695 create table t2 (a datetime not null, primary key(a)) engine='InnoDB'
696 partition by key (a) partitions 12;
697 show create table t2;
698 Table Create Table
699 t2 CREATE TABLE `t2` (
700 `a` datetime NOT NULL,
701 PRIMARY KEY (`a`)
702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
703 PARTITION BY KEY (`a`)
704 PARTITIONS 12
705 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');
706 select * from t2;
707 a
708 2020-12-31 10:11:12
709 2000-06-15 14:15:16
710 1980-10-14 13:14:15
711 1975-01-01 00:01:01
712 select * from t2 where a='1980-10-14 13:14:15';
713 a
714 1980-10-14 13:14:15
715 delete from t2 where a='1980-10-14 13:14:15';
716 select * from t2;
717 a
718 2020-12-31 10:11:12
719 2000-06-15 14:15:16
720 1975-01-01 00:01:01
721 delete from t2;
722 59 inserts;
723 select count(*) from t2;
724 count(*)
725 59
726 select * from t2;
727 a
728 1970-01-01 00:00:09
729 1970-01-01 00:00:13
730 1970-01-01 00:00:19
731 1970-01-01 00:00:23
732 1970-01-01 00:00:33
733 1970-01-01 00:00:37
734 1970-01-01 00:00:51
735 1970-01-01 00:00:59
736 1970-01-01 00:00:04
737 1970-01-01 00:00:10
738 1970-01-01 00:00:14
739 1970-01-01 00:00:24
740 1970-01-01 00:00:28
741 1970-01-01 00:00:34
742 1970-01-01 00:00:38
743 1970-01-01 00:00:48
744 1970-01-01 00:00:52
745 1970-01-01 00:00:56
746 1970-01-01 00:00:03
747 1970-01-01 00:00:07
748 1970-01-01 00:00:17
749 1970-01-01 00:00:21
750 1970-01-01 00:00:27
751 1970-01-01 00:00:31
752 1970-01-01 00:00:35
753 1970-01-01 00:00:39
754 1970-01-01 00:00:41
755 1970-01-01 00:00:45
756 1970-01-01 00:00:08
757 1970-01-01 00:00:12
758 1970-01-01 00:00:18
759 1970-01-01 00:00:22
760 1970-01-01 00:00:32
761 1970-01-01 00:00:36
762 1970-01-01 00:00:42
763 1970-01-01 00:00:46
764 1970-01-01 00:00:01
765 1970-01-01 00:00:05
766 1970-01-01 00:00:11
767 1970-01-01 00:00:15
768 1970-01-01 00:00:25
769 1970-01-01 00:00:29
770 1970-01-01 00:00:43
771 1970-01-01 00:00:47
772 1970-01-01 00:00:49
773 1970-01-01 00:00:53
774 1970-01-01 00:00:55
775 1970-01-01 00:00:57
776 1970-01-01 00:00:02
777 1970-01-01 00:00:06
778 1970-01-01 00:00:16
779 1970-01-01 00:00:20
780 1970-01-01 00:00:26
781 1970-01-01 00:00:30
782 1970-01-01 00:00:40
783 1970-01-01 00:00:44
784 1970-01-01 00:00:50
785 1970-01-01 00:00:54
786 1970-01-01 00:00:58
787 drop table t2;
788 create table t3 (a datetime not null, primary key(a)) engine='InnoDB'
789 partition by range (month(a)) subpartition by key (a)
790 subpartitions 3 (
791 partition quarter1 values less than (4),
792 partition quarter2 values less than (7),
793 partition quarter3 values less than (10),
794 partition quarter4 values less than (13)
795 );
796 show create table t3;
797 Table Create Table
798 t3 CREATE TABLE `t3` (
799 `a` datetime NOT NULL,
800 PRIMARY KEY (`a`)
801 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
802 PARTITION BY RANGE (month(`a`))
803 SUBPARTITION BY KEY (`a`)
804 SUBPARTITIONS 3
805 (PARTITION `quarter1` VALUES LESS THAN (4) ENGINE = InnoDB,
806 PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = InnoDB,
807 PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = InnoDB,
808 PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = InnoDB)
809 12 inserts;
810 select count(*) from t3;
811 count(*)
812 12
813 select * from t3;
814 a
815 1970-02-01 00:00:00
816 1970-01-01 00:00:00
817 1970-03-01 00:00:00
818 1970-04-01 00:00:00
819 1970-05-01 00:00:00
820 1970-06-01 00:00:00
821 1970-08-01 00:00:00
822 1970-09-01 00:00:00
823 1970-07-01 00:00:00
824 1970-10-01 00:00:00
825 1970-11-01 00:00:00
826 1970-12-01 00:00:00
827 drop table t3;
828 create table t4 (a datetime not null, primary key(a)) engine='InnoDB'
829 partition by list (month(a)) subpartition by key (a)
830 subpartitions 3 (
831 partition quarter1 values in (1,2,3),
832 partition quarter2 values in (4,5,6),
833 partition quarter3 values in (7,8,9),
834 partition quarter4 values in (10,11,12)
835 );
836 show create table t4;
837 Table Create Table
838 t4 CREATE TABLE `t4` (
839 `a` datetime NOT NULL,
840 PRIMARY KEY (`a`)
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
842 PARTITION BY LIST (month(`a`))
843 SUBPARTITION BY KEY (`a`)
844 SUBPARTITIONS 3
845 (PARTITION `quarter1` VALUES IN (1,2,3) ENGINE = InnoDB,
846 PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = InnoDB,
847 PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = InnoDB,
848 PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = InnoDB)
849 12 inserts;
850 select count(*) from t4;
851 count(*)
852 12
853 select * from t4;
854 a
855 1970-02-01 00:00:00
856 1970-01-01 00:00:00
857 1970-03-01 00:00:00
858 1970-04-01 00:00:00
859 1970-05-01 00:00:00
860 1970-06-01 00:00:00
861 1970-08-01 00:00:00
862 1970-09-01 00:00:00
863 1970-07-01 00:00:00
864 1970-10-01 00:00:00
865 1970-11-01 00:00:00
866 1970-12-01 00:00:00
867 drop table t4;
868 create table t1 (a year not null, primary key(a)) engine='InnoDB'
869 partition by key (a) (
870 partition pa1 max_rows=20 min_rows=2,
871 partition pa2 max_rows=30 min_rows=3,
872 partition pa3 max_rows=30 min_rows=4,
873 partition pa4 max_rows=40 min_rows=2);
874 show create table t1;
875 Table Create Table
876 t1 CREATE TABLE `t1` (
877 `a` year(4) NOT NULL,
878 PRIMARY KEY (`a`)
879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
880 PARTITION BY KEY (`a`)
881 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
882 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
883 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
884 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
885 insert into t1 values ('1975'), (2020), ('1980'), ('2000');
886 select * from t1;
887 a
888 1980
889 2000
890 2020
891 1975
892 select * from t1 where a=1980;
893 a
894 1980
895 delete from t1 where a=1980;
896 select * from t1;
897 a
898 2000
899 2020
900 1975
901 drop table t1;
902 create table t2 (a year not null, primary key(a)) engine='InnoDB'
903 partition by key (a) partitions 12;
904 show create table t2;
905 Table Create Table
906 t2 CREATE TABLE `t2` (
907 `a` year(4) NOT NULL,
908 PRIMARY KEY (`a`)
909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
910 PARTITION BY KEY (`a`)
911 PARTITIONS 12
912 insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
913 select * from t2;
914 a
915 2020
916 1975
917 1980
918 2000
919 select * from t2 where a='1980';
920 a
921 1980
922 delete from t2 where a='1980';
923 select * from t2;
924 a
925 2020
926 1975
927 2000
928 delete from t2;
929 255 inserts;
930 Warnings:
931 Warning 1264 Out of range value for column 'a' at row 1
932 select count(*) from t2;
933 count(*)
934 255
935 select * from t2;
936 a
937 1909
938 1921
939 1933
940 1945
941 1957
942 1969
943 1981
944 1993
945 2001
946 2013
947 2029
948 2041
949 2053
950 2065
951 2077
952 2089
953 2100
954 2101
955 2102
956 2103
957 2114
958 2132
959 2133
960 2134
961 2135
962 2146
963 1904
964 1916
965 1928
966 1932
967 1940
968 1952
969 1964
970 1976
971 1988
972 2008
973 2020
974 2024
975 2036
976 2048
977 2060
978 2072
979 2084
980 1907
981 1919
982 1931
983 1943
984 1955
985 1967
986 1979
987 1991
988 1999
989 2011
990 2027
991 2039
992 2051
993 2063
994 2075
995 2087
996 2097
997 2099
998 2110
999 2111
1000 2112
1001 2113
1002 2123
1003 2129
1004 2131
1005 2142
1006 2143
1007 2144
1008 2145
1009 2147
1010 1902
1011 1914
1012 1926
1013 1938
1014 1950
1015 1962
1016 1974
1017 1986
1018 2006
1019 2018
1020 2034
1021 2046
1022 2058
1023 2070
1024 2082
1025 1905
1026 1917
1027 1929
1028 1941
1029 1953
1030 1965
1031 1977
1032 1989
1033 1997
1034 2009
1035 2021
1036 2025
1037 2037
1038 2049
1039 2061
1040 2073
1041 2085
1042 2092
1043 2093
1044 2094
1045 2095
1046 2120
1047 2121
1048 2124
1049 2125
1050 2126
1051 2127
1052 2152
1053 2153
1054 0000
1055 1912
1056 1924
1057 1936
1058 1948
1059 1960
1060 1972
1061 1984
1062 1996
1063 2004
1064 2016
1065 2032
1066 2044
1067 2056
1068 2068
1069 2080
1070 2116
1071 2148
1072 1903
1073 1915
1074 1927
1075 1939
1076 1951
1077 1963
1078 1975
1079 1987
1080 2007
1081 2019
1082 2023
1083 2035
1084 2047
1085 2059
1086 2071
1087 2083
1088 2155
1089 1910
1090 1922
1091 1934
1092 1946
1093 1958
1094 1970
1095 1982
1096 1994
1097 2002
1098 2014
1099 2030
1100 2042
1101 2054
1102 2066
1103 2078
1104 2090
1105 1913
1106 1925
1107 1937
1108 1949
1109 1961
1110 1973
1111 1985
1112 2005
1113 2017
1114 2033
1115 2045
1116 2057
1117 2069
1118 2081
1119 2104
1120 2105
1121 2106
1122 2107
1123 2117
1124 2118
1125 2119
1126 2136
1127 2137
1128 2138
1129 2139
1130 2149
1131 2150
1132 2151
1133 1908
1134 1920
1135 1944
1136 1956
1137 1968
1138 1980
1139 1992
1140 2000
1141 2012
1142 2028
1143 2040
1144 2052
1145 2064
1146 2076
1147 2088
1148 2096
1149 2098
1150 2108
1151 2128
1152 2130
1153 2140
1154 1911
1155 1923
1156 1935
1157 1947
1158 1959
1159 1971
1160 1983
1161 1995
1162 2003
1163 2015
1164 2031
1165 2043
1166 2055
1167 2067
1168 2079
1169 2091
1170 2115
1171 1906
1172 1918
1173 1930
1174 1942
1175 1954
1176 1966
1177 1978
1178 1990
1179 1998
1180 2010
1181 2022
1182 2026
1183 2038
1184 2050
1185 2062
1186 2074
1187 2086
1188 2109
1189 2122
1190 2141
1191 2154
1192 drop table t2;