How do I assign auto-incrementing IDs that restart for each new person in MySQL?

If I have a table like this:

PersonId   
Joe         
Joe
Joe
Joe
Frank
Frank
Frank
Frank

And want to get it to look like this:

PersonId   id
Joe        1     
Joe        2
Joe        3
Joe        4
Frank      1
Frank      2
Frank      3
Frank      4

How do I do that? Something like alter table add id int auto_increment, add primary key (id) but with more arguments to make the auto-increment reset after each new name and with no primary key because there will be duplicate values.

Solution

Try this for doing it on the fly:

select personid, 
       (@rn := if(@p = personid, @rn + 1,
                  if(@p := personid, 1, 1)
                 )
       ) as id
from your_table cross join
     (select @rn := 0, @p := 0) vars
order by personId;

This differs from Juergen's answer in two specific ways. First, there is an explicit order by because you cannot rely on the ordering of a select with no order by. Second, both variables being used are assigned in a single statement. MySQL does not guarantee the order of evaluation of statements in a select, so the only safe way to do this is with a single statement.