How to best display in Terminal a MySQL SELECT returning too many fields?

I'm using PuTTY to run:

mysql> SELECT * FROM sometable;

sometable has many fields and this results in many columns trying to be displayed in the terminal. The fields wrap onto the next line so it is very hard to line up column titles with field values.

What solutions are there for viewing such data in terminal?

I don't have nor want access to phpMyAdmin - or any other GUI interfaces. I'm looking for command-line solutions such as this one: Save MySQL Query results into text or CVS file

Solution 1:

Terminate the query with \G in place of ;. For example:

SELECT * FROM sometable\G

This query displays the rows vertically, like this:

*************************** 1. row ***************************
                 Host: localhost
                   Db: mydatabase1
                 User: myuser1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          ...
*************************** 2. row ***************************
                 Host: localhost
                   Db: mydatabase2
                 User: myuser2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          ...

Solution 2:

You might also find this useful (non-Windows only):

mysql> pager less -SFX
mysql> SELECT * FROM sometable;

This will pipe the outut through the less command line tool which - with these parameters - will give you a tabular output that can be scrolled horizontally and vertically with the cursor keys.

Leave this view by hitting the q key, which will quit the less tool.

Solution 3:

Try enabling vertical mode, using \G to execute the query instead of ;:

mysql> SELECT * FROM sometable \G

Your results will be listed in the vertical mode, so each column value will be printed on a separate line. The output will be narrower but obviously much longer.