create table t1 (a timestamp not null, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */ 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'); select * from t1; a 2000-06-15 23:59:00 1980-10-14 03:03:00 1975-01-01 21:21:21 2020-12-31 12:10:30 select * from t1 where a=19801014030300; a 1980-10-14 03:03:00 delete from t1 where a=19801014030300; select * from t1; a 2000-06-15 23:59:00 1975-01-01 21:21:21 2020-12-31 12:10:30 drop table t1; create table t2 (a timestamp not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 12 */ 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'); select * from t2; a 1980-10-14 13:14:15 2000-06-15 14:15:16 2020-12-31 10:11:12 1975-01-01 00:01:01 select * from t2 where a='1980-10-14 13:14:15'; a 1980-10-14 13:14:15 delete from t2 where a='1980-10-14 13:14:15'; select * from t2; a 2000-06-15 14:15:16 2020-12-31 10:11:12 1975-01-01 00:01:01 delete from t2; 59 inserts; insert into t2 values (19710101000000+59); insert into t2 values (19710101000000+58); insert into t2 values (19710101000000+57); insert into t2 values (19710101000000+56); insert into t2 values (19710101000000+55); insert into t2 values (19710101000000+54); insert into t2 values (19710101000000+53); insert into t2 values (19710101000000+52); insert into t2 values (19710101000000+51); insert into t2 values (19710101000000+50); insert into t2 values (19710101000000+49); insert into t2 values (19710101000000+48); insert into t2 values (19710101000000+47); insert into t2 values (19710101000000+46); insert into t2 values (19710101000000+45); insert into t2 values (19710101000000+44); insert into t2 values (19710101000000+43); insert into t2 values (19710101000000+42); insert into t2 values (19710101000000+41); insert into t2 values (19710101000000+40); insert into t2 values (19710101000000+39); insert into t2 values (19710101000000+38); insert into t2 values (19710101000000+37); insert into t2 values (19710101000000+36); insert into t2 values (19710101000000+35); insert into t2 values (19710101000000+34); insert into t2 values (19710101000000+33); insert into t2 values (19710101000000+32); insert into t2 values (19710101000000+31); insert into t2 values (19710101000000+30); insert into t2 values (19710101000000+29); insert into t2 values (19710101000000+28); insert into t2 values (19710101000000+27); insert into t2 values (19710101000000+26); insert into t2 values (19710101000000+25); insert into t2 values (19710101000000+24); insert into t2 values (19710101000000+23); insert into t2 values (19710101000000+22); insert into t2 values (19710101000000+21); insert into t2 values (19710101000000+20); insert into t2 values (19710101000000+19); insert into t2 values (19710101000000+18); insert into t2 values (19710101000000+17); insert into t2 values (19710101000000+16); insert into t2 values (19710101000000+15); insert into t2 values (19710101000000+14); insert into t2 values (19710101000000+13); insert into t2 values (19710101000000+12); insert into t2 values (19710101000000+11); insert into t2 values (19710101000000+10); insert into t2 values (19710101000000+9); insert into t2 values (19710101000000+8); insert into t2 values (19710101000000+7); insert into t2 values (19710101000000+6); insert into t2 values (19710101000000+5); insert into t2 values (19710101000000+4); insert into t2 values (19710101000000+3); insert into t2 values (19710101000000+2); insert into t2 values (19710101000000+1); select count(*) from t2; count(*) 59 select * from t2; a 1971-01-01 00:00:04 1971-01-01 00:00:05 1971-01-01 00:00:06 1971-01-01 00:00:09 1971-01-01 00:00:13 1971-01-01 00:00:16 1971-01-01 00:00:19 1971-01-01 00:00:21 1971-01-01 00:00:22 1971-01-01 00:00:26 1971-01-01 00:00:27 1971-01-01 00:00:28 1971-01-01 00:00:30 1971-01-01 00:00:41 1971-01-01 00:00:43 1971-01-01 00:00:44 1971-01-01 00:00:45 1971-01-01 00:00:47 1971-01-01 00:00:48 1971-01-01 00:00:51 1971-01-01 00:00:54 1971-01-01 00:00:01 1971-01-01 00:00:03 1971-01-01 00:00:12 1971-01-01 00:00:15 1971-01-01 00:00:17 1971-01-01 00:00:23 1971-01-01 00:00:29 1971-01-01 00:00:33 1971-01-01 00:00:34 1971-01-01 00:00:35 1971-01-01 00:00:36 1971-01-01 00:00:39 1971-01-01 00:00:40 1971-01-01 00:00:46 1971-01-01 00:00:49 1971-01-01 00:00:53 1971-01-01 00:00:56 1971-01-01 00:00:57 1971-01-01 00:00:58 1971-01-01 00:00:59 1971-01-01 00:00:02 1971-01-01 00:00:07 1971-01-01 00:00:08 1971-01-01 00:00:10 1971-01-01 00:00:11 1971-01-01 00:00:14 1971-01-01 00:00:18 1971-01-01 00:00:20 1971-01-01 00:00:24 1971-01-01 00:00:25 1971-01-01 00:00:31 1971-01-01 00:00:32 1971-01-01 00:00:37 1971-01-01 00:00:38 1971-01-01 00:00:42 1971-01-01 00:00:50 1971-01-01 00:00:52 1971-01-01 00:00:55 drop table t2; create table t1 (a date not null, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */ insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15'); select * from t1; a 1980-10-14 2020-12-31 1975-01-01 2000-06-15 select * from t1 where a=19801014; a 1980-10-14 delete from t1 where a=19801014; select * from t1; a 2020-12-31 1975-01-01 2000-06-15 drop table t1; create table t2 (a date not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` date NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 12 */ insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15'); select * from t2; a 1975-01-01 1980-10-14 2020-12-31 2000-06-15 select * from t2 where a='1980-10-14'; a 1980-10-14 delete from t2 where a='1980-10-14'; select * from t2; a 1975-01-01 2020-12-31 2000-06-15 delete from t2; 28 inserts; insert into t2 values (19700101+28-1); insert into t2 values (19700201+28-1); insert into t2 values (19700301+28-1); insert into t2 values (19700101+27-1); insert into t2 values (19700201+27-1); insert into t2 values (19700301+27-1); insert into t2 values (19700101+26-1); insert into t2 values (19700201+26-1); insert into t2 values (19700301+26-1); insert into t2 values (19700101+25-1); insert into t2 values (19700201+25-1); insert into t2 values (19700301+25-1); insert into t2 values (19700101+24-1); insert into t2 values (19700201+24-1); insert into t2 values (19700301+24-1); insert into t2 values (19700101+23-1); insert into t2 values (19700201+23-1); insert into t2 values (19700301+23-1); insert into t2 values (19700101+22-1); insert into t2 values (19700201+22-1); insert into t2 values (19700301+22-1); insert into t2 values (19700101+21-1); insert into t2 values (19700201+21-1); insert into t2 values (19700301+21-1); insert into t2 values (19700101+20-1); insert into t2 values (19700201+20-1); insert into t2 values (19700301+20-1); insert into t2 values (19700101+19-1); insert into t2 values (19700201+19-1); insert into t2 values (19700301+19-1); insert into t2 values (19700101+18-1); insert into t2 values (19700201+18-1); insert into t2 values (19700301+18-1); insert into t2 values (19700101+17-1); insert into t2 values (19700201+17-1); insert into t2 values (19700301+17-1); insert into t2 values (19700101+16-1); insert into t2 values (19700201+16-1); insert into t2 values (19700301+16-1); insert into t2 values (19700101+15-1); insert into t2 values (19700201+15-1); insert into t2 values (19700301+15-1); insert into t2 values (19700101+14-1); insert into t2 values (19700201+14-1); insert into t2 values (19700301+14-1); insert into t2 values (19700101+13-1); insert into t2 values (19700201+13-1); insert into t2 values (19700301+13-1); insert into t2 values (19700101+12-1); insert into t2 values (19700201+12-1); insert into t2 values (19700301+12-1); insert into t2 values (19700101+11-1); insert into t2 values (19700201+11-1); insert into t2 values (19700301+11-1); insert into t2 values (19700101+10-1); insert into t2 values (19700201+10-1); insert into t2 values (19700301+10-1); insert into t2 values (19700101+9-1); insert into t2 values (19700201+9-1); insert into t2 values (19700301+9-1); insert into t2 values (19700101+8-1); insert into t2 values (19700201+8-1); insert into t2 values (19700301+8-1); insert into t2 values (19700101+7-1); insert into t2 values (19700201+7-1); insert into t2 values (19700301+7-1); insert into t2 values (19700101+6-1); insert into t2 values (19700201+6-1); insert into t2 values (19700301+6-1); insert into t2 values (19700101+5-1); insert into t2 values (19700201+5-1); insert into t2 values (19700301+5-1); insert into t2 values (19700101+4-1); insert into t2 values (19700201+4-1); insert into t2 values (19700301+4-1); insert into t2 values (19700101+3-1); insert into t2 values (19700201+3-1); insert into t2 values (19700301+3-1); insert into t2 values (19700101+2-1); insert into t2 values (19700201+2-1); insert into t2 values (19700301+2-1); insert into t2 values (19700101+1-1); insert into t2 values (19700201+1-1); insert into t2 values (19700301+1-1); select count(*) from t2; count(*) 84 select * from t2; a 1970-01-04 1970-01-12 1970-01-14 1970-01-24 1970-01-28 1970-02-02 1970-02-04 1970-02-10 1970-02-14 1970-02-16 1970-03-04 1970-03-06 1970-03-12 1970-01-13 1970-01-27 1970-02-03 1970-02-09 1970-02-13 1970-02-21 1970-03-05 1970-03-07 1970-03-09 1970-03-15 1970-03-23 1970-01-06 1970-01-08 1970-01-16 1970-01-18 1970-01-22 1970-02-06 1970-02-12 1970-02-20 1970-02-22 1970-02-28 1970-03-02 1970-03-08 1970-03-16 1970-03-20 1970-03-22 1970-03-24 1970-01-05 1970-01-07 1970-01-11 1970-01-15 1970-01-21 1970-02-01 1970-02-11 1970-02-15 1970-02-19 1970-03-03 1970-03-11 1970-03-13 1970-03-17 1970-03-19 1970-03-25 1970-03-27 1970-01-02 1970-01-10 1970-01-20 1970-01-26 1970-02-08 1970-02-18 1970-02-24 1970-02-26 1970-03-10 1970-03-14 1970-03-18 1970-03-26 1970-03-28 1970-01-01 1970-01-03 1970-01-09 1970-01-17 1970-01-19 1970-01-23 1970-01-25 1970-02-05 1970-02-07 1970-02-17 1970-02-23 1970-02-25 1970-02-27 1970-03-01 1970-03-21 drop table t2; create table t3 (a date not null, primary key(a)) engine='InnoDB' partition by range (month(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values less than (4), partition quarter2 values less than (7), partition quarter3 values less than (10), partition quarter4 values less than (13) ); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` date NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */ 12 inserts; insert into t3 values (adddate(19700101,interval 12-1 month)); insert into t3 values (adddate(19700101,interval 11-1 month)); insert into t3 values (adddate(19700101,interval 10-1 month)); insert into t3 values (adddate(19700101,interval 9-1 month)); insert into t3 values (adddate(19700101,interval 8-1 month)); insert into t3 values (adddate(19700101,interval 7-1 month)); insert into t3 values (adddate(19700101,interval 6-1 month)); insert into t3 values (adddate(19700101,interval 5-1 month)); insert into t3 values (adddate(19700101,interval 4-1 month)); insert into t3 values (adddate(19700101,interval 3-1 month)); insert into t3 values (adddate(19700101,interval 2-1 month)); insert into t3 values (adddate(19700101,interval 1-1 month)); select count(*) from t3; count(*) 12 select * from t3; a 1970-02-01 1970-01-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 1970-09-01 1970-07-01 1970-08-01 1970-12-01 1970-10-01 1970-11-01 drop table t3; create table t4 (a date not null, primary key(a)) engine='InnoDB' partition by list (month(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values in (1,2,3), partition quarter2 values in (4,5,6), partition quarter3 values in (7,8,9), partition quarter4 values in (10,11,12) ); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` date NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB, PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB, PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */ 12 inserts; insert into t4 values (adddate(19700101,interval 12-1 month)); insert into t4 values (adddate(19700101,interval 11-1 month)); insert into t4 values (adddate(19700101,interval 10-1 month)); insert into t4 values (adddate(19700101,interval 9-1 month)); insert into t4 values (adddate(19700101,interval 8-1 month)); insert into t4 values (adddate(19700101,interval 7-1 month)); insert into t4 values (adddate(19700101,interval 6-1 month)); insert into t4 values (adddate(19700101,interval 5-1 month)); insert into t4 values (adddate(19700101,interval 4-1 month)); insert into t4 values (adddate(19700101,interval 3-1 month)); insert into t4 values (adddate(19700101,interval 2-1 month)); insert into t4 values (adddate(19700101,interval 1-1 month)); select count(*) from t4; count(*) 12 select * from t4; a 1970-02-01 1970-01-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 1970-09-01 1970-07-01 1970-08-01 1970-12-01 1970-10-01 1970-11-01 drop table t4; create table t1 (a time not null, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */ insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59'); select * from t1; a 03:03:03 23:59:00 12:10:30 21:21:21 select * from t1 where a=030303; a 03:03:03 delete from t1 where a=030303; select * from t1; a 23:59:00 12:10:30 21:21:21 drop table t1; create table t2 (a time not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 12 */ insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16'); select * from t2; a 13:14:15 10:11:12 14:15:16 00:01:01 select * from t2 where a='13:14:15'; a 13:14:15 delete from t2 where a='13:14:15'; select * from t2; a 10:11:12 14:15:16 00:01:01 delete from t2; 59 inserts; insert into t2 values (000100+59); insert into t2 values (000100+58); insert into t2 values (000100+57); insert into t2 values (000100+56); insert into t2 values (000100+55); insert into t2 values (000100+54); insert into t2 values (000100+53); insert into t2 values (000100+52); insert into t2 values (000100+51); insert into t2 values (000100+50); insert into t2 values (000100+49); insert into t2 values (000100+48); insert into t2 values (000100+47); insert into t2 values (000100+46); insert into t2 values (000100+45); insert into t2 values (000100+44); insert into t2 values (000100+43); insert into t2 values (000100+42); insert into t2 values (000100+41); insert into t2 values (000100+40); insert into t2 values (000100+39); insert into t2 values (000100+38); insert into t2 values (000100+37); insert into t2 values (000100+36); insert into t2 values (000100+35); insert into t2 values (000100+34); insert into t2 values (000100+33); insert into t2 values (000100+32); insert into t2 values (000100+31); insert into t2 values (000100+30); insert into t2 values (000100+29); insert into t2 values (000100+28); insert into t2 values (000100+27); insert into t2 values (000100+26); insert into t2 values (000100+25); insert into t2 values (000100+24); insert into t2 values (000100+23); insert into t2 values (000100+22); insert into t2 values (000100+21); insert into t2 values (000100+20); insert into t2 values (000100+19); insert into t2 values (000100+18); insert into t2 values (000100+17); insert into t2 values (000100+16); insert into t2 values (000100+15); insert into t2 values (000100+14); insert into t2 values (000100+13); insert into t2 values (000100+12); insert into t2 values (000100+11); insert into t2 values (000100+10); insert into t2 values (000100+9); insert into t2 values (000100+8); insert into t2 values (000100+7); insert into t2 values (000100+6); insert into t2 values (000100+5); insert into t2 values (000100+4); insert into t2 values (000100+3); insert into t2 values (000100+2); insert into t2 values (000100+1); select count(*) from t2; count(*) 59 select * from t2; a 00:01:05 00:01:17 00:01:29 00:01:41 00:01:53 00:01:08 00:01:20 00:01:32 00:01:44 00:01:56 00:01:07 00:01:19 00:01:31 00:01:43 00:01:55 00:01:10 00:01:22 00:01:34 00:01:46 00:01:58 00:01:09 00:01:21 00:01:33 00:01:45 00:01:57 00:01:12 00:01:24 00:01:36 00:01:48 00:01:11 00:01:23 00:01:35 00:01:47 00:01:59 00:01:02 00:01:14 00:01:26 00:01:38 00:01:50 00:01:01 00:01:13 00:01:25 00:01:37 00:01:49 00:01:04 00:01:16 00:01:28 00:01:40 00:01:52 00:01:03 00:01:15 00:01:27 00:01:39 00:01:51 00:01:06 00:01:18 00:01:30 00:01:42 00:01:54 drop table t2; create table t3 (a time not null, primary key(a)) engine='InnoDB' partition by range (second(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values less than (16), partition quarter2 values less than (31), partition quarter3 values less than (46), partition quarter4 values less than (61) ); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` time NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (second(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (16) ENGINE = InnoDB, PARTITION quarter2 VALUES LESS THAN (31) ENGINE = InnoDB, PARTITION quarter3 VALUES LESS THAN (46) ENGINE = InnoDB, PARTITION quarter4 VALUES LESS THAN (61) ENGINE = InnoDB) */ 59 inserts; insert into t3 values (100000+59); insert into t3 values (100000+58); insert into t3 values (100000+57); insert into t3 values (100000+56); insert into t3 values (100000+55); insert into t3 values (100000+54); insert into t3 values (100000+53); insert into t3 values (100000+52); insert into t3 values (100000+51); insert into t3 values (100000+50); insert into t3 values (100000+49); insert into t3 values (100000+48); insert into t3 values (100000+47); insert into t3 values (100000+46); insert into t3 values (100000+45); insert into t3 values (100000+44); insert into t3 values (100000+43); insert into t3 values (100000+42); insert into t3 values (100000+41); insert into t3 values (100000+40); insert into t3 values (100000+39); insert into t3 values (100000+38); insert into t3 values (100000+37); insert into t3 values (100000+36); insert into t3 values (100000+35); insert into t3 values (100000+34); insert into t3 values (100000+33); insert into t3 values (100000+32); insert into t3 values (100000+31); insert into t3 values (100000+30); insert into t3 values (100000+29); insert into t3 values (100000+28); insert into t3 values (100000+27); insert into t3 values (100000+26); insert into t3 values (100000+25); insert into t3 values (100000+24); insert into t3 values (100000+23); insert into t3 values (100000+22); insert into t3 values (100000+21); insert into t3 values (100000+20); insert into t3 values (100000+19); insert into t3 values (100000+18); insert into t3 values (100000+17); insert into t3 values (100000+16); insert into t3 values (100000+15); insert into t3 values (100000+14); insert into t3 values (100000+13); insert into t3 values (100000+12); insert into t3 values (100000+11); insert into t3 values (100000+10); insert into t3 values (100000+9); insert into t3 values (100000+8); insert into t3 values (100000+7); insert into t3 values (100000+6); insert into t3 values (100000+5); insert into t3 values (100000+4); insert into t3 values (100000+3); insert into t3 values (100000+2); insert into t3 values (100000+1); select count(*) from t3; count(*) 59 select * from t3; a 10:00:02 10:00:05 10:00:01 10:00:03 10:00:04 10:00:08 10:00:09 10:00:14 10:00:06 10:00:07 10:00:10 10:00:11 10:00:12 10:00:13 10:00:15 10:00:16 10:00:18 10:00:19 10:00:25 10:00:30 10:00:20 10:00:21 10:00:22 10:00:27 10:00:29 10:00:17 10:00:23 10:00:24 10:00:26 10:00:28 10:00:37 10:00:39 10:00:41 10:00:42 10:00:44 10:00:31 10:00:32 10:00:33 10:00:35 10:00:38 10:00:40 10:00:34 10:00:36 10:00:43 10:00:45 10:00:47 10:00:49 10:00:50 10:00:52 10:00:48 10:00:51 10:00:54 10:00:55 10:00:57 10:00:58 10:00:46 10:00:53 10:00:56 10:00:59 drop table t3; create table t4 (a time not null, primary key(a)) engine='InnoDB' partition by list (second(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15), partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30), partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45), partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` time NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (second(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = InnoDB, PARTITION quarter2 VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION quarter3 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = InnoDB, PARTITION quarter4 VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ 59 inserts; insert into t4 values (100000+59); insert into t4 values (100000+58); insert into t4 values (100000+57); insert into t4 values (100000+56); insert into t4 values (100000+55); insert into t4 values (100000+54); insert into t4 values (100000+53); insert into t4 values (100000+52); insert into t4 values (100000+51); insert into t4 values (100000+50); insert into t4 values (100000+49); insert into t4 values (100000+48); insert into t4 values (100000+47); insert into t4 values (100000+46); insert into t4 values (100000+45); insert into t4 values (100000+44); insert into t4 values (100000+43); insert into t4 values (100000+42); insert into t4 values (100000+41); insert into t4 values (100000+40); insert into t4 values (100000+39); insert into t4 values (100000+38); insert into t4 values (100000+37); insert into t4 values (100000+36); insert into t4 values (100000+35); insert into t4 values (100000+34); insert into t4 values (100000+33); insert into t4 values (100000+32); insert into t4 values (100000+31); insert into t4 values (100000+30); insert into t4 values (100000+29); insert into t4 values (100000+28); insert into t4 values (100000+27); insert into t4 values (100000+26); insert into t4 values (100000+25); insert into t4 values (100000+24); insert into t4 values (100000+23); insert into t4 values (100000+22); insert into t4 values (100000+21); insert into t4 values (100000+20); insert into t4 values (100000+19); insert into t4 values (100000+18); insert into t4 values (100000+17); insert into t4 values (100000+16); insert into t4 values (100000+15); insert into t4 values (100000+14); insert into t4 values (100000+13); insert into t4 values (100000+12); insert into t4 values (100000+11); insert into t4 values (100000+10); insert into t4 values (100000+9); insert into t4 values (100000+8); insert into t4 values (100000+7); insert into t4 values (100000+6); insert into t4 values (100000+5); insert into t4 values (100000+4); insert into t4 values (100000+3); insert into t4 values (100000+2); insert into t4 values (100000+1); select count(*) from t4; count(*) 59 select * from t4; a 10:00:02 10:00:05 10:00:01 10:00:03 10:00:04 10:00:08 10:00:09 10:00:14 10:00:06 10:00:07 10:00:10 10:00:11 10:00:12 10:00:13 10:00:15 10:00:16 10:00:18 10:00:19 10:00:25 10:00:30 10:00:20 10:00:21 10:00:22 10:00:27 10:00:29 10:00:17 10:00:23 10:00:24 10:00:26 10:00:28 10:00:37 10:00:39 10:00:41 10:00:42 10:00:44 10:00:31 10:00:32 10:00:33 10:00:35 10:00:38 10:00:40 10:00:34 10:00:36 10:00:43 10:00:45 10:00:47 10:00:49 10:00:50 10:00:52 10:00:48 10:00:51 10:00:54 10:00:55 10:00:57 10:00:58 10:00:46 10:00:53 10:00:56 10:00:59 drop table t4; create table t1 (a datetime not null, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */ 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'); select * from t1; a 2020-12-31 12:10:30 1975-01-01 21:21:21 2000-06-15 23:59:00 1980-10-14 03:03:00 select * from t1 where a=19801014030300; a 1980-10-14 03:03:00 delete from t1 where a=19801014030300; select * from t1; a 2020-12-31 12:10:30 1975-01-01 21:21:21 2000-06-15 23:59:00 drop table t1; create table t2 (a datetime not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 12 */ 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'); select * from t2; a 2020-12-31 10:11:12 2000-06-15 14:15:16 1975-01-01 00:01:01 1980-10-14 13:14:15 select * from t2 where a='1980-10-14 13:14:15'; a 1980-10-14 13:14:15 delete from t2 where a='1980-10-14 13:14:15'; select * from t2; a 2020-12-31 10:11:12 2000-06-15 14:15:16 1975-01-01 00:01:01 delete from t2; 59 inserts; insert into t2 values (19700101000000+59); insert into t2 values (19700101000000+58); insert into t2 values (19700101000000+57); insert into t2 values (19700101000000+56); insert into t2 values (19700101000000+55); insert into t2 values (19700101000000+54); insert into t2 values (19700101000000+53); insert into t2 values (19700101000000+52); insert into t2 values (19700101000000+51); insert into t2 values (19700101000000+50); insert into t2 values (19700101000000+49); insert into t2 values (19700101000000+48); insert into t2 values (19700101000000+47); insert into t2 values (19700101000000+46); insert into t2 values (19700101000000+45); insert into t2 values (19700101000000+44); insert into t2 values (19700101000000+43); insert into t2 values (19700101000000+42); insert into t2 values (19700101000000+41); insert into t2 values (19700101000000+40); insert into t2 values (19700101000000+39); insert into t2 values (19700101000000+38); insert into t2 values (19700101000000+37); insert into t2 values (19700101000000+36); insert into t2 values (19700101000000+35); insert into t2 values (19700101000000+34); insert into t2 values (19700101000000+33); insert into t2 values (19700101000000+32); insert into t2 values (19700101000000+31); insert into t2 values (19700101000000+30); insert into t2 values (19700101000000+29); insert into t2 values (19700101000000+28); insert into t2 values (19700101000000+27); insert into t2 values (19700101000000+26); insert into t2 values (19700101000000+25); insert into t2 values (19700101000000+24); insert into t2 values (19700101000000+23); insert into t2 values (19700101000000+22); insert into t2 values (19700101000000+21); insert into t2 values (19700101000000+20); insert into t2 values (19700101000000+19); insert into t2 values (19700101000000+18); insert into t2 values (19700101000000+17); insert into t2 values (19700101000000+16); insert into t2 values (19700101000000+15); insert into t2 values (19700101000000+14); insert into t2 values (19700101000000+13); insert into t2 values (19700101000000+12); insert into t2 values (19700101000000+11); insert into t2 values (19700101000000+10); insert into t2 values (19700101000000+9); insert into t2 values (19700101000000+8); insert into t2 values (19700101000000+7); insert into t2 values (19700101000000+6); insert into t2 values (19700101000000+5); insert into t2 values (19700101000000+4); insert into t2 values (19700101000000+3); insert into t2 values (19700101000000+2); insert into t2 values (19700101000000+1); select count(*) from t2; count(*) 59 select * from t2; a 1970-01-01 00:00:02 1970-01-01 00:00:03 1970-01-01 00:00:14 1970-01-01 00:00:18 1970-01-01 00:00:19 1970-01-01 00:00:31 1970-01-01 00:00:32 1970-01-01 00:00:33 1970-01-01 00:00:35 1970-01-01 00:00:38 1970-01-01 00:00:41 1970-01-01 00:00:42 1970-01-01 00:00:44 1970-01-01 00:00:45 1970-01-01 00:00:46 1970-01-01 00:00:47 1970-01-01 00:00:50 1970-01-01 00:00:51 1970-01-01 00:00:53 1970-01-01 00:00:57 1970-01-01 00:00:59 1970-01-01 00:00:01 1970-01-01 00:00:04 1970-01-01 00:00:05 1970-01-01 00:00:07 1970-01-01 00:00:16 1970-01-01 00:00:17 1970-01-01 00:00:20 1970-01-01 00:00:22 1970-01-01 00:00:23 1970-01-01 00:00:26 1970-01-01 00:00:27 1970-01-01 00:00:30 1970-01-01 00:00:36 1970-01-01 00:00:40 1970-01-01 00:00:48 1970-01-01 00:00:52 1970-01-01 00:00:54 1970-01-01 00:00:56 1970-01-01 00:00:06 1970-01-01 00:00:08 1970-01-01 00:00:09 1970-01-01 00:00:10 1970-01-01 00:00:11 1970-01-01 00:00:12 1970-01-01 00:00:13 1970-01-01 00:00:15 1970-01-01 00:00:21 1970-01-01 00:00:24 1970-01-01 00:00:25 1970-01-01 00:00:28 1970-01-01 00:00:29 1970-01-01 00:00:34 1970-01-01 00:00:37 1970-01-01 00:00:39 1970-01-01 00:00:43 1970-01-01 00:00:49 1970-01-01 00:00:55 1970-01-01 00:00:58 drop table t2; create table t3 (a datetime not null, primary key(a)) engine='InnoDB' partition by range (month(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values less than (4), partition quarter2 values less than (7), partition quarter3 values less than (10), partition quarter4 values less than (13) ); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` datetime NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION quarter2 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION quarter3 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION quarter4 VALUES LESS THAN (13) ENGINE = InnoDB) */ 12 inserts; insert into t3 values (adddate(19700101000000,interval 12-1 month)); insert into t3 values (adddate(19700101000000,interval 11-1 month)); insert into t3 values (adddate(19700101000000,interval 10-1 month)); insert into t3 values (adddate(19700101000000,interval 9-1 month)); insert into t3 values (adddate(19700101000000,interval 8-1 month)); insert into t3 values (adddate(19700101000000,interval 7-1 month)); insert into t3 values (adddate(19700101000000,interval 6-1 month)); insert into t3 values (adddate(19700101000000,interval 5-1 month)); insert into t3 values (adddate(19700101000000,interval 4-1 month)); insert into t3 values (adddate(19700101000000,interval 3-1 month)); insert into t3 values (adddate(19700101000000,interval 2-1 month)); insert into t3 values (adddate(19700101000000,interval 1-1 month)); select count(*) from t3; count(*) 12 select * from t3; a 1970-03-01 00:00:00 1970-01-01 00:00:00 1970-02-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 1970-09-01 00:00:00 1970-07-01 00:00:00 1970-08-01 00:00:00 1970-12-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 drop table t3; create table t4 (a datetime not null, primary key(a)) engine='InnoDB' partition by list (month(a)) subpartition by key (a) subpartitions 3 ( partition quarter1 values in (1,2,3), partition quarter2 values in (4,5,6), partition quarter3 values in (7,8,9), partition quarter4 values in (10,11,12) ); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` datetime NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = InnoDB, PARTITION quarter2 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION quarter3 VALUES IN (7,8,9) ENGINE = InnoDB, PARTITION quarter4 VALUES IN (10,11,12) ENGINE = InnoDB) */ 12 inserts; insert into t4 values (adddate(19700101000000,interval 12-1 month)); insert into t4 values (adddate(19700101000000,interval 11-1 month)); insert into t4 values (adddate(19700101000000,interval 10-1 month)); insert into t4 values (adddate(19700101000000,interval 9-1 month)); insert into t4 values (adddate(19700101000000,interval 8-1 month)); insert into t4 values (adddate(19700101000000,interval 7-1 month)); insert into t4 values (adddate(19700101000000,interval 6-1 month)); insert into t4 values (adddate(19700101000000,interval 5-1 month)); insert into t4 values (adddate(19700101000000,interval 4-1 month)); insert into t4 values (adddate(19700101000000,interval 3-1 month)); insert into t4 values (adddate(19700101000000,interval 2-1 month)); insert into t4 values (adddate(19700101000000,interval 1-1 month)); select count(*) from t4; count(*) 12 select * from t4; a 1970-03-01 00:00:00 1970-01-01 00:00:00 1970-02-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 1970-09-01 00:00:00 1970-07-01 00:00:00 1970-08-01 00:00:00 1970-12-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 drop table t4; create table t1 (a year not null, primary key(a)) engine='InnoDB' partition by key (a) ( partition pa1 max_rows=20 min_rows=2, partition pa2 max_rows=30 min_rows=3, partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` year(4) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */ insert into t1 values ('1975'), (2020), ('1980'), ('2000'); select * from t1; a 1980 2000 2020 1975 select * from t1 where a=1980; a 1980 delete from t1 where a=1980; select * from t1; a 2000 2020 1975 drop table t1; create table t2 (a year not null, primary key(a)) engine='InnoDB' partition by key (a) partitions 12; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` year(4) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) PARTITIONS 12 */ insert into t2 values ('1975'), ('2020'), ('1980'), ('2000'); select * from t2; a 2000 2020 1975 1980 select * from t2 where a='1980'; a 1980 delete from t2 where a='1980'; select * from t2; a 2000 2020 1975 delete from t2; 255 inserts; Warnings: Warning 1264 Out of range value for column 'a' at row 1 select count(*) from t2; count(*) 255 select * from t2; a 1909 1921 1933 1945 1957 1969 1981 1993 2005 2017 2029 2041 2053 2065 2077 2089 2101 2113 2125 2137 2149 1904 1916 1928 1940 1952 1964 1976 1988 2000 2012 2024 2036 2048 2060 2072 2084 2096 2108 2120 2132 2144 1907 1919 1931 1943 1955 1967 1979 1991 2003 2015 2027 2039 2051 2063 2075 2087 2099 2111 2123 2135 2147 1902 1914 1926 1938 1950 1962 1974 1986 1998 2010 2022 2034 2046 2058 2070 2082 2094 2106 2118 2130 2142 2154 1905 1917 1929 1941 1953 1965 1977 1989 2001 2013 2025 2037 2049 2061 2073 2085 2097 2109 2121 2133 2145 0000 1912 1924 1936 1948 1960 1972 1984 1996 2008 2020 2032 2044 2056 2068 2080 2092 2104 2116 2128 2140 2152 1903 1915 1927 1939 1951 1963 1975 1987 1999 2011 2023 2035 2047 2059 2071 2083 2095 2107 2119 2131 2143 2155 1910 1922 1934 1946 1958 1970 1982 1994 2006 2018 2030 2042 2054 2066 2078 2090 2102 2114 2126 2138 2150 1913 1925 1937 1949 1961 1973 1985 1997 2009 2021 2033 2045 2057 2069 2081 2093 2105 2117 2129 2141 2153 1908 1920 1932 1944 1956 1968 1980 1992 2004 2016 2028 2040 2052 2064 2076 2088 2100 2112 2124 2136 2148 1911 1923 1935 1947 1959 1971 1983 1995 2007 2019 2031 2043 2055 2067 2079 2091 2103 2115 2127 2139 2151 1906 1918 1930 1942 1954 1966 1978 1990 2002 2014 2026 2038 2050 2062 2074 2086 2098 2110 2122 2134 2146 drop table t2;