MYSQL定时创建表分区

MYSQL定时创建表分区

一.存储过程-表分区
-----------------------------------------------------------------
需求：
每月创建一个分区
分区名名称格式:p201201,p201202……
------------------------------------------

CREATE DEFINER = `root`@`%` PROCEDURE `Auto_Create_Partition`(IN `databaseName` varchar(50),IN `tableName` varchar(50))
L_END:BEGIN 
# 初始化变量
DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0; 
DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0; 
DECLARE i INT DEFAULT 1; 
DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0; 
DECLARE next_p_name varchar(255) DEFAULT 0;
# 查询表分区    最近的一个月的分区（ORDER BY partition_description DESC LIMIT 1）    
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 ; 

IF ISEXIST_PARTITION <=> "" THEN 
SELECT "Partition table not is exist" AS "*****ERROR*****"; 
LEAVE L_END; 
END IF;

# 根据时间设置要创建的分区的名称
SELECT concat('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y%m')) INTO next_p_name; 

# 判断要创建的分区是否存在
IF ISEXIST_PARTITION = next_p_name THEN
SELECT concat("Partition(",next_p_name,") is exist") AS "*****INFO*****"; 
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;

# 创建新的分区
# 1.设置新的分区的右边界值
SET P_DESCRIPTION = TO_DAYS(DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 2 MONTH),'%Y%m01')); 
# 2.拼接新增分区的SQL语句
SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION (PARTITION ',next_p_name,' VALUES LESS THAN (',P_DESCRIPTION,'))'); 
SELECT @S;
# 3.使用预处理执行SQL
# ------------------------------------------------------------------------------------------
# 语法：语法 
#     PREPARE statement_name FROM sql_text /*定义*/ 
#     EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/ 
#     DEALLOCATE PREPARE statement_name /*删除定义*/ 
# ------------------------------------------------------------------------------------------
PREPARE stmt2 FROM @S;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

END L_END

 

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

二.定时事件创建