MySQL终端显示格式化

默认的mysql cli客户端非常方面，但是有时候表字段太多就会显示交错行，看起来很费劲。

测试环境

• macos 10
• mysql 5.6 +
• mysql 自带客户端 mysql 命令

来看一个例子

mysql> select *from radacct limit 2;
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+----------------+--------------+--------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-------------------------------+-------------------+--------------------+-------------+----------------+-----------------+
| radacctid | acctsessionid                    | acctuniqueid                    | username    | groupname | realm | nasipaddress | nasportid | nasporttype    | acctstarttime | acctupdatetime | acctstoptime | acctinterval | acctsessiontime | acctauthentic | connectinfo_start | connectinfo_stop | acctinputoctets | acctoutputoctets | calledstationid              | callingstationid  | acctterminatecause | servicetype | framedprotocol | framedipaddress |
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+----------------+--------------+--------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-------------------------------+-------------------+--------------------+-------------+----------------+-----------------+
|      665 | 0001.0902005073300000e74b7c204341 | 0f01812fefcea2e1abccc5314a775243 | 132****2090 |          |      | 221.7.11.xxx | 33575645  | Wireless-802.11 |    1428041359 |    1428042057 |  1428042057 |        NULL |            698 | RADIUS        |                  | 1000000000      |          640184 |          9515780 | 00-00-00-00-00-00:ChinaUnicom | 00:16:6d:ce:13:de | Lost-Carrier      | Framed-User | PPP            | 10.12.9.35      |
|      666 | 0001.09020050733000008fbfa7040635 | ca7f3485db61b207454c13b796df8c57 | 132****3834 |          |      | 221.7.11.xxx | 33575645  | Wireless-802.11 |    1428041341 |    1428043501 |  1428043656 |            0 |            2315 | RADIUS        |                  | 1000000000      |        4456853 |        138112319 | 00-00-00-00-00-00:ChinaUnicom | 90:27:e4:55:65:34 | NAS-Request        | Framed-User | PPP            | 10.12.9.166    |
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+----------------+--------------+--------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-------------------------------+-------------------+--------------------+-------------+----------------+-----------------+

在终端看是折行显示的，看起来非常费劲

方法一

mysql> pager less -SFX;
PAGER set to 'less -SFX'
mysql> select *from radacct limit 2;
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+--------
| radacctid | acctsessionid                    | acctuniqueid                    | username    | groupname | realm | nasipaddress | nasportid | nasporttype    | acctstarttime | acctupd
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+--------
|      665 | 0001.0902005073300000e74b7c204341 | 0f01812fefcea2e1abccc5314a775243 | 132***2090 |          |      | 221.7.16.202 | 33575645  | Wireless-802.11 |    1428041359 |    142
|      666 | 0001.09020050733000008fbfa7040635 | ca7f3485db61b207454c13b796df8c57 | 132***3834 |          |      | 221.7.16.202 | 33575645  | Wireless-802.11 |    1428041341 |    142
+-----------+-----------------------------------+----------------------------------+-------------+-----------+-------+--------------+-----------+-----------------+---------------+--------
(END)

其实在终端显示的时候是2行的，使用键盘的左右方向键可以控制查看右边的隐藏列，esc可以退出查看。参考这个回答 https://stackoverflow.com/questions/924729/mysql-select-many-fields-how-best-to-display-in-terminal 。

方法二

使用 \G , 比较推荐这个用法，把行转化成列显示

mysql> select *from radacct limit 2 \G
*************************** 1. row ***************************
        radacctid: 665
    acctsessionid: 0001.0902005073300000e74b7c204341
      acctuniqueid: 0f01812fefcea2e1abccc5314a775243
          username: 132****2090
        groupname:
            realm:
      nasipaddress: 221.7.16.202
        nasportid: 33575645
      nasporttype: Wireless-802.11
    acctstarttime: 1428041359
    acctupdatetime: 1428042057
      acctstoptime: 1428042057
      acctinterval: NULL
  acctsessiontime: 698
    acctauthentic: RADIUS
connectinfo_start:
  connectinfo_stop: 1000000000
  acctinputoctets: 640184
  acctoutputoctets: 9515780
  calledstationid: 00-00-00-00-00-00:ChinaUnicom
  callingstationid: 00:16:6d:ce:13:de
acctterminatecause: Lost-Carrier
      servicetype: Framed-User
    framedprotocol: PPP
  framedipaddress: 10.12.9.35
*************************** 2. row ***************************
        radacctid: 666
    acctsessionid: 0001.09020050733000008fbfa7040635
      acctuniqueid: ca7f3485db61b207454c13b796df8c57
          username: 132****3834
        groupname:
            realm:
      nasipaddress: 221.7.16.202
        nasportid: 33575645
      nasporttype: Wireless-802.11
    acctstarttime: 1428041341
    acctupdatetime: 1428043501
      acctstoptime: 1428043656
      acctinterval: 0
  acctsessiontime: 2315
    acctauthentic: RADIUS
connectinfo_start:
  connectinfo_stop: 1000000000
  acctinputoctets: 4456853
  acctoutputoctets: 138112319
  calledstationid: 00-00-00-00-00-00:ChinaUnicom
  callingstationid: 90:27:e4:55:65:34
acctterminatecause: NAS-Request
      servicetype: Framed-User
    framedprotocol: PPP
  framedipaddress: 10.12.9.166
2 rows in set (0.00 sec)

查询语句使用 \G 结尾，不需要 ;了。推荐经常用这种写法，很实用。