Query OK, 0 rows affected (0.08 sec)

从最大值后加个分区

mysql> alter table prange1 add partition (partition p3 values less than (40));

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

从分区的最小值前加个分区

mysql> alter table prange1 add partition (partition p43 values less than (1));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

由此可见，RANGE 的分区方式在加分区的时候，只能从最大值后面加，而最大值前面不可以添加；

6. 用时间做分区测试

create table ptime2(id int,createdate datetime) engine=myisam partition by range (to_days(createdate))

(partition po values less than (20100801),partition p1 values less than (20100901));

Query OK, 0 rows affected (0.01 sec)

mysql> create table ptime3(id int,createdate datetime) engine=myisam partition by range (createdate)

(partition po values less than (20100801),partition p1 values less than (20100901));

ERROR 1491 (HY000): The PARTITION function returns the wrong type

直接使用时间列不可以，RANGE分区函数返回的列需要是整型。

mysql> create table ptime6(id int,createdate datetime) engine=myisam partition by range (year(createdate))

(partition po values less than (2010),partition p1 values less than (2011));

Query OK, 0 rows affected (0.01 sec) www.2cto.com

使用年函数也可以分区。

7.Mysql可用的分区函数

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT()

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

WEEKDAY()

YEAR()

YEARWEEK() 等

当然，还有FLOOR（），CEILING() 等，前提是使用这两个分区函数的分区健必须是整型。

要小心使用其中的一些函数，避免犯逻辑性的错误，引起全表扫描。

比如：

create table ptime11(id int,createdate datetime) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));

mysql> insert into ptime11 values (1,'2010-06-17');

mysql> explain partitions select count(1) from ptime11 where createdate>'2010-08-17'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: ptime11

partitions: po,p1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

8.主键及约束测试

分区健不包含在主键内

mysql> create table pprimary(id int,createdate datetime,primary key(id)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31)); www.2cto.com

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

分区健包含在主键内

mysql> create table pprimary1(id int,createdate datetime,primary key(id,createdate)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));

Query OK, 0 rows affected (0.05 sec)

说明分区健必须包含在主键里面。

mysql> create table pprimary2(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(uid)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function