MySQL实现清空分区表单个分区数据
作者:Demonson 发布时间:2024-01-14 03:45:34
标签: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
/*
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
0
投稿
猜你喜欢
- 深入认识Python内建类型——dict注:本篇是根据教程学习记录的笔记,部分内容与教程是相同的,因
- 参数strSQL 要导出的SQL查询语句strFields 字段名称列表,如果为空字符,则使用SQL语句中的字段名用法示例:1:export
- SQL Server 2005默认是不允许远程连接的,要想通过远程连接实现MSSQL,数据库备份,需要做如下设置:步骤/方法1.打开SQL
- 假如文件夹有大量视频文件,需求目标是想从每个视频中提取一帧作为视频的一个封面图片,本文利用opencv-python模块实现需求。结合自己的
- 图形由json格式加载,可以灵活配置。下一步是完善用户的操作,做这个感觉还是蛮有意思的。呵呵。截图:部分源码:<script type
- arange()类似于内置函数range(),通过指定开始值、终值和步长创建表示等差数列的一维数组,注意得到的结果数组不包含终值。linsp
- 在MySQL中,对于索引的使用并是一直都采用正确的决定。简单表的示例:CREATE TABLE `r2` (ID` int(11) DEFA
- asp在线备份sql server数据库: 1、备份sqlserver 代码如下:<% SQL="backup&n
- 先看下jupyter和pycharm环境的差别左边是jupyter-------------------------------------
- 1.lxml库介绍lxml是XML和HTML的解析器,其主要功能是解析和提取XML和HTML中的数据;lxml和正则一样,也是用C语言实现的
- 一、连接MYSQL格式: mysql -h主机地址 -u用户名 -p用户密码1、 连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录
- 1、可以控制左横向滚动还是右横向滚动。2、鼠标悬停上时暂停滚动,移开后恢复滚动。3、间歇时间按需调整,但不要低于容器向左/右移动的时间。JA
- Python产生一个数值范围内的不重复的随机数,可以使用random模块中的random.sample函数,其用法如下:import ran
- 根据TIOBE 编程语言排行榜前20的语言分享相关图书(部分空缺)。在正式介绍编程语言排行之前,你敢不敢先挑战一下自己的编程技能?!测试下自
- 如下所示:def append(arr, values, axis=None): """ Append val
- python打印完整异常import tracebackif __name__ == "__main__":  
- 现象:在IE下,用JS修改p标签的innerHTML时,出"未知的运行时错误(unknown runtime error)&quo
- 最近切换到了Ubuntu的系统作为工作环境, 在使用Pycharm的时候, 出现了个奇怪的问题中文是无法正常输入的, 然后找遍了网上的解决办
- 首先需要注意的是,本文即将提到的 Druid,并非阿里巴巴的 Druid 数据库连接池,而是另一个大数据场景下的解决方案:Apache Dr
- where条件表达式--统计函数Select count(1) from student;--like模糊查询--统计班上姓张的人数sele