MYSQL版本:MySQL5.1.7-noinstall-beta(win32)
--建表语句
DROP TABLE IF EXISTS MD_TEST;
CREATE TABLE MD_TEST
(
STAT_TIME DATETIME NOT NULL,
VALUE INT UNSIGNED NOT NULL
)
PARTITION BY RANGE (DAYOFYEAR(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN MAXVALUE);
--插入测试数据库
INSERT INTO `md_test` (`STAT_TIME`, `VALUE`) VALUES
('2007-01-01',100),
('2007-01-02',200),
('2007-01-21',120);
--跨分区查询
SELECT STAT_TIME,SUM(VALUE) FROM md_test
WHERE STAT_TIME>=DATE'2007-01-01' AND STAT_TIME<=DATE'2007-01-03'
GROUP BY STAT_TIME
结果
STAT_TIME SUM(VALUE)
2007-1-1 0:00 100
查询结果与期望不符合
是我的建表还是查询语句有问题,百思不得其解,望高手不吝赐教!万分感谢。
--建表语句
DROP TABLE IF EXISTS MD_TEST;
CREATE TABLE MD_TEST
(
STAT_TIME DATETIME NOT NULL,
VALUE INT UNSIGNED NOT NULL
)
PARTITION BY RANGE (DAYOFYEAR(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN MAXVALUE);
--插入测试数据库
INSERT INTO `md_test` (`STAT_TIME`, `VALUE`) VALUES
('2007-01-01',100),
('2007-01-02',200),
('2007-01-21',120);
--跨分区查询
SELECT STAT_TIME,SUM(VALUE) FROM md_test
WHERE STAT_TIME>=DATE'2007-01-01' AND STAT_TIME<=DATE'2007-01-03'
GROUP BY STAT_TIME
结果
STAT_TIME SUM(VALUE)
2007-1-1 0:00 100
查询结果与期望不符合
是我的建表还是查询语句有问题,百思不得其解,望高手不吝赐教!万分感谢。
可能是 MySQL5.1.7在这种情况下查询有问题。以后大家遇到类似问题升级MYSQL版本即可:)
--方案1
DROP TABLE IF EXISTS TEST_DATA_SPEED;
CREATE TABLE TEST_DATA_SPEED
(
STAT_TIME DATETIME NOT NULL,
VALUE INT NOT NULL DEFAULT 0
)
PARTITION BY HASH(TO_DAYS(STAT_TIME))
PARTITIONS 100;EXPLAIN PARTITIONS
SELECT STAT_TIME,COUNT(*) FROM TEST_DATA_SPEED WHERE
((STAT_TIME>=ADDDATE('2007-01-27 10:00:00',-1) AND STAT_TIME<=STR_TO_DATE('2007-01-27 10:00:00','%Y-%m-%d %H:%i:%S')))
GROUP BY STAT_TIME
结果不能在指定的分区上查询,是全表检索。--方案2
DROP TABLE IF EXISTS TEST_DATA_SPEED2;
CREATE TABLE TEST_DATA_SPEED2
(
STAT_TIME DATETIME NOT NULL,
VALUE INT NOT NULL DEFAULT 0
)
PARTITION BY RANGE (TO_DAYS(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (to_days('2007-01-20')),
PARTITION p2 VALUES LESS THAN (to_days('2007-01-21')),
PARTITION p3 VALUES LESS THAN (to_days('2007-01-22')),
PARTITION p4 VALUES LESS THAN (to_days('2007-01-23')),
PARTITION p5 VALUES LESS THAN (to_days('2007-01-24')),
PARTITION p6 VALUES LESS THAN (to_days('2007-01-25')),
PARTITION p7 VALUES LESS THAN (to_days('2007-01-26')),
PARTITION p8 VALUES LESS THAN (to_days('2007-01-27')),
PARTITION p9 VALUES LESS THAN (to_days('2007-01-28')),
PARTITION p10 VALUES LESS THAN (to_days('2007-01-29')),
PARTITION p11 VALUES LESS THAN (to_days('2007-01-30')),
PARTITION p12 VALUES LESS THAN MAXVALUE );EXPLAIN PARTITIONS
SELECT STAT_TIME,COUNT(*) FROM TEST_DATA_SPEED2 WHERE
((STAT_TIME>=ADDDATE('2007-01-27 10:00:00',-1) AND STAT_TIME<=STR_TO_DATE('2007-01-27 10:00:00','%Y-%m-%d %H:%i:%S')))
GROUP BY STAT_TIME
结果可在指定的分区上(p8,p9)查询。可是该方案不能循环利用分区。是否有即可循环利用分区,又可跨分区查询指定分区的分区实现方法,请版主、高手们指教!