How to best display in Terminal a MySQL SELECT ret

2019-01-29 14:36发布

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

12条回答
爱情/是我丢掉的垃圾
2楼-- · 2019-01-29 15:10

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"
查看更多
相关推荐>>
3楼-- · 2019-01-29 15:12
SELECT * FROM sometable\G

This query displays the rows vertically, like this:

*************************** 1. row ***************************
             id: 1
查看更多
混吃等死
4楼-- · 2019-01-29 15:22

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.

查看更多
在下西门庆
5楼-- · 2019-01-29 15:22

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.

查看更多
男人必须洒脱
6楼-- · 2019-01-29 15:26

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
+-----------+------------+------------+-------------+-------------+----------
~
~
~
查看更多
▲ chillily
7楼-- · 2019-01-29 15:26

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

tee somepath\filename.txt
查看更多
登录 后发表回答