一、使用說明:
1.此腳本為分區(qū)后,定時自動增加分區(qū).（被自動分區(qū)的表,一定要先手動分幾個區(qū)）
2.每隔15天,定時器會執(zhí)行一個存儲過程,對分區(qū)日期最后的那天再往后新增15個分區(qū).
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
二、分區(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');
- /* 如果需要向多個表分區(qū),可以寫多個 CALL 調(diào)用
- CALL create_Partition('database_name','table_name');
- */
- END ||
- DELIMITER ;
這個事件每隔15天執(zhí)行一次.
本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權內(nèi)容，請
點擊舉報。
