1
create table t1 (a timestamp not null, primary key(a)) engine='MyISAM'
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);
10
`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
12
) ENGINE=MyISAM DEFAULT CHARSET=latin1
13
/*!50100 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');
25
select * from t1 where a=19801014030300;
28
delete from t1 where a=19801014030300;
35
create table t2 (a timestamp not null, primary key(a)) engine='MyISAM'
36
partition by key (a) partitions 12;
39
t2 CREATE TABLE `t2` (
40
`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
42
) ENGINE=MyISAM DEFAULT CHARSET=latin1
43
/*!50100 PARTITION BY KEY (a)
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');
52
select * from t2 where a='1980-10-14 13:14:15';
55
delete from t2 where a='1980-10-14 13:14:15';
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;
187
create table t1 (a date not null, primary key(a)) engine='MyISAM'
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;
195
t1 CREATE TABLE `t1` (
198
) ENGINE=MyISAM DEFAULT CHARSET=latin1
199
/*!50100 PARTITION BY KEY (a)
200
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
201
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
202
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
203
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) */
204
insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
211
select * from t1 where a=19801014;
214
delete from t1 where a=19801014;
221
create table t2 (a date not null, primary key(a)) engine='MyISAM'
222
partition by key (a) partitions 12;
223
show create table t2;
225
t2 CREATE TABLE `t2` (
228
) ENGINE=MyISAM DEFAULT CHARSET=latin1
229
/*!50100 PARTITION BY KEY (a)
231
insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
238
select * from t2 where a='1980-10-14';
241
delete from t2 where a='1980-10-14';
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;
423
create table t3 (a date not null, primary key(a)) engine='MyISAM'
424
partition by range (month(a)) subpartition by key (a)
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)
431
show create table t3;
433
t3 CREATE TABLE `t3` (
436
) ENGINE=MyISAM DEFAULT CHARSET=latin1
437
/*!50100 PARTITION BY RANGE (month(a))
438
SUBPARTITION BY KEY (a)
440
(PARTITION quarter1 VALUES LESS THAN (4) ENGINE = MyISAM,
441
PARTITION quarter2 VALUES LESS THAN (7) ENGINE = MyISAM,
442
PARTITION quarter3 VALUES LESS THAN (10) ENGINE = MyISAM,
443
PARTITION quarter4 VALUES LESS THAN (13) ENGINE = MyISAM) */
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;
475
create table t4 (a date not null, primary key(a)) engine='MyISAM'
476
partition by list (month(a)) subpartition by key (a)
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)
483
show create table t4;
485
t4 CREATE TABLE `t4` (
488
) ENGINE=MyISAM DEFAULT CHARSET=latin1
489
/*!50100 PARTITION BY LIST (month(a))
490
SUBPARTITION BY KEY (a)
492
(PARTITION quarter1 VALUES IN (1,2,3) ENGINE = MyISAM,
493
PARTITION quarter2 VALUES IN (4,5,6) ENGINE = MyISAM,
494
PARTITION quarter3 VALUES IN (7,8,9) ENGINE = MyISAM,
495
PARTITION quarter4 VALUES IN (10,11,12) ENGINE = MyISAM) */
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;
527
create table t1 (a time not null, primary key(a)) engine='MyISAM'
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;
535
t1 CREATE TABLE `t1` (
538
) ENGINE=MyISAM DEFAULT CHARSET=latin1
539
/*!50100 PARTITION BY KEY (a)
540
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
541
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
542
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
543
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) */
544
insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
551
select * from t1 where a=030303;
554
delete from t1 where a=030303;
561
create table t2 (a time not null, primary key(a)) engine='MyISAM'
562
partition by key (a) partitions 12;
563
show create table t2;
565
t2 CREATE TABLE `t2` (
568
) ENGINE=MyISAM DEFAULT CHARSET=latin1
569
/*!50100 PARTITION BY KEY (a)
571
insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
578
select * from t2 where a='13:14:15';
581
delete from t2 where a='13:14:15';
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;
713
create table t3 (a time not null, primary key(a)) engine='MyISAM'
714
partition by range (second(a)) subpartition by key (a)
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)
721
show create table t3;
723
t3 CREATE TABLE `t3` (
726
) ENGINE=MyISAM DEFAULT CHARSET=latin1
727
/*!50100 PARTITION BY RANGE (second(a))
728
SUBPARTITION BY KEY (a)
730
(PARTITION quarter1 VALUES LESS THAN (16) ENGINE = MyISAM,
731
PARTITION quarter2 VALUES LESS THAN (31) ENGINE = MyISAM,
732
PARTITION quarter3 VALUES LESS THAN (46) ENGINE = MyISAM,
733
PARTITION quarter4 VALUES LESS THAN (61) ENGINE = MyISAM) */
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;
859
create table t4 (a time not null, primary key(a)) engine='MyISAM'
860
partition by list (second(a)) subpartition by key (a)
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)
867
show create table t4;
869
t4 CREATE TABLE `t4` (
872
) ENGINE=MyISAM DEFAULT CHARSET=latin1
873
/*!50100 PARTITION BY LIST (second(a))
874
SUBPARTITION BY KEY (a)
876
(PARTITION quarter1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = MyISAM,
877
PARTITION quarter2 VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
878
PARTITION quarter3 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = MyISAM,
879
PARTITION quarter4 VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
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;
1005
create table t1 (a datetime not null, primary key(a)) engine='MyISAM'
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;
1013
t1 CREATE TABLE `t1` (
1014
`a` datetime NOT NULL,
1016
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1017
/*!50100 PARTITION BY KEY (a)
1018
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
1019
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
1020
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
1021
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) */
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');
1029
select * from t1 where a=19801014030300;
1032
delete from t1 where a=19801014030300;
1039
create table t2 (a datetime not null, primary key(a)) engine='MyISAM'
1040
partition by key (a) partitions 12;
1041
show create table t2;
1043
t2 CREATE TABLE `t2` (
1044
`a` datetime NOT NULL,
1046
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1047
/*!50100 PARTITION BY KEY (a)
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');
1056
select * from t2 where a='1980-10-14 13:14:15';
1059
delete from t2 where a='1980-10-14 13:14:15';
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;
1191
create table t3 (a datetime not null, primary key(a)) engine='MyISAM'
1192
partition by range (month(a)) subpartition by key (a)
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)
1199
show create table t3;
1201
t3 CREATE TABLE `t3` (
1202
`a` datetime NOT NULL,
1204
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1205
/*!50100 PARTITION BY RANGE (month(a))
1206
SUBPARTITION BY KEY (a)
1208
(PARTITION quarter1 VALUES LESS THAN (4) ENGINE = MyISAM,
1209
PARTITION quarter2 VALUES LESS THAN (7) ENGINE = MyISAM,
1210
PARTITION quarter3 VALUES LESS THAN (10) ENGINE = MyISAM,
1211
PARTITION quarter4 VALUES LESS THAN (13) ENGINE = MyISAM) */
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;
1243
create table t4 (a datetime not null, primary key(a)) engine='MyISAM'
1244
partition by list (month(a)) subpartition by key (a)
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)
1251
show create table t4;
1253
t4 CREATE TABLE `t4` (
1254
`a` datetime NOT NULL,
1256
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1257
/*!50100 PARTITION BY LIST (month(a))
1258
SUBPARTITION BY KEY (a)
1260
(PARTITION quarter1 VALUES IN (1,2,3) ENGINE = MyISAM,
1261
PARTITION quarter2 VALUES IN (4,5,6) ENGINE = MyISAM,
1262
PARTITION quarter3 VALUES IN (7,8,9) ENGINE = MyISAM,
1263
PARTITION quarter4 VALUES IN (10,11,12) ENGINE = MyISAM) */
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;
1295
create table t1 (a year not null, primary key(a)) engine='MyISAM'
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;
1303
t1 CREATE TABLE `t1` (
1304
`a` year(4) NOT NULL,
1306
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1307
/*!50100 PARTITION BY KEY (a)
1308
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM,
1309
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM,
1310
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM,
1311
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) */
1312
insert into t1 values ('1975'), (2020), ('1980'), ('2000');
1319
select * from t1 where a=1980;
1322
delete from t1 where a=1980;
1329
create table t2 (a year not null, primary key(a)) engine='MyISAM'
1330
partition by key (a) partitions 12;
1331
show create table t2;
1333
t2 CREATE TABLE `t2` (
1334
`a` year(4) NOT NULL,
1336
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1337
/*!50100 PARTITION BY KEY (a)
1339
insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
1346
select * from t2 where a='1980';
1349
delete from t2 where a='1980';
1358
Warning 1264 Out of range value for column 'a' at row 1
1359
select count(*) from t2;