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

创建

create table user (id int(11) 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 1697 (HY000): VALUES value for partition 'p0' must have type INT

正常创建

create table user (id int(11) 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);

执行解释扫描

explain partitions select birthday from user where birthday>'2006-06-01' and birthday<'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: Using where; Using index

1 row in set (0.00 sec)

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/