MySql RANGE分区

---

 

创建按年度分区的数据表：

CREATE TABLE `thesis` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `title` varchar(64) NOT NULL COMMENT '标题',
  `year` int(4) NOT NULL COMMENT '年份',
  PRIMARY KEY (`id`, `year`)
) 
PARTITION BY RANGE (`year`) (
    PARTITION `p2000` VALUES LESS THAN (2000),
    PARTITION `p2010` VALUES LESS THAN (2010),
    PARTITION `p2020` VALUES LESS THAN (2020),
    PARTITION `p2030` VALUES LESS THAN (2030) 
);

 

---

 

为已有数据表按年度分区：

ALTER TABLE  `thesis`
PARTITION BY RANGE (`year`) (
    PARTITION `p2000` VALUES LESS THAN (2000),
    PARTITION `p2010` VALUES LESS THAN (2010),
    PARTITION `p2020` VALUES LESS THAN (2020),
    PARTITION `p2030` VALUES LESS THAN (2030)
);

 

---

 

删除老旧分区：

ALTER TABLE `thesis` DROP PARTITION p2000, p2010;