1.查看Mysql版本是否支持分区

SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| have_partitioning | YES |

+-------------------+-------+

如果VALUE 为YES 则支持分区，

2.测试那种存储引擎支持分区

INOODB引擎 www.2cto.com

mysql> Create table engine1(id int) engine=innodb partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

MRG_MYISAM引擎

mysql> Create table engine2(id int) engine=MRG_MYISAM partition by range(id)(partition po values less than(10));

ERROR 1572 (HY000): Engine cannot be used in partitioned tables

blackhole引擎

mysql> Create table engine3(id int) engine=blackhole partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

CSV引擎

mysql> Create table engine4(id int) engine=csv partition by range(id)(partition po values less than(10));

ERROR 1572 (HY000): Engine cannot be used in partitioned tables

Memory引擎 www.2cto.com

mysql> Create table engine5(id int) engine=memory partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

federated引擎

mysql> Create table engine6(id int) engine=federated partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

archive引擎

mysql> Create table engine7(id int) engine=archive partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

myisam 引擎

mysql> Create table engine8(id int) engine=myisam partition by range(id)(partition po values less than(10));

Query OK, 0 rows affected (0.01 sec)

3.Mysql分区表，分区引擎测试

表分区的存储引擎相同

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);

Query OK, 0 rows affected (0.05 sec)

表分区的存储引擎不同

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 www.2cto.com

同一个分区表中的所有分区必须使用同一个存储引擎，并且存储引擎要和主表的保持一致。

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值