MySQL Cookbook

Making Long Output Lines More Readable

Problem

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

Solution

Use vertical output format.

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:^[10]

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.)

---

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