Search code examples
mysqlsqldatabase

Add new columns without using Alter

Is it possible to add new columns to an existing table without using alter statement?

Solution

  • Other people are answering unequivocally "no, it is not possible." This is the answer to your literal question. But I'm wondering why you ask the question.

    One of the biggest pain points of MySQL is that using ALTER TABLE locks the table while you're making a change like adding a column, and the more data in your table, the longer this lasts while it restructures the table. I'm guessing this is the issue you have, and you're trying to get an alternative that doesn't block access to the table while you're adding a new column.

    (In the future, it would help folks give you the best answers if you explain more about what you're trying to do.)

    The answer to this question is yes, there is a solution: pt-online-schema-change is a free tool that accomplishes this.

    You use it just like you would use ALTER TABLE, but you use it at the command-line instead of in an SQL query.

    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

    In this example, the database name is sakila and the table name is actor. The script does a lot of work behind the scenes:

    • Create a table like the original table, but empty of rows
    • ALTER TABLE to add the column or whatever other alteration you told it. You can do anything you would normally do with ALTER TABLE. In fact, it's doing ALTER TABLE for you, against the empty copy table.
    • Copy rows from the original table to the new table in the background.
    • Create triggers to capture any changes made to the original table while it's gradually copying the bulk of the data.
    • Swap the names of the new table (with the extra column) and the original table, once all data has been copied.
    • Drop the original table.

    This has a few caveats, like the original table must have a primary key, and must not have existing triggers.

    It tends to take longer than doing a traditional ALTER TABLE, but since it's not blocking access to the original table, it's still more convenient.

    Does this help?