mysql分区表按日期分区查询_mysql5.5 数据表根据日期分区实例

将产生的数据根据日期分区，一天一个分区，创建分区实例如下:

/*==============================================================*/

/* Table: histdata */

/*==============================================================*/

create table histdata

(

id int not null,

sid char(36) not null,

sname char(20),

date datetime not null,

vtype int,

value binary(16),

aid char(36),

aname char(20),

did char(36),

dname char(20),

primary key (id,date)

)

PARTITION BY RANGE COLUMNS (date) (

PARTITION p1 VALUES LESS THAN ('2000-01-02'),

PARTITION p2 VALUES LESS THAN ('2000-01-03'),

PARTITION p3 VALUES LESS THAN ('2000-01-04'),

PARTITION p4 VALUES LESS THAN ('2000-01-05'),

PARTITION p5 VALUES LESS THAN ('2000-01-06'),

PARTITION p6 VALUES LESS THAN ('2000-01-07'),

PARTITION p7 VALUES LESS THAN ('2000-01-08'),

PARTITION p8 VALUES LESS THAN ('2000-01-09'),

PARTITION p9 VALUES LESS THAN ('2000-01-10'),

PARTITION p10 VALUES LESS THAN ('2000-01-11'),

PARTITION p11 VALUES LESS THAN ('2000-01-12'),

PARTITION p12 VALUES LESS THAN ('2000-01-13'),

PARTITION p13 VALUES LESS THAN ('2000-01-14'),

PARTITION p14 VALUES LESS THAN ('2000-01-15'),

PARTITION p15 VALUES LESS THAN ('2000-01-16'),

PARTITION p16 VALUES LESS THAN ('2000-01-17'),

PARTITION p17 VALUES LESS THAN ('2000-01-18'),

PARTITION p18 VALUES LESS THAN ('2000-01-19'),

PARTITION p19 VALUES LESS THAN ('2000-01-20'),

PARTITION p20 VALUES LESS THAN ('2000-01-21'),

PARTITION p21 VALUES LESS THAN ('2000-01-22'),

PARTITION p22 VALUES LESS THAN ('2000-01-23'),

PARTITION p23 VALUES LESS THAN ('2000-01-24'),

PARTITION p24 VALUES LESS THAN ('2000-01-25'),

PARTITION p25 VALUES LESS THAN ('2000-01-26'),

PARTITION p26 VALUES LESS THAN ('2000-01-27'),

PARTITION p27 VALUES LESS THAN ('2000-01-28'),

PARTITION p28 VALUES LESS THAN ('2000-01-29'),

PARTITION p29 VALUES LESS THAN ('2000-01-30'),

PARTITION p30 VALUES LESS THAN ('2000-01-31'),

PARTITION p31 VALUES LESS THAN ('2000-02-01'),

PARTITION p32 VALUES LESS THAN ('2000-02-02'),

PARTITION p33 VALUES LESS THAN ('2000-02-03'),

PARTITION p34 VALUES LESS THAN ('2000-02-04'),

PARTITION p35 VALUES LESS THAN ('2000-02-05'),

PARTITION p36 VALUES LESS THAN ('2000-02-06'),

PARTITION p37 VALUES LESS THAN ('2000-02-07'),

PARTITION p38 VALUES LESS THAN ('2000-02-08'),

PARTITION p39 VALUES LESS THAN ('2000-02-09'),

PARTITION p40 VALUES LESS THAN ('2000-02-10'),

PARTITION p41 VALUES LESS THAN ('2000-02-11'),

PARTITION p42 VALUES LESS THAN ('2000-02-12'),

PARTITION p43 VALUES LESS THAN ('2000-02-13'),

PARTITION p44 VALUES LESS THAN ('2000-02-14'),

PARTITION p45 VALUES LESS THAN ('2000-02-15'),

PARTITION p46 VALUES LESS THAN ('2000-02-16'),

PARTITION p47 VALUES LESS THAN ('2000-02-17'),

PARTITION p48 VALUES LESS THAN ('2000-02-18'),

PARTITION p49 VALUES LESS THAN ('2000-02-19'),

PARTITION p50 VALUES LESS THAN ('2000-02-20'),

PARTITION p51 VALUES LESS THAN ('2000-02-21'),

PARTITION p52 VALUES LESS THAN ('2000-02-22'),

PARTITION p53 VALUES LESS THAN ('2000-02-23'),

PARTITION p54 VALUES LESS THAN ('2000-02-24'),

PARTITION p55 VALUES LESS THAN ('2000-02-25'),

PARTITION p56 VALUES LESS THAN ('2000-02-26'),

PARTITION p57 VALUES LESS THAN ('2000-02-27'),

PARTITION p58 VALUES LESS THAN ('2000-02-28'),

PARTITION p59 VALUES LESS THAN ('2000-02-29'),

PARTITION p60 VALUES LESS THAN ('2000-03-01'),

PARTITION p61 VALUES LESS THAN ('2000-03-02'),

PARTITION p62 VALUES LESS THAN ('2000-03-03'),

PARTITION p63 VALUES LESS THAN ('2000-03-04'),

PARTITION p64 VALUES LESS THAN ('