Re: make number of partitions variable

[Phil Hildebrand]

If you wanted to create the partitions automatically from code, you'd probably have to alter the procedure to use a prepared statement, and keep track of what the max partition is (maybe through information schema?). You could do it in php code as well, and then on some schedule (through cron or some other scheduler) execute the stored procedure to add the partitions.

(there may be a more efficient way, but this should work)

Here's an example to add the partitions:

DELIMITER //

CREATE PROCEDURE add_parts (IN mytable varchar(100))
BEGIN
DECLARE counter INT DEFAULT 10;
DECLARE partno INT DEFAULT 1;
DECLARE part_str VARCHAR(512);
DECLARE al_str TEXT;

SELECT max(partition_description)
FROM information_schema.partitions
WHERE table_name = mytable
AND partition_name != 'p_max'
INTO partno;

SET @al_str = CONCAT ('ALTER TABLE ', mytable, ' REORGANIZE PARTITION p_max INTO ( ');

WHILE counter > 0 DO
SET part_str = CONCAT( 'partition "p_', FROM_DAYS(partno), ' " VALUES LESS THAN (',partno + 1,'),' );
SET @al_str = CONCAT ( @al_str, part_str);
SET counter = counter - 1;
SET partno = partno + 1;
END WHILE;

SET @al_str = CONCAT ( @al_str, ' PARTITION p_max VALUES LESS THAN MAXVALUE );' );

PREPARE exec_str FROM @al_str;

EXECUTE exec_str;

DROP PREPARE exec_str;

END;
//