Assign a consecutive number to each row in a mySQL table

Say that you need to add a unique index to an existing catalog table, so you decide to add a new column with a consecutive number in it. You may make it an auto_increment column, but that would work only for new inserts. How would you populate the column for the exisiting rows?
If you’re using mySQL you may try this little recipe:
First let’s add the column that we’ll use later as primary key.

alter table mytable add column id integer first;

Now we declare a counter and use it to populate each row.

set @i = 0;
update mytable set id=(@i:=@i+1);

Finally, we transform the new column into the primary key.

alter table mytable modify column id integer auto_increment primary key;

There you go. A new auto-increment primary key that has been correctly populated for all existing rows.