MySQL自动分区脚本

古城微笑少年丶 2022-06-17 13:54 279阅读 0赞

**一、使用说明:**  
1.此脚本为分区后,定时自动增加分区.（被自动分区的表,一定要先手动分几个区）  
2.每隔15天,定时器会执行一个存储过程,对分区日期最后的那天再往后新增15个分区.  
3.Script里面Auto\_partitions.sql 为存储过程  
4.Script里面Timer\_event.sql 为定时事件脚本  
5.MySQL5.5默认并没有开启EVENT机制,需要在my.cnf文件中添加\[mysqld\] event\_scheduler= ON  
7.增加打开文件上线.这个很重要.open\_files\_limit = 5000

**二、分区脚本**

DELIMITER ||  
    DROP PROCEDURE IF EXISTS create_Partition ||  
    CREATE PROCEDURE create_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50))  
    L_END:BEGIN       
         DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;  
         DECLARE P_NAME VARCHAR(255) DEFAULT 0;         
         DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;     
         DECLARE i INT DEFAULT 1;  
         DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0;   
         SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;  
    
         IF ISEXIST_PARTITION <=> "" THEN  
           SELECT "Partition table not is exist" AS "*****ERROR*****";  
           LEAVE  L_END;  
         END IF;  
    
         SELECT partition_description INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;  
    
    
         IF MAX_PARTITION_DESCRIPTION <=> "" THEN  
           SELECT "Partition table is error" AS "*****ERROR*****";  
           LEAVE  L_END;  
         END IF;  
    
    
        SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', ''); 
        WHILE i <= 15 DO  
               SET P_DESCRIPTION = adddate(MAX_PARTITION_DESCRIPTION, INTERVAL i day);  
               SET P_NAME = REPLACE(P_DESCRIPTION, '-', ''); 
               SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION (PARTITION p',P_NAME,' VALUES LESS THAN (\'',P_DESCRIPTION,'\'))'); 
               SELECT @S;  
               PREPARE stmt2 FROM @S;  
               EXECUTE stmt2;  
               DEALLOCATE PREPARE stmt2;  
               SET i = i + 1 ;  
        END WHILE;            
    END L_END;||  
    DELIMITER ;

\# 其中传入参数databaseName为数据库名,参数tableName为表名.

**三、添加事件处理**

DELIMITER ||  
    CREATE EVENT auto_set_partitions ON SCHEDULE EVERY 15 DAY DO BEGIN CALL create_Partition('database_name','table_name');  
             /* 如果需要向多个表分区,可以写多个 CALL 调用 CALL create_Partition('database_name','table_name'); */  
     END || DELIMITER ;

这个事件每隔15天执行一次.