Mysql 按天自动分区,合并老分区
适用于每天一个分区。。。不断加分区,导致分区不够用的情况
CREATE DEFINER=hehe@XXXXXX PROCEDURE p_auto_partition_day(IN databaseName VARCHAR(50),IN tableName VARCHAR(50),IN daynum VARCHAR(50))
BEGIN
DECLARE v_partion_date DATE;
DECLARE v_partion_date_old DATE;
DECLARE v_now DATE DEFAULT CURDATE();
DECLARE v_partion_name_old VARCHAR(32);
DECLARE v_partion_name VARCHAR(32);
DECLARE v_partion_cond VARCHAR(128);
DECLARE v_sql VARCHAR(1024);
DECLARE v_partion_number VARCHAR(32);
SELECT SUBSTR(MAX(partition_name), 2) INTO v_partion_date FROM information_schema.partitions WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName;
WHILE NOT v_partion_date > v_now DO
SET v_partion_name = DATE_FORMAT(DATE_ADD(v_partion_date,INTERVAL 1 DAY), '%Y%m%d');
SET v_partion_cond = CONCAT(DATE_FORMAT(DATE_ADD(v_partion_date,INTERVAL 2 DAY), '%Y-%m%-%d'), ' 00:00:00');
SET v_sql = CONCAT('alter table ',tableName,' add partition(partition p', v_partion_name, ' values less than (UNIX_TIMESTAMP(''', v_partion_cond, ''')))');
SET @v_sql = v_sql;
-- select @v_sql;
PREPARE stmt from @v_sql;
EXECUTE stmt;
SET v_partion_date = DATE_ADD(v_partion_date,INTERVAL 1 DAY);
END WHILE;
SELECT SUBSTR(MIN(partition_name), 2) INTO v_partion_date_old FROM information_schema.partitions WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName;
SELECT COUNT(1) INTO v_partion_number FROM information_schema.partitions WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName;
WHILE v_partion_number > daynum DO
SET v_partion_name = DATE_FORMAT(DATE_ADD(v_partion_date_old,INTERVAL 1 DAY), '%Y%m%d');
SET v_partion_name_old = DATE_FORMAT(v_partion_date_old, '%Y%m%d');
SET v_partion_cond = CONCAT(DATE_FORMAT(DATE_ADD(v_partion_date_old,INTERVAL 2 DAY), '%Y-%m%-%d'), ' 00:00:00');
SET v_sql = CONCAT('ALTER TABLE ',tableName,' REORGANIZE PARTITION p',v_partion_name_old,',p',v_partion_name,' into(partition p', v_partion_name, ' values less than (UNIX_TIMESTAMP(''', v_partion_cond, ''')))');
SET @v_sql = v_sql;
-- select @v_sql;
PREPARE stmt from @v_sql;
EXECUTE stmt;
SET v_partion_number = v_partion_number-1;
END WHILE;
END