1.26 Suppressing Column Headings in Query Output

1.26.1 Problem

You don't want to include column headings in query output.

1.26.2 Solution

Turn column headings off with the appropriate command-line option. Normally this is -N or --skip-column- names , but you can use -ss instead.

1.26.3 Discussion

Tab-delimited format is convenient for generating datafiles that you can import into other programs. However, the first row of output for each query lists the column headings by default, which may not always be what you want. Suppose you have a program named summarize the produces various descriptive statistics for a column of numbers . If you're producing output from mysql to be used with this program, you wouldn't want the header row because it would throw off the results. That is, if you ran a command like this, the output would be inaccurate because summarize would count the column heading:

%

mysql -e "SELECT arms FROM limbs" cookbook  summarize

To create output that contains only data values, suppress the column header row with the -N (or --skip-column-names ) option:

%

mysql -N -e "SELECT arms FROM limbs" cookbook  summarize

-N and --skip-column-names were introduced in MySQL 3.22.20. For older versions, you can achieve the same effect by specifying the "silent" option ( -s or --silent ) twice:

%

mysql -ss -e "SELECT arms FROM limbs" cookbook  summarize

Under Unix, another alternative is to use tail to skip the first line:

%

mysql -e "SELECT arms FROM limbs" cookbook  tail +2  summarize

1.27 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 
+--------+--------------+------+------+

1.28 Making Long Output Lines More Readable

1.28.1 Problem

The output lines from a query are too long. They wrap around and make a mess of your screen.

1.28.2 Solution

Use vertical output format.

1.28.3 Discussion

Some queries generate output lines that are so long they take up more than one line on your terminal, which can make query results difficult to read. Here is an example that shows what excessively long query output lines might look like on your screen: ^[9]

^[9] Prior to MySQL 3.23.32, omit the FULL keyword from the SHOW COLUMNS statement.

mysql>

SHOW FULL COLUMNS FROM limbs;

+-------+-------------+------+-----+---------+-------+-------------------------
--------+
 Field  Type         Null  Key  Default  Extra  Privileges
        
+-------+-------------+------+-----+---------+-------+-------------------------
--------+
 thing  varchar(20)  YES        NULL            select,insert,update,ref
erences 
 legs   int(11)      YES        NULL            select,insert,update,ref
erences 
 arms   int(11)      YES        NULL            select,insert,update,ref
erences 
+-------+-------------+------+-----+---------+-------+-------------------------
--------+

An alternative is to generate "vertical" output with each column value on a separate line. This is done by terminating a query with \G rather than with a ; character or with \g . Here's what the result from the preceding query looks like when displayed using vertical format:

mysql>

SHOW FULL COLUMNS FROM limbs\G

*************************** 1. row ***************************
     Field: thing
      Type: varchar(20)
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
*************************** 2. row ***************************
     Field: legs
      Type: int(11)
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
*************************** 3. row ***************************
     Field: arms
      Type: int(11)
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references

To specify vertical output from the command line, use the -E (or --vertical ) option when you invoke mysql . This affects all queries issued during the session, something that can be useful when using mysql to execute a script. (If you write the statements in the SQL script file using the usual semicolon terminator, you can select normal or vertical output from the command line by selective use of -E .)