I have a mysql database table that I want to partition by date, particularly by month & year. However, when new data is added for a new month, I don’t want to need to manually update the database.
When I initially create my database, I have data in Nov 09, Dec 09, Jan 10, etc. Now when February starts, I’d like a Feb 10 partition automatically created. Is this possible?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
There are a few solutions out there, if you want a total solution, check this post out on kickingtyres. It’s a basic combination of a stored procedure handling the partition analysis and creation (with some logging!).
All you need to do is adjust it to your partition type (the example uses a bigint partition) and schedule the procedure to run with the MySQL Event Scheduler.
Method 2
yes, you might like the enhanced partitioning functionality in 5.5:
http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html
Method 3
I will do what the other done (post a link) Quick Introdunction to MySQL Partitioning
(look at page 50 for what you need – partition by date)
Edit: I reread your post more carefully – if you want to change partitions by month why don’t you create a Mysql event that does that
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0