mysql 分区表

11) NOT NULL AUTO_INCREMENT, `confirm_time` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00‘, `order_id` varchar(50) DEFAULT NULL COMMENT ‘平台单号‘, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`confirm_time`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (TO_DAYS (confirm_time)) (PARTITION p_less_min VALUES LESS THAN (737425) ENGINE = InnoDB, PARTITION p_201901 VALUES LESS THAN (737456) ENGINE = InnoDB, PARTITION p_201902 VALUES LESS THAN (737484) ENGINE = InnoDB, PARTITION p_201903 VALUES LESS THAN (737515) ENGINE = InnoDB, PARTITION p_201904 VALUES LESS THAN (737545) ENGINE = InnoDB, PARTITION p_201905 VALUES LESS THAN (737576) ENGINE = InnoDB, PARTITION p_201906 VALUES LESS THAN (737606) ENGINE = InnoDB, PARTITION p_201907 VALUES LESS THAN (737637) ENGINE = InnoDB, PARTITION p_201908 VALUES LESS THAN (737668) ENGINE = InnoDB, PARTITION p_201909 VALUES LESS THAN (737698) ENGINE = InnoDB, PARTITION p_201910 VALUES LESS THAN (737729) ENGINE = InnoDB, PARTITION p_201911 VALUES LESS THAN (737759) ENGINE = InnoDB, PARTITION p_201912 VALUES LESS THAN (737790) ENGINE = InnoDB, PARTITION p_202001 VALUES LESS THAN (737821) ENGINE = InnoDB, PARTITION p_202002 VALUES LESS THAN (737850) ENGINE = InnoDB, PARTITION p_202003 VALUES LESS THAN (737881) ENGINE = InnoDB, PARTITION p_202004 VALUES LESS THAN (737911) ENGINE = InnoDB, PARTITION p_202005 VALUES LESS THAN (737942) ENGINE = InnoDB, PARTITION p_202006 VALUES LESS THAN (737972) ENGINE = InnoDB, PARTITION p_202007 VALUES LESS THAN (738003) ENGINE = InnoDB, PARTITION p_202008 VALUES LESS THAN (738034) ENGINE = InnoDB, PARTITION p_202009 VALUES LESS THAN (738064) ENGINE = InnoDB, PARTITION p_202010 VALUES LESS THAN (738095) ENGINE = InnoDB, PARTITION p_202011 VALUES LESS THAN (738125) ENGINE = InnoDB, PARTITION p_202012 VALUES LESS THAN (738156) ENGINE = InnoDB, PARTITION p_less_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

以confirm_time 作为分区字段

2.判断分区是否存在

SELECT partition_name,partition_expression,partition_description,table_rows FROM  INFORMATION_SCHEMA.partitions
              WHERE  TABLE_SCHEMA=SCHEMA() AND TABLE_NAME=‘表名‘ and partition_name=‘分区名称‘

3.新增分区

alter table 表名 add partition (
                        partition 分区名 values less than (分区范围) engine = innodb
                )

4.删除分区

alter table 表名 drop partition 分区名

mysql 分区表

标签：current   分区表   primary   sch   script   rop   table   max   create