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

Question

I m using PuTTY to run   mysql gt  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

User · Answer

If you are using MySQL interactively  you can set your pager to use sed like this     mysql -u  lt user gt  p lt password gt  mysql gt  pager sed  s    n g   PAGER set to  sed  s    n g   mysql gt  SELECT blah FROM blah WHERE blah   blah         blah   blah   blah   blah   blah   blah    If you don t use sed as the pager  the output is like this    blah   blah   blah   blah   blah   blah

User · Answer

Just to complement the answer that I thought best  I also use less -SFX but in a different way  I like to ad it to my  my cnf file in my home folder  an example cnf file looks like this    client  user root password MyPwD  mysql  pager  less -SFX    The good thing about having it this way  is that less is only used when the output of a query is actually more than one page long  here is the explanation of all the flags    -S  Single line  don t skip line when line is wider than screen  instead allow to scroll to the right  -F  Quit if one screen  if content doesn t need scrolling then just send to stdout  -X  No init  disables any output  less  might have configured to output every time it loads    Note  in the  my cnf file don t put the pager command below the  client  keyword  although it might work with mysql well  mysqldump will complain about not recognizing it

User · Answer

I believe putty has a maximum number of columns you can specify for the window   For Windows I personally use Windows PowerShell and set the screen buffer width reasonably high  The column width remains fixed and you can use a horizontal scroll bar to see the data  I had the same problem you re having now   edit  For remote hosts that you have to SSH into you would use something like plink   Windows PowerShell

User · Answer

I wrote pspg - https   github com okbob pspg   This pager is designed for tabular data - and MySQL is supported too    MariaDB  sakila   pager pspg -s 14 -X --force-uniborder --quit-if-one-screen PAGER set to  pspg -s 14 -X --force-uniborder --quit-if-one-screen  MariaDB  sakila   select now    MariaDB  sakila   select   from nicer but slower film list limit 100

User · Answer

You can use the --table or -t option  which will output a nice looking set of results    echo  desc table name    mysql -uroot database -t   or some other method to pass a query to mysql  like   mysql -uroot table name --table  lt   tmp somequery sql   output    -------------- -------------- ------ ----- --------- ----------------    Field          Type           Null   Key   Default   Extra             -------------- -------------- ------ ----- --------- ----------------    id             int 11         NO     PRI   NULL      auto increment     username       varchar 30     NO     UNI   NULL                         first name     varchar 30     NO           NULL                         last name      varchar 30     NO           NULL                         email          varchar 75     NO           NULL                         password       varchar 128    NO           NULL                         is staff       tinyint 1      NO           NULL                         is active      tinyint 1      NO           NULL                         is superuser   tinyint 1      NO           NULL                         last login     datetime       NO           NULL                         date joined    datetime       NO           NULL                        -------------- -------------- ------ ----- --------- ----------------

User · Answer

Using mysql s ego command  From mysql s help command      ego                      G  Send command to mysql server  display result vertically    So by appending a  G to your select  you can get a very clean vertical output   mysql gt  SELECT   FROM sometable  G   Using a pager  You can tell MySQL to use the less pager with its -S option that chops wide lines and gives you an output that you can scroll with the arrow keys   mysql gt  pager less -S   Thus  next time you run a command with a wide output  MySQL will let you browse the output with the less pager   mysql gt  SELECT   FROM sometable    If you re done with the pager and want to go back to the regular output on stdout  use this   mysql gt  nopager

User · Answer

You might also find this useful  non-Windows only    mysql gt  pager less -SFX mysql gt  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

User · Answer

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

User · Answer

Using the Windows Command Prompt you can increase the buffer size of the window as much you want to see the number of columns  This depends on the no of columns in the table

User · Answer

You can use tee to write the result of your query to a file   tee somepath filename txt

User · Answer

Try enabling vertical mode  using  G to execute the query instead of     mysql gt  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

User · Answer

The default pager is stdout  The stdout has the column limitation  so the output would be wrapped  You could set other tools as pager to format the output  There are two methods  One is to limit the column  the other is to processed it in vim   The first method         echo  COLUMNS 179  mysql gt  nopager PAGER set to stdout mysql gt  pager cut -c -179 PAGER set to  cut -c -179  mysql gt  select   from db   ----------- ------------ ------------ ------------- ------------- ------------- ------------- ------------- ----------- ------------ ----------------- ------------ ------------ -   Host        Db           User         Select priv   Insert priv   Update priv   Delete priv   Create priv   Drop priv   Grant priv   References priv   Index priv   Alter priv    ----------- ------------ ------------ ------------- ------------- ------------- ------------- ------------- ----------- ------------ ----------------- ------------ ------------ -               test                      Y             Y             Y             Y             Y             Y           N            Y                 Y            Y                          test                      Y             Y             Y             Y             Y             Y           N            Y                 Y            Y              localhost   phpmyadmin   phpmyadmin   Y             Y             Y             Y             Y             Y           N            Y                 Y            Y              localhost   it           it           Y             Y             Y             Y             Y             Y           N            Y                 Y            Y             ----------- ------------ ------------ ------------- ------------- ------------- ------------- ------------- ----------- ------------ ----------------- ------------ ------------ - 4 rows in set  0 00 sec   mysql gt    The output is not complete  The content fits to your screen   The second one   Set vim mode to nowrap in your  vimrc        tail    vimrc    no-wrap for myslq cli set nowrap  mysql gt  pager vim - PAGER set to  vim -  mysql gt  select   from db      Vim  Reading from stdin     ----------- ------------ ------------ ------------- ------------- ----------   Host        Db           User         Select priv   Insert priv   Update pr  ----------- ------------ ------------ ------------- ------------- ----------               test                      Y             Y             Y               test                      Y             Y             Y   localhost   phpmyadmin   phpmyadmin   Y             Y             Y   localhost   it           it           Y             Y             Y  ----------- ------------ ------------ ------------- ------------- ----------

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

Examples related to mysql

Examples related to command-line

Examples related to select

Examples related to putty