mysql 分区表 range分区

首先呢我们来看下怎么创建一个分区表

在上节课的时候 我们也说过 在分区的时候
如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。

1 ,按照年龄的范围
create table staff_r12(
id int not null auto_increment,
name varchar(40) not null,
age int not null,
primary key (id,age)
)engine=myisam default charset=utf8
partition by range(age)(
partition age_1 values less than(20),
partition age_2 values less than(40),
partition age_3 values less than maxvalue
);

2，按照时间戳timestamp范围来分区

create table staff_r13(
id int not null,
staff_status varchar(20) not null,
report_updated timestamp not null default current_timestamp on update current_timestamp
)
partition by range(unix_timestamp(report_updated))(
partition p0 values less than (unix_timestamp(‘2008-01-01 00:00:00’)),
partition p1 values less than (unix_timestamp(‘2008-04-01 00:00:00’)),
partition p2 values less than (unix_timestamp(‘2008-07-01 00:00:00’)),
partition p3 values less than (unix_timestamp(‘2008-10-01 00:00:00’)),
partition p9 values less than maxvalue
);

6.1.3、根据DATE、DATETIME范围
mysql不支持在日期类型上面直接创建分区，需要借助函数来创建
create table staff_14(
name varchar(25) not null,
age int not null,
joined date not null
)
partition by range columns(joined)(
partition p0 values less than (‘1960-01-01’),
partition p1 values less than (‘1970-01-01’),
partition p2 values less than (‘1980-01-01’),
partition p3 values less than (‘1990-01-01’),
partition p4 values less than maxvalue
);
6.1.4、RANGE分区在如下场合特别有用

create table staff_15(
id int not null,
name varchar(30),
joined date not null default ‘9999-12-31’
)engine=myisam default charset=utf8
partition by range(year(joined))(
partition p0 values less than (2016),
partition p1 values less than (2017),
partition p4 values less than MAXVALUE
);