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
同一個分區(qū)表中的所有分區(qū)必須使用同一個存儲引擎，并且存儲引擎要和主表的保持一致。
4.分區(qū)類型
Range:基于一個連續(xù)區(qū)間的列值，把多行分配給分區(qū)；
LIST：列值匹配一個離散集合；
Hash:基于用戶定義的表達(dá)式的返回值選擇分區(qū)，表達(dá)式對要插入表中的列值進(jìn)行計算。這個函數(shù)可以包含SQL中有效的，產(chǎn)生非負(fù)整
數(shù)值的任何表達(dá)式。
KEY：類似于HASH分區(qū)，區(qū)別在于KEY 分區(qū)的表達(dá)式可以是一列或多列，且MYSQL提供自身的HASH函數(shù)。
5.RANGE分區(qū)MAXVALUE值 及加分區(qū)測試；
創(chuàng)建表 PRANGE，最后分區(qū)一個分區(qū)值是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)
加分區(qū)
mysql> alter table prange add partition (partition p3 values less than (20));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
在分區(qū)P0前面加個分區(qū)
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值后，直接加分區(qū)是不可行的；
創(chuàng)建表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)
從最大值后加個分區(qū)
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
從分區(qū)的最小值前加個分區(qū)
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 的分區(qū)方式在加分區(qū)的時候，只能從最大值后面加，而最大值前面不可以添加；
6. 用時間做分區(qū)測試
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分區(qū)函數(shù)返回的列需要是整型。
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)
使用年函數(shù)也可以分區(qū)。
7.Mysql可用的分區(qū)函數(shù)
DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() DATEDIFF() EXTRACT() HOUR() MICROSECOND() MINUTE() MOD() MONTH() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() WEEKDAY() YEAR() YEARWEEK() 等 |
當(dāng)然，還有FLOOR（），CEILING() 等，前提是使用這兩個分區(qū)函數(shù)的分區(qū)健必須是整型。
要小心使用其中的一些函數(shù)，避免犯邏輯性的錯誤，引起全表掃描。
比如：
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.主鍵及約束測試
分區(qū)健不包含在主鍵內(nèi)
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
分區(qū)健包含在主鍵內(nèi)
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)
說明分區(qū)健必須包含在主鍵里面。
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
說明在表上建約束索引會有問題，必須把約束索引列包含在分區(qū)健內(nèi)。
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)
雖然在表上可以加約束索引，但是只有包含在分區(qū)健內(nèi)，這種情況在實際應(yīng)用過程中會遇到問題，這個問題點在以后的MYSQL 版本中也許會改進(jìn)。
9.子分區(qū)測試
只有RANGE和LIST分區(qū)才能有子分區(qū)，每個分區(qū)的子分區(qū)數(shù)量必須相同，
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
提示了重復(fù)的分區(qū)名稱錯誤，這和MYSQL5.1幫助文檔中的說明有出入，不知道是不是這個問題在某個小版本中修改過。
10.MYSQL分區(qū)健NULL值測試；
MYSQL將NULL值視為0.自動插入最小的分區(qū)中。
11.MYSQL分區(qū)管理測試
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中已經(jīng)支持，很好的一個命令；
ALTER TABLE reorganize 可以重新組織分區(qū)。