Auto-number Rows in MySQL

The following SQL snippet is a really simple example of how to add an “id” field that is auto-numbered based on the number of rows returned by a query in MySQL.

    @id:=@id+1 as id,
    (select @id := 1000) id,
    mysql.user u

This takes the data in the users table in MySQL and adds a column to the start called “id” which starts at 1000 and increments for each row (1001, 1002, 1003 … n).

Handy for creating things like id fields in inserts etc.