MYSQL使用心得(十一)----按日期进行表分区

创建

createtableuser(idint(11)NOTNULLAUTO_INCREMENT,

namevarchar(255),

birthdaydatedefaultNULL,

PRIMARYKEY(id,birthday))

partitionbyrange(birthday)

(partitionp0valueslessthan('1985-12-26'),

partitionp1VALUESLESSTHAN('2013-12-01'));

报错

ERROR1697(HY000):VALUESvalueforpartition'p0'musthavetypeINT

正常创建

createtableuser(idint(11)NOTNULLAUTO_INCREMENT,

namevarchar(255),

birthdaydatedefaultNULL,

PRIMARYKEY(id,birthday))engine=innodb

partitionbyrange(to_days(birthday))

(PARTITIONp0VALUESLESSTHAN(to_days('1985-01-01')),

PARTITIONp1VALUESLESSTHAN(to_days('2004-01-01')),

PARTITIONp2VALUESLESSTHAN(to_days('2005-01-01')),

PARTITIONp3VALUESLESSTHAN(to_days('2006-01-01')),

PARTITIONp4VALUESLESSTHAN(to_days('2007-01-01')),

PARTITIONp5VALUESLESSTHAN(to_days('2010-01-01')),

PARTITIONp6VALUESLESSTHANMAXVALUE);

执行解释扫描

explainpartitionsselectbirthdayfromuserwherebirthday>'2006-06-01'andbirthday<'2006-12-12'\G

输出

***************************1.row***************************

id:1

select_type:SIMPLE

table:user

partitions:p0,p4

type:index

possible_keys:NULL

key:PRIMARY

key_len:7

ref:NULL

rows:2

Extra:Usingwhere;Usingindex

1rowinset(0.00sec)