mysql 表分区设置

#查看分区支持
#5.6之前，使用show variables like '%partition%'
#5.6之后
SHOW PLUGINS;

#创建表
CREATE TABLE `dd_tree` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `TreeType` INT(11) NOT NULL DEFAULT '0' COMMENT '树类型',
  `TreeName` VARCHAR(50) DEFAULT NULL COMMENT '树名称',
  PRIMARY KEY (`ID`,`TreeType`)
) ENGINE=INNODB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO dd_tree(`TreeType`,`TreeName`) 
VALUES
(2,'2'),
(3,'3'),
(4,'4'),
(6,'6'),
(1,'1');

 
#删除主键，在删除主键的时候，这个自增会让该语句执行失败，先取消字段自增，然后执行该语句后，再加上自增
ALTER TABLE dd_tree CHANGE `ID` `ID` BIGINT(20) NOT NULL COMMENT '主键',  DROP PRIMARY KEY;
#添加主键，（分区要求：分区中使用的字段必须都包含在主键当中）
ALTER TABLE dd_tree
  CHANGE `ID` `ID` BIGINT(20) NOT NULL AUTO_INCREMENT   COMMENT '主键',
  CHANGE `TreeType` `TreeType` INT(11) DEFAULT 0  NOT NULL   COMMENT '树类型', 
  ADD PRIMARY KEY (`ID`, `TreeType`);


-- 创建分区(分区要求：分区中使用的字段必须都包含在主键当中)
ALTER TABLE dd_tree PARTITION BY LIST(TreeType)
(
  PARTITION p0 VALUES IN (0), 
  PARTITION p1 VALUES IN (1), 
  PARTITION p2 VALUES IN (2), 
  PARTITION p3 VALUES IN (3), 
  PARTITION p4 VALUES IN (4)
);


-- 删除分区
ALTER TABLE dd_tree DROP PARTITION p5;
-- 添加新的分区
ALTER TABLE dd_tree ADD PARTITION
(
    PARTITION p5 VALUES IN (5) ENGINE = INNODB,
    PARTITION p6 VALUES IN (6) ENGINE = INNODB
    #PARTITION p9 VALUES less then (6) ENGINE = INNODB
    
);
 

#查看某张表的分区名字，分区内行数，分区表达的列，分区范围
SELECT PARTITION_NAME,TABLE_ROWS,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='liu_jun_test' AND TABLE_NAME = 'dd_tree'; 

参考资料

https://cloud.tencent.com/developer/article/1349522
https://www.cnblogs.com/TheoryDance/p/11719572.html
https://blog.youkuaiyun.com/poxiaonie/article/details/76914305