Debian Codegraph @

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

raw
1 create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='MyISAM'
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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 PARTITION BY KEY (`a`)
14 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
15 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
16 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
17 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
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='MyISAM'
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=MyISAM 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='MyISAM'
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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
140 PARTITION BY KEY (`a`)
141 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
142 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
143 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
144 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
145 insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
146 select * from t1;
147 a
148 1975-01-01
149 1980-10-14
150 2000-06-15
151 2020-12-31
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 1975-01-01
159 2000-06-15
160 2020-12-31
161 drop table t1;
162 create table t2 (a date not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 2000-06-15
178 2020-12-31
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 2000-06-15
187 2020-12-31
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-01
196 1970-01-02
197 1970-01-03
198 1970-01-04
199 1970-01-05
200 1970-01-06
201 1970-01-07
202 1970-01-08
203 1970-01-09
204 1970-01-10
205 1970-01-11
206 1970-01-12
207 1970-01-13
208 1970-01-14
209 1970-01-15
210 1970-01-16
211 1970-01-17
212 1970-01-18
213 1970-01-19
214 1970-01-20
215 1970-01-21
216 1970-01-22
217 1970-01-23
218 1970-01-24
219 1970-01-25
220 1970-01-26
221 1970-01-27
222 1970-01-28
223 1970-02-01
224 1970-02-02
225 1970-02-03
226 1970-02-04
227 1970-02-05
228 1970-02-06
229 1970-02-07
230 1970-02-08
231 1970-02-09
232 1970-02-10
233 1970-02-11
234 1970-02-12
235 1970-02-13
236 1970-02-14
237 1970-02-15
238 1970-02-16
239 1970-02-17
240 1970-02-18
241 1970-02-19
242 1970-02-20
243 1970-02-21
244 1970-02-22
245 1970-02-23
246 1970-02-24
247 1970-02-25
248 1970-02-26
249 1970-02-27
250 1970-02-28
251 1970-03-01
252 1970-03-02
253 1970-03-03
254 1970-03-04
255 1970-03-05
256 1970-03-06
257 1970-03-07
258 1970-03-08
259 1970-03-09
260 1970-03-10
261 1970-03-11
262 1970-03-12
263 1970-03-13
264 1970-03-14
265 1970-03-15
266 1970-03-16
267 1970-03-17
268 1970-03-18
269 1970-03-19
270 1970-03-20
271 1970-03-21
272 1970-03-22
273 1970-03-23
274 1970-03-24
275 1970-03-25
276 1970-03-26
277 1970-03-27
278 1970-03-28
279 drop table t2;
280 create table t3 (a date not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 = MyISAM,
298 PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = MyISAM,
299 PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = MyISAM,
300 PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = MyISAM)
301 12 inserts;
302 select count(*) from t3;
303 count(*)
304 12
305 select * from t3;
306 a
307 1970-01-01
308 1970-02-01
309 1970-03-01
310 1970-04-01
311 1970-05-01
312 1970-06-01
313 1970-07-01
314 1970-08-01
315 1970-09-01
316 1970-10-01
317 1970-11-01
318 1970-12-01
319 drop table t3;
320 create table t4 (a date not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 = MyISAM,
338 PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = MyISAM,
339 PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = MyISAM,
340 PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = MyISAM)
341 12 inserts;
342 select count(*) from t4;
343 count(*)
344 12
345 select * from t4;
346 a
347 1970-01-01
348 1970-02-01
349 1970-03-01
350 1970-04-01
351 1970-05-01
352 1970-06-01
353 1970-07-01
354 1970-08-01
355 1970-09-01
356 1970-10-01
357 1970-11-01
358 1970-12-01
359 drop table t4;
360 create table t1 (a time not null, primary key(a)) engine='MyISAM'
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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
372 PARTITION BY KEY (`a`)
373 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
374 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
375 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
376 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
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 12:10:30
382 21:21:21
383 23:59:00
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 12:10:30
391 21:21:21
392 23:59:00
393 drop table t1;
394 create table t2 (a time not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 00:01:01
408 10:11:12
409 13:14:15
410 14:15:16
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 00:01:01
418 10:11:12
419 14:15:16
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:01
428 00:01:02
429 00:01:03
430 00:01:04
431 00:01:05
432 00:01:06
433 00:01:07
434 00:01:08
435 00:01:09
436 00:01:10
437 00:01:11
438 00:01:12
439 00:01:13
440 00:01:14
441 00:01:15
442 00:01:16
443 00:01:17
444 00:01:18
445 00:01:19
446 00:01:20
447 00:01:21
448 00:01:22
449 00:01:23
450 00:01:24
451 00:01:25
452 00:01:26
453 00:01:27
454 00:01:28
455 00:01:29
456 00:01:30
457 00:01:31
458 00:01:32
459 00:01:33
460 00:01:34
461 00:01:35
462 00:01:36
463 00:01:37
464 00:01:38
465 00:01:39
466 00:01:40
467 00:01:41
468 00:01:42
469 00:01:43
470 00:01:44
471 00:01:45
472 00:01:46
473 00:01:47
474 00:01:48
475 00:01:49
476 00:01:50
477 00:01:51
478 00:01:52
479 00:01:53
480 00:01:54
481 00:01:55
482 00:01:56
483 00:01:57
484 00:01:58
485 00:01:59
486 drop table t2;
487 create table t3 (a time not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 = MyISAM,
505 PARTITION `quarter2` VALUES LESS THAN (31) ENGINE = MyISAM,
506 PARTITION `quarter3` VALUES LESS THAN (46) ENGINE = MyISAM,
507 PARTITION `quarter4` VALUES LESS THAN (61) ENGINE = MyISAM)
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:02 2 1
516 10:00:03 3 1
517 10:00:04 4 1
518 10:00:05 5 1
519 10:00:06 6 1
520 10:00:07 7 1
521 10:00:08 8 1
522 10:00:09 9 1
523 10:00:10 10 1
524 10:00:11 11 1
525 10:00:12 12 1
526 10:00:13 13 1
527 10:00:14 14 1
528 10:00:15 15 1
529 10:00:16 16 2
530 10:00:17 17 2
531 10:00:18 18 2
532 10:00:19 19 2
533 10:00:20 20 2
534 10:00:21 21 2
535 10:00:22 22 2
536 10:00:23 23 2
537 10:00:24 24 2
538 10:00:25 25 2
539 10:00:26 26 2
540 10:00:27 27 2
541 10:00:28 28 2
542 10:00:29 29 2
543 10:00:30 30 2
544 10:00:31 31 3
545 10:00:32 32 3
546 10:00:33 33 3
547 10:00:34 34 3
548 10:00:35 35 3
549 10:00:36 36 3
550 10:00:37 37 3
551 10:00:38 38 3
552 10:00:39 39 3
553 10:00:40 40 3
554 10:00:41 41 3
555 10:00:42 42 3
556 10:00:43 43 3
557 10:00:44 44 3
558 10:00:45 45 4
559 10:00:46 46 4
560 10:00:47 47 4
561 10:00:48 48 4
562 10:00:49 49 4
563 10:00:50 50 4
564 10:00:51 51 4
565 10:00:52 52 4
566 10:00:53 53 4
567 10:00:54 54 4
568 10:00:55 55 4
569 10:00:56 56 4
570 10:00:57 57 4
571 10:00:58 58 4
572 10:00:59 59 4
573 drop table t3;
574 create table t4 (a time not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 = MyISAM,
592 PARTITION `quarter2` VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
593 PARTITION `quarter3` VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = MyISAM,
594 PARTITION `quarter4` VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM)
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:02
603 10:00:03
604 10:00:04
605 10:00:05
606 10:00:06
607 10:00:07
608 10:00:08
609 10:00:09
610 10:00:10
611 10:00:11
612 10:00:12
613 10:00:13
614 10:00:14
615 10:00:15
616 10:00:16
617 10:00:17
618 10:00:18
619 10:00:19
620 10:00:20
621 10:00:21
622 10:00:22
623 10:00:23
624 10:00:24
625 10:00:25
626 10:00:26
627 10:00:27
628 10:00:28
629 10:00:29
630 10:00:30
631 10:00:31
632 10:00:32
633 10:00:33
634 10:00:34
635 10:00:35
636 10:00:36
637 10:00:37
638 10:00:38
639 10:00:39
640 10:00:40
641 10:00:41
642 10:00:42
643 10:00:43
644 10:00:44
645 10:00:45
646 10:00:46
647 10:00:47
648 10:00:48
649 10:00:49
650 10:00:50
651 10:00:51
652 10:00:52
653 10:00:53
654 10:00:54
655 10:00:55
656 10:00:56
657 10:00:57
658 10:00:58
659 10:00:59
660 drop table t4;
661 create table t1 (a datetime not null, primary key(a)) engine='MyISAM'
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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
673 PARTITION BY KEY (`a`)
674 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
675 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
676 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
677 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
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 1980-10-14 03:03:00
683 2000-06-15 23:59:00
684 2020-12-31 12:10:30
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='MyISAM'
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=MyISAM 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 1975-01-01 00:01:01
709 1980-10-14 13:14:15
710 2000-06-15 14:15:16
711 2020-12-31 10:11:12
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 1975-01-01 00:01:01
719 2000-06-15 14:15:16
720 2020-12-31 10:11:12
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:01
729 1970-01-01 00:00:02
730 1970-01-01 00:00:03
731 1970-01-01 00:00:04
732 1970-01-01 00:00:05
733 1970-01-01 00:00:06
734 1970-01-01 00:00:07
735 1970-01-01 00:00:08
736 1970-01-01 00:00:09
737 1970-01-01 00:00:10
738 1970-01-01 00:00:11
739 1970-01-01 00:00:12
740 1970-01-01 00:00:13
741 1970-01-01 00:00:14
742 1970-01-01 00:00:15
743 1970-01-01 00:00:16
744 1970-01-01 00:00:17
745 1970-01-01 00:00:18
746 1970-01-01 00:00:19
747 1970-01-01 00:00:20
748 1970-01-01 00:00:21
749 1970-01-01 00:00:22
750 1970-01-01 00:00:23
751 1970-01-01 00:00:24
752 1970-01-01 00:00:25
753 1970-01-01 00:00:26
754 1970-01-01 00:00:27
755 1970-01-01 00:00:28
756 1970-01-01 00:00:29
757 1970-01-01 00:00:30
758 1970-01-01 00:00:31
759 1970-01-01 00:00:32
760 1970-01-01 00:00:33
761 1970-01-01 00:00:34
762 1970-01-01 00:00:35
763 1970-01-01 00:00:36
764 1970-01-01 00:00:37
765 1970-01-01 00:00:38
766 1970-01-01 00:00:39
767 1970-01-01 00:00:40
768 1970-01-01 00:00:41
769 1970-01-01 00:00:42
770 1970-01-01 00:00:43
771 1970-01-01 00:00:44
772 1970-01-01 00:00:45
773 1970-01-01 00:00:46
774 1970-01-01 00:00:47
775 1970-01-01 00:00:48
776 1970-01-01 00:00:49
777 1970-01-01 00:00:50
778 1970-01-01 00:00:51
779 1970-01-01 00:00:52
780 1970-01-01 00:00:53
781 1970-01-01 00:00:54
782 1970-01-01 00:00:55
783 1970-01-01 00:00:56
784 1970-01-01 00:00:57
785 1970-01-01 00:00:58
786 1970-01-01 00:00:59
787 drop table t2;
788 create table t3 (a datetime not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 = MyISAM,
806 PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = MyISAM,
807 PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = MyISAM,
808 PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = MyISAM)
809 12 inserts;
810 select count(*) from t3;
811 count(*)
812 12
813 select * from t3;
814 a
815 1970-01-01 00:00:00
816 1970-02-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-07-01 00:00:00
822 1970-08-01 00:00:00
823 1970-09-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='MyISAM'
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=MyISAM 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 = MyISAM,
846 PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = MyISAM,
847 PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = MyISAM,
848 PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = MyISAM)
849 12 inserts;
850 select count(*) from t4;
851 count(*)
852 12
853 select * from t4;
854 a
855 1970-01-01 00:00:00
856 1970-02-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-07-01 00:00:00
862 1970-08-01 00:00:00
863 1970-09-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='MyISAM'
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=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
880 PARTITION BY KEY (`a`)
881 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
882 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
883 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
884 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM)
885 insert into t1 values ('1975'), (2020), ('1980'), ('2000');
886 select * from t1;
887 a
888 1975
889 1980
890 2000
891 2020
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 1975
899 2000
900 2020
901 drop table t1;
902 create table t2 (a year not null, primary key(a)) engine='MyISAM'
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=MyISAM 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 1975
916 1980
917 2000
918 2020
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 1975
926 2000
927 2020
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 0000
938 1902
939 1903
940 1904
941 1905
942 1906
943 1907
944 1908
945 1909
946 1910
947 1911
948 1912
949 1913
950 1914
951 1915
952 1916
953 1917
954 1918
955 1919
956 1920
957 1921
958 1922
959 1923
960 1924
961 1925
962 1926
963 1927
964 1928
965 1929
966 1930
967 1931
968 1932
969 1933
970 1934
971 1935
972 1936
973 1937
974 1938
975 1939
976 1940
977 1941
978 1942
979 1943
980 1944
981 1945
982 1946
983 1947
984 1948
985 1949
986 1950
987 1951
988 1952
989 1953
990 1954
991 1955
992 1956
993 1957
994 1958
995 1959
996 1960
997 1961
998 1962
999 1963
1000 1964
1001 1965
1002 1966
1003 1967
1004 1968
1005 1969
1006 1970
1007 1971
1008 1972
1009 1973
1010 1974
1011 1975
1012 1976
1013 1977
1014 1978
1015 1979
1016 1980
1017 1981
1018 1982
1019 1983
1020 1984
1021 1985
1022 1986
1023 1987
1024 1988
1025 1989
1026 1990
1027 1991
1028 1992
1029 1993
1030 1994
1031 1995
1032 1996
1033 1997
1034 1998
1035 1999
1036 2000
1037 2001
1038 2002
1039 2003
1040 2004
1041 2005
1042 2006
1043 2007
1044 2008
1045 2009
1046 2010
1047 2011
1048 2012
1049 2013
1050 2014
1051 2015
1052 2016
1053 2017
1054 2018
1055 2019
1056 2020
1057 2021
1058 2022
1059 2023
1060 2024
1061 2025
1062 2026
1063 2027
1064 2028
1065 2029
1066 2030
1067 2031
1068 2032
1069 2033
1070 2034
1071 2035
1072 2036
1073 2037
1074 2038
1075 2039
1076 2040
1077 2041
1078 2042
1079 2043
1080 2044
1081 2045
1082 2046
1083 2047
1084 2048
1085 2049
1086 2050
1087 2051
1088 2052
1089 2053
1090 2054
1091 2055
1092 2056
1093 2057
1094 2058
1095 2059
1096 2060
1097 2061
1098 2062
1099 2063
1100 2064
1101 2065
1102 2066
1103 2067
1104 2068
1105 2069
1106 2070
1107 2071
1108 2072
1109 2073
1110 2074
1111 2075
1112 2076
1113 2077
1114 2078
1115 2079
1116 2080
1117 2081
1118 2082
1119 2083
1120 2084
1121 2085
1122 2086
1123 2087
1124 2088
1125 2089
1126 2090
1127 2091
1128 2092
1129 2093
1130 2094
1131 2095
1132 2096
1133 2097
1134 2098
1135 2099
1136 2100
1137 2101
1138 2102
1139 2103
1140 2104
1141 2105
1142 2106
1143 2107
1144 2108
1145 2109
1146 2110
1147 2111
1148 2112
1149 2113
1150 2114
1151 2115
1152 2116
1153 2117
1154 2118
1155 2119
1156 2120
1157 2121
1158 2122
1159 2123
1160 2124
1161 2125
1162 2126
1163 2127
1164 2128
1165 2129
1166 2130
1167 2131
1168 2132
1169 2133
1170 2134
1171 2135
1172 2136
1173 2137
1174 2138
1175 2139
1176 2140
1177 2141
1178 2142
1179 2143
1180 2144
1181 2145
1182 2146
1183 2147
1184 2148
1185 2149
1186 2150
1187 2151
1188 2152
1189 2153
1190 2154
1191 2155
1192 drop table t2;