MySQL 分区创建

分区创建"/>

MySQL 分区创建

创建分区存储过程
# 修改maxvalue ，添加新的分区
ALTER TABLE test_userREORGANIZE PARTITION default_partINTO (PARTITION p3 VALUES LESS THAN (400), PARTITION default_part VALUES LESS THAN MAXVALUE);## 修改pN为default_part
ALTER TABLE test_userREORGANIZE PARTITION pNINTO (PARTITION p5 VALUES LESS THAN (600), PARTITION default_part VALUES LESS THAN MAXVALUE);
#合并分区
alter table userreorganize partition p0,p1,p2,p3 into(partition p02 values less than (12));# 创建分区存储过程
DROP PROCEDURE IF EXISTS add_partition;
DELIMITER //CREATE PROCEDURE add_partition()
BEGINDECLARE 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 FORSELECT max(PARTITION_NAME)        as 'max_pd_name',max(partition_description) as 'max_pd_num'FROM information_schema.PARTITIONSWHERE table_schema = 'mydatabase'AND table_name = 'test_user'AND PARTITION_NAME like "p%";DECLARE cur_max_id CURSOR FORSELECT 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_userREORGANIZE PARTITION default_partINTO (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 THENLEAVE 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_partitionON SCHEDULE EVERY 1 HOURON COMPLETION PRESERVE ENABLEDO CALL p_partition_month();