How To Best Display In Terminal A MySQL SELECT Returning Too Many Fields? - Free Websites, Share News And Posts Publicly

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

The solution is for the developer to fix the bug that stops the terminal being expanded to wider than a single screen.

–

@Owl, is this really a bug? Do the solutions provided here not solved the problem yet?

–

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
          ...

Despite the Windows being not case-sensitive, this G must be capitalized.

–

Just to clarify the above comment, when you type SELECT * FROM sometable\G you are sending the string to the mysql command line client, not Windows, which is why the G is case sensitive

–

Except it doesn’t work that well with large quantities of records.

–

The linked documentation says: “ego (\G) Send command to mysql server, display result vertically.Be careful about defining a delimiter that might occur within other words. For example, if you define the delimiter as X, you will be unable to use the word INDEX in statements.”

–

I encountered this. Could you please tell me how to fix it? sh: 1: less: not found 44 rows in set (0.01 sec)

–

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.

this is genius. excellent tip. Since the dawn of time I have wanted this.

–

can be reset using nopager.

–

Searching in less is done by hitting / and then write the search string, which can also be regexes, and then press enter. Search forward by pressing n and backwards by pressing N (Shift + n).

–

less can do even more cool things. Using & you can filter (only show matching lines) the result further using regular expressions. This works in addition to the / search (which will still cause the highlighting). Reset the filter by hitting & again and then just pressing Return.

–

F will quit less immediately, if the output already fits the screen. X is to prevent less from trying to clear the screen on exit. See the less man page.

–

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.

as a complement to \G, you can also use \gas a replacement for ;, I know, who would do that, but it gives context to why \Gworkd in the first place.

–

Table of Contents

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> 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> 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> SELECT * FROM sometable;

If you’re done with the pager and want to go back to the regular output on stdout, use this:

mysql> nopager

This! Beautiful, only way to make this more perfect is to have a way to see how far the columns stretch horizontally. Like a scrollbar. Nevertheless, great solution.

–

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 < /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    |                |
+--------------+--------------+------+-----+---------+----------------+

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.

Note that if you execute a “limit-less” query on a large table, you will not notice the chaos caused by it because less will stay anchored to the first line of the output.

–

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> nopager
PAGER set to stdout
mysql> pager cut -c -179
PAGER set to 'cut -c -179'
mysql> 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>

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> pager vim -
PAGER set to 'vim -'
mysql> 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

This worked best for me in a lightweight docker container. pager cut -c -200. The more accepted answers here required me to download unnecessary dependencies.

–

If you are using MySQL interactively, you can set your pager to use sed like this:

$ mysql -u <user> p<password>
mysql> pager sed 's/,/\n/g' 
PAGER set to 'sed 's/,/\n/g''
mysql> 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"

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;

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

You can use tee to write the result of your query to a file:

tee somepath\filename.txt

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.

source : https://stackoverflow.com/questions/924729/how-to-best-display-in-terminal-a-mysql-select-returning-too-many-fields

Chat read-only to anonymous users. Chat with Anyone and Anywhere. Only registered users are allowed to send messages.

Loading the chat ...

201 Register Login

Using mysql‘s ego command

Using a pager

Leave a Reply Cancel reply

Using `mysql`‘s `ego` command