MySQL如何实现清空分区表单个分区数据

这篇“MySQL如何实现清空分区表单个分区数据”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“MySQL如何实现清空分区表单个分区数据”文章吧。

MySQL清空分区表单个分区数据

1.单个分区清空

ALTER TABLE xxx TRUNCATE PARTITION p20220104；

2.编辑存储过程

功能：指定清空之前某一天的数据，直接调用存储过程实现

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);        SET trunc_part_name = CONCAT('p',DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL p_trunc_before_date DAY),'%Y%m%d'));        SET @trunc_partitions = CONCAT("ALTER TABLE ", p_schema_name, ".", p_table_name, " TRUNCATE PARTITION ",trunc_part_name); -- 拼执行语句SELECT @trunc_partitions; -- 打印删除详情                 PREPARE STMT FROM @trunc_partitions;                         EXECUTE STMT;                         DEALLOCATE PREPARE STMT;        END$$ DELIMITER ;

实例：

call managerdb.partition_trunc('test','t_001',1);

清空test.t_001一天前的单个分区数据

MySQL自动分区自动清理

mysql分区表功能特别有用，其中一个应用就是保存固定时间的数据信息，自动分区自动purge，不用担心数据量越积累越多。

比较实用的一个实现方式是表一天一个分区，保持固定天数的数据。

完整的SQL

以数据库log为例，里面有一个表tb_log, 按天分区，始终保存最新的30天的数据。

存储过程sp_create_log_partition和sp_drop_log_partition用于创建和删除分区。

事件event_log_auto_partition每天执行一次，用于向前创建新的分区和删除过期的分区。

存储过程和事件结合使用就实现了tb_log数据的自动分区自动删除。

---- Definition for database log--DROP DATABASE IF EXISTS log;CREATE DATABASE IF NOT EXISTS logCHARACTER SET utf8COLLATE utf8_general_ci; -- -- Set default database--USE log; ---- Definition for table tb_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 varchar(512) NOT NULL DEFAULT '',  PRIMARY KEY (id, created_at))ENGINE = INNODBAUTO_INCREMENT = 1AVG_ROW_LENGTH = 16384CHARACTER SET utf8mb4COLLATE utf8mb4_general_ciPARTITION BY RANGE(TO_DAYS(created_at))(PARTITION pbasic VALUES LESS THAN (0)); DELIMITER $$ ---- Definition for procedure sp_create_log_partition--CREATE DEFINER = 'uiadmin'@'%'PROCEDURE sp_create_log_partition (day_value datetime, tb_name varchar(128))BEGIN  DECLARE par_name varchar(32);  DECLARE par_value varchar(32);  DECLARE _err int(1);  DECLARE par_exist int(1);  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      SET par_value = DATE_FORMAT(day_value, '%Y-%m-%d');      SET @alter_sql = CONCAT('alter table ', tb_name, ' add PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (TO_DAYS("', par_value, '")+1))');      PREPARE stmt1 FROM @alter_sql;      EXECUTE stmt1;    END IF;  END  $$ ---- Definition for procedure sp_drop_log_partition--CREATE DEFINER = 'uiadmin'@'%'PROCEDURE sp_drop_log_partition (day_value datetime, tb_name varchar(128))BEGIN  DECLARE str_day varchar(64);  DECLARE _err int(1);  DECLARE done int DEFAULT 0;  DECLARE par_name varchar(64);  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 SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  SET str_day = DATE_FORMAT(day_value, '%Y%m%d');  OPEN cur_partition_name;  REPEAT    FETCH cur_partition_name INTO par_name;    IF (str_day > SUBSTRING(par_name, 2)) THEN      SET @alter_sql = CONCAT('alter table ', tb_name, ' drop PARTITION ', par_name);      PREPARE stmt1 FROM @alter_sql;      EXECUTE stmt1;    END IF;  UNTIL done END REPEAT;  CLOSE cur_partition_name;END$$ ---- Definition for event event_log_auto_partition--CREATEDEFINER = 'uiadmin'@'%'EVENT event_log_auto_partitionON SCHEDULE EVERY '1' DAYSTARTS '1972-01-01 00:00:00'ON COMPLETION PRESERVEDOBEGIN  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');  CALL sp_drop_log_partition(DATE_ADD(NOW(), INTERVAL - 30 DAY), 'tb_log'); END$$ ---- Create partitions based on current time--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')$$ DELIMITER ;

查看分区

select TABLE_SCHEMA, TABLE_NAME,PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='tb_log';

在磁盘上一个分区表现为一个文件，所以删除操作会很快完成的。

以上就是关于“MySQL如何实现清空分区表单个分区数据”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注编程网行业资讯频道。