Skip to content

Instantly share code, notes, and snippets.

@danielgindi
Last active November 5, 2023 10:02
Show Gist options
  • Save danielgindi/95937faefa3a82d30cd016f8e5dd5fde to your computer and use it in GitHub Desktop.
Save danielgindi/95937faefa3a82d30cd016f8e5dd5fde to your computer and use it in GitHub Desktop.
MySql automatic partitioning by RANGE of month/day, no external scripts
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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment