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)