Creating a temporary copy of a table with partitions

I’ve got a little issue and wonder if anyone has come across it themselves or seen a solution.

In one of my reporting scripts, it creates a temporary copy of a table before inserting some rows in order to a bulk update on the source table. To create a copy I used the SQL

CREATE TEMPORARY TABLE myTable LIKE source.myTable;

Which works fine, apart from one table which is partitioned. MySQL can’t create temp tables with partitions.

I can’t do the alternative which would be to do…

CREATE TEMPORARY TABLE myTable SELECT * from source.myTable;

If you do that it a) only creates the column definitions to fit the data in the select (i.e. a VARCHAR column will only be defined according to the max length of the column in the select statement) and b) I don’t want to copy all 4 million rows simply to truncate them in the next statement!

Is the only other option to create it manually from the information schema? That really feels like using a sledgehammer to crack a walnut, surely there must be a simpler way to do it? Not too mention a lot of work.

CREATE TEMPORARY TABLE myTable
    SELECT * from source.myTable
        LIMIT 0;           -- Add this

Still creates the temp table, but puts no rows in it.

You can create a non temporary copy of the original table first.

CREATE TABLE intermediate_table LIKE my_table;

It won’t have any records so altering it should be quick:

ALTER TABLE intermediate_table REMOVE PARTITIONING;

Then create your temporary table:

CREATE TEMPORARY TABLE tmp_table LIKE intermediate_table;

Then just drop the intermediate:

DROP TABLE intermediate_table;

Now you can play around with your temporary table without worrying of leaving any permanent table behind if your script halts, which I assume is the reason why we use temporary tables instead of permanent ones.

Why don’t you do this in two steps instead?

CREATE TEMPORARY TABLE myTable LIKE db1.new_table;

Insert into db1.myTable (col1, col2, col3)
select col11, col22, col33
from db1.new_table
where id > 10 and id <20 ;

Then you don’t have to transfer 4 million rows, only the rows you need