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