SELECT increment counter in MySQL?

To select increment counter in MySQL, first you need to declare and initialize a variable. The syntax is as follows −

set @anyVariableName=0; select yourColumnName, @anyVariableName:=@anyVariableName+1 as anyVariableName from yourTableName;

To understand the above syntax and set an increment counter, let us first create a table. The query to create a table is as follows.

mysql> create table incrementCounterDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (1.01 sec)

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

mysql> insert into incrementCounterDemo values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into incrementCounterDemo values('Carol'); Query OK, 1 row affected (0.20 sec) mysql> insert into incrementCounterDemo values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into incrementCounterDemo values('Mike'); Query OK, 1 row affected (0.21 sec) mysql> insert into incrementCounterDemo values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into incrementCounterDemo values('Sam'); Query OK, 1 row affected (0.16 sec)

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

mysql> select *from incrementCounterDemo;

The following is the output.

+-------+ | Name  | +-------+ | John  | | Carol | | David | | Mike  | | Bob   | | Sam   | +-------+ 6 rows in set (0.00 sec)

Set variable

mysql> set @counter=0; Query OK, 0 rows affected (0.00 sec)

Now select the increment counter.

mysql> select Name, -> @counter:=@counter+1 as IncrementingValuebyOne -> from incrementCounterDemo;

The following is the output.

+-------+------------------------+ | Name  | IncrementingValuebyOne | +-------+------------------------+ | John  | 1                      | | Carol | 2                      | | David | 3                      | | Mike  | 4                      | | Bob   | 5                      | | Sam   | 6                      | +-------+------------------------+ 6 rows in set (0.00 sec)