Search code examples
mysqlsqldatabasedatabase-schemaauto-increment

Copying Tables Without Data in MySQL Retaining Auto Increment

I have ran into trouble when copying my MySQL Tables to a new one, excluding the data, using the query:

CREATE TABLE foo SELECT * FROM bar WHERE 1=0.

The tables are copied, the structure and column names are correctly inserted. But there is a problem with the auto_increment fields and the primary key fields as they are not inserted as they were on the original table. (The fields are not PKs and AI anymore) I am using MySQL 5.5 and PMA 3.5.8.2

I hope someone can help me out.

Thank you SO.

Solution

  • You will probably have to run 2 queries.

    CREATE TABLE foo LIKE bar;
ALTER TABLE foo AUTO_INCREMENT = (SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'bar');

    You would have to replace DatabaseName with the name of your database. This is untested, but I think it will give you what you are looking for.

    So I tried testing the above query and the ALTER TABLE statement seems to fail due to the select. There might be a better way, but the way that worked for me was to set the auto increment value to a variable and then prepare the statement and execute it.

    For example you would go ahead and create your table first:

    CREATE TABLE foo LIKE bar;

    Then set your ALTER TABLE statement into a variable

    SET @ai = CONCAT("ALTER TABLE foo AUTO_INCREMENT =", (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename' AND TABLE_NAME = 'bar'));

    Finally, you would prepare and execute the statement.

    PREPARE query FROM @ai;
EXECUTE query;
DEALLOCATE PREPARE query;