MySQL分区表创建，分区创建、删除示例

内置函数相互转换：

SELECT TO_DAYS('2021-01-01');
SELECT FROM_DAYS(738162);

 

创建分区表示例

CREATE TABLE IF NOT EXISTS `{tb_name}` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `line_id` int(11) DEFAULT NULL,
      `link_type` varchar(255) DEFAULT NULL,
      `link_bandwidth` int(11) DEFAULT NULL,
      `in_max_utilization` float DEFAULT NULL,
      `in_avg_utilization` float DEFAULT NULL,
      `out_max_utilization` float DEFAULT NULL,
      `out_avg_utilization` float DEFAULT NULL,
      `in_max_bps` float DEFAULT NULL,
      `in_avg_bps` float DEFAULT NULL,
      `out_max_bps` float DEFAULT NULL,
      `out_avg_bps` float DEFAULT NULL,
      `node_id` int(11) DEFAULT NULL,
      `node_ip` varchar(255) DEFAULT NULL,
      `node_name` varchar(255) DEFAULT NULL,
      `interface_id` int(11) DEFAULT NULL,
      `interface_name` varchar(255) DEFAULT NULL,
      `site_code` varchar(64) DEFAULT NULL,
      `date_t` datetime NOT NULL,
      `local_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`, `date_t`),
      KEY `index_date_t` (`date_t`) USING BTREE,
      KEY `index_line_id` (`line_id`),
      KEY `index_sitecode` (`site_code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
     partition BY range (TO_DAYS(date_t))(
        PARTITION part_1 VALUES LESS THAN TO_DAYS('2015-01-01')
);

 

删除分区表

ALTER TABLE `{tb_name}` DROP PARTITION `{partition_name}`;

 

在原分区表上增加新的分区

ALTER TABLE `{tb_name}` PARTITION BY RANGE(TO_DAYS(date_t)) (
    PARTITION {part2_name} VALUES LESS THAN TO_DAYS('2016-01-01'),
    PARTITION {part3_name} VALUES LESS THAN TO_DAYS('2016-02-01')
);