MySQL – How to Promote Table Partition to Table

alter-tableMySQLpartitioning

My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own?

Let's assume I have part0, part1, and part2. Is there any ALTER TABLE that I can give that converts part0 to a new table?

My thought process was, since the data is already existing in a separate file on disk, the table creation process may be faster than creating a new table using a SELECT query.

Best Answer

You do not need SELECT. This can be done with pure DDL.

For this example, let say the partitioned table is as follows:

  1. Database mydb
  2. Table mytable
  3. Partitions (part0,part1,part2)

This can be tricky but here is goes

USE mydb
CREATE TABLE mynewtable LIKE mytable;
ALTER TABLE mynewtable REMOVE PARTITIONING; 
ALTER TABLE mytable
    EXCHANGE PARTITION p0
    WITH TABLE mynewtable
    WITHOUT VALIDATION
;
ALTER TABLE mytable DROP PARTITION p0;

What this does is first make mynewtable without any partitions and the same schema in all other aspects. Then, it swaps the mynewtable table with part0.

If you are not sure of this, then run

USE mydb
CREATE TABLE mynewtable LIKE mytable;
ALTER TABLE mynewtable REMOVE PARTITIONING; 
ALTER TABLE mytable
    EXCHANGE PARTITION p0
    WITH TABLE mynewtable
    WITHOUT VALIDATION
;

Examine the rows in mynewtable and the new part0 and make sure

  • mynewtable has the data from the original part0
  • The new part0 should be empty

Once you are sure the transposition worked then run

ALTER TABLE mytable DROP PARTITION p0;

Please test this in a lab or sqlfiddle. GIVE IT A TRY !!!

Related Question