MySQL清空分区表单个分区数据及自动清理实现

清空单个分区数据的操作

要清空分区表中的特定分区，可以使用 ALTER TABLE ... TRUNCATE PARTITION 语法。此命令会物理删除该分区中的数据文件，执行速度极快，远优于基于主键或条件 DELETE 操作。

第1步：手动清空指定分区

目的： 快速释放特定分区占用的磁盘空间并清除数据。
入口： MySQL命令行客户端或数据库管理工具（如Navicat, DBeaver）。
动作： 执行 ALTER TABLE 语句。
参数： p20220104 为具体的分区名称。

ALTER TABLE your_table_name TRUNCATE PARTITION p20220104;

执行完成后，该分区将变为空，但分区结构保留。可以通过查询 information_schema.partitions 查看分区状态。

使用存储过程批量或动态清空

如果需要根据日期动态计算分区名并清空，或者定期清理历史数据，封装为存储过程会更加便捷和安全。

第2步：创建动态清空存储过程

目的： 根据传入的天数参数，自动拼接对应日期的分区名并执行清空操作。
入口： MySQL Workbench 或 CLI 中的 DATABASE 上下文。
动作： 定义存储过程，使用预处理语句构建和执行 ALTER TABLE 命令。
重要参数： p_trunc_before_date 表示清空距今多少天的数据；分区名格式假定为 pYYYYMMDD。

DELIMITER $$

USE `managerdb`$$

DROP PROCEDURE IF EXISTS `partition_trunc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_trunc`(p_schema_name VARCHAR(64), p_table_name VARCHAR(64), p_trunc_before_date INT)
BEGIN
    DECLARE trunc_part_name VARCHAR(16);
    
    -- 1. 计算目标分区的名称 (假设分区名为 pYYYYMMDD 格式)
    SET trunc_part_name = CONCAT('p', DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL p_trunc_before_date DAY), '%Y%m%d'));
    
    -- 2. 构建 ALTER TABLE TRUNCATE PARTITION 语句
    SET @trunc_partitions = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, " TRUNCATE PARTITION ", trunc_part_name);
    
    -- 3. 打印 SQL 以便调试和确认
    SELECT @trunc_partitions AS executed_sql;
    
    -- 4. 执行动态 SQL
    PREPARE STMT FROM @trunc_partitions;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
END$$

DELIMITER ;

第3步：调用存储过程

完成判断： 过程无报错返回即成功。
动作： 调用刚才创建的 partition_trunc 过程。

-- 清空 test 库下 t_001 表，距今1天前的分区数据
CALL managerdb.partition_trunc('test', 't_001', 1);

在磁盘层面上，MySQL的一个分区通常对应一个单独的文件（取决于表和引擎配置）。因此，TRUNCATE PARTITION 本质上是直接删除该数据文件，这就是为什么它比逐行删除数据快得多的原因。

实现分区表数据的自动清理

对于日志类或监控类数据，通常按天分区并希望只保留最近N天的数据。手动维护容易遗漏，可以通过“预创建未来分区 + 自动删除过期分区”的策略实现全自动管理。

第4步：设置基础环境

确保数据库支持分区功能，并创建测试用表。以下示例创建一个按 created_at 字段范围分区的表。

DROP DATABASE IF EXISTS log;
CREATE DATABASE IF NOT EXISTS log CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE log;

CREATE TABLE IF NOT EXISTS tb_log (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  log_msg varchar(512) NOT NULL DEFAULT '',
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at))
(
  PARTITION pbasic VALUES LESS THAN (0)
);

第5步：创建辅助存储过程

包含两个核心过程：一个是创建未来几天的分区，另一个是检查并删除过期的分区。

1. sp_create_log_partition： 检查指定日期的分区是否存在，若不存在则添加。防止重复创建报错。
2. sp_drop_log_partition： 遍历当前分区，如果发现某个分区的起始日期早于指定的“保留截止时间”，则将其删除。

-- 创建新分区（带事务保护和存在性检查）
DELIMITER $$
CREATE DEFINER = 'uiadmin'@'%'
PROCEDURE sp_create_log_partition (day_value datetime, tb_name varchar(128))
BEGIN
  DECLARE par_name varchar(32);
  DECLARE par_exist int(1);
  DECLARE _err int(1) DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;

  START TRANSACTION;
  SET par_name = CONCAT('p', DATE_FORMAT(day_value, '%Y%m%d'));

  -- 检查分区是否已存在
  SELECT COUNT(1) INTO par_exist
  FROM information_schema.PARTITIONS
  WHERE TABLE_SCHEMA = 'log' AND TABLE_NAME = tb_name AND PARTITION_NAME = par_name;

  IF (par_exist = 0) THEN
    -- 不存在则添加，Less Than 值为次日零点
    SET @alter_sql = CONCAT('ALTER TABLE ', tb_name, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (TO_DAYS("', DATE_FORMAT(day_value, "%Y-%m-%d"), '")+1))');
    PREPARE stmt1 FROM @alter_sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
  END IF;
  COMMIT;
END$$

-- 删除过期分区
CREATE DEFINER = 'uiadmin'@'%'
PROCEDURE sp_drop_log_partition (day_value datetime, tb_name varchar(128))
BEGIN
  DECLARE str_day varchar(64);
  DECLARE par_name varchar(64);
  DECLARE done int DEFAULT 0;
  DECLARE cur_partition_name CURSOR FOR
    SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'log' AND table_name = tb_name ORDER BY partition_ordinal_position;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur_partition_name;
  REPEAT
    FETCH cur_partition_name INTO par_name;
    IF NOT done THEN
      -- 如果分区名里的数字小于截止日期，说明已过保，应删除
      IF (DATE_FORMAT(day_value, '%Y%m%d') > SUBSTRING(par_name, 2)) THEN
        SET @drop_sql = CONCAT('ALTER TABLE ', tb_name, ' DROP PARTITION ', par_name);
        PREPARE stmt1 FROM @drop_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
      END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur_partition_name;
END$$
DELIMITER ;

第6步：配置定时事件（Event Scheduler）

创建一个每天执行的定时器，负责“向前看几天做备份/预留，向后推30天删旧数据”。这将实现真正的无人值守维护。

1. 开启事件调度器： 检查全局变量 event_scheduler 是否为 ON。
2. 创建 Event： 每天调用上述存储过程。

-- 确保事件调度器开启
SET GLOBAL event_scheduler = ON;

DELIMITER $$
CREATE DEFINER = 'uiadmin'@'%'
EVENT event_log_auto_partition
ON SCHEDULE EVERY 1 DAY
STARTS '1972-01-01 00:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
  -- 1. 提前创建未来3天的分区，避免业务插入时报错
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL -3 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL -2 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL -1 DAY), 'tb_log');
  CALL sp_create_log_partition(NOW(), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 1 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 2 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 3 DAY), 'tb_log');

  -- 2. 删除超过30天以前的数据分区
  CALL sp_drop_log_partition(DATE_ADD(NOW(), INTERVAL -30 DAY), 'tb_log');
END$$
DELIMITER ;

第7步：验证与维护

查看当前分区： 通过以下 SQL 可以查看表当前的分区分布情况，确认清理和新增是否生效。

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='log' AND TABLE_NAME='tb_log';

通过上述组合拳，即可实现MySQL分区表的自动生命周期管理。建议在正式运行前，先在测试环境中验证存储过程的逻辑，特别是 sp_drop_log_partition 中的日期对比逻辑是否符合你的业务归档要求。

总结

MySQL分区表的维护核心在于合理的分区策略和定期的清理动作。对于单分区清理，首选 TRUNCATE PARTITION；对于长期运维，建议部署基于 EVENT 和存储过程的自动化脚本，以最小化人工干预成本。

以上就是MySQL清空分区表单个分区数据及自动清理方案的详细内容，更多关于MySQL分区表优化的资料请关注本站其它相关文章！