MySql automatic partitioning by RANGE of month/day, no external scripts

MySql_Partitioning.sql

DELIMITER $$

DROP TABLE IF EXISTS _partitioned_tables; $$

CREATE TABLE IF NOT EXISTS `_partitioned_tables` (
  `owner_name` VARCHAR(100) NOT NULL,
  `table_name` VARCHAR(100) NOT NULL,
  `column_name` VARCHAR(100) NOT NULL,
  `mode` ENUM('day', 'month'),
  `future_partitions` INT NOT NULL,
  `max_partitions` INT NOT NULL,
  `has_future` TINYINT NOT NULL,
  PRIMARY KEY (`owner_name`,`table_name`)
); $$

DROP PROCEDURE IF EXISTS `PREPARE_PARTITIONS` $$

CREATE PROCEDURE `PREPARE_PARTITIONS`(IN in_owner_name TEXT, IN in_table_name TEXT)
BEGIN
    DECLARE done               BOOL DEFAULT FALSE;
    DECLARE p_has_partitions    INT DEFAULT 0;
    DECLARE p_date_from         INT DEFAULT 0;
    DECLARE p_date_to           INT DEFAULT 0;
    DECLARE p_date_max          INT DEFAULT 0;
    DECLARE p_date_cur          INT DEFAULT 0;
    DECLARE p_part_first        TINYINT;
    DECLARE p_part_name         VARCHAR(20);    
    DECLARE p_future_part_name  VARCHAR(64) DEFAULT NULL;  
    DECLARE p_partition_count   INT; 
    DECLARE p_remove_partitions INT;
    DECLARE p_remove_partition_list TEXT;
    DECLARE p_range_date        VARCHAR(10);    
    DECLARE v_owner_name        VARCHAR(100);
    DECLARE v_table_name        VARCHAR(100);
    DECLARE v_column_name       VARCHAR(100);
    DECLARE v_mode              ENUM('day', 'month');
    DECLARE v_future_parts      INT;
    DECLARE v_max_partitions    INT;
    DECLARE v_has_future        TINYINT;

    -- Select list of tables to partition
    DECLARE cur CURSOR FOR SELECT `owner_name`,`table_name`,`column_name`,`mode`,`future_partitions`,`max_partitions`,`has_future` FROM _partitioned_tables
        WHERE (_partitioned_tables.owner_name <=> in_owner_name AND _partitioned_tables.table_name <=> in_table_name)
            OR (in_owner_name IS NULL AND in_table_name IS NULL);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

    -- For each table in the resultset
    OPEN cur;
    tloop: LOOP
        IF done THEN
            CLOSE cur;
            LEAVE tloop;
        END IF;

        -- Fetch table configuration
        FETCH cur INTO v_owner_name,v_table_name,v_column_name,v_mode,v_future_parts,v_max_partitions,v_has_future;

        -- Is the table already partitioned?
        SELECT 1 INTO p_has_partitions
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME=v_table_name
           AND TABLE_SCHEMA=v_owner_name
           AND PARTITION_NAME IS NOT NULL
           LIMIT 1;

        -- Does the table have a future (MAXVALUE) partition? If so, fetch its name
        SELECT PARTITION_NAME INTO p_future_part_name
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME=v_table_name
           AND TABLE_SCHEMA=v_owner_name
           AND PARTITION_DESCRIPTION = "MAXVALUE"
           LIMIT 1;
     
        IF v_mode = 'month' THEN
            SET p_date_to=TIMESTAMPDIFF(MONTH, '0000-01-01', DATE(NOW() + INTERVAL (v_future_parts) MONTH)) + 1;
            
            IF p_has_partitions = 0 THEN
                SET p_date_cur=TIMESTAMPDIFF(MONTH, '0000-01-01', DATE(NOW() - INTERVAL (v_max_partitions - v_future_parts) MONTH)) + 1;
            ELSE
                SELECT TIMESTAMPDIFF(MONTH, '0000-01-01', TRIM(BOTH "'" FROM MAX(PARTITION_DESCRIPTION))) + 1 INTO p_date_cur
                       FROM INFORMATION_SCHEMA.PARTITIONS
                       WHERE TABLE_NAME=v_table_name
                       AND TABLE_SCHEMA=v_owner_name
                       AND PARTITION_DESCRIPTION!="MAXVALUE";
            END IF;
        ELSE
            SET p_date_to=TO_DAYS(DATE(NOW() + INTERVAL (v_future_parts) DAY));
            
            IF p_has_partitions = 0 THEN
                SET p_date_cur=TO_DAYS(DATE(NOW() - INTERVAL (v_max_partitions - v_future_parts) DAY));
            ELSE
                SELECT TO_DAYS(TRIM(BOTH "'" FROM MAX(PARTITION_DESCRIPTION))) INTO p_date_cur
                       FROM INFORMATION_SCHEMA.PARTITIONS
                       WHERE TABLE_NAME=v_table_name
                       AND TABLE_SCHEMA=v_owner_name
                       AND PARTITION_DESCRIPTION!="MAXVALUE";
            END IF;
        END IF;
        
        IF p_date_cur < p_date_to THEN
            SET p_part_first = 1;
            
            IF p_has_partitions = 0 THEN
                -- Setup partitioning for the table from scratch
                SET @p_query = CONCAT('ALTER TABLE `', v_owner_name, '`.`', v_table_name, '` PARTITION BY RANGE COLUMNS(`', v_column_name, '`) (');
            ELSE
                -- Add missing partitions (or reorganize `future` into missing partitions+future
                IF p_future_part_name IS NULL THEN
                    SET @p_query = CONCAT('ALTER TABLE ', v_owner_name, '.', v_table_name, ' ADD PARTITION (');
                ELSE
                    SET @p_query = CONCAT('ALTER TABLE ', v_owner_name, '.', v_table_name, ' REORGANIZE PARTITION ', p_future_part_name, ' INTO (');
                END IF;
            END IF;
            
            WHILE p_date_cur < p_date_to DO
                SET p_date_cur = p_date_cur + 1;
               
                IF v_mode = 'month' THEN
                    SET p_range_date = DATE_ADD('0000-01-01', INTERVAL (p_date_cur - 1) MONTH);
                ELSE
                    SET p_range_date = FROM_DAYS(p_date_cur);
                END IF;
                
                IF p_part_first = 1 THEN
                    SET p_part_first = 0;
                ELSE
                    SET @p_query = CONCAT(@p_query, ', ');
                END IF;
                
                SET p_part_name = CONCAT('p', DATE_FORMAT(p_range_date, '%Y%m%d'));
                SET @p_query = CONCAT(@p_query, 'PARTITION `', p_part_name, '` VALUES LESS THAN ("', DATE_FORMAT(p_range_date,'%Y-%m-%d'), '")');
            END WHILE;
            
            IF p_has_partitions = 0 THEN
                IF v_has_future = 1 THEN
                    IF p_part_first = 1 THEN
                        SET p_part_first = 0;
                    ELSE
                        SET @p_query = CONCAT(@p_query, ', ');
                    END IF;
                    
                    SET @p_query = CONCAT(@p_query, 'PARTITION `future` VALUES LESS THAN MAXVALUE');
                END IF;
            ELSE
                IF p_future_part_name IS NOT NULL THEN
                    IF p_part_first = 1 THEN
                        SET p_part_first = 0;
                    ELSE
                        SET @p_query = CONCAT(@p_query, ', ');
                    END IF;
                    
                    SET @p_query = CONCAT(@p_query, 'PARTITION `', p_future_part_name, '` VALUES LESS THAN MAXVALUE');
                END IF;
            END IF;
            
            SET @p_query = CONCAT(@p_query, ')');
            PREPARE stmt FROM @p_query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
        
        -- Add missing `future` partition or remove existing one if it's not required anymore
        IF p_has_partitions = 1 THEN
            IF p_future_part_name IS NOT NULL AND v_has_future = 0 THEN
                SET @p_query = CONCAT('ALTER TABLE ', v_owner_name, '.', v_table_name, ' DROP PARTITION `', p_future_part_name, '`');
                PREPARE stmt FROM @p_query;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            ELSEIF p_future_part_name IS NULL AND v_has_future = 1 THEN
                SET @p_query = CONCAT('ALTER TABLE ', v_owner_name, '.', v_table_name, ' ADD PARTITION (PARTITION `future` VALUES LESS THAN MAXVALUE)');
                PREPARE stmt FROM @p_query;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    
        IF p_has_partitions = 1 THEN
            -- Remove excess partitions
            
            SELECT COUNT(DISTINCT(PARTITION_DESCRIPTION)) INTO p_partition_count
                   FROM INFORMATION_SCHEMA.PARTITIONS
                   WHERE TABLE_NAME=v_table_name
                   AND TABLE_SCHEMA=v_owner_name
                   AND PARTITION_DESCRIPTION != 'MAXVALUE';

            IF p_partition_count > v_max_partitions THEN
                SET p_remove_partitions = p_partition_count - v_max_partitions;
              
                IF p_remove_partitions > 0 THEN
                    SELECT GROUP_CONCAT(DISTINCT(CONCAT('`', PARTITION_NAME, '`')))
                      INTO p_remove_partition_list 
                      FROM (
                        SELECT PARTITION_NAME 
                            FROM INFORMATION_SCHEMA.PARTITIONS
                            WHERE TABLE_NAME = v_table_name
                            AND TABLE_SCHEMA = v_owner_name
                            AND PARTITION_DESCRIPTION != 'MAXVALUE'
                            ORDER BY PARTITION_DESCRIPTION
                            LIMIT p_remove_partitions
                        ) AS t_parts;

                    SET @p_query = CONCAT('ALTER TABLE ', v_table_name, ' DROP PARTITION ', p_remove_partition_list);
                  
                    PREPARE stmt FROM @p_query;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END IF;
            
        END IF;
    END LOOP;
END $$

DROP EVENT IF EXISTS evt_maintain_partitions; $$

CREATE EVENT evt_maintain_partitions ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  CALL PREPARE_PARTITIONS(NULL, NULL);
END $$

DELIMITER ;

-- Now just add your requirements into _partitioned_tables.
-- You can manually call `PREPARE_PARTITIONS(NULL, NULL)` (or pass specific owner+table names) to do the initial partitioning immediately.