mysql表分区

mysql表分区主要分为RANGE分区，LIST分区，HASH分区，LINEAR HASH分区，KEY分区

目前，我所使用的是RANGE分区，其中主要注意两点：

1.分区所用的字段必须是主键，并且，如果有设置唯一键，该字段也需要添加到唯一键中，否则会报错。

创建表时，建立分区的sql如下所示：

CREATETABLE`T_part` (

      `f_id`INTDEFAULTNULL,

      `f_name`VARCHAR(20)DEFAULTNULL,

       PRIMARYKEY(`f_id`)

) ENGINE=myisamDEFAULTCHARSET=utf8

PARTITIONBYRANGE (f_id)(-----指定分区方式

      PARTITION p0VALUESless THAN (10),--分了两个区

      PARTITION p1VALUESless THAN (20)

）;

修改表，建立分区时sql如下所示：

ALTER TABLE wms_pac_package_detail PARTITION BY RANGE(TO_DAYS(`created_at`))

(

     PARTITION p0 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = INNODB,

     PARTITION p1 VALUES LESS THAN (TO_DAYS('2017-05-01')) ENGINE = INNODB,

     PARTITION p2 VALUES LESS THAN (TO_DAYS('2017-06-01')) ENGINE = INNODB,

     PARTITION p3 VALUES LESS THAN (TO_DAYS('2017-07-01')) ENGINE = INNODB,

     PARTITION p4 VALUES LESS THAN (TO_DAYS('2017-08-01')) ENGINE = INNODB,

     PARTITION p5 VALUES LESS THAN (TO_DAYS('2017-09-01')) ENGINE = INNODB,

     PARTITION p6 VALUES LESS THAN (TO_DAYS('2017-10-01')) ENGINE = INNODB,

     PARTITION p7 VALUES LESS THAN (TO_DAYS('2017-11-01')) ENGINE = INNODB,

     PARTITION p8 VALUES LESS THAN (TO_DAYS('2017-12-01')) ENGINE = INNODB,

     PARTITION p9 VALUES LESS THAN (TO_DAYS('2018-01-01')) ENGINE = INNODB,

     PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = INNODB

);