DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Monthly Partitions

10.22.2011
| 4718 views |
  • submit to reddit
        // create to_days(first_day), to_days(second_day) syntax that can be directly used while creating partitions based on month.


Create a table in the test database with the name "date_helper"

mysql> CREATE TABLE test.date_helper (
    id INT NOT NULL,
    to_timestamp datetime
);

Run the shell script and feed the output to mysql

unix> sh mydate.sh | mysql -uUser -pPassWd test

_____

The altered table will look something like this:

CREATE TABLE `date_helper` (
  `id` int(11) NOT NULL,
  `to_timestamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (to_days(to_timestamp))
(PARTITION January2010 VALUES IN (734138,734139,734140, ...) ENGINE = InnoDB,
 PARTITION February2010 VALUES IN (734169,734170,734171,... ) ENGINE = InnoDB,
...
...
PARTITION November2011 VALUES IN (734807,734808,734809,...) ENGINE = InnoDB,
PARTITION December2011 VALUES IN (734837,734838,734839,...) ENGINE = InnoDB) */

_____


You can change the number of years as well as the starting month while running the script. If you want 3 years (365*3) partition starting from Jan 1990, use the following syntax:

sh mydate.sh 1990-01-01 1096



#!/bin/sh
# change the table name and partition column name below

tblname='date_helper'
colname='to_timestamp'

# if no start date is specified, then set the default date to 1 Jan 2010
startdate=${1:-'2010-01-01'}
# 24 partitions for 2 years viz 2010 and 2011 will be created by default
totaldate=${2:-'730'}
# change the days above from 730 for 2 years to 365 for 1 year
# run the script and save the output to a file > torun.sql
# open the file torun.sql and remove the last comma , before executing the query
# mysql test < torun.sql
# CREATE TABLE employees (id INT NOT NULL, store_date date)
# sample table

mysql -e"create table if not exists test.date_helper (id int, to_timestamp datetime);"

mysql -e"drop table if exists test.mycalendar;"
mysql -e"create table test.mycalendar (id int not null auto_increment, dateval date, primary key (id));"

echo "ALTER TABLE $tblname PARTITION BY LIST(to_days($colname)) ("

for (( i = 0 ; i < $totaldate ; i++ ))
do
mysql -e"insert into test.mycalendar (dateval) select '$startdate' + interval $i day;"
done

mysql -Bse"select concat(' PARTITION ', concat(monthname(dateval), extract(year from dateval)), ' VALUES IN ( ') as '', group_concat(concat('to_days(', '\'',dateval,'\')') order by dateval) as '', '), ' as '' from test.mycalendar group by extract(year_month from dateval);" |  sed '$s/,//31'

echo ');'

exit


# use the following syntax to use
# sh /root/calendar.sh '2002-01-01'

## Use the output to create monthly partitions with date 
CREATE TABLE employees (
    id INT NOT NULL,
    store_date date
)
PARTITION BY LIST(to_days(store_date)) (

PARTITION Jan02 VALUES IN (
to_days('2002-01-01'),to_days('2002-01-02'),...
),
PARTITION Feb02 VALUES IN (
to_days('2002-02-01'),to_days('2002-02-02'),...
)
);

# monthly partitions less than 40 years are recommended, in other words not more than 480 partitions