注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

帅小伙的博客

希望能在这里交到更多的朋友

 
 
 

日志

 
 
 
 

MySQL中如何横向显示结果集  

2008-06-30 16:31:49|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
          当我们的查询结果字段比较多的时候,显示会比较乱,比如查询mysql数据库中的user表,一般查询会显示如下:
(root@localhost) [Fri Jul 11 17:02:16 2008]> select * from user;
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host                  | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |

+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost             | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost.localdomain | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                |          |            |             |              |             0 |           0 |               0 |                    0 |
| 127.0.0.1             | root |                                           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                |          |            |             |           
。。。。。。

这个结果几乎无法进行查看,当然,我们可以垂直显示,这也是我们经常使用的方法:
(root@localhost) [Fri Jul 11 17:04:07 2008]> select * from user G;
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: localhost.localdomain
                 User: root
。。。。。。

但是当我们的结果记录有很多时候,这种垂直显示明显效率不高,这时候有啥好办法吗?

我们可以借助与pager工具,pager可以和操作系统的文档显示命令进行结合,来进行记录的分页显示:
(root@localhost) [Fri Jul 11 17:06:26 2008]> pager less
PAGER set to 'less'
(root@localhost) [Fri Jul 11 17:06:30 2008]> select * from user G;
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: localhost.localdomain
                 User: root
             Password:
          Select_priv: Y
          Insert_priv: Y
:通过less的翻页功能可以让记录上下翻页,此时我们加上less的-S功能,即显示长行功能,即可以实现我们的翻页+水平显示功能:
(root@localhost) [Fri Jul 11 17:10:06 2008]> pager less -S
PAGER set to 'less -S'
(root@localhost) [Fri Jul 11 17:10:09 2008]> select * from user ;
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+
| Host                  | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+
| localhost             | root |                                           | Y           | Y           | Y           | Y           |
| localhost.localdomain | root |                                           | Y           | Y           | Y           | Y           |
| 127.0.0.1             | root |                                           | Y           | Y           | Y           | Y           |
| localhost             | z1   |                                           | N           | N           | N           | N           |
| localhost             | z4   |                                           | N           | N           | N           | N           |
| localhost             | z    |                                           | N           | N           | N           | N           |
| localhost             | z3   |                                           | N           | N           | N           | N           |
| test_hostname         | z1   |                                           | N           | N           | N           | N           |
| 192.168               | z1   |                                           | N           | N           | N           | N           |
| 1921168               | z1   |                                           | N           | N           | N           | N           |
| localhost             | z10  |                                           | N           | N           | N           | N           |
| %                     | z1   |                                           | N           | N           | N           | N           |
| %                     | z2   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N           | N           | N           | N           |
| %                     | z3   |                                           | N           | N           | N           | N           |
| %                     | z4   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | N           | N           | N           |
+-----------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+
(END)

此时我们再按左右键就可以水平显示剩余字段的内容,如下:
---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+----------
         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_p
---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+----------
         | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y       
         | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y       
         | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
E05AA257 | N           | N           | N           | N           | N           | N         | N           | N             | N       
         | N           | N           | N           | N           | N           | N         | N           | N             | N       
E05AA257 | Y           | N           | N           | N           | N           | N         | N           | N             | N       
---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+----------
~
~这个功能在对字段比较多的表查询时将非常方便。


  评论这张
 
阅读(996)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018