Image for post
Image for post

Auto-numbering rows in MySQL

The following SQL snippet shows how to add an auto-numbering id field to each row of data returned by a query in MySQL/MariaDB.

select
@autoid := @autoid + 1 as autoid,
u.*
from mysql.user u
cross join (select @autoid := 0) aid
order by u.User, u.Host;

This takes the data in the users table in and adds a column to the start called autoid which starts at 1 and increments for each user in the table. These results are sorted by the User and Host fields.

A few tips:

  • You can start at any value e.g. instead of select @autoid := 0 if you want to start as say 1000 use select @autoid := 1000
  • You can order by any combination of field(s) or not at all.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store