MYSQL的分区操作

来源：互联网发布：mysql存储过程中游标编辑：程序博客网时间：2024/05/22 12:49

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))#联合主键，且联合主键中有自增列时，表引擎必须为MYISAMENGINE = MYISAM; #创建表分区，记住要有columns，分区名必须一样（区分大小写），分区编号从0开始ALTER TABLE PartitionTestPARTITION 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_1INTO(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;

0 0