MySQL Cookbook, 2nd Edition

Numbering Query Output Lines

Problem

You’d like the lines of a query result nicely numbered.

Solution

Postprocess the output from mysql or use a user-defined variable.

Discussion

The --skip-column-names option for mysql can be useful in combination with cat -n when you want to number the output rows from a query under Unix:

%mysql --skip-column-names -e "SELECT thing, arms FROM limbs" cookbook | cat -n
     1  human   2
     2  insect  0
     3  squid   10
     4  octopus 8
     5  fish    0
     6  centipede       0
     7  table   0
     8  armchair        2
     9  phonograph      1
    10  tripod  0
    11  Peg Leg Pete    2
    12  NULL

Another option is to use a user variable. Expressions involving variables are evaluated for each row of a query result, a property that you can use to provide a column of row numbers in the output:

mysql>SET @n = 0;
mysql> SELECT @n := @n+1 AS rownum, thing, arms, legs FROM limbs;
+--------+--------------+------+------+
| rownum | thing        | arms | legs |
+--------+--------------+------+------+
|      1 | human        |    2 |    2 |
|      2 | insect       |    0 |    6 |
|      3 | squid        |   10 |    0 |
|      4 | octopus      |    8 |    0 |
|      5 | fish         |    0 |    0 |
|      6 | centipede    |    0 |  100 |
|      7 | table        |    0 |    4 |
|      8 | armchair     |    2 |    4 |
|      9 | phonograph   |    1 |    0 |
|     10 | tripod       |    0 |    3 |
|     11 | Peg Leg Pete |    2 |    1 |
|     12 | space alien  | NULL | NULL |
+--------+--------------+------+------+