Experience with MySQL (11) Table partitioning by date

Create

CREATE table user (id int (one) not NULL auto_increment,

Name varchar (255),

Birthday Date Default NULL,

PRIMARY KEY (Id,birthday))

Partition by range (birthday)

(partition P0 values less than (' 1985-12-26 '),

Partition P1 VALUES less THAN (' 2013-12-01 '));

Error

ERROR 1697 (HY000): VALUES value for partition ' P0 ' must type INT

Normal creation

CREATE table user (id int (one) not NULL auto_increment,

Name varchar (255),

Birthday Date Default NULL,

PRIMARY KEY (id,birthday)) Engine=innodb

Partition by Range (To_days (birthday))

(PARTITION p0 VALUES less THAN (to_days (' 1985-01-01 ')),

PARTITION p1 VALUES Less THAN (to_days (' 2004-01-01 ')),

PARTITION P2 VALUES Less THAN (to_days (' 2005-01-01 ')),

PARTITION P3 VALUES Less THAN (to_days (' 2006-01-01 ')),

PARTITION P4 VALUES Less THAN (to_days (' 2007-01-01 ')),

PARTITION P5 VALUES Less THAN (to_days (' 2010-01-01 ')),

PARTITION P6 VALUES less THAN MAXVALUE);

Perform an interpretation scan

Explain partitions select birthday from user where birthday> ' 2006-06-01 ' and birthday< ' 2006-12-12 '

Output

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:using where; Using Index

1 row in Set (0.00 sec)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/