Insert auto increment primary key to existing table

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

suppose you don't have column for auto increment like id, no, then you can add using following query:

ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

If you've column, then alter to auto increment using following query:

 ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

For those like myself getting a Multiple primary key defined error try:

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;

On MySQL v5.5.31 this set the id column as the primary key for me and populated each row with an incrementing value.

yes, something like this would do it, might not be the best though, you might wanna make a backup

$get_query = mysql_query("SELECT `any_field` FROM `your_table`");

$auto_increment_id = 1;

while($row = mysql_fetch_assoc($get_query))
{
  $update_query = mysql_query("UPDATE `your_table` SET `auto_increment_id`=$auto_increment_id WHERE `any_field` = '".$row['any_field']."'");
  $auto_increment_id++;
}

notice that the the any_field you select must be the same when updating.