【操作文档】mysql分区操作步骤.docx

news/2024/11/29 6:36:12/

1、建立分区表

执行 tb_intercept_notice表-重建-添加分区.sql 文件;

DROP TABLE IF EXISTS `tb_intercept_notice_20241101_new`;
CREATE TABLE `tb_intercept_notice_20241101_new` (`id` char(32) NOT NULL COMMENT 'id',`number` varchar(30) NOT NULL COMMENT '号码',`create_time` datetime(3) NOT NULL DEFAULT '2000-01-22 00:00:00.000' COMMENT '创建时间',PRIMARY KEY (`id`,`create_time`) USING BTREE,KEY `number` (`number`) USING BTREE,KEY `create_time` (`create_time`) USING BTREE,KEY `intercept_time` (`intercept_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='拦截信息';# 添加分区(20241001 号开始)
ALTER TABLE tb_intercept_notice_20241101_new
PARTITION BY RANGE (TO_DAYS(`create_time`))
(PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-10-02'))
);

功能:创建带有分区的 tb_intercept_notice_20241101_new 表,并添加初始分区 20241001;

2、创建调用存储过程

#添加分区-存储过程-创建 - 不带数据库参数
delimiter $$
DROP PROCEDURE IF EXISTS procedure_add_partition
$$# 参数
# tableName:表名
# partitionName:分区名称的日期部分(格式:yyyyMMdd)
CREATE PROCEDURE procedure_add_partition(IN tableName varchar(100),IN partitionName varchar(8))BEGIN# 声明 新增分区后一天日期DECLARE v_next_day varchar(20);# 赋值SET v_next_day = date_format(partitionName + INTERVAL 1 DAY, '%Y-%m-%d');SET @sql = concat('alter table ',tableName,' add partition (partition p', partitionName, ' values less than(TO_DAYS(''',v_next_day, ''')))');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END$$
delimiter;#删除分区-存储过程-创建 - 不带数据库参数
delimiter $$
DROP PROCEDURE IF EXISTS procedure_del_partition
$$# 参数
# tableName:表名
# partitionName:分区名称的日期部分(格式:yyyyMMdd)
CREATE PROCEDURE procedure_del_partition(IN tableName varchar(100),IN partitionName varchar(8))BEGINSET @sql = concat('alter table ',tableName,' drop partition p', partitionName);PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END$$
delimiter;

执行 添加分区-存储过程-创建.sql 文件创建添加分区的存储过程;

执行 删除分区-存储过程-创建.sql 文件创建删除分区的存储过程;

3、开启 mysql 事件

执行 SELECT @@event_scheduler; 命令,查看mysql 是否开启事件,Value 为 NO 表示开启成功;若为OFF,则使用 SET GLOBAL event_scheduler = ON; 命令开启;

4、创建事件

更改tb_intercept_notice表-添加分区-事件.sql 文件并执行;

(1)打开 tb_intercept_notice表-添加分区-事件.sql 文件,根据需求,更新脚本中的变量;(主要更改数据库名称和往当前日期之后提前创建分区的天数,默认30天

DELIMITER $$
drop event if exists tb_intercept_notice_add_partition_event
$$
create event tb_intercept_notice_add_partition_event
on schedule-- AT TIMESTAMP表示该事件只执行一次
-- AT TIMESTAMP '2020-11-20 00:00:00'-- 测试设置为每10秒执行一次
-- every 2 second
-- every 1 minute
-- STARTS CURRENT_TIMESTAMP-- 现网可以设置为每天执行一次(每天的当前时间执行)every 1 daySTARTS CURRENT_TIMESTAMPdo BEGINDECLARE v_sysdate date;        				# 声明 当前时间DECLARE v_maxdate date;   		 			# 声明 目前分区值中的最大值DECLARE v_pt varchar(20);      				# 声明 分区名称数字部分DECLARE databaseName varchar(100);			# 声明 数据库名称		DECLARE tableName varchar(100);				# 声明 表名DECLARE i int;								# 声明 从当前日期往后新增分区天数#变量赋值#数据库名称(根据需求更改)set databaseName = '111_gc_test_1';#表名(根据需求更改)set tableName = 'tb_intercept_notice';#从当前日期往后新增分区天数(根据需求更改)set i = 30; # 赋值v_sysdate为当前时间(不必更改)set v_sysdate = sysdate();		 SELECT max(cast(replace(partition_name, 'p', '') AS date)) AS valINTO   v_maxdateFROM   INFORMATION_SCHEMA.PARTITIONSWHERE  TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName;# INTERVAL 时间计算的关键字WHILE v_maxdate < (v_sysdate + INTERVAL i DAY) DO# 下一分区日期为当前分区最大值加一SET v_pt = date_format(v_maxdate+ INTERVAL 1 DAY ,'%Y%m%d');# 调用增加分区存储过程call procedure_add_partition(databaseName,tableName,v_pt);# 最大值加一SET v_maxdate = v_maxdate + INTERVAL 1 DAY;END WHILE;END$$
delimiter;

(2)更改完成后,执行 tb_intercept_notice表-添加分区-事件.sql 文件;

功能:提前往当前日期后创建设定天数的分区;

(3)打开 tb_intercept_notice表-删除分区-事件.sql 文件,根据需求,更新脚本中的变量;(主要更改数据库名称和从当前日期往前保留分区的天数,默认30天

(4)更改完成后,执行 tb_intercept_notice表-删除分区-事件.sql 文件;

功能:删除超过设定保留天数的分区;

DELIMITER $$
drop event if exists tb_intercept_notice_del_partition_event 
$$
create event tb_intercept_notice_del_partition_event
on schedule-- AT TIMESTAMP表示该事件只执行一次
-- AT TIMESTAMP '2020-11-20 00:00:00'-- 测试设置为每10秒执行一次
-- every 2 second
-- every 1 minute
-- STARTS CURRENT_TIMESTAMP-- 现网可以设置为每天执行一次(每天的当前时间执行)every 1 daySTARTS CURRENT_TIMESTAMPdo BEGINDECLARE v_sysdate date;        				# 声明 当前时间DECLARE v_mindate date;   		 		    # 声明 目前分区值中的最小值DECLARE v_pt varchar(20);      				# 声明 分区名称数字部分DECLARE databaseName varchar(100);			# 声明 数据库名称		DECLARE tableName varchar(100);				# 声明 表名DECLARE i int;								# 声明 从当前日期往前保留分区天数#变量赋值#数据库名称(根据需求更改)set databaseName = '111_gc_test_1';#表名(根据需求更改)set tableName = 'tb_intercept_notice';#从当前日期往前保留分区天数(根据需求更改)set i = 30; # 赋值v_sysdate为当前时间(不必更改)set v_sysdate = sysdate();SELECT min(cast(replace(partition_name, 'p', '') AS date)) AS valINTO   v_mindateFROM   INFORMATION_SCHEMA.PARTITIONSWHERE  TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName;# INTERVAL 时间计算的关键字WHILE v_mindate < (v_sysdate - INTERVAL i DAY) DO# 将最小分区值赋给 v_pt 变量SET v_pt = date_format(v_mindate,'%Y%m%d');# 调用删除分区存储过程call procedure_del_partition(databaseName,tableName,v_pt);# 最小值加一操作SET v_mindate = v_mindate + INTERVAL 1 DAY;END WHILE;END$$
delimiter;

5、表分区检查

执行下面sql,查看分区情况,注意更改sql中的变量(数据库名称、表名)

select partition_name, partition_description as val 
from information_schema.partitions
where table_schema='数据库名称' and table_name='表名';

根据执行结果,分析分区是否是设定的范围,比如新增和删除分区都是使用3天,若今天是20241101,则执行结果分区最大值为:20241104,分区最小值为:20241029;

6、tb_intercept_notice 表替换

若以上步骤都成功,则执行 tb_intercept_notice表-重建-之后新旧表重命名.sql 文件;

功能:将 tb_intercept_notice 表替换为分区后的表;tb_intercept_notice 表被改名为:tb_intercept_notice_backups_20241101;

# 重命名tb_intercept_notice表
ALTER TABLE tb_intercept_notice RENAME TO tb_intercept_notice_backups_20241101;# 将新创建的表改名为tb_intercept_notice表
ALTER TABLE tb_intercept_notice_20241101_new RENAME TO tb_intercept_notice;

7、单次调用存储过程添加/删除分区

打开 添加分区-存储过程-调用.sql 文件,更改存储过程参数值后执行该文件;

打开 删除分区-存储过程-调用.sql 文件,更改存储过程参数值后执行该文件;

#添加分区-存储过程-调用 - 不带数据库参数
call procedure_add_partition('表名','分区名称的日期部分(格式:yyyyMMdd)');#删除分区-存储过程-调用 - 不带数据库参数
call procedure_del_partition('表名','分区名称的日期部分(格式:yyyyMMdd)');

8、其他命令

  • 查看存储过程:show procedure status like 'proc%';
  • 删除存储过程:DROP PROCEDURE IF EXISTS 存储过程名;
  • 查看当前数据库事件:SHOW EVENTS;
  • 查看所有数据库事件:SELECT * FROM information_schema.EVENTS;
  • 开启事件功能:SET GLOBAL event_scheduler = ON;
  • 关闭事件功能:SET GLOBAL event_scheduler = OFF;
  • 关闭指定事件:ALTER EVENT 事件名称 ON COMPLETION PRESERVE DISABLE;
  • 开启指定事件:ALTER EVENT 事件名称 ON COMPLETION PRESERVE ENABLE;
  • 删除指定事件:drop event if exists 事件名称;


http://www.ppmy.cn/news/1550834.html

相关文章

遗传算法与深度学习实战——进化优化的局限性

遗传算法与深度学习实战——进化优化的局限性 0. 前言1. 数据集加载2. 模型构建相关链接 0. 前言 深度学习 (Deep learning, DL) 模型的规模不断扩大&#xff0c;从早期只有数百个参数的模型到最新的拥有数十亿个参数的 transformer 模型。优化或训练这些网络需要大量的计算资…

【Linux】自定义简易shell

【Linux】自定义简易shell &#x1f955;个人主页&#xff1a;开敲&#x1f349; &#x1f525;所属专栏&#xff1a;Linux&#x1f34a; &#x1f33c;文章目录&#x1f33c; 1. 实现思路 2. 实现代码 2.1 输出命令行提示符 2.2 获取用户输入信息 2.3 解析用户输入命令 2.4 …

鸿蒙开发App 如何通过抓包查看 http 网络请求?

通过借助第三方工具 Charles https://www.charlesproxy.com/ https://www.zzzmode.com/mytools/charles/https://www.zzzmode.com/mytools/charles/ Charles 激活码计算器 相关博客日志&#xff1a;https://zhuanlan.zhihu.com/p/281126584 MAC上的使用方法&#xff1a; ch…

c++类模板成员函数的特化

是的&#xff0c;类成员函数可以是模板函数。在C中&#xff0c;类模板和非模板类都可以包含模板成员函数。这种设计允许类在某些成员函数中具有泛型行为&#xff0c;而不需要将整个类设计为模板。 本文将详细介绍类成员函数作为模板函数的概念、声明和定义方法&#xff0c;以及…

算法竞赛(Python)-链表

文章目录 一 链表简介1.1链表定义1.2 双向链表1.3 循环链表 二、链表的基本操作2.1 链表的结构定义2.2 建立一个线性链表2.3 求线性链表的长度2.4 查找元素2.5 插入元素2.5.1 链表头部插入元素2.5.2 链表尾部插入元素2.5.3 链表中间插入元素 2.6 改变元素2.7 删除元素2.7.1 链表…

Linux环境下配置neo4j图数据库

1.下载安装包 openjdk-11.0.1_linux-x64_bin.tar.gz neo4j-community-4.2.19-unix.tar.gz 2.之前配置好的配置文件 neo4j.conf 3.安装 3.1-jdk11的安装&#xff08;jdk1.8不够用&#xff09; 解压缩 tar -zxvf openjdk-11.0.1_linux-x64_bin.tar.gz修改系统环境变量 打开pro…

vue3项目搭建-4-正式启动项目,git管理

安装插件&#xff1a; npm install vue router npm install eslint 完成目录&#xff1a; 需要添置文件夹&#xff1a; apis -> api接口 composables -> 组合函数 directives -> 全局指令 styles -> 全局样式 utils -> 工具函数 git 管理&#xff1a; …

Francek Chen 的365天创作纪念日

文章目录 Francek Chen 的365天创作纪念日机缘收获日常成就憧憬 Francek Chen 的365天创作纪念日 Francek Chen 的个人主页 机缘 不知不觉的加入 CSDN 已有三年时间了&#xff0c;最初我第一次接触 CSDN 技术社区是在 2022 年 4 月的时候&#xff0c;通过学长给我们推荐了几个 …