MySQL 分区创建

莫茗岂妙

已于 2023-11-14 11:16:00 修改

阅读量99

点赞数

文章标签：数据库 java

于 2023-11-14 11:15:10 首次发布

本文链接：https://blog.csdn.net/jingyu333/article/details/134394894

版权

文章介绍了如何在MySQL中创建、修改和删除分区存储过程，包括分区表的重组、使用存储过程自动添加新分区以及管理定时任务的创建和控制。

摘要由CSDN通过智能技术生成

创建分区存储过程
# 修改maxvalue ，添加新的分区
ALTER TABLE test_user
    REORGANIZE PARTITION default_part
        INTO (PARTITION p3 VALUES LESS THAN (400)
        , PARTITION default_part VALUES LESS THAN MAXVALUE);


## 修改pN为default_part
ALTER TABLE test_user
    REORGANIZE PARTITION pN
        INTO (PARTITION p5 VALUES LESS THAN (600)
        , PARTITION default_part VALUES LESS THAN MAXVALUE);
#合并分区
alter table user
    reorganize partition p0,p1,p2,p3 into
        (partition p02 values less than (12));


# 创建分区存储过程
DROP PROCEDURE IF EXISTS add_partition;
DELIMITER //

CREATE PROCEDURE add_partition()
BEGIN
    DECLARE max_pd_num INT;
    DECLARE max_pd_name varchar(32);
    DECLARE netxt_pation INT;
    DECLARE netxt_pation_name varchar(32);
    DECLARE max_id INT;

    DECLARE cur_test1 CURSOR FOR
        SELECT max(PARTITION_NAME)        as 'max_pd_name',
               max(partition_description) as 'max_pd_num'
        FROM information_schema.PARTITIONS

        WHERE table_schema = 'mydatabase'
          AND table_name = 'test_user'
          AND PARTITION_NAME like "p%";

    DECLARE cur_max_id CURSOR FOR
        SELECT max(id) as 'max_id'
        FROM mydatabase.test_user;

    OPEN cur_test1;

    FETCH cur_test1 INTO max_pd_name,max_pd_num;

    SELECT max_pd_name, max_pd_num;

    CLOSE cur_test1;


    OPEN cur_max_id;
    FETCH cur_max_id INTO max_id;

    SELECT max_id;

    out_loop:
    LOOP
        -- 	 设置值
        set netxt_pation := max_pd_num + 100;
        SELECT netxt_pation, max_pd_name;
        set netxt_pation_name = CONCAT('p', REPLACE(max_pd_name, 'p', '') + 1);

        SELECT netxt_pation_name;

        SET @sql = CONCAT('ALTER TABLE test_user
			REORGANIZE PARTITION default_part
        INTO (PARTITION ', netxt_pation_name, ' VALUES LESS THAN (', netxt_pation, ')
        , PARTITION default_part VALUES LESS THAN MAXVALUE);');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        IF netxt_pation > max_id THEN
            LEAVE out_loop;
        END IF;


    END LOOP out_loop;
    CLOSE cur_max_id;


END
//

DELIMITER ;


call add_partition();
# 创建定时任务

# 查看定时任务
SELECT event_name, event_definition, interval_value, interval_field, status
FROM information_schema.EVENTS;

# 开启
alter event run_event on completion preserve enable;
# 关闭
alter event run_event on completion preserve disable;


drop event e_partition;

CREATE EVENT e_partition
    ON SCHEDULE EVERY 1 HOUR
    ON COMPLETION PRESERVE ENABLE
    DO CALL p_partition_month();