Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Stored Procedure for Table Partitioning
CREATE DEFINER=`root`@`localhost` PROCEDURE `Cmonk_partition_manager`(in partition_frequency varchar(100), in db_schema varchar(100),in input_table_name varchar(100), in partition_column varchar(100))
BEGIN
-- Author - Code Monk
-- Version - 1.0
-- Procedure for automated partitioning of table
-- Inputs :
-- 1- Partition_frequency : Options-(Daily,Monthly,Weekly)
-- 2- db_schema : Name of Database schema
-- 3- input_table_name : Table Name
-- 4- partition_column : Table column
-- Limitations:
-- 1- Only range partitioning is supported.
-- 2- Partition column type should be Datetime
-- Variables to set:
-- no_of_partitions: number of partitions after last partition.
-- Eg: If current date ='2015-11-07' ,frequency ="daily" and no_of_partitions=4
-- then it will also create partitions for next coming 4 days from current_date,
-- which means last date of partition will be "2015-11-11".
-- Note: For Automatic partitioning, Schedule it using MySQL Event.
declare partitions_count int;
declare last_partition_value datetime;
declare partition_query text;
declare no_of_partitions int;
declare first_day_of_current_month datetime;
declare partition_name varchar(256);
declare partition_start_date date;
declare partition_last_date date;
declare partition_names text;
declare partition_date date;
declare temp_current_partition_count int;
-- First day of current month. Eg: '2015-11-01'
set first_day_of_current_month=date(date_sub(now(),interval dayofmonth(now())-1 day));
set no_of_partitions=2;
set partition_names=' ';
-- Increase group concat maximum length.
set @@group_concat_max_len = 100000;
-- Get last partition detail of input table
drop temporary table if exists temp_partition_information;
create temporary table temp_partition_information
select table_name, PARTITION_NAME,PARTITION_DESCRIPTION
from information_schema.PARTITIONS
where TABLE_SCHEMA=db_schema and table_name=input_table_name
and PARTITION_DESCRIPTION is not null
order by PARTITION_DESCRIPTION desc limit 1;
set temp_current_partition_count=(select count(*) from temp_partition_information);
-- If currently there is no partitions exists for table
if temp_current_partition_count=0
then
-- Get minimum value of partition column
set @temp_query=concat('set @min_value_of_partition=(','select ',partition_column,' from ',db_schema,'.',input_table_name,' order by cast(',partition_column,' as datetime) asc limit 1',')');
select @temp_query;
prepare sql_statement from @temp_query;
execute sql_statement;
deallocate prepare sql_statement;
-- if there is no record in table
if @min_value_of_partition is null
then
-- set minimum value current system date
set @min_value_of_partition=current_date;
end if;
else
-- Get partition descripion of last partition created on table
set @min_value_of_partition= (select from_days(partition_description) from temp_partition_information);
end if;
select @min_value_of_partition;
-- Calaculate start and last date of partitions based on the frequency
case when partition_frequency='daily'
then
select @min_value_of_partition;
set partition_start_date=case when temp_current_partition_count>0
then
date_ADD(date(@min_value_of_partition), interval 1 day)
ELSE date_sub(date(@min_value_of_partition), interval 1 day) END;
set partition_last_date=date_add(date(current_date), interval no_of_partitions+1 day);
when partition_frequency ='weekly'
then
set partition_start_date=date_sub(date_add(date(@min_value_of_partition), interval 6-(WEEKDAY(date(@min_value_of_partition))) day),interval 7 day);
set partition_last_date=date_add(date_add(date(current_date), interval 6-(WEEKDAY(date(current_date))) day),interval no_of_partitions*7 day);
when partition_frequency='monthly'
then
set partition_start_date=case when temp_current_partition_count>0
then
date_Add(date_sub(date(@min_value_of_partition), interval dayofmonth(@min_value_of_partition)-1 day),interval 1 month)
else
date_sub(date(@min_value_of_partition), interval dayofmonth(@min_value_of_partition)-1 day)
end ;
set partition_last_date=date_add(date(first_day_of_current_month), interval no_of_partitions+1 month);
end case;
set partition_date=partition_start_date;
select partition_start_date,partition_last_date,partition_date;
-- Temporary table to hold the dynamically created partition details
drop temporary table if exists temp_partition_name;
create temporary table temp_partition_name
(
partition_list varchar(256)
);
-- Loop create partition query for each partition
loop_to_make_partition_query:REPEAT
set partition_name=concat('p',year(partition_date),
if( length(month(partition_date))=1,concat('0',month(partition_date)),month(partition_date)),
if( length(day(partition_date))=1,concat('0',day(partition_date)),day(partition_date) )
);
-- Prepare partition values and store it to the temp table
insert into temp_partition_name values( concat('PARTITION ', partition_name,' VALUES LESS THAN (','to_days(','''',partition_date,'''',')',')'));
-- Calaculate next partition date based on the frequency of partitioning
case when partition_frequency='daily'
then
set partition_date=date_Add(partition_date, interval 1 day);
when partition_frequency ='weekly'
then
set partition_date=date_Add(partition_date, interval 7 day);
when partition_frequency='monthly'
then
set partition_date=date_Add(partition_date, interval 1 month);
end case;
-- set partition_date=date_Add(partition_date, interval 1 month);
-- Prepare partition query till it reach to the last partition date
until partition_last_date<=partition_date
END REPEAT loop_to_make_partition_query;
-- select * from temp_partition_name;
-- After collecting all partition details in the temporary table. concat all partition query in comma seperated format
select group_concat(partition_list) into partition_names from temp_partition_name;
-- Preparing final partition query and store it to the variable "partition_query"
if temp_current_partition_count=0
then
set partition_query=concat('alter table ',db_schema,'.',input_table_name,' partition by range(to_days(',partition_column,'))',
'(',partition_names,');');
else
set partition_query=concat('alter table ',db_schema,'.',input_table_name,' add partition(',partition_names,');');
end if;
select partition_query ;
select partition_query into @partition_query;
SELECT * FROM temp_partition_name;
IF(partition_last_date>=partition_date)
THEN
-- Execute query to add new partitions to table
prepare stmt from @partition_query;
execute stmt;
deallocate prepare stmt;
END IF;
END
@ashkilniuk

This comment has been minimized.

Copy link

ashkilniuk commented Aug 9, 2017

Unit tests:

drop table if EXISTS bigduckgames.consolidated_report_prt;
create table bigduckgames.consolidated_report_prt like bigduckgames.consolidated_report;
CALL bigduckgames.spr_prt_maintenance('daily', 'bigduckgames', 'consolidated_report_prt', 'day');

drop table if EXISTS bigduckgames.consolidated_report_prt;
create table bigduckgames.consolidated_report_prt like bigduckgames.consolidated_report;
CALL bigduckgames.spr_prt_maintenance('weekly', 'bigduckgames', 'consolidated_report_prt', 'day');

drop table if EXISTS bigduckgames.consolidated_report_prt;
create table bigduckgames.consolidated_report_prt like bigduckgames.consolidated_report;
CALL bigduckgames.spr_prt_maintenance('monthly', 'bigduckgames', 'consolidated_report_prt', 'day');
@ashkilniuk

This comment has been minimized.

Copy link

ashkilniuk commented Aug 9, 2017

SP parameters:
prt_frequency varchar(100) - possible values(daily,weekly, monthly)
db_schema varchar(100) - DB schema
input_table_name varchar(100) - table that is going to be partitioned
in prt_column varchar(100) - table column which partitions will be based on

@royagustafson

This comment has been minimized.

Copy link

royagustafson commented Mar 31, 2020

Spellcheck - feel free to delete if you make these changes

  • descripion -> description
  • Calaculate -> Calculate
@CodMonk

This comment has been minimized.

Copy link
Owner Author

CodMonk commented Mar 31, 2020

Hey @royagustafson ,
Thanks !!
Can you make a pull request for changes require?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.