Adding a Primary key to a MySQL table, and auto-populating it

开发者_开发问答I have a bunch of huge tables that don't have primary keys. (Don't ask me why) I will append an 'id' field to each table. It will be an integer type. Later, I will promote it to a non-null, unique-value index, and a primary key.

My question: Is there a way in MySQL (5 ish) We have about a hundred tables, and the largest among them have over 1 million records. After creating the new 'id' column, is there a way to have MySQL backfill (ie, add a value to the existing records) the 'id' field? I would rather be able to do this all in MySQL. Otherwise I will have to write a PHP script to populate the existing records.

Thanx, Don!

---

If you do a

ALTER TABLE table1 ADD COLUMN id INTEGER NOT NULL auto_increment PRIMARY KEY

It will auto populate your table with a auto_incrementing primary key.

Might take a while on a large table.