MySql按年月日自动创建分区存储过程

-- 创建存储过程【通过数据库和表名】建立【partition_number】get分区，分区间隔为【gaps】
-- datasource 数据库名称
-- table_name 数据库表名
-- partition_number 新建分区的数量
-- partition_type 分区类型（0-按天分区，1-按月分区，2-按年分区）
-- gaps 分区间隔（按分区类型分别为gaps天、gaps月、gaps年）
-- max_p_num 最大分区数
-- 此存储过程执行的前提是执行的表是有分区的表

DELIMITER $$
DROP PROCEDURE IF EXISTS `auto_add_partitions`$$
CREATE PROCEDURE `auto_add_partitions`(
	in datasource varchar(50),
	in table_name varchar(50),
	in partition_number int,
	in partition_type int,
	in gaps int,
	in max_p_num int
)
L_END:
BEGIN
declare max_p_name varchar(50);
declare next_partition_name varchar(50);
declare min_p_name varchar(50);
declare cur_date_str varchar(20);
declare next_date_str varchar(20);
declare exec_sql varchar(300);
declare p_num int default 0;
declare i int default 1;
declare min_partition_description int;
-- 查询表是否是分区表,不是则结束
set p_num = (select count(1) from information_schema.PARTITIONS p where TABLE_NAME = table_name and TABLE_SCHEMA = datasource);
if p_num = 0 then 
	select concat( 'not partition with ', table_name);
	leave L_END;
else
	while (i <= partition_number) do 
		-- 最大分区
		SET max_p_name=(select IFNULL(partition_name,'') from information_schema.PARTITIONS p where TABLE_NAME = table_name and TABLE_SCHEMA = datasource order by PARTITION_DESCRIPTION desc limit 1);
		if max_p_name = NULL then 
			select concat('no partitions on ', table_name);
			leave L_END;
		end if;
		if max_p_name = '' then 
			select concat('no partitions on ', table_name);
			leave L_END;
		end if;
		-- 按日分区
		if partition_type = 0 then 
			SET cur_date_str = (select cast(DATE_SUB(STR_TO_DATE(substr(max_p_name, 2, length(max_p_name)) ,'%Y%m%d'),INTERVAL -1*gaps day) as char) from dual);
			set next_date_str = (select cast(DATE_SUB(STR_TO_DATE(replace(cur_date_str, '-', '') ,'%Y%m%d'),INTERVAL -1*gaps day) as char) from dual);
			SET next_partition_name= concat('p',replace(cur_date_str, '-', ''));
			set @exec_sql = concat('alter table ', datasource, '.', table_name,' add partition (partition ', next_partition_name, ' values less than (to_days(''', next_date_str, ''')));');
			prepare stmt from @exec_sql;
			execute stmt;
			deallocate prepare stmt;
		end if;
		-- 按月分区
		if partition_type = 1 then 
			SET cur_date_str = (select cast(DATE_SUB(STR_TO_DATE(substr(max_p_name, 2, length(max_p_name)) ,'%Y%m'),INTERVAL -1*gaps month) as char) from dual);
			set next_date_str = (select cast(DATE_SUB(STR_TO_DATE(replace(cur_date_str, '-', '') ,'%Y%m'),INTERVAL -1*gaps month) as char) from dual);
			SET next_partition_name= concat('p',replace(cur_date_str, '-', ''));
			set @exec_sql = concat('alter table ', datasource, '.', table_name,' add partition (partition ', next_partition_name, ' values less than (to_days(''', next_date_str, ''')));');
		    prepare stmt from @exec_sql;
			execute stmt;
			deallocate prepare stmt;
		end if;
		-- 按年分区
		if partition_type = 2 then 
			SET cur_date_str = (select cast(DATE_SUB(STR_TO_DATE(substr(max_p_name, 2, length(max_p_name)) ,'%Y'),INTERVAL -1*gaps year) as char) from dual);
			set next_date_str = (select cast(DATE_SUB(STR_TO_DATE(replace(cur_date_str, '-', '') ,'%Y'),INTERVAL -1*gaps year) as char) from dual);
			SET next_partition_name= concat('p',replace(cur_date_str, '-', ''));
			set @exec_sql = concat('alter table ', datasource, '.', table_name,' add partition (partition ', next_partition_name, ' values less than (to_days(''', next_date_str, ''')));');
		    prepare stmt from @exec_sql;
			execute stmt;
			deallocate prepare stmt;
		end if;
		commit;
		set i = (i + 1);
	end while;
    set @delFlag = 1;
	while @delFlag > 0 do
		-- 查询当前分区数
		select count(PARTITION_NAME) into p_num from information_schema.PARTITIONS p where TABLE_NAME = table_name and TABLE_SCHEMA = datasource;
 		if p_num <= max_p_num then
 		  set @delFlag = 0;
 		end if;
 		if p_num > max_p_num then
			-- 最小分区
 			set min_partition_description = (select min(PARTITION_DESCRIPTION) from information_schema.PARTITIONS p where TABLE_NAME = table_name and TABLE_SCHEMA = datasource);
			set min_p_name = (select partition_name from information_schema.PARTITIONS p where TABLE_NAME = table_name and TABLE_SCHEMA = datasource and PARTITION_DESCRIPTION = min_partition_description);
			set @exec_sql = concat('alter table ', datasource, '.', table_name,' drop partition ', min_p_name, ';');
			prepare stmt from @exec_sql;
			execute stmt;
			deallocate prepare stmt;
		end if;
	end while;
end if;
END$$
DELIMITER ;

执行此存储过程有两个方式，以下使用的是MySql的事件调度器（另一种是通过代码执行call脚本实现）

首先，确保事件调度器（Event Scheduler）已经开启

SHOW VARIABLES LIKE 'event_scheduler';

如果返回的值是OFF，则可以通过以下命令开启事件调度器：

SET GLOBAL event_scheduler = ON;

然后新建一个事件

-- 对sys_log表的 create_time 字段设置分区条件为按日分区
-- 创建一个Event,每天执行一次，同时最多保存365天的日志数据
DELIMITER $$
DROP EVENT IF EXISTS `sys_log_auto_partition`$$
CREATE EVENT `sys_log_auto_partition`
ON SCHEDULE EVERY 1 DAY ON COMPLETION PRESERVE
ENABLE
DO
L_END:
BEGIN
declare partition_number int default 1;
declare max_p_name varchar(50);
-- 最大分区
SET max_p_name=(select IFNULL(partition_name,'') from information_schema.PARTITIONS p where TABLE_NAME = 'sys_log' and TABLE_SCHEMA = 'mock' order by PARTITION_DESCRIPTION desc limit 1);
if max_p_name = NULL then 
	select concat('no partitions on ', table_name);
	leave L_END;
end if;
if max_p_name = '' then 
	select concat('no partitions on ', table_name);
	leave L_END;
end if;
-- 两个日期之间的差
SET partition_number = (select DATEDIFF(DATE_FORMAT(now(),'%Y%m%d'), substr(max_p_name, 2, length(max_p_name))) from dual);
if partition_number > 0 then
	call auto_add_partitions('mock', 'sys_log', partition_number, 0, 1, 365);
end if;
END$$
DELIMITER ;

-- 注意事项：
-- 对于Mysql 5.1以上版本来说，表分区的索引字段必须是主键
-- 存储过程中，DECLARE 必须紧跟着BEGIN，否则会报看不懂的错误
-- 游标的DECLARE需要在定义声明之后，否则会报错
-- 如果是自己安装的Mysql，有可能Event功能是未开启的，在创建Event时会提示错误；修改my.cnf,在 [mysqld] 下添加event_scheduler=1后重启即可。
-- 执行时，需要选中DELIMITER $$到DELIMITER ;

相关表

CREATE TABLE `sys_log` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `case_name` varchar(300) DEFAULT NULL COMMENT '案例名称',
  `env_ip` varchar(39) DEFAULT NULL COMMENT '环境IP',
  `service_scene` varchar(20) DEFAULT NULL COMMENT '服务码+场景码',
  `request_method` varchar(10) DEFAULT NULL COMMENT '请求方式',
  `response_format` varchar(10) DEFAULT NULL COMMENT '响应格式',
  `request_ip` varchar(39) DEFAULT NULL COMMENT '请求IP（最长可存IPv6：XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX）',
  `check_key` varchar(300) DEFAULT NULL COMMENT '校验键',
  `check_value` varchar(300) DEFAULT NULL COMMENT '校验值',
  `response_content` longtext COMMENT '响应报文',
  `request_content` longtext COMMENT '请求报文',
  `del_flag` int(1) DEFAULT '0' COMMENT '删除标识，0-未删除；1-已删除',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(300) DEFAULT NULL COMMENT '备注',
  `ser_no` varchar(64) DEFAULT NULL COMMENT '接口流水号',
  `response_id` bigint(20) DEFAULT NULL COMMENT '报文响应表主键',
  `hit_target` int(1) DEFAULT NULL COMMENT '命中接口，0-未命中；1-命中',
  PRIMARY KEY (`log_id`,`create_time`),
  KEY `sys_log_case_name_IDX` (`case_name`) USING BTREE,
  KEY `sys_log_env_ip_IDX` (`env_ip`) USING BTREE,
  KEY `sys_log_service_scene_IDX` (`service_scene`) USING BTREE,
  KEY `sys_log_check_key_IDX` (`check_key`) USING BTREE,
  KEY `sys_log_check_value_IDX` (`check_value`) USING BTREE,
  KEY `sys_log_ser_no_IDX` (`ser_no`) USING BTREE,
  KEY `sys_log_response_id_IDX` (`response_id`) USING BTREE,
  KEY `sys_log_hit_target_IDX` (`hit_target`) USING BTREE
) COMMENT='系统接口请求日志表'
PARTITION BY RANGE(to_days(`create_time`)) (
	partition p20250101 values less than (to_days('2025-01-01'))
);

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/952208.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

新版2024AndroidStudio项目目录结构拆分

新版2024AndroidStudio项目目录结构拆分

如题 下载了最新版的android studio 发现目录结构和以前不一样 自动帮你合并了 如何层层抽丝剥茧呢 按照一下步骤即可解决问题&#xff01;
阅读更多...
【Rust自学】11.6. 控制测试运行：并行和串行（连续执行）测试

【Rust自学】11.6. 控制测试运行：并行和串行（连续执行）测试

喜欢的话别忘了点赞、收藏加关注哦&#xff0c;对接下来的教程有兴趣的可以关注专栏。谢谢喵&#xff01;(&#xff65;ω&#xff65;) 11.6.1. 控制测试的运行方式 cargo test和cargo run一样&#xff0c;cargo test也会编译代码并生成一个二进制文件用于测试&#xff0c;…
阅读更多...
linux:文件的创建/删除/复制/移动/查看/查找/权限/类型/压缩/打包

linux:文件的创建/删除/复制/移动/查看/查找/权限/类型/压缩/打包

关于文件的关键词 创建 touch 删除 rm 复制 cp 权限 chmod 移动 mv 查看内容 cat(全部); head(前10行); tail(末尾10行); more,less 查找 find 压缩 gzip ; bzip 打包 tar 编辑 sed 创建文件 格式&#xff1a; touch 文件名 删除文件 复制文件 移动文件 查看文…
阅读更多...
Docker 基础知识

Docker 基础知识

背景 传统的linux的环境部署 命令多步骤多安装版本多 使用docker的话&#xff0c;一个命令就可以全部搞定 安装linux 之前安装过&#xff0c;所以直接使用的开罩进行复制的如果之前配置过静态地址&#xff0c;需要改成IPV4静态地址访问 安装docker 参考连接&#xff1a;https:/…
阅读更多...
Docker 从入门到精通

Docker 从入门到精通

文章目录 Ubuntu 安装Docker步骤前言1. 进入Docker官网&#xff0c;进入开发者页面2. 选择适合自己的安装方式3. 安装 Docker1.更新系统包&#xff0c;安装插件&#xff0c;创建秘钥及目录2.安装 Docker 软件包3.设置开机启动4.通过运行 hello-world 镜像验证安装是否成功 常见…
阅读更多...
概率图模型01

概率图模型01

机器学习中&#xff0c;线性回归、树、集成和概率图都属于典型的统计学习方法&#xff0c;概率图模型会更深入地体现出‘统计’两字 概率图模型的常见算法 概率图模型中的图 概率图模型如图主要分为两种&#xff0c;即贝叶斯网络和马尔可夫网络&#xff0c;有向图与无向图&…
阅读更多...
Vue Router4

Vue Router4

Vue Router 是 Vue.js 官方的路由管理器。Vue Router 基于路由和组件的映射关系&#xff0c;页面路径发生改变&#xff0c;就进行对应的组件切换。 安装&#xff1a; npm install vue-router。 基本 使用&#xff1a; // src/router/index.js import {createRouter, create…
阅读更多...
深度学习知识点：LSTM

深度学习知识点：LSTM

文章目录 1.应用现状2.发展历史3.基本结构4.LSTM和RNN的差异 1.应用现状 长短期记忆神经网络&#xff08;LSTM&#xff09;是一种特殊的循环神经网络(RNN)。原始的RNN在训练中&#xff0c;随着训练时间的加长以及网络层数的增多&#xff0c;很容易出现梯度爆炸或者梯度消失的问…
阅读更多...
通过氧化最小化工艺提高SiC MOSFET迁移率的深入分析

通过氧化最小化工艺提高SiC MOSFET迁移率的深入分析

标题 Insight Into Mobility Improvement by the Oxidation-Minimizing Process in SiC MOSFETs&#xff08;TED2024&#xff09; 文章的研究内容 文章的研究内容主要围绕氧化最小化工艺&#xff08;oxidation-minimizing process&#xff09;对碳化硅&#xff08;SiC&…
阅读更多...
【Unity小技巧】解决Visual Code中文乱码

【Unity小技巧】解决Visual Code中文乱码

在Mac下使用VS Code打开代码时&#xff0c;中文注释显示乱码。 解决方法&#xff1a; VS Code&#xff1a;Setting -> Settings -> 搜索“autoGuessEncoding”&#xff0c;然后勾选上即可。 简体中文的Encoding是GB 2312。
阅读更多...
maven 下载依赖 jhash:2.1.2 和对应 jar 包

maven 下载依赖 jhash:2.1.2 和对应 jar 包

原文地址 前言 25年新的一年&#xff0c;那就先更新一篇技术文章吧&#xff0c;这个是这几天刚遇到的一个有意思的bug&#xff0c;记录分享一下 原因分析 在使用maven加载一个项目的时&#xff0c;发现maven的依赖一直无法解析&#xff0c;更换阿里云镜像和中央仓库都没办法…
阅读更多...
回归预测 | MATLAB基于RF-Adaboost多输入单输出回归预测

回归预测 | MATLAB基于RF-Adaboost多输入单输出回归预测

回归预测 | MATLAB基于RF-Adaboost多输入单输出回归预测 目录 回归预测 | MATLAB基于RF-Adaboost多输入单输出回归预测预测效果基本介绍程序设计参考资料 预测效果 基本介绍 回归预测 | MATLAB基于RF-Adaboost多输入单输出回归预测。 1.Matlab实现RF-Adaboost随机森林集成学习…
阅读更多...
【网络协议】动态路由协议

【网络协议】动态路由协议

前言 本文将概述动态路由协议&#xff0c;定义其概念&#xff0c;并了解其与静态路由的区别。同时将讨论动态路由协议相较于静态路由的优势&#xff0c;学习动态路由协议的不同类别以及无类别&#xff08;classless&#xff09;和有类别&#xff08;classful&#xff09;的特性…
阅读更多...
基于SSM实现的垃圾分类平台系统功能实现二

基于SSM实现的垃圾分类平台系统功能实现二

一、前言介绍&#xff1a; 1.1 项目摘要 随着城市化进程的加速和居民生活水平的提高&#xff0c;城市生活垃圾的产量急剧增加&#xff0c;给城市环境管理带来了巨大压力。传统的垃圾处理方式&#xff0c;如填埋和焚烧&#xff0c;不仅占用大量土地资源&#xff0c;还可能对环…
阅读更多...
如何实现多级缓存？

如何实现多级缓存？

本文重点说一说在Java应用中&#xff0c;多级缓存如何实现。 多级缓存是比较常见的一种性能优化的手段&#xff0c;一般来说就是本地缓存分布式缓存。 本地缓存一般采用Caffeine和Guava&#xff0c;这两种是性能比较高的本地缓存的框架。他们都提供了缓存的过期、管理等功能。…
阅读更多...
美摄科技为企业打造专属PC端视频编辑私有化部署方案

美摄科技为企业打造专属PC端视频编辑私有化部署方案

美摄科技&#xff0c;作为视频编辑技术的先行者&#xff0c;凭借其在多媒体处理领域的深厚积累&#xff0c;为企业量身打造了PC端视频编辑私有化部署解决方案&#xff0c;旨在帮助企业构建高效、安全、定制化的视频创作平台&#xff0c;赋能企业内容创新&#xff0c;提升品牌影…
阅读更多...
嵌入式C语言：什么是指针？

嵌入式C语言：什么是指针？

目录 一、指针的基本概念 1.1. 定义指针 1.2. 赋值给指针 1.3. 解引用指针 1.4. 指针运算 1.5. 空指针 1.6. 函数参数 1.7. 数组和指针 1.8. 示例代码 二、指针在内存中的表示 2.1. 内存地址存储 2.2. 内存模型 2.3. 指针与硬件交互 2.4. 示例代码 三 、指针的重…
阅读更多...
计算机网络相关习题整理

计算机网络相关习题整理

第一讲 传输媒介 【知识点回顾】 两种导线可以减小电磁干扰&#xff1a; 双绞线&#xff08;分为非屏蔽双绞线、屏蔽双绞线&#xff09;&#xff08;RJ-45用&#xff09;同轴电缆&#xff08;短距离使用&#xff09;网络通信的基本单位&#xff1a;位&#xff08;bit&#xff…
阅读更多...
应急响应之入侵排查（下）

应急响应之入侵排查（下）

一.进程排查 1.Windows 任务管理器查看 在 Windows 系统中&#xff0c;可通过任务管理器查看进程信息。操作步骤为&#xff1a;在任务管理器界面&#xff0c;于 “查看” 选项中选择 “选择列”&#xff0c;随后添加 “映像路径名称” 和 “命令行”&#xff0c;以此查看更多进…
阅读更多...
极狐GitLab 正式发布安全版本17.7.1、17.6.3、17.5.5

极狐GitLab 正式发布安全版本17.7.1、17.6.3、17.5.5

本分分享极狐GitLab 补丁版本 17.7.1, 17.6.3, 17.5.5 的详细内容。这几个版本包含重要的缺陷和安全修复代码&#xff0c;我们强烈建议所有私有化部署用户应该立即升级到上述的某一个版本。对于极狐GitLab SaaS&#xff0c;技术团队已经进行了升级&#xff0c;无需用户采取任何…
阅读更多...
最新文章

Copyright @ 2022~2023