// 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
<code>
#!/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
</code>
