Mysql: Print the output of a query vertically on screen.

Mysql always displays the output of any query as horizontal row format as default. I mean the traditional format. First line is one row. Second line is another row, and so on. For example the below format.

mysql> select * from collations limit 4;
+-----------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+----+------------+-------------+---------+
| big5_chinese_ci | big5               |  1 | Yes        | Yes         |       1 |
| big5_bin        | big5               | 84 |            | Yes         |       1 |
| dec8_swedish_ci | dec8               |  3 | Yes        |             |       0 |
| dec8_bin        | dec8               | 69 |            |             |       0 |
+-----------------+--------------------+----+------------+-------------+---------+

But you can change the table output display format from default horizontal vertical format. If you just type backslash G (\G) after the SQL query, it will display you the output at vertical format. For example, I'm have run the above query at mysql using \G and it returns the data as below format;

mysql> select * from collations limit 4\G
*************************** 1. row ***************************
    COLLATION_NAME: big5_chinese_ci
CHARACTER_SET_NAME: big5
                ID: 1
        IS_DEFAULT: Yes
       IS_COMPILED: Yes
           SORTLEN: 1
*************************** 2. row ***************************
    COLLATION_NAME: big5_bin
CHARACTER_SET_NAME: big5
                ID: 84
        IS_DEFAULT:
       IS_COMPILED: Yes
           SORTLEN: 1
*************************** 3. row ***************************
    COLLATION_NAME: dec8_swedish_ci
CHARACTER_SET_NAME: dec8
                ID: 3
        IS_DEFAULT: Yes
       IS_COMPILED:
           SORTLEN: 0
*************************** 4. row ***************************
    COLLATION_NAME: dec8_bin
CHARACTER_SET_NAME: dec8
                ID: 69
        IS_DEFAULT:
       IS_COMPILED:
           SORTLEN: 0

you can see that the data are displaying at column name, and value format. Using \G you are only changing for each query execution. If you want to display all your query output into vertical format, you can do this while login your mysql. The command is as below.

mysql -E -uroot

Using -E will enable all your mysql query output into vertical format for this session.

How this vertical format helps you?

This actually helps you when your mysql table has large number of columns or fields. That case the traditional default horizontal format will be difficult to read the data from screen. For example the below query and output.

mysql> select * from statistics limit 1;
+---------------+--------------+------------+------------+--------------+-------
-----+--------------+-------------+-----------+-------------+----------+--------
+----------+------------+---------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_
NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED
| NULLABLE | INDEX_TYPE | COMMENT |
+---------------+--------------+------------+------------+--------------+-------
-----+--------------+-------------+-----------+-------------+----------+--------
+----------+------------+---------+
| NULL          | andrew       | items      |          0 | andrew       | PRIMAR
Y    |            1 | id          | A         |           0 |     NULL | NULL
|          | BTREE      |         |
+---------------+--------------+------------+------------+--------------+-------
-----+--------------+-------------+-----------+-------------+----------+--------
+----------+------------+---------+

See? Its really hard to understand which value for which column. But if you The Vertical view format for these cases, it will be helpful for you. Just see the above query into vertical format.

mysql> select * from statistics limit 1\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: andrew
   TABLE_NAME: items
   NON_UNIQUE: 0
 INDEX_SCHEMA: andrew
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: id
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:

This is much easier to read, and pleasant for your eyes to see.

So, If you are using your mysql from your command line, or from console, that case this article will be helpful for you.