Recipe 1.22. Making Long Output Lines More Readable

Problem

You have a query that produces long output lines that wrap around and look messy on your screen.

Solution

Use vertical output format.

Discussion

Some statements 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:

mysql> SHOW FULL COLUMNS FROM limbs; +-------+-------------+-------------------+------+-----+---------+-------+----- ----------------------------+---------+ | Field | Type        | Collation         | Null | Key | Default | Extra | Priv ileges                      | Comment | +-------+-------------+-------------------+------+-----+---------+-------+----- ----------------------------+---------+ | thing | varchar(20) | latin1_swedish_ci | YES  |     | NULL    |       | sele ct,insert,update,references |         | | legs  | int(11)     | NULL              | YES  |     | NULL    |       | sele ct,insert,update,references |         | | arms  | int(11)     | NULL              | YES  |     | NULL    |       | sele ct,insert,update,references |         | +-------+-------------+-------------------+------+-----+---------+-------+----- ----------------------------+---------+ 

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

mysql> SHOW FULL COLUMNS FROM limbs\G *************************** 1. row ***************************      Field: thing       Type: varchar(20)  Collation: latin1_swedish_ci       Null: YES        Key:    Default: NULL      Extra: Privileges: select,insert,update,references    Comment: *************************** 2. row ***************************      Field: legs       Type: int(11)  Collation: NULL       Null: YES        Key:    Default: NULL      Extra: Privileges: select,insert,update,references    Comment: *************************** 3. row ***************************      Field: arms       Type: int(11)  Collation: NULL       Null: YES        Key:    Default: NULL      Extra: Privileges: select,insert,update,references    Comment: 

To specify vertical output from the command line, use the -E (or --vertical) option when you invoke mysql. This affects all statements 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.)