MYSQL doesn’t have any system function like in SQL server where SQL Server uses row_number() to generate to row number for each row. However, this can be generated using the variable in the SELECT statement.
In this example below, the table has five rows:
CREATE TABLEÂ mysql_testing(db_namesVARCHAR(100));
INSERT INTOÂ mysql_testing
SELECTÂ ‘SQL Server’Â UNIONÂ ALL
SELECTÂ ‘MySQL’Â UNIONÂ ALL
SELECTÂ ‘Oracle’Â UNIONÂ ALL
SELECTÂ ‘MongoDB’Â UNIONÂ ALL
SELECTÂ ‘PostGreSQL’;
You can generate two row number using a variable in two methods:
1. Set the variable and use it in a SELECT statement.
SETÂ @row_number:=0;
SELECT @row_number:=@row_number+1 ASrow_number,db_names FROM mysql_testing
ORDER BYÂ db_names;
Or use the second method as demoed in this article:Â http://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/