mysql 按小时分区_mysql怎么按小时分区

如果是mysql5.5还是可以做到的，5.1不行

CREATE TABLE part_date

( c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,

c2 varchar(40) not null default '',

c3 datetime not  NULL,

PRIMARY KEY (c1,c3),

KEY partidx(c3))  ENGINE=InnoDB DEFAULT CHARSET=utf8

partition by range  COLUMNS(c3)

(

PARTITION p201012 VALUES LESS THAN ('2011-01-01 06:00:00'),

PARTITION p201101 VALUES LESS THAN ('2011-01-01 12:00:00'),

PARTITION p201102 VALUES LESS THAN ('2011-01-01  18:00:00'),

PARTITION p201103 VALUES LESS THAN ('2011-01-01  23:59:59'),

PARTITION p201912 VALUES LESS THAN MAXVALUE );

然后用函数录入数据

DELIMITER $$

DROP PROCEDURE IF EXISTS `load_data` $$

CREATE DEFINER=`root`@`%` PROCEDURE `load_data`()

BEGIN

declare v int default 0;

while v < 10000

do

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 01:00:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 03:00:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 05:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 07:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 15:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 16:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 17:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 18:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 19:01:00');

set v = v + 1;

end while;

END $$

DELIMITER ;

分区

explain partitions select count(*) from part_date where c3 > date '2011-01-01 06:02:00' and c3 < date '2011-01-01 08:02:00'

看一下只走了p201101分区