MySQL顯示操作控制小技巧

MySQL顯示操作控制小技巧

MySQL 顯現格式化

一、使用G按行垂直顯示結果

未使用

mysql> select * from wp_users limit 1,2;

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

| ID | user_login      | user_pass                          | user_nicename    | user_email                  | user_url | user_registered    | user_activation_key | user_status | display_name    |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

|  2 | NicholaJorgenson | $P$BrLC6ljpc5i6IvCw4V2dTHn.ZTrXd/1 | nicholajorgenson | almatrostelgt3776@yahoo.com |          | 2015-02-15 09:40:54 |                    |          0 | NicholaJorgenson |

|  3 | ElvinMoney      | $P$BebFywousFh0mVmoqHEWAx7IofQmEJ0 | elvinmoney      | alizanoguera@mailcatch.com  |          | 2015-02-15 10:04:36 |                    |          0 | ElvinMoney      |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

2 rows in set (0.00 sec)

使用

mysql> select * from wp_users limit 1,2\G;

*************************** 1. row ***************************

                ID: 2

        user_login: NicholaJorgenson

          user_pass: $P$BrLC6ljpc5i6IvCw4V2dTHn.ZTrXd/1

      user_nicename: nicholajorgenson

        user_email: almatrostelgt3776@yahoo.com

          user_url: 

    user_registered: 2015-02-15 09:40:54

user_activation_key: 

        user_status: 0

      display_name: NicholaJorgenson

*************************** 2. row ***************************

                ID: 3

        user_login: ElvinMoney

          user_pass: $P$BebFywousFh0mVmoqHEWAx7IofQmEJ0

      user_nicename: elvinmoney

        user_email: alizanoguera@mailcatch.com

          user_url: 

    user_registered: 2015-02-15 10:04:36

user_activation_key: 

        user_status: 0

      display_name: ElvinMoney

2 rows in set (0.00 sec)

二、使用pager來設定翻頁

pager 'more'      此模式會顯示一頁，等待按鍵再顯示一頁   

pager 'less'      此模式會顯示一頁，等待按鍵再顯示一行

nopager            此模式直接顯示到最完

三、使用tee儲存運行結果到檔案，類似於Oracle中的spool命令

mysql> tee test.txt

Logging to file 'test.txt'

select * from wp_users limit 1,2;

 mysql> notee

mysql> quit

Bye

 [mysql@sam ~]$ cat test.txt

mysql> select * from wp_users limit 1,2;

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

| ID | user_login      | user_pass                          | user_nicename    | user_email                  | user_url | user_registered    | user_activation_key | user_status | display_name    |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

|  2 | NicholaJorgenson | $P$BrLC6ljpc5i6IvCw4V2dTHn.ZTrXd/1 | nicholajorgenson | almatrostelgt3776@yahoo.com |          | 2015-02-15 09:40:54 |                    |          0 | NicholaJorgenson |

|  3 | ElvinMoney      | $P$BebFywousFh0mVmoqHEWAx7IofQmEJ0 | elvinmoney      | alizanoguera@mailcatch.com  |          | 2015-02-15 10:04:36 |                    |          0 | ElvinMoney      |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

2 rows in set (0.00 sec)

mysql> notee

[mysql@sam ~]$ 

四、直接執行作業系統命令

mysql> system ls -l

total 10092

-rw-rw-r-- 1 mysql mysql 1638854 Apr 22 16:05 backup1.sql

-rw-rw-r-- 1 mysql mysql 1767787 Apr 23 10:49 backup2.sql

-rw-rw-r-- 1 mysql mysql  13807 Apr 23 09:48 backup3.sql

-rw-rw-r-- 1 mysql mysql  12258 Apr 23 10:53 backup4.sql

-rw-rw-r-- 1 mysql mysql 2293238 Apr 23 11:03 backup5.sql

-rw-rw-r-- 1 mysql mysql 2297583 Apr 23 11:16 backup6.sql

-rw-rw-r-- 1 mysql mysql 2297579 Apr 23 11:23 backup7.sql

-rw-rw-r-- 1 mysql mysql    1280 Aug 26 11:18 test.txt

五、執行SQL檔案指令碼

mysql> source 1.sql

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

| ID | user_login      | user_pass                          | user_nicename    | user_email                  | user_url | user_registered    | user_activation_key | user_status | display_name    |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

|  2 | NicholaJorgenson | $P$BrLC6ljpc5i6IvCw4V2dTHn.ZTrXd/1 | nicholajorgenson | almatrostelgt3776@yahoo.com |          | 2015-02-15 09:40:54 |                    |          0 | NicholaJorgenson |

|  3 | ElvinMoney      | $P$BebFywousFh0mVmoqHEWAx7IofQmEJ0 | elvinmoney      | alizanoguera@mailcatch.com  |          | 2015-02-15 10:04:36 |                    |          0 | ElvinMoney      |

+----+------------------+------------------------------------+------------------+-----------------------------+----------+---------------------+---------------------+-------------+------------------+

2 rows in set (0.00 sec)

mysql> system cat 1.sql

select * from wp_users limit 1,2;

mysql> 

六、修改命令提示字元

mysql> prompt \u@\h(\d) \r:\m:\s:>

PROMPT set to '\u@\h(\d) \r:\m:\s:>'

 sam@localhost(wordpress) 03:15:28:>

本文永久更新連結地址：