MySQL

2024-07-14 02:18| 来源: 网络整理| 查看: 265

1．背景原因

当前MySQL不支持在添加和删除分区时，使用IF NOT EXISTS和IF EXISTS。所以在执行调度任务时，直接通过ADD PARTITION和DROP PARTITION不可避免会报错。本文通过创建存储过程来添加和删除分区，可以避免在分区存在时添加分区报错，或者分区不存在时删除分区报错的问题。

本文介绍的是关于LIST分区的添加和删除。

2．前提准备

创建List分区表

DROP TABLE IF EXISTS `list_part_table` ; CREATE TABLE IF NOT EXISTS `list_part_table` ( `id` bigint(32) NOT NULL COMMENT '主键', `request_time` datetime(0) NOT NULL COMMENT '请求时间', `response_time` datetime(0) NOT NULL COMMENT '响应时间', `time_used` int(11) NOT NULL COMMENT '耗时(ms)', `create_by` varchar(48) DEFAULT NULL COMMENT '创建人', `update_by` varchar(48) DEFAULT NULL COMMENT '修改人', `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间', `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间', PRIMARY KEY (`id`, `request_time`) USING BTREE ) PARTITION BY list(TO_DAYS(request_time)) ( PARTITION p0 VALUES IN (0) ) ;

查看表中的分区信息

select * from information_schema.partitions where table_name like 'list_part_table%' ;

3．添加和删除分区语句

（1）添加分区

alter table list_part_table add partition(partition p202001 values in (202001)); alter table list_part_table add partition(partition p20201201 values in (20201201));

查看表的分区信息

select * from information_schema.partitions where table_name like 'list_part_table%' ;

（2）删除分区

alter table list_part_table drop partition p202001,p20201201 ;

查看表的分区信息

select * from information_schema.partitions where table_name like 'list_part_table%' ;

说明：当上面的添加分区和删除分区语句执行多次时，就会报错。

4．通过存储过程添加LIST分区（1）添加分区的存储过程 DROP PROCEDURE IF EXISTS create_list_partition ; DELIMITER $$ CREATE PROCEDURE IF NOT EXISTS create_list_partition (par_value bigint, tb_schema varchar(128),tb_name varchar(128)) BEGIN DECLARE par_name varchar(32); DECLARE par_value_str varchar(32); DECLARE par_exist int(1); DECLARE _err int(1); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1; START TRANSACTION; SET par_value_str = CONCAT('', par_value); SET par_name = CONCAT('p', par_value); SELECT COUNT(1) INTO par_exist FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = tb_schema AND TABLE_NAME = tb_name AND PARTITION_NAME = par_name; IF (par_exist = 0) THEN SET @alter_sql = CONCAT('alter table ', tb_name, ' add PARTITION (PARTITION ', par_name, ' VALUES IN (', par_value_str, '))'); PREPARE stmt1 FROM @alter_sql; EXECUTE stmt1; END IF; COMMIT; END $$

（2）调用存储过程添加分区

添加分区

CALL create_list_partition(202201, 'test', 'list_part_table'); CALL create_list_partition(202202, 'test', 'list_part_table'); CALL create_list_partition(20230912, 'test', 'list_part_table'); CALL create_list_partition(20230913, 'test', 'list_part_table');

查看表的分区信息

select * from information_schema.partitions where table_name like 'list_part_table%' ;

5．通过存储过程删除LIST分区（1）删除分区的存储过程 DROP PROCEDURE IF EXISTS drop_list_partition ; DELIMITER $$ CREATE PROCEDURE IF NOT EXISTS drop_list_partition (part_value bigint, tb_schema varchar(128), 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 = tb_schema 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 = CONCAT('',part_value); 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 $$

（2）调用存储过程删除分区

删除分区

CALL drop_list_partition(202201, 'test', 'list_part_table'); CALL drop_list_partition(202202, 'test', 'list_part_table');

查看表的分区信息

select * from information_schema.partitions where table_name like 'list_part_table%' ;

本文来自博客园，作者：业余砖家，转载请注明原文链接：https://www.cnblogs.com/yeyuzhuanjia/p/17700338.html

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章