Mysql 按天自动分区，合并老分区

最新推荐文章于 2024-09-23 06:23:04 发布

weixin_30553837

最新推荐文章于 2024-09-23 06:23:04 发布

阅读量286

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Coye/p/7255901.html

版权

适用于每天一个分区。。。不断加分区，导致分区不够用的情况

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

转载于:https://www.cnblogs.com/Coye/p/7255901.html