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)
