How to provide autonumbering to a field in mysql?

Question

I want to provide auto numbering to a field in MySQL Database?

This is like

I have fields name,number

name       number
--------------------
aaaa          1
bbbb          2
cccc          3
dddd          4
eeee          5

Now I want to remove 3rd field(i.e.,cccc) After removing if I selected all the rows from that table I want to get output like

name      number
-----------------------
aaaa        1
bbbb        2
dddd        3
eeee        4

Can anyone provide better solution for that?

Answer 1

First of: why do this? It seems your number sequence is in general a primary key and that means - it has no other meaning that number itself. Therefore, you don't need to worry about gaps in it - and let DBMS handle that for you.

If you still want to rearrange your numbers, you can do this with:

UPDATE t CROSS JOIN (SELECT @i:=0) AS init SET t.number=@i:=@i+1

-but, really, think about this - i.e. if you really need to do that.

Answer 2

You could do a ranking on-the-fly while selecting

select name, 
       @rank := @rank + 1 as number
from your_table, (select @rank := 0) r
order by name

Then you don't need a number column in your table at all.