我正在使用分区创建一个表，如下所示：
CREATE TABLE `alert` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`eventId` int(10) unsigned NOT NULL,
`occurred` datetime NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (TO_DAYS(occurred))
(PARTITION 28_06 VALUES LESS THAN (735413) ENGINE = InnoDB,
PARTITION 29_06 VALUES LESS THAN (735414) ENGINE = InnoDB,
PARTITION 30_06 VALUES LESS THAN (735415) ENGINE = InnoDB,
PARTITION 01_07 VALUES LESS THAN (735416) ENGINE = InnoDB,
PARTITION 02_07 VALUES LESS THAN (735417) ENGINE = InnoDB,
PARTITION 03_07 VALUES LESS THAN (735418) ENGINE = InnoDB,
PARTITION 04_07 VALUES LESS THAN (735419) ENGINE = InnoDB,
PARTITION 05_07 VALUES LESS THAN (735420) ENGINE = InnoDB,
PARTITION 06_07 VALUES LESS THAN (735421) ENGINE = InnoDB,
PARTITION 07_07 VALUES LESS THAN (735422) ENGINE = InnoDB) */
似乎一切正常，但当我执行以下查询时：
EXPLAIN PARTITIONS SELECT * FROM alert WHERE occurred >= '2013-06-30' AND occurred <= '2013-07-01';
我得到以下输出：
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | alert | 28_06,30_06,01_07 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
为什么分区
28_06会出现在这些结果中？当我非常清楚地选择启动2013-06-30时。有什么想法吗？
奇怪的是，下面的问题似乎很好：
EXPLAIN PARTITIONS SELECT * FROM alert WHERE occurred >= '2013-07-02' AND occurred <= '2013-07-04';
输出：
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | alert | 02_07,03_07,04_07 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
最佳答案
看起来这是一个回归错误。我正在Mac OS X 10.8上使用5.6.11版本。
http://bugs.mysql.com/bug.php?id=49754
解决方案是在开始时创建一个从未使用过的分区。即。：
PARTITION unused VALUES LESS THAN (0)
关于mysql - 为什么此MySql查询(在分区表上)总是命中第一个分区？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17364190/