定时添加，删除Mysql分区

最新推荐文章于 2025-01-12 00:06:12 发布

转载最新推荐文章于 2025-01-12 00:06:12 发布 · 2k 阅读

Mysql 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍如何在MySQL中创建和管理分区表，包括使用UNIX_TIMESTAMP进行范围分区，通过存储过程自动创建和删除分区，以及设置定时任务实现自动化分区管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

注意：
分区指定字段一定是这个表的主健,一段创建好分区，不能跳跃创建和删除

步骤：
1、添加、删除分区存储过程脚本
2、Event定时执行
3、创建分区

1、创建表

create table mc_stat_trends(
    udate TIMESTAMP PRIMARY KEY,
    item varchar(20) not null,
    node varchar(20) not null,
    itemvalue float(11) not null
) default CHARSET=utf8

注意：错误码: 1486
Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
需要timestamp类型分区时不起作用要改成DATETIME or DATE 或者 DATETIME or DATE 不起作用时timestamp

2、表已创建中途加入创建分区

ALTER TABLE mc_stat_trends PARTITION BY RANGE (UNIX_TIMESTAMP(udate))(
    PARTITION p20160901 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-02')),
    PARTITION p20160902 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-03')),
    PARTITION p20160903 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-04')),
    PARTITION p20160904 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-05')),
    PARTITION p20160905 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-06')),
    PARTITION p20160906 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-07')),
    PARTITION p20160907 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-08')),
    PARTITION p20160908 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-09')),
    PARTITION p20160909 VALUES LESS THAN (UNIX_TIMESTAMP('2016-09-10'))
)

3、创建、删除分区存储过程

CREATE PROCEDURE `proc_add_drop_partition`(IN v_tablename VARCHAR(50),v_drop_interval INT,v_add_interval INT)
BEGIN
/* =======================================================================
     * 系统名 : **系统
     * 子系统名 : 创建,删除分区
     * PROC名 : proc_mc_addjust_partition
     * 概要 : 每天规定时间内创建和删除分区
     * 例：加分区： alter table mc_stat_trends add partition (partition p20150417 VALUES LESS THAN('2015-04-17'))
     *     删分区: alter table mc_stat_trends drop partition p20150310
     * 改版履历 :
     * 版本日期作者名备注
     * 1.0.1 2015.03.01 ### 初次作成
     * ======================================================================= */
    -- 传入变量: v_drop_interval 删除日 v_add_interval 添加日
    DECLARE v_add_interval_1 INT;
    SET v_add_interval_1=v_add_interval+1;
    START TRANSACTION;
    SET @s=CONCAT('alter table ',v_tablename,' drop partition ', CONCAT('p',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m%d')));
    SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ', CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval DAY),'%Y%m%d')), ' VALUES LESS THAN','(',UNIX_TIMESTAMP(DATE(DATE_ADD(NOW(),INTERVAL v_add_interval_1 DAY))),')',')');
    SELECT @s;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT @t;
    PREPARE stmt FROM @t;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    COMMIT;
END;

4、创建Event定时执行

CREATE DEFINER=`root`@`localhost`
EVENT `NewEvent`
ON SCHEDULE EVERY 1 DAY STARTS '2016-09-02 01:10:00'
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
CALL proc_add_drop_partition('mc_stat_trends',8,1);
END;

5、查看已创建分区

show create table mc_stat_trends

原文：https://blog.youkuaiyun.com/xlxxcc/article/details/52486426