MySQL: Forgot to set Auto Increment

Question

I set up a database, with an auto incrementing column that was meant to be an ID. The problem is that I forgot to check off auto increment when creating the table in phpMyAdmin...and didn't notice until now! So what I have is something like:

ID   |   column one   |   column two
------------------------------------
0    |   some value   |   some value
0    |   other value  |   something else
0    |   example val. |   something
0    |   my example   |   something

Basically, my "auto incrementing" column shows zeros across the board. How could I modify this table to show auto increment for all previous and all future rows?

Answer 1

If the table were empty you could just do:

ALTER TABLE mytable MODIFY COLUMN id bigint not null primary key auto_increment

but I'm not sure what will happen with rows in it. It might be best to create a new one with the correct definitions, copy the data over minus the id column, drop the old (wrong) table, and move the new one to the old name.

-- Either: 
CREATE TABLE newtable LIKE mytable;
ALTER TABLE newtable MODIFY COLUMN id bigint not null primary key auto_increment;
-- or:
CREATE TABLE newtable (
    id bigint not null primary key auto_increment,
    column1 type1,
    column2 type2,
    ...
);

INSERT INTO newtable 
    (column1, column2, ...) 
    SELECT column1, column2, column3, ... 
    FROM mytable;
-- Make SURE that insert worked BEFORE you drop the old table 
-- and lose data if it didn't.
DROP TABLE mytable;
ALTER TABLE newtable RENAME TO mytable;

I'm sure phpMyAdmin has the ability to do this more graphically.