MySQL5.5 自动分区脚本

转 http://blog.csdn.net/m582445672/article/details/7670743

一、使用说明:

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

二、分区脚本

[sql] view plaincopyprint?

1. DELIMITER ||  
2. DROP PROCEDURE IF EXISTS create_Partition ||  
3. CREATE PROCEDURE create_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50))  
4. L_END:BEGIN       
5.      DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;  
6.      DECLARE P_NAME VARCHAR(255) DEFAULT 0;         
7.      DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;     
8.      DECLARE i INT DEFAULT 1;  
9.      DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0;   
10.      SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;  
11.         
12.      IF ISEXIST_PARTITION <=> "" THEN  
13.        SELECT "Partition table not is exist" AS "*****ERROR*****";  
14.        LEAVE  L_END;  
15.      END IF;  
16.    
17.      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;  
18.          
19.          
20.      IF MAX_PARTITION_DESCRIPTION <=> "" THEN  
21.        SELECT "Partition table is error" AS "*****ERROR*****";  
22.        LEAVE  L_END;  
23.      END IF;  
24.   
25.         
26.     SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');  
27.     WHILE i <= 15 DO  
28.            SET P_DESCRIPTION = adddate(MAX_PARTITION_DESCRIPTION, INTERVAL i day);  
29.            SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');  
30.            SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN (\'',P_DESCRIPTION,'\'))');  
31.            SELECT @S;  
32.            PREPARE stmt2 FROM @S;  
33.            EXECUTE stmt2;  
34.            DEALLOCATE PREPARE stmt2;  
35.            SET i = i + 1 ;  
36.     END WHILE;            
37. END L_END;||  
38. DELIMITER ;  

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为表名.

三、添加事件处理  

[sql] view plaincopyprint?

1. DELIMITER ||  
2. CREATE EVENT auto_set_partitions  
3.           ON SCHEDULE  
4.           EVERY 15 DAY   
5.           DO  
6.       BEGIN  
7.           CALL create_Partition('database_name','table_name');  
8.          /* 如果需要向多个表分区,可以写多个 CALL 调用    
9.           CALL create_Partition('database_name','table_name');  
10.         */  
11.  END ||  
12. DELIMITER ;  

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天执行一次.