Enabling Vertical (\G) Output in the MySQL Client

One problem with the MySQL command line client is that queries with lots of columns tend to wrap crazily based on your terminal size. To overcome this, you can get a vertical output by terminating your queries with \G instead of ;. I find this format extremely helpful in cases where I know only one row will be returned. By way of example, here's the output of a query that shows the last IP to be inserted into the Spam Karma 2 blacklist for this website. When I originally ran this query, it wrapped multiple lines on my screen and was very difficult to read. What an improvement!

(slap@localhost) [slaptijack]> SELECT * FROM sk2_blacklist ORDER BY id DESC LIMIT 1 \\G
*************************** 1. row ***************************
           id: 701
         type: ip_black
        value: 200.175.116.162
        score: 44
        trust: 100
     comments: 
        added: 2007-12-20 02:21:16
     added_by: sk2_blacklist_plugin
    last_used: 2007-12-20 02:21:16
   used_count: 0
user_reviewed: no
1 row in set (0.00 sec)

(slap@localhost) [slaptijack]>

If you want this sort of output all the time, you need to add an option to the .my.cnf file in your home directory.

[mysql]
vertical

That's all there is to it. With this change in place, you can terminate your queries with ; as usual but get the \G output instead. You could even make this change globally for all users on your system by making the same change in /etc/my.cnf.

Further Reading

High Performance MySQL: Optimization, Backups, Replication, and More by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz, and Derek Balling
MySQL Stored Procedure Programming by Guy Harrison, Steven Feuerstein

My Bookshelf