Edit File: partition_datetime_innodb.result
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 2020-12-31 12:10:30 1975-01-01 21:21:21 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 2000-06-15 23:59:00 2020-12-31 12:10:30 1975-01-01 21:21:21 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:14 1971-01-01 00:00:34 1971-01-01 00:00:42 1971-01-01 00:00:58 1971-01-01 00:00:21 1971-01-01 00:00:29 1971-01-01 00:00:37 1971-01-01 00:00:53 1971-01-01 00:00:57 1971-01-01 00:00:04 1971-01-01 00:00:08 1971-01-01 00:00:20 1971-01-01 00:00:36 1971-01-01 00:00:44 1971-01-01 00:00:11 1971-01-01 00:00:23 1971-01-01 00:00:27 1971-01-01 00:00:31 1971-01-01 00:00:02 1971-01-01 00:00:06 1971-01-01 00:00:10 1971-01-01 00:00:22 1971-01-01 00:00:38 1971-01-01 00:00:50 1971-01-01 00:00:17 1971-01-01 00:00:33 1971-01-01 00:00:45 1971-01-01 00:00:12 1971-01-01 00:00:52 1971-01-01 00:00:56 1971-01-01 00:00:07 1971-01-01 00:00:19 1971-01-01 00:00:35 1971-01-01 00:00:39 1971-01-01 00:00:47 1971-01-01 00:00:51 1971-01-01 00:00:18 1971-01-01 00:00:26 1971-01-01 00:00:30 1971-01-01 00:00:46 1971-01-01 00:00:54 1971-01-01 00:00:01 1971-01-01 00:00:05 1971-01-01 00:00:09 1971-01-01 00:00:13 1971-01-01 00:00:25 1971-01-01 00:00:41 1971-01-01 00:00:49 1971-01-01 00:00:16 1971-01-01 00:00:24 1971-01-01 00:00:28 1971-01-01 00:00:32 1971-01-01 00:00:40 1971-01-01 00:00:48 1971-01-01 00:00:03 1971-01-01 00:00:15 1971-01-01 00:00:43 1971-01-01 00:00:55 1971-01-01 00:00:59 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 1980-10-14 1975-01-01 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-16 1970-01-20 1970-01-28 1970-02-02 1970-02-08 1970-02-26 1970-03-02 1970-03-08 1970-03-26 1970-01-05 1970-01-21 1970-01-25 1970-02-01 1970-02-15 1970-02-17 1970-02-19 1970-02-27 1970-03-01 1970-03-15 1970-03-17 1970-03-19 1970-03-27 1970-01-08 1970-01-18 1970-01-24 1970-02-10 1970-02-14 1970-02-16 1970-02-20 1970-02-22 1970-03-10 1970-03-14 1970-03-16 1970-03-20 1970-03-22 1970-03-28 1970-01-01 1970-01-09 1970-01-13 1970-01-19 1970-01-27 1970-02-09 1970-02-21 1970-02-25 1970-03-09 1970-03-21 1970-03-25 1970-01-02 1970-01-06 1970-01-10 1970-01-22 1970-01-26 1970-02-04 1970-02-06 1970-02-12 1970-02-18 1970-02-24 1970-02-28 1970-03-04 1970-03-06 1970-03-12 1970-03-18 1970-03-24 1970-01-03 1970-01-07 1970-01-11 1970-01-15 1970-01-17 1970-01-23 1970-02-03 1970-02-05 1970-02-07 1970-02-11 1970-02-13 1970-02-23 1970-03-03 1970-03-05 1970-03-07 1970-03-11 1970-03-13 1970-03-23 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-01-01 1970-02-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 1970-07-01 1970-08-01 1970-09-01 1970-10-01 1970-11-01 1970-12-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-01-01 1970-02-01 1970-03-01 1970-04-01 1970-05-01 1970-06-01 1970-07-01 1970-08-01 1970-09-01 1970-10-01 1970-11-01 1970-12-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 21:21:21 23:59:00 12:10:30 select * from t1 where a=030303; a 03:03:03 delete from t1 where a=030303; select * from t1; a 21:21:21 23:59:00 12:10:30 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 10:11:12 13:14:15 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:15 00:01:23 00:01:39 00:01:47 00:01:59 00:01:16 00:01:24 00:01:40 00:01:48 00:01:05 00:01:13 00:01:21 00:01:37 00:01:45 00:01:57 00:01:14 00:01:22 00:01:38 00:01:46 00:01:58 00:01:03 00:01:11 00:01:19 00:01:35 00:01:43 00:01:55 00:01:04 00:01:12 00:01:20 00:01:36 00:01:44 00:01:56 00:01:01 00:01:09 00:01:29 00:01:33 00:01:41 00:01:53 00:01:02 00:01:10 00:01:18 00:01:34 00:01:42 00:01:54 00:01:07 00:01:27 00:01:31 00:01:51 00:01:08 00:01:28 00:01:32 00:01:52 00:01:17 00:01:25 00:01:49 00:01:06 00:01:26 00:01:30 00:01:50 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:01 10:00:06 10:00:07 10:00:14 10:00:15 10:00:02 10:00:03 10:00:08 10:00:09 10:00:04 10:00:05 10:00:10 10:00:11 10:00:12 10:00:13 10:00:20 10:00:21 10:00:26 10:00:27 10:00:16 10:00:17 10:00:22 10:00:23 10:00:28 10:00:29 10:00:18 10:00:19 10:00:24 10:00:25 10:00:30 10:00:32 10:00:33 10:00:35 10:00:40 10:00:41 10:00:34 10:00:36 10:00:37 10:00:42 10:00:43 10:00:31 10:00:38 10:00:39 10:00:44 10:00:45 10:00:46 10:00:48 10:00:49 10:00:54 10:00:55 10:00:50 10:00:51 10:00:56 10:00:57 10:00:59 10:00:47 10:00:52 10:00:53 10:00:58 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:01 10:00:06 10:00:07 10:00:14 10:00:15 10:00:02 10:00:03 10:00:08 10:00:09 10:00:04 10:00:05 10:00:10 10:00:11 10:00:12 10:00:13 10:00:20 10:00:21 10:00:26 10:00:27 10:00:16 10:00:17 10:00:22 10:00:23 10:00:28 10:00:29 10:00:18 10:00:19 10:00:24 10:00:25 10:00:30 10:00:32 10:00:33 10:00:35 10:00:40 10:00:41 10:00:34 10:00:36 10:00:37 10:00:42 10:00:43 10:00:31 10:00:38 10:00:39 10:00:44 10:00:45 10:00:46 10:00:48 10:00:49 10:00:54 10:00:55 10:00:50 10:00:51 10:00:56 10:00:57 10:00:59 10:00:47 10:00:52 10:00:53 10:00:58 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 1975-01-01 21:21:21 2000-06-15 23:59:00 2020-12-31 12:10:30 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 1975-01-01 21:21:21 2000-06-15 23:59:00 2020-12-31 12:10:30 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 1980-10-14 13:14:15 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 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:09 1970-01-01 00:00:13 1970-01-01 00:00:19 1970-01-01 00:00:23 1970-01-01 00:00:33 1970-01-01 00:00:37 1970-01-01 00:00:51 1970-01-01 00:00:59 1970-01-01 00:00:04 1970-01-01 00:00:10 1970-01-01 00:00:14 1970-01-01 00:00:24 1970-01-01 00:00:28 1970-01-01 00:00:34 1970-01-01 00:00:38 1970-01-01 00:00:48 1970-01-01 00:00:52 1970-01-01 00:00:56 1970-01-01 00:00:03 1970-01-01 00:00:07 1970-01-01 00:00:17 1970-01-01 00:00:21 1970-01-01 00:00:27 1970-01-01 00:00:31 1970-01-01 00:00:35 1970-01-01 00:00:39 1970-01-01 00:00:41 1970-01-01 00:00:45 1970-01-01 00:00:08 1970-01-01 00:00:12 1970-01-01 00:00:18 1970-01-01 00:00:22 1970-01-01 00:00:32 1970-01-01 00:00:36 1970-01-01 00:00:42 1970-01-01 00:00:46 1970-01-01 00:00:01 1970-01-01 00:00:05 1970-01-01 00:00:11 1970-01-01 00:00:15 1970-01-01 00:00:25 1970-01-01 00:00:29 1970-01-01 00:00:43 1970-01-01 00:00:47 1970-01-01 00:00:49 1970-01-01 00:00:53 1970-01-01 00:00:55 1970-01-01 00:00:57 1970-01-01 00:00:02 1970-01-01 00:00:06 1970-01-01 00:00:16 1970-01-01 00:00:20 1970-01-01 00:00:26 1970-01-01 00:00:30 1970-01-01 00:00:40 1970-01-01 00:00:44 1970-01-01 00:00:50 1970-01-01 00:00:54 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-02-01 00:00:00 1970-01-01 00:00:00 1970-03-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 1970-08-01 00:00:00 1970-09-01 00:00:00 1970-07-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 1970-12-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-02-01 00:00:00 1970-01-01 00:00:00 1970-03-01 00:00:00 1970-04-01 00:00:00 1970-05-01 00:00:00 1970-06-01 00:00:00 1970-08-01 00:00:00 1970-09-01 00:00:00 1970-07-01 00:00:00 1970-10-01 00:00:00 1970-11-01 00:00:00 1970-12-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 2020 1975 1980 2000 select * from t2 where a='1980'; a 1980 delete from t2 where a='1980'; select * from t2; a 2020 1975 2000 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 2001 2013 2029 2041 2053 2065 2077 2089 2100 2101 2102 2103 2114 2132 2133 2134 2135 2146 1904 1916 1928 1932 1940 1952 1964 1976 1988 2008 2020 2024 2036 2048 2060 2072 2084 1907 1919 1931 1943 1955 1967 1979 1991 1999 2011 2027 2039 2051 2063 2075 2087 2097 2099 2110 2111 2112 2113 2123 2129 2131 2142 2143 2144 2145 2147 1902 1914 1926 1938 1950 1962 1974 1986 2006 2018 2034 2046 2058 2070 2082 1905 1917 1929 1941 1953 1965 1977 1989 1997 2009 2021 2025 2037 2049 2061 2073 2085 2092 2093 2094 2095 2120 2121 2124 2125 2126 2127 2152 2153 0000 1912 1924 1936 1948 1960 1972 1984 1996 2004 2016 2032 2044 2056 2068 2080 2116 2148 1903 1915 1927 1939 1951 1963 1975 1987 2007 2019 2023 2035 2047 2059 2071 2083 2155 1910 1922 1934 1946 1958 1970 1982 1994 2002 2014 2030 2042 2054 2066 2078 2090 1913 1925 1937 1949 1961 1973 1985 2005 2017 2033 2045 2057 2069 2081 2104 2105 2106 2107 2117 2118 2119 2136 2137 2138 2139 2149 2150 2151 1908 1920 1944 1956 1968 1980 1992 2000 2012 2028 2040 2052 2064 2076 2088 2096 2098 2108 2128 2130 2140 1911 1923 1935 1947 1959 1971 1983 1995 2003 2015 2031 2043 2055 2067 2079 2091 2115 1906 1918 1930 1942 1954 1966 1978 1990 1998 2010 2022 2026 2038 2050 2062 2074 2086 2109 2122 2141 2154 drop table t2;