PARTITION dayofmonth invalid?
Posted by:
asd asd
Date: May 09, 2017 01:07AM
Server version: 5.7.18 MySQL Community Server (GPL)
CREATE TABLE `p0_05` (
`aid` bigint(20) NOT NULL AUTO_INCREMENT,
`atime` datetime NOT NULL,
`num` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`,`atime`),
KEY `atime` (`atime`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (dayofmonth(atime))
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM) */;
insert into `p0_05` (`atime`,`num`) values('2017-05-01 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-02 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-03 10:00:00',1);
mysql> explain partitions select *
-> from `p0_05`
-> where atime BETWEEN '2017-05-01' and
-> '2017-05-02';
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | p0_05 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30 | range | atime | atime | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
why?
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30