快捷導(dǎo)航

MySQL通過(guò)存儲(chǔ)過(guò)程來(lái)添加和刪除分區(qū)的過(guò)程(List分區(qū))

更新時(shí)間：2023年09月14日 09:01:30 作者：業(yè)余磚家

這篇文章主要介紹了MySQL-通過(guò)存儲(chǔ)過(guò)程來(lái)添加和刪除分區(qū)（List分區(qū)）,本文通過(guò)創(chuàng)建存儲(chǔ)過(guò)程來(lái)添加和刪除分區(qū)，可以避免在分區(qū)存在時(shí)添加分區(qū)報(bào)錯(cuò)，或者分區(qū)不存在時(shí)刪除分區(qū)報(bào)錯(cuò)的問(wèn)題，需要的朋友可以參考下

1．背景原因

當(dāng)前MySQL不支持在添加和刪除分區(qū)時(shí)，使用IF NOT EXISTS和IF EXISTS。所以在執(zhí)行調(diào)度任務(wù)時(shí)，直接通過(guò)ADD PARTITION和DROP PARTITION不可避免會(huì)報(bào)錯(cuò)。本文通過(guò)創(chuàng)建存儲(chǔ)過(guò)程來(lái)添加和刪除分區(qū)，可以避免在分區(qū)存在時(shí)添加分區(qū)報(bào)錯(cuò)，或者分區(qū)不存在時(shí)刪除分區(qū)報(bào)錯(cuò)的問(wèn)題。

本文介紹的是關(guān)于LIST分區(qū)的添加和刪除。

2．前提準(zhǔn)備

創(chuàng)建List分區(qū)表

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 '請(qǐng)求時(shí)間',
`response_time` datetime(0) NOT NULL COMMENT '響應(yīng)時(shí)間',
`time_used` int(11) NOT NULL COMMENT '耗時(shí)(ms)',
`create_by` varchar(48)  DEFAULT NULL COMMENT '創(chuàng)建人',
`update_by` varchar(48)  DEFAULT NULL COMMENT '修改人',
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新時(shí)間',
PRIMARY KEY (`id`, `request_time`) USING BTREE
) PARTITION BY list(TO_DAYS(request_time)) (
PARTITION p0 VALUES IN  (0)
) ;

查看表中的分區(qū)信息

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

3．添加和刪除分區(qū)語(yǔ)句

（1）添加分區(qū)

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

查看表的分區(qū)信息

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

（2）刪除分區(qū)

alter  table  list_part_table  drop partition  p202001,p20201201 ;

查看表的分區(qū)信息

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

說(shuō)明：當(dāng)上面的添加分區(qū)和刪除分區(qū)語(yǔ)句執(zhí)行多次時(shí)，就會(huì)報(bào)錯(cuò)。

4．通過(guò)存儲(chǔ)過(guò)程添加LIST分區(qū)

（1）添加分區(qū)的存儲(chǔ)過(guò)程

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）調(diào)用存儲(chǔ)過(guò)程添加分區(qū)

添加分區(qū)

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');

查看表的分區(qū)信息

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

5．通過(guò)存儲(chǔ)過(guò)程刪除LIST分區(qū)

（1）刪除分區(qū)的存儲(chǔ)過(guò)程

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）調(diào)用存儲(chǔ)過(guò)程刪除分區(qū)

刪除分區(qū)

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

查看表的分區(qū)信息

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

到此這篇關(guān)于MySQL-通過(guò)存儲(chǔ)過(guò)程來(lái)添加和刪除分區(qū)（List分區(qū)）的文章就介紹到這了,更多相關(guān)MySQL添加和刪除分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: