MYSQL定時建立表分區

標籤：

MYSQL定時建立表分區

一.預存程序-表分區
-----------------------------------------------------------------
需求：
每月建立一個分區
分區名名稱格式:p201201,p201202……
------------------------------------------

 1 CREATE DEFINER = `root`@`%` PROCEDURE `Auto_Create_Partition`(IN `databaseName` varchar(50),IN `tableName` varchar(50)) 2 L_END:BEGIN  3 # 初始設定變數 4 DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;  5 DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;  6 DECLARE i INT DEFAULT 1;  7 DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0;  8 DECLARE next_p_name varchar(255) DEFAULT 0; 9 # 查詢表分區    最近的一個月的分區（ORDER BY partition_description DESC LIMIT 1）    10 SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC 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 # 根據時間設定要建立的分區的名稱18 SELECT concat(‘p‘,DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),‘%Y%m‘)) INTO next_p_name; 19 20 # 判斷要建立的分區是否存在21 IF ISEXIST_PARTITION = next_p_name THEN22 SELECT concat("Partition(",next_p_name,") is exist") AS "*****INFO*****"; 23 LEAVE L_END; 24 END IF;25 26 # 最近一個分區的設定的最大值（右邊界）27 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; 28 29 IF MAX_PARTITION_DESCRIPTION <=> "" THEN 30 SELECT "Partition table is error" AS "*****ERROR*****"; 31 LEAVE L_END; 32 END IF;33 34 # 建立新的分區35 # 1.設定新的分區的右邊界值36 SET P_DESCRIPTION = TO_DAYS(DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 2 MONTH),‘%Y%m01‘)); 37 # 2.拼接新增分區的SQL語句38 SET @S=CONCAT(‘ALTER TABLE ‘,tableName,‘ ADD PARTITION (PARTITION ‘,next_p_name,‘ VALUES LESS THAN (‘,P_DESCRIPTION,‘))‘); 39 SELECT @S;40 # 3.使用預先處理執行SQL41 # ------------------------------------------------------------------------------------------42 # 文法：文法 43 #     PREPARE statement_name FROM sql_text /*定義*/ 44 #     EXECUTE statement_name [USING variable [,variable...]] /*執行預先處理語句*/ 45 #     DEALLOCATE PREPARE statement_name /*刪除定義*/ 46 # ------------------------------------------------------------------------------------------47 PREPARE stmt2 FROM @S;48 EXECUTE stmt2;49 DEALLOCATE PREPARE stmt2;50 51 END L_END

 

-----------------------------------------------------------------------

二.定時事件建立

 

MYSQL定時建立表分區