I found a fast way to add a new column to a table.

MySQL

1. Create the new table like the old table (foo), but with the new column you want in it. Make the new table with a different name (foo_new):

2. Add the records from the old table into the new table:

    INSERT INTO foo_new (col1, col2, col3)
         SELECT col1, col2, col3 FROM foo
   

3. If you have a lot of records, you can disable index updates during insert:

    ALTER TABLE foo_new DISABLE KEYS;
    (do insert)
    ALTER TABLE foo_new ENABLE KEYS;
   

4. Do an atomic table rename:

    RENAME TABLE foo TO foo_old, foo_new TO foo;
   

SQLite

SQLite has an alter table command that adds a column to the end of the table:

ALTER TABLE foo ADD COLUMN new_field INTEGER;

To add a column elsewhere in the table requires a rename, create, and insert into. If you’re trying to add a column in a specific location, you may not understand SQL and set theory well enough.

Last modified on 2013-05-23