Formating Output of SQL

I mentioned a few posts ago about Oracle's SQL*Plus client program and how great it was at formatting output in different and novel ways, providing you know the commands to do it. One of the biggest problems is that the default linesize (the width of the screen buffer used to display rows of data in a table) is small and only actually displays something if you have small amount of columns. About 95% of the SQL I write breaks this default and displays something which is totally unfathomable.

The answer of course is to change the size of the linesize parameter, it's easily done and in fact with SQL*Plus you can set up a file to set this on log in, but I'm often connecting to servers where this file doesn't exist.

Anyway getting to the point I found a nifty way to format data in MySQL into a more readable format, this isn't some great secret but I wasn't aware of it until recently. Let's take a normal SQL statement and result from the MySQL command line.

mysql> select * from emps where emp_id = 1;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | bonus  |
+--------+----------+---------+--------+--------+
|      1 | Paul     |       1 | 100.00 | 100.00 |
+--------+----------+---------+--------+--------+
1 row in set (0.08 sec)

MySQL actually does a very good job of formatting the data in a readable format, but if we have a table with a large number of columns things get a little crowded, we have to keep scrolling left to right to see which data at one end relates to the other.

But help is at hand, in MySQL you can format the returned results to be returned vertically rather than horizontally. This makes it easy to see data which is relevant to a single row only. To do this you simply append \G to the end of your select statement like so..

mysql> select * from emps where emp_id = 1\G
****************** 1. row *****************  
  emp_id: 1
emp_name: Paul
 dept_id: 1
  salary: 100.00
   bonus: 100.00
1 row in set (0.00 sec)