在MySQL5.1和以上版本都能够支持表的分区了,今天也试了一下。查询效率确实是很高,不过每跨一个分区基本就多一倍时间。
但是遇到一个问题:
比如我的表如下: SQL codeCREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL
)engine=myisam PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE );
注意上面的代码是在创建表的同时创建分区,上面的表中没有设置主键,能够执行成功。 但是我想要设置一个主键,比如设置字段c1为主键,这时候就执行不了。。提示主键没有包含在分区里面。怎么办?
因为我分区不是按主键,而是按另外一个字段的值来分,怎么解决呢?有人碰到没
但是遇到一个问题:
比如我的表如下: SQL codeCREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL
)engine=myisam PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE );
注意上面的代码是在创建表的同时创建分区,上面的表中没有设置主键,能够执行成功。 但是我想要设置一个主键,比如设置字段c1为主键,这时候就执行不了。。提示主键没有包含在分区里面。怎么办?
因为我分区不是按主键,而是按另外一个字段的值来分,怎么解决呢?有人碰到没
比如下面的主键c1,c3 包含了你的range c3mysql> CREATE TABLE part_tab (
-> c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL ,
-> primary key (c1,c3)
-> )engine=myisam
-> PARTITION BY RANGE (year(c3)) (
-> PARTITION p0 VALUES LESS THAN (1995),
-> PARTITION p1 VALUES LESS THAN (1996) ,
-> PARTITION p2 VALUES LESS THAN (1997) ,
-> PARTITION p3 VALUES LESS THAN (1998) ,
-> PARTITION p4 VALUES LESS THAN (1999) ,
-> PARTITION p5 VALUES LESS THAN (2000) ,
-> PARTITION p6 VALUES LESS THAN (2001) ,
-> PARTITION p7 VALUES LESS THAN (2002) ,
-> PARTITION p8 VALUES LESS THAN (2003) ,
-> PARTITION p9 VALUES LESS THAN (2004) ,
-> PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p11 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)mysql>