Mysql分区创建与删除
实例代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | CREATE PROCEDURE `add_table_partition`() COMMENT '增加表分区'BEGIN /****************************************************************** * Creator: Donne * Create Date: 2019-05-29 * Description: 增加所有表的分区，分区类型为p_date_20190529 每月最后一天执行，生成下一个月所有分区 ******************************************************************/ DECLARE v_table_name varchar(50); DECLARE v_par_name varchar(20); DECLARE i int DEFAULT 0; DECLARE no_more int DEFAULT 0; ##将表名和分区名放入游标 DECLARE cursor_employee CURSOR FOR SELECT table_name,max(partition_name) FROM information_schema.partitions WHERE table_schema='test' and partition_name is not null and partition_name'p_date_min' GROUP BY table_name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more= 1; ##打开游标 OPEN cursor_employee; FETCH cursor_employee INTO v_table_name,v_par_name; ##循环 1:ture, 0:false,!0:ture,!1:false ##select 1 from where !0 WHILE !no_more DO my_loop: LOOP ##从当前开始 SET @j:= DATE_ADD(curdate(),INTERVAL i DAY); ##下个月最后一天 SET @end_date:= last_day(DATE_ADD(last_day(curdate()),INTERVAL 1 DAY)); IF @j > @end_date THEN LEAVE my_loop; END IF; #计数 SET i=i+1; #分区使用values less than,所以+1 SET @par_value:= UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL i DAY)); SET @par_name:= concat('p_date_',date_format(@j,'%Y%m%d')); #如果新建分区大于已有分区，则创建 IF @par_name>v_par_name THEN SET @add_par:= concat('ALTER TABLE ',v_table_name,' ADD PARTITION(PARTITION ',@par_name,' VALUES LESS THAN (',@par_value,'));'); PREPARE stmt from @add_par; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END LOOP; #重新计数 SET i= 0; #从游标中取出下一条数据 FETCH cursor_employee INTO v_table_name,v_par_name; END WHILE; CLOSE cursor_employee;END |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE PROCEDURE `drop table partition`( in start_date date , in end_date date) COMMENT '删除分区'BEGIN /****************************************************************** * Creator: Donne * Create Date: 2019-05-29 * Description: 删除表分区 ******************************************************************/ DECLARE v_table_name varchar(50); DECLARE v_par_name varchar(20); DECLARE i int DEFAULT 0; DECLARE no_more int DEFAULT 0; ##将表名和分区名放入游标 DECLARE cursor_employee CURSOR FOR SELECT table_name,partition_name FROM information_schema.partitions WHERE table_schema='test' and partition_name is not null and partition_name'p_date_min' AND partition_name>=concat('p_date_',date_format(end_date,'%Y%m%d')) AND partition_name |
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
