MySQL实现清空分区表单个分区数据
作者:Demonson 时间:2024-01-14 03:45:34
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
/*
p_trunc_before_date 清空分区表第N天的数据
*/
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 log
CHARACTER SET utf8
COLLATE 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 = INNODB
AUTO_INCREMENT = 1
AVG_ROW_LENGTH = 16384
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
PARTITION 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
--
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
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';
在磁盘上一个分区表现为一个文件,所以删除操作会很快完成的。
来源:https://blog.csdn.net/demonson/article/details/127683373
标签:MySQL,清空,分区表,分区数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
设计和企业文化
2009-03-28 10:35:00
pycharm远程连接vagrant虚拟机中mariadb数据库
2024-01-13 19:11:33
![](https://img.aspxhome.com/file/2023/2/134692_0s.png)
从源码深入理解golang RWMutex读写锁操作
2024-02-08 10:50:33
![](https://img.aspxhome.com/file/2023/0/113090_0s.jpg)
python使用三角迭代计算圆周率PI的方法
2021-12-29 06:47:02
对Python中plt的画图函数详解
2023-08-16 08:49:07
PHP addslashes()函数讲解
2023-06-04 04:28:24
![](https://img.aspxhome.com/file/2023/0/55400_0s.png)
OpenCV+Python3.5 简易手势识别的实现
2022-06-02 18:38:26
![](https://img.aspxhome.com/file/2023/4/107994_0s.jpg)
自动化收集SQLSERVER诊断信息的工具选择及使用介绍
2024-01-14 11:01:04
![](https://img.aspxhome.com/file/2023/9/116299_0s.gif)
python连接FTP服务器的实现方法
2022-04-07 14:34:03
![](https://img.aspxhome.com/file/2023/3/120553_0s.png)
Python基于递归算法实现的走迷宫问题
2023-08-25 03:55:05
利用xmlhttp和adodb.stream加缓存技术下载远程Web文
2009-04-23 18:33:00
Python 文件操作的详解及实例
2021-06-05 12:59:12
Python Spyder 调出缩进对齐线的操作
2023-07-17 00:56:04
![](https://img.aspxhome.com/file/2023/2/61172_0s.jpg)
Python进程通信之匿名管道实例讲解
2021-08-26 16:23:46
Python入门学习指南分享
2023-09-02 15:02:21
![](https://img.aspxhome.com/file/2023/1/124681_0s.png)
sql语句返回主键SCOPE_IDENTITY()
2024-01-12 13:14:36
Python configparser模块操作代码实例
2021-11-05 18:48:08
如何将python代码打包成pip包(可以pip install)
2021-08-23 21:38:10
MySQL分页优化解析
2008-12-22 14:56:00
Flask response响应的具体使用
2021-01-30 06:48:27
![](https://img.aspxhome.com/file/2023/0/107530_0s.png)