If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the "next" partition? How and when do you drop your older partitions?

Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

A closer look at why this is magic

This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider:

The query automatically deduces the LESS THAN value of the new partition. It looks for a constant interval, time-based or integer-based, and keeps this interval onward.

It understands that 5.1 does not allow you to partition by DATETIME, only via integers. It understands your integer may sometimes stand for TO_DAYS(), and sometimes for UNIX_TIMESTAMP() of your datetime. It auto-detects that.

The query recognizes a MAXVALUE partition, and if such partition exists, it provides with a REORGANIZE PARTITION statement rather than ADD PARTITION statement.

It suggests names for your partitions which give you a clue on what the partition contains. p_20160101000000 (can you splot the date/time?) tells me a lot more than some arbitrary p17.

It recognizes the common case of using a LESS THAN (0) as first partition, to take care of NULLs. It skips this partition: the query does not offer to drop it, not does it consider it while examining the interval.

So I never have to tell the query "I want a 3 month interval between partitions, and these are implemented using TO_DAYS()". I let it understand it on its own.

It is just a view

Which means you can SELECT sql_drop_first_partition, or you can SELECT sql_add_next_partition, or you can only SELECT ... WHERE table_schema='your_schema'. Or you can select them all.

You can eval() it

This view will be released with common_schema's next version. common_schema has a lot of these views which generate SQL statements. And it provides with the means to evaluate them: the eval() routine. So you don't need to export the text INTO OUTFILE and execute it via SOURCE. You can simply:

Get it

The sql_range_partitions view will be included in common_schema1.2, schedules to be released soon. Meanwhile, you can import this file: sql_range_partitions_addon.sql onto your existing common_schema1.1 install (what? You don't already have common_schema installed? You should know it's packed with lots of stuff like this one!)

If, by the time you read this, common_schema1.2 is already out, you don't need to add anything.

Our query auto-detected the meaning of those numbers like 1238533200, and has found the next partition to be created: p_20090701000000. That's '2009-07-01 00:00:00', and now we know what the partition stands for. A new MAXVALUE partition called p_maxvalue is created.

Just for the fun of it, let's issue the same a few more times and see what comes out: