mysql 5.5 分区

1。如果要启用mysql5.5 的分区功能，have_partitioning需要为yes，我当初编译的时候有加-DWITHOUT_PARTITION_STORAGE_ENGINE=1，导致have_partitioning值为no，重新执行cmake . -DWITHOUT_PARTITION_STORAGE_ENGINE=0才将have_partitioning修改为yes   所以编译的源文件最好保留起来，以后要再修改编译项可以直接用

2.对新建表的分区语句如下

 CREATE TABLE `URIC_ACID` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `local_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_phone` varchar(11) NOT NULL,
  `uric_acid_value` decimal(6,2) NOT NULL,
  `eval_result` varchar(256) NOT NULL,
  `reserve_1` decimal(12,0) DEFAULT NULL,
  `reserve_2` decimal(12,0) DEFAULT NULL,
  `reserve_3` varchar(64) DEFAULT NULL,
  `reserve_4` varchar(64) DEFAULT NULL,
  `result_flag` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) 
(
PARTITION uric_acid_50 VALUES LESS THAN (500000)  ,
 PARTITION uric_acid_100 VALUES LESS THAN (1000000)  ,
 PARTITION uric_acid_150 VALUES LESS THAN (1500000)  ,
 PARTITION uric_acid_200 VALUES LESS THAN (2000000)  ,
 PARTITION uric_acid_250 VALUES LESS THAN (2500000)  ,
 PARTITION uric_acid_300 VALUES LESS THAN (3000000)  ,
 PARTITION uric_acid_350 VALUES LESS THAN (3500000)  ,
 PARTITION uric_acid_400 VALUES LESS THAN (4000000)  ,
 PARTITION uric_acid_450 VALUES LESS THAN (4500000)  ,
 PARTITION uric_acid_500 VALUES LESS THAN (5000000)  ,
 PARTITION uric_acid_max VALUES LESS THAN (10000000000)

 )

3.对已存在表建分区，语句如下

alter table `CHOLESTEROL` partition by range (id)
(
PARTITION cholesterol_50  VALUES LESS THAN (500000),
PARTITION cholesterol_100 VALUES LESS THAN (1000000),
PARTITION cholesterol_150 VALUES LESS THAN (1500000),
PARTITION cholesterol_200 VALUES LESS THAN (2000000),
PARTITION cholesterol_250 VALUES LESS THAN (2500000),
PARTITION cholesterol_300 VALUES LESS THAN (3000000),
PARTITION cholesterol_350 VALUES LESS THAN (3500000),
PARTITION cholesterol_400 VALUES LESS THAN (4000000),
PARTITION cholesterol_450 VALUES LESS THAN (4500000),
PARTITION cholesterol_500 VALUES LESS THAN (5000000),
PARTITION cholesterol_max VALUES LESS THAN (10000000000)
);

4.查看分区信息

4.1  show crate table BLOOD_PRESS ;

4.2  查看INFORMATION_SCHEMA.PARTITIONS表

select TABLE_SCHEMA,TABLE_NAME ,PARTITION_NAME,DATA_LENGTH,TABLE_ROWS from  INFORMATION_SCHEMA.PARTITIONS;

5.mysql 5.5 移除分区

alter table 表名 remove partitioning   <---不会删除数据

6.注意 mysql的分区字段必须包含在主键字段里，否则报错，如果分区字段a不是主键，又必须要按a分区，那么只能将a设为主键或与别的字段建成复合主键。

报错为：

错误提示：#1503

 A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION