mysql> Create table pengine1(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=myisam);
mysql> Create table pengine2(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=innodb);
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
同一個分區表中的所有分區必須使用同一個存儲引擎，并且存儲引擎要和主表的保持一致。
4.分區類型
Range:基于一個連續區間的列值，把多行分配給分區；
LIST：列值匹配一個離散集合；
Hash:基于用戶定義的表達式的返回值選擇分區，表達式對要插入表中的列值進行計算。這個函數可以包含SQL中有效的，產生非負整
數值的任何表達式。
KEY：類似于HASH分區，區別在于KEY 分區的表達式可以是一列或多列，且MYSQL提供自身的HASH函數。
5.RANGE分區MAXVALUE值 及加分區測試；
創建表 PRANGE，最后分區一個分區值是MAXVALUE
mysql> Create table prange(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.06 sec)
加分區
mysql> alter table prange add partition (partition p3 values less than (20));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
在分區P0前面加個分區
mysql> alter table prange add partition (partition p3 values less than (1));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
說明有MAXVALUE值后，直接加分區是不可行的；
創建表PRANGE1，無MAXVALUE值
mysql> Create table prange1(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than (30)); www.2cto.com
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)
使用年函數也可以分區。
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
說明在表上建約束索引會有問題，必須把約束索引列包含在分區健內。
mysql> create table pprimary3(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(createdate)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));
Query OK, 0 rows affected (0.00 sec)
雖然在表上可以加約束索引，但是只有包含在分區健內，這種情況在實際應用過程中會遇到問題，這個問題點在以后的MYSQL 版本中也許會改進。
9.子分區測試
只有RANGE和LIST分區才能有子分區，每個分區的子分區數量必須相同，
mysql> create table pprimary7(id int,createdate datetime,uid char(10),primary key(id,createdate)) engine=myisam partition by range(to_days(createdate)) subpartition by hash(to_days(createdate))(partition p0 values less than (20100801) ( subpartition so,subpartition s1) ,partition p1 values less than (20100901) (subpartition s0,subpartition s1)); www.2cto.com
ERROR 1517 (HY000): Duplicate partition name s1
提示了重復的分區名稱錯誤，這和MYSQL5.1幫助文檔中的說明有出入，不知道是不是這個問題在某個小版本中修改過。
10.MYSQL分區健NULL值測試；
MYSQL將NULL值視為0.自動插入最小的分區中。
11.MYSQL分區管理測試
mysql> alter table pprimary4 truncate partition p1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'truncate partition p1' at line 1
5.1版本中還不支持這個語法，5.5中已經支持，很好的一個命令；
ALTER TABLE reorganize 可以重新組織分區。