Numbering Query Output Lines

1.27.1 Problem

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

1.27.2 Solution

Postprocess the output from mysql, or use a SQL variable.

1.27.3 Discussion

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

% mysql -N -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 SQL 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 |
+--------+--------------+------+------+