mysql表分区存储过程

本文为博主原创，未经允许不得转载：

　　由于数据库一张表数据量有几千万条，而且在不断增长，看见公司前辈写了一个创建表分区的存储过程，感觉

甚是牛逼，在此供自己保留学习。

/*PROCEDURE create_partition_by_month*/
DROP PROCEDURE IF EXISTS `create_partition_by_month`;
DELIMITER $$
CREATE PROCEDURE `create_partition_by_month`(IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE EXPIRE_ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE PARTITIONRANGE INT UNSIGNED;
    DECLARE EXPIRETIME TIMESTAMP;
    DECLARE EXPIREPARTITION VARCHAR(16);
    
    SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
    SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y%m' );
    SET ENDTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 2 MONTH);
    SET PARTITIONRANGE = extract(YEAR_MONTH FROM ENDTIME);
    SET EXPIRETIME = DATE(NOW() - INTERVAL 3 YEAR);
    SET EXPIREPARTITION = DATE_FORMAT( EXPIRETIME, 'p%Y%m' );
    
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions WHERE  table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', PARTITIONRANGE ,') ENGINE = InnoDB);' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    ELSE
        SELECT CONCAT("partition `", PARTITIONNAME, "` for table `", IN_TABLENAME, "` already exists") AS result;
    END IF;
    
    SELECT COUNT(*) INTO EXPIRE_ROWS_CNT FROM information_schema.partitions WHERE  table_name = IN_TABLENAME AND partition_name = EXPIREPARTITION;
    IF EXPIRE_ROWS_CNT = 0 THEN
          SELECT CONCAT("partition `", EXPIREPARTITION, "` for table `", IN_TABLENAME, "` not exists") AS result;
    ELSE
          SET @SQL = CONCAT( 'ALTER TABLE `', IN_TABLENAME, '`', ' DROP PARTITION ', PARTITIONNAME, ';' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
    
END$$
DELIMITER ;
/*EVENT e_alarm_partition*/
DROP EVENT IF EXISTS `e_alarm_partition`;
DELIMITER $$
CREATE EVENT IF NOT EXISTS `e_alarm_partition`
ON SCHEDULE EVERY 1 DAY   #执行周期，还有天、月等等
STARTS '2017-12-23 04:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    CALL create_partition_by_month('tbl_alarminfo');
    CALL create_partition_by_month('tbl_user_log');
END$$
DELIMITER ;

SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

tbl_alarminfo为告警信息表，tbl_user_log为日志操作记录表