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_us服务器托管网ed` 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%' ;
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: 当AI成为基础资源，360OS 发力在线教育的重心——专访360OS张焰
2020年疫情爆发，在疫情期间帮助行业降本增效，正成为一门大的学问和生意，其中AI在多行业应用中展现了前所未有的能量。360OS AI视觉在线上教育领域的突飞崛起，使得行业将眼光注入到这支AI新生力量中。为此，我们采访了360OS AI影像事业部研发总监张焰，…
