ROW_NUMBER() equivalent in MySQL for inserting?

There is no equivalent of ROW_NUMBER() in MySQL for inserting but you can achieve this with the help of variable. The syntax is as follows −

SELECT
(@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`,
yourColumnName1,yourColumnName2,...N
FROM
yourTableName ,(SELECT @yourVariableName:=0) AS anyAliasName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table RowNumberDemo
   -> (
   -> UserId int,
   -> UserName varchar(20)
   -> );
Query OK, 0 rows affected (0.74 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into RowNumberDemo values(10,'John');
Query OK, 1 row affected (0.14 sec)
mysql> insert into RowNumberDemo values(50,'Carol');
Query OK, 1 row affected (0.20 sec)
mysql> insert into RowNumberDemo values(100,'Sam');
Query OK, 1 row affected (0.20 sec)
mysql> insert into RowNumberDemo values(150,'Mike');
Query OK, 1 row affected (0.54 sec)
mysql> insert into RowNumberDemo values(210,'Bob');
Query OK, 1 row affected (0.58 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from RowNumberDemo;

The following is the output −

+--------+----------+
| UserId | UserName |
+--------+----------+
|     10 | John     |
|     50 | Carol    |
|    100 | Sam      |
|    150 | Mike     |
|    210 | Bob      |
+--------+----------+
5 rows in set (0.00 sec)

Here is the query to get something like ROW_NUMBER equivalent −

mysql> SELECT
   -> (@ROW_NUMBER:=@ROW_NUMBER + 1) AS `ROW_NUMBER`,
UserId,UserName
   -> FROM
   -> RowNumberDemo,(SELECT @ROW_NUMBER:=0) AS t;

The following is the output −

+------------+--------+----------+
| ROW_NUMBER | UserId | UserName |
+------------+--------+----------+
|          1 |     10 | John     |
|          2 |     50 | Carol    |
|          3 |    100 | Sam      |
|          4 |    150 | Mike     |
|          5 |    210 | Bob      |
+------------+--------+----------+
5 rows in set (0.03 sec)