如何在终端中最好地显示 MySQL SELECT 返回太多字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to best display in Terminal a MySQL SELECT returning too many fields?
提问by Chris Jacob
I'm using PuTTYto run:
我正在使用PuTTY运行：
mysql> SELECT * FROM sometable;
sometablehas 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.
sometable有许多字段，这导致许多列试图在终端中显示。字段环绕到下一行，因此很难将列标题与字段值对齐。
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
我没有也不想访问 phpMyAdmin - 或任何其他 GUI 界面。我正在寻找这样的命令行解决方案：Save MySQL Query results into text or CVS file
回答by Rytmis
Terminate the query with \Gin place of;. For example:
用\G代替终止查询;。例如：
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
...
回答by Daniel Schneller
You might also find this useful (non-Windows only):
您可能还会发现这很有用（仅限非 Windows）：
mysql> pager less -SFX
mysql> SELECT * FROM sometable;
This will pipe the outut through the lesscommand line tool which - with these parameters - will give you a tabular output that can be scrolled horizontally and vertically with the cursor keys.
这将通过less命令行工具输出输出- 使用这些参数 - 将为您提供一个表格输出，可以使用光标键水平和垂直滚动。
Leave this view by hitting the qkey, which will quit the lesstool.
通过按q键退出此视图，这将退出该less工具。
回答by Swiety
Try enabling vertical mode, using \Gto execute the query instead of ;:
尝试启用垂直模式，\G用于执行查询而不是;：
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.
您的结果将以垂直模式列出，因此每列值都将打印在单独的行上。输出会更窄，但显然更长。
回答by Ronan Boiteau
Using mysql's egocommand
使用mysql的ego命令
From mysql's helpcommand:
Frommysql的help命令：
ego??????????(\G) Send command to mysql server, display result vertically.
ego??????????(\G) 向mysql服务器发送命令，垂直显示结果。
So by appending a \Gto your select, you can get a very clean vertical output:
因此，通过将 a 附加\G到您的select，您可以获得非常干净的垂直输出：
mysql> SELECT * FROM sometable \G
Using a pager
使用寻呼机
You can tell MySQL to use the lesspager with its -Soption that chops wide lines and gives you an output that you can scroll with the arrow keys:
您可以告诉 MySQL 使用less分页器，它的-S选项可以截断宽行并为您提供可以使用箭头键滚动的输出：
mysql> pager less -S
Thus, next time you run a command with a wide output, MySQL will let you browse the output with the lesspager:
因此，下次运行带有宽输出的命令时，MySQL 将允许您使用less分页器浏览输出：
mysql> SELECT * FROM sometable;
If you're done with the pager and want to go back to the regular output on stdout, use this:
如果您已完成寻呼机并想返回到 上的常规输出stdout，请使用以下命令：
mysql> nopager
回答by davidmh
You can use the --tableor -toption, which will output a nice looking set of results
您可以使用--tableor-t选项，它将输出一组漂亮的结果
echo 'desc table_name' | mysql -uroot database -t
or some other method to pass a query to mysql, like:
或其他一些将查询传递给 mysql 的方法，例如：
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 | |
+--------------+--------------+------+-----+---------+----------------+
回答by hyang0
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.
默认寻呼机是标准输出。stdout 具有列限制，因此输出将被包装。您可以将其他工具设置为寻呼机来格式化输出。有两种方法。一是限制列，二是在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
在你的 .vimrc 中将 vim 模式设置为 nowrap
? ~ 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
+-----------+------------+------------+-------------+-------------+----------
~
~
~
回答by santiago arizti
Just to complement the answer that I thought best, I also use less -SFXbut in a different way: I like to ad it to my .my.cnffile in my home folder, an example cnf file looks like this:
为了补充我认为最好的答案，我也less -SFX以不同的方式使用：我喜欢将它添加到.my.cnf我的主文件夹中的文件中，示例 cnf 文件如下所示：
[client]
user=root
password=MyPwD
[mysql]
pager='less -SFX'
The good thing about having it this way, is that lessis only used when the output of a query is actually more than one page long, here is the explanation of all the flags:
这样做的好处是，less只有当查询的输出实际上超过一页时才使用它，这里是所有标志的解释：
- -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.
- -S：单行，当行比屏幕宽时不要跳过行，而是允许向右滚动。
- -F:一屏退出，如果内容不需要滚动，则发送到标准输出。
- -X：无初始化，禁用任何输出“较少”可能已配置为每次加载时输出。
Note: in the .my.cnffile don't put the pagercommand below the [client]keyword; although it might work with mysqlwell, mysqldumpwill complain about not recognizing it.
注意：在.my.cnf文件中不要把pager命令放在[client]关键字下面；虽然它可能工作得mysql很好，但mysqldump会抱怨不认识它。
回答by Paul Ericson
If you are using MySQL interactively, you can set your pager to use sedlike this:
如果您以交互方式使用 MySQL，则可以将寻呼机设置为如下使用sed：
$ 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 sedas the pager, the output is like this:
如果不用sed作寻呼机，则输出如下：
"blah":"blah","blah":"blah","blah":"blah"
回答by Pavel Stehule
I wrote pspg- https://github.com/okbob/pspg
我写了pspg- https://github.com/okbob/pspg
This pager is designed for tabular data - and MySQL is supported too.
此分页器专为表格数据而设计 - 也支持 MySQL。
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;
回答by Patrick Gryciuk
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.
对于 Windows，我个人使用 Windows PowerShell 并将屏幕缓冲区宽度设置得相当高。列宽保持固定，您可以使用水平滚动条查看数据。我遇到了你现在遇到的同样问题。
edit: For remote hosts that you have to SSH into you would use something like plink + Windows PowerShell
编辑：对于必须通过 SSH 连接的远程主机，您可以使用 plink + Windows PowerShell 之类的东西
