Tuesday, April 28, 2009

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


Share at Facebook

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.




No comments: