Display result of command line query vertically


Problem

If you are a person that likes to work in a terminal, you'll probably have used mysql before. It requires some knowledge, but it works quite well for most things, but the result of a select query is often hard to read.



Solution

Assuming we've got a select query, doesn't matter what it is as long as it selects something:

SELECT * FROM information_schema.tables WHERE table_schema = database() LIMIT 1,1;

This will show you a table in your terminal window and since that table has a lot of columns, it's pertty hard to read.

mysql> SELECT * FROM information_schema.tables WHERE table_schema = database() LIMIT 1,1;
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | egc          | migrations | BASE TABLE | InnoDB |      10 | Dynamic    |         11 |           1489 |       16384 |               0 |            0 |         0 |           NULL | 2016-08-08 10:14:11 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)

There's a simple trick though, just add \G to the end and everything will be shown vertically.

SELECT * FROM information_schema.tables WHERE table_schema = database() LIMIT 1,1\G;

And suddenly we've got this:

mysql> SELECT * FROM information_schema.tables WHERE table_schema = database() LIMIT 1,1\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: egc
     TABLE_NAME: migrations
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 11
 AVG_ROW_LENGTH: 1489
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-08-08 10:14:11
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

Comments




Please sign-in to post a comment