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
输出
*************************** 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)