MySQL5.5 自動(dòng)分區(qū)腳本

一、使用說明:

1.此腳本為分區(qū)后,定時(shí)自動(dòng)增加分區(qū).（被自動(dòng)分區(qū)的表,一定要先手動(dòng)分幾個(gè)區(qū)）

2.每隔15天,定時(shí)器會(huì)執(zhí)行一個(gè)存儲(chǔ)過程,對(duì)分區(qū)日期最后的那天再往后新增15個(gè)分區(qū).

3.Script里面Auto_partitions.sql 為存儲(chǔ)過程

4.Script里面Timer_event.sql 為定時(shí)事件腳本-
 

5.MySQL5.5默認(rèn)并沒有開啟EVENT機(jī)制,需要在my.cnf文件中添加[mysqld] event_scheduler= ON

7.增加打開文件上線.這個(gè)很重要.open_files_limit = 5000

二、分區(qū)腳本

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 ; 

# 其中傳入?yún)?shù)databaseName為數(shù)據(jù)庫名,參數(shù)tableName為表名.

三、添加事件處理  

DELIMITER ||  
CREATE EVENT auto_set_partitions  
          ON SCHEDULE  
          EVERY 15 DAY   
          DO  
      BEGIN  
          CALL create_Partition('database_name','table_name');  
         /* 如果需要向多個(gè)表分區(qū),可以寫多個(gè) CALL 調(diào)用    
          CALL create_Partition('database_name','table_name');  
        */  
 END ||  
DELIMITER ; 

這個(gè)事件每隔15天執(zhí)行一次.

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]