How to add IDs to each unique value in a table

I have a table in mysql which has a column filled with text values, lets say pets. So I would have a column pets with values like: "cat", "dog", "turtle", "cat", "cat", "turtle". In reality there are hundreds of thousands of different pet types in that column, but many repeats.

What I would like to do is create a second column which has an index for each entry in the first column. So for instance, in the above case, we would assign 'cat' the id of 1, 'dog' an id of 2, 'turtle' of 3 etc. and end up with a column with the values: 1, 2, 3, 1, 1, 3

Is there a way to do this? (preferably a quick way as the table is over 7 million rows).

Solution

Create a new table with the lookup values. You can do this easily:

create table PetTypes as
    select (@rn := @rn + 1) as pettypeid, pettype
    from (select distinct pettype from pets) p cross join
         (select @rn := 0) params;

Then you can readily add, update, or just use the column in the select:

alter table pets add petttypeid int;

update pets p join
       pettypes pt
       on p.pettype = pt.pettype
    set p.pettypeid = pt.pettypeid;

I would then recommend that you remove the pettype column from the original table.

Note: you can do the above without making a new table. But I think creating a new lookup table for this column is the right way to go.