MYSQL的分区操作

DROP TABLE IF EXISTS PartitionTest;

#创建表
CREATE TABLE PartitionTest
(
   pid                  INT NOT NULL AUTO_INCREMENT,
   pname                VARCHAR(100) NOT NULL,
   ptime                DATETIME NOT NULL,
   PRIMARY KEY (ptime, pid)
)
#联合主键，且联合主键中有自增列时，表引擎必须为MYISAM
ENGINE = MYISAM;
 
#创建表分区，记住要有columns，分区名必须一样（区分大小写），分区编号从0开始
ALTER TABLE PartitionTest
PARTITION BY RANGE COLUMNS(ptime)(
	PARTITION PartitionTest_0 VALUES LESS THAN('2014-04-01'),
	PARTITION PartitionTest_1 VALUES LESS THAN('2014-05-01')
);

#添加分区
ALTER TABLE PartitionTest 
ADD PARTITION(
	PARTITION PartitionTest_2 
	VALUES LESS THAN('2014-06-01')
);

#合并分区
ALTER TABLE PartitionTest 
REORGANIZE PARTITION PartitionTest_0,PartitionTest_1
INTO(
	PARTITION p0 VALUES LESS THAN('2014-05-01')
);

#删除分区，当只有一个分区时，无法删除会报错，删除分区会同时删除分区中的数据
ALTER TABLE PartitionTest 
DROP PARTITION p0;

#查询分区信息
SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_schema='JYC_BTR' 
  AND partition_name IS NOT NULL;