MySQL客戶端的進階操作_ZenDei技術網路在線

GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。作者：土豆娃娃文章來源：社區投稿引言作為一個MySQL資料庫從業者，我們最常用的工具就是mysql了，然而如何讓它變的更好用，你瞭解嗎？ mysq ...

GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。
作者：土豆娃娃
文章來源：社區投稿

引言

作為一個MySQL資料庫從業者，我們最常用的工具就是mysql了，然而如何讓它變的更好用，你瞭解嗎？

mysql Client Commands

作為連接MySQL資料庫的工具，mysql其實有很多非常有用的命令設置，有一些是我們日常使用的，比如\g、\G、\q，也有我們不太常用的\P、\T。今天分享一下我對這些設置的理解，希望能對大家在日常的工作中，有所幫助。

支持哪些設置

mysql提供了help命令，用以說明支持哪些命令的設置，我們先看一下

Great[(none)]> help ;

For information about Percona products and services, visit:
   http://www.percona.com/
Percona Server manual: http://www.percona.com/doc/percona-server/8.0/
For the MySQL Reference Manual: http://dev.mysql.com/
To buy Percona support, training, or other products, visit:
   https://www.percona.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for 'help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
source_decrypt Execute an encrypted script file. Takes file name, decrypt key as arguments.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.

For server side help, type 'help contents'

顯示幫助信息，列出所有支持的操作命令，這個最簡單，就不做進一步說明瞭。

clear(\c)

MySQL手冊介紹為清除當前輸入的SQL語句，我個人更願意理解為撤銷已輸入SQL的執行。看下麵的例子：

Great[test]> select * from tt1;
+------+------+------+------+
| id   | dd   | c1   | c2   |
+------+------+------+------+
|    1 | aaa  | NULL | NULL |
|    2 | bbb  | NULL | NULL |
|    3 | NULL | NULL | NULL |
|    4 | 8    | NULL | NULL |
|    5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[test]> delete from tt1 
    -> where id = 5 
    -> and c1 is not null
    ->

當我輸入完上面的SQL時，發現邏輯未考慮全，不想執行這個SQL了，怎麼辦呢？這裡有好幾種解決辦法：你可以ctrl+c 終止命令的執行，也可以關閉當前的mysql客戶端，甚至關閉操作命令的電腦。然後有一種標準且簡單的方式，就是在命令的最後加上\c，就可以實現：

Great[test]> delete from tt1 
    -> where id = 5 
    -> and c1 is not null
    -> \c
Great[test]>

可以看到，在命令視窗最後輸入\c後，SQL不會被執行，並且重新啟動新的命令行接收客戶端輸入。需要註意的是：\c和需要取消的SQL間，不能有分隔符(預設為分號;)，否則會先執行分隔符前面的SQL，然後再執行\c，這時就達不到取消已輸入SQL執行的效果了。

connect(\r)

重新連接資料庫服務端，支持重連過程中，指定database名字和連接主機。

這個功能看起來好像沒什麼新奇的，客戶端執行了SQL，就算連接超時了，預設情況下，自動就會重新連接服務端。

但是在MGR、主從複製場景下，如果所有實例的維護賬號都具有相同的用戶名、密碼、埠，那麼通過\r就很方便的切換多個實例進行維護，而不需要重新輸入其他連接信息

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.82  |
+---------------+
1 row in set (0.00 sec)

Great[test]> \r test 172.16.50.81
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    911698
Current database: test

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.81  |
+---------------+
1 row in set (0.00 sec)

Great[test]>

重連信息中的ip地址，也可以是在/etc/hosts中配置的主機名

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.81  |
+---------------+
1 row in set (0.00 sec)

Great[test]> \r test Great82
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    2460607
Current database: test

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.82  |
+---------------+
1 row in set (0.00 sec)

Great[test]>

delimiter(\d)

自定義分隔符，在創建、修改存儲過程、函數、觸發器、事件、視圖都會用到，替換用於替換預設的分號分隔符。

edit(\e)

官網解釋說命令用於編輯當前輸入SQL命令，預設的編輯器是vi，也可以通過設置環境變數EDITOR來改變成其他的編輯器，比如調整為vim編輯器export EDITOR=$(which vim)。

有這個命令後，輸出錯誤的SQL，就不需要再用\c終止了，而是直接在其後加上\e進行編輯，修改成正確的後，再執行。

比如我實際上想要執行的命令中tt2表不存在，那麼只需要通過\e更新SQL語句中的表名字就可以繼續執行，不需要再重新編輯整條SQL。

Great[test]> select * from test.tt2 \e

vi中替換表名字的操作就不再演示了，編輯後的執行情況如下：

Great[test]> select * from test.tt2 \e 
    -> ;
+------+------+------+------+
| id   | dd   | c1   | c2   |
+------+------+------+------+
|    1 | aaa  | NULL | NULL |
|    2 | bbb  | NULL | NULL |
|    3 | NULL | NULL | NULL |
|    4 | 8    | NULL | NULL |
|    5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[test]>

另外在測試過程中，還發現\e可以對上一次執行的SQL進行編輯，即單獨執行\e時，其實是對上次執行的SQL命令做編輯。

Great[test]> \e
select * from test.tt1

這裡有人就會想到，我按向上的方向鍵，也能編輯上一條SQL呀，何必這麼麻煩，這裡有下麵的SQL情況，有多次換行，或者結構更複雜的SQL

Great[test]> select * from test.z1
    -> join test.z2 
    -> using(id)
    -> limit 3;
+------+------+------+
| id   | name | name |
+------+------+------+
|    1 | 11   | 11   |
|    2 | 22   | 22   |
|   11 | 11   | 11   |
+------+------+------+
3 rows in set (0.00 sec)

如果按上的方向鍵，整個SQL語句結構就發生了變化，本來規整的SQL語句，變成了很長的一行，很難調整，但是使用\e就不會有這種情況，會保持之前的輸入結構，更方便語句的調整

Great[test]> select * from test.z1
    -> join test.z2 
    -> using(id)
    -> limit 3;
+------+------+------+
| id   | name | name |
+------+------+------+
|    1 | 11   | 11   |
|    2 | 22   | 22   |
|   11 | 11   | 11   |
+------+------+------+
3 rows in set (0.00 sec)

Great[test]> \e

select * from test.z1
join test.z2
using(id)
limit 3

另外，有同學經常苦惱，我昨天才輸入的SQL命令，如果需要重新執行，又得輸入一遍，其實這裡也有一個小技巧，通過快捷鍵CTRL+R，輸入SQL中的關鍵信息(比如表名字)，就能快速翻出對應的SQL，如果匹配出來的行不是想要的SQL，可以繼續按CTRL+R繼續上翻，直到查找到需要的SQL，當然，也有可能需要的SQL已經被清理出歷史記錄中，這種情況是無法被找到的。

ego(\G)

提交SQL語句到伺服器，並且將返回的數據列式顯示。

exit(\q)

退出mysql客戶端連接。

go(\g)

提交SQL語句到伺服器。

pager(\P)

設置pager規則，對查詢結果執行pager規則後，再輸出結果。這也是一個非常有用的設置，我們常常因為processlist結果太多而不方便查看，需要通過單行grep或者查詢information_schema.processlist來實現，有這個工具後，我們看看效果

Great[test]> show processlist;
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| Id      | User            | Host               | db   | Command | Time    | State                                                  | Info                             | Time_ms    | Rows_sent | Rows_examined |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
|       6 | event_scheduler | localhost          | NULL | Daemon  | 5019248 | Waiting on empty queue                                 | NULL                             | 5019247326 |         0 |             0 |
|      26 | system user     |                    | NULL | Connect | 5018577 | waiting for handler commit                             | Group replication applier module | 5018576436 |         0 |             0 |
|      29 | system user     |                    | NULL | Query   |    1010 | Slave has read all relay log; waiting for more updates | NULL                             |    1010045 |         0 |             0 |
|      30 | system user     |                    | NULL | Query   |    1010 | Waiting for an event from Coordinator                  | NULL                             |    1010045 |         0 |             0 |
|      31 | system user     |                    | NULL | Query   |  100958 | Waiting for an event from Coordinator                  | NULL                             |  100956966 |         0 |             0 |
|      32 | system user     |                    | NULL | Query   |  100958 | Waiting for an event from Coordinator                  | NULL                             |  100956966 |         0 |             0 |
|      33 | system user     |                    | NULL | Connect | 5018577 | Waiting for an event from Coordinator                  | NULL                             | 5018576419 |         0 |             0 |
|      34 | system user     |                    | NULL | Connect | 5018577 | Waiting for an event from Coordinator                  | NULL                             | 5018576418 |         0 |             0 |
|      35 | system user     |                    | NULL | Connect | 5018577 | Waiting for an event from Coordinator                  | NULL                             | 5018576417 |         0 |             0 |
|      36 | system user     |                    | NULL | Connect | 5018577 | Waiting for an event from Coordinator                  | NULL                             | 5018576415 |         0 |             0 |
|      37 | system user     |                    | NULL | Connect | 5018577 | Waiting for an event from Coordinator                  | NULL                             | 5018576413 |         0 |             0 |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
14 rows in set (0.00 sec)

Great[test]> \P grep -vE 'system user|Sleep'
PAGER set to 'grep -vE 'system user|Sleep''
Great[test]> show processlist;
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| Id      | User            | Host               | db   | Command | Time    | State                                                  | Info                             | Time_ms    | Rows_sent | Rows_examined |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
|       6 | event_scheduler | localhost          | NULL | Daemon  | 5019255 | Waiting on empty queue                                 | NULL                             | 5019255045 |         0 |             0 |
| 2460607 | Great         | 172.16.50.81:59062 | test | Query   |       0 | init                                                   | show processlist                 |          0 |         0 |             0 |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
14 rows in set (0.01 sec)

Great[test]>

pager後面可以跟很多的shell命令，比如awk、grep、wc ，對結果集的處理，當SQL不方便處理而shell方便處理時，不用再使用-e參數每次都進行連接，然後處理，比如主從結構對Slave_IO_Running、Slave_SQL_Running的監控，可設置pager grep -E 'Slave_IO_Running|Slave_SQL_Running'。

pager的另外一個用途：經常有同學問，一些欄位中是否有包含某些特定字元串，正常的SQL處理是需要寫成col1 like '%abc%' or col2 like '%abc%'...，寫出來後SQL結構相對複雜，通過pager設置pager grep -i abc; select * from tab即可方便查看。

nopager(\n)

pager的設置是整個session生命周期內都生效，通過執行nopager進行關閉設置

prompt(\R)

修改mysql客戶端的命令行提示信息，支持顯示的提示信息非常多，具體可以參見MySQL官網介紹。修改mysql客戶端的命令行提示信息，也有好幾種方式：

1、設置操作系統環境變數MYSQL_PS1 export MYSQL_PS1= " \D_\h_\p_\u > "，格式為"時間_主機_埠_用戶名"

2、通過客戶端的命令prompt修改 \R \D_\h_\p_\u >

3、通過my.cnf的[mysql]域進行配置

[mysql]
prompt="\R \D_\h_\p_\u > "

4、如果session中通過\R xxx 臨時修改了命令行提示信息，可以通過單獨執行\R來恢復預設設置。

quit(\q)

退出當前session連接。

auto-rehash(#)

在使用mysql客戶端連接伺服器時，預設情況下會自動收集一些元數據信息，在後續輸入SQL命令時可以通過tab鍵補齊命令，比如補齊表名字、列名字。

Great[test]> select * from tt(此時按tab鍵)
tt1     tt1.c1  tt1.c2  tt1.dd  tt1.id  ttt     ttt.id

但是如果在session中新建了表，或者給表上新增了欄位，是無法通過tab鍵補齊的，這時通過\#命令刷新元數據信息，之後就能對新的DDL結構進行補齊

Great[test]> create table tt2 as select * from tt1;
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

Great[test]> select * from tt(此時按tab鍵)
tt1     tt1.c1  tt1.c2  tt1.dd  tt1.id  ttt     ttt.id  
Great[test]> \#
Great[test]> select * from tt(此時按tab鍵)
tt1     tt1.c1  tt1.c2  tt1.dd  tt1.id  tt2     tt2.c1  tt2.c2  tt2.dd  tt2.id  ttt     ttt.id  
Great[test]> select * from tt

source(.)

命令後需要跟一個文件名，\. filename會對filename文件中的內容按標準SQL進行解析執行。

status(\s)

輸出本次連接的相關信息及伺服器的一些信息，如果連接時指定了--safe-updates，還會輸出查詢限制相關的信息，\s輸出了很多有用的信息，可仔細閱讀。

[#8#root@Great81 ~ 20:26:13]8 m5 3306 --safe-updates 
greatsql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 916197

********

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Great[(none)]> \s
--------------
********

Connection id:  916197
Current database: 
Current user:  [email protected]
SSL:   Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:  stdout
Using outfile:  ''
Using delimiter: ;
**********
Protocol version: 10
Connection:  127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db     characterset: utf8mb4
Client characterset: utf8mb4
Conn.  characterset: utf8mb4
TCP port:  3306
Binary data as:  Hexadecimal
Uptime:   51 days 9 hours 5 min 13 sec

Threads: 16  Questions: 4354604  Slow queries: 339  Opens: 19188  Flush tables: 3  Open tables: 6095  Queries per second avg: 0.980

Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000

--------------

Great[(none)]>Great

system(!)

使用預設命令解釋器執行給定命令，簡單的說就是返回到操作系統執行\!之後的命令，比如下麵

Great[(none)]> \! date
2022年 11月 16日 星期三 20:32:34 CST
Great[(none)]> \! pwd
/root
Great[(none)]> \! cd /Great
Great[(none)]> \! vmstat -w
procs -----------------------memory---------------------- ---swap-- -----io---- -system-- --------cpu--------
 r  b         swpd         free         buff        cache   si   so    bi    bo   in   cs  us  sy  id  wa  st
 1  0            0       358508            0      3117236    0    0   486    20    0    0   5   3  92   0   0
Great[(none)]>

在8.0.19之前，只支持在unix系統中使用該命令，到8.0.19後，在windows中也支持了該命令。

tee(\T)

將所有執行的SQL命令及輸出結果保存到指定文件中。這在調測、生產維護過程中，都是非常有用的一個功能，特別是一些安全要求高的環境中，控制台只能顯示幾十行命令時，想要查找之前執行的命令及執行的結果比較難，此時就能用上\T了。

Great[(none)]> \T /root/a.log
Logging to file '/root/a.log'
Great[(none)]> select * from test.tt1;
+------+------+------+------+
| id   | dd   | c1   | c2   |
+------+------+------+------+
|    1 | aaa  | NULL | NULL |
|    2 | bbb  | NULL | NULL |
|    3 | NULL | NULL | NULL |
|    4 | 8    | NULL | NULL |
|    5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[(none)]> \! cat /root/a.log
Great[(none)]> select * from test.tt1;
+------+------+------+------+
| id   | dd   | c1   | c2   |
+------+------+------+------+
|    1 | aaa  | NULL | NULL |
|    2 | bbb  | NULL | NULL |
|    3 | NULL | NULL | NULL |
|    4 | 8    | NULL | NULL |
|    5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[(none)]>

如果想要記錄每個人登錄資料庫，做了哪些操作，由於tee不支持根據當前時間動態產生日誌文件名，我們可以這樣設置

## 首先創建一個log目錄
mkdir -p /Great/logs/client/

## 然後設置環境變數，為了連接安全，建議使用--login-path的方式進行登錄，我這裡使用的是gdb1登錄。

echo "alias mlogin='mysql --login-path=gdb1 --tee /Great/logs/client/\$(date +%Y-%m-%d_%H-%M-%S).log'" >> ~/.bashrc; source ~/.bashrc

[#15#root@Great81 /Great/logs/client 20:48:53]15 echo "alias mlogin='mysql --login-path=gdb1 --tee /Great/logs/client/$(date +\"%Y-%m-%d_%H-%M\").log'" >> ~/.bashrc; source ~/.bashrc

## 通過設置的alias登錄資料庫

[#16#root@Great81 /Great/logs/client 20:49:43]16 mlogin 
Logging to file '/Great/logs/client/2022-11-16_20-49.log'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 916482
Great

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables from tpcc;
+------------------+
| Tables_in_tpcc   |
+------------------+
| bmsql_config     |
| bmsql_customer   |
| bmsql_district   |
| bmsql_history    |
| bmsql_item       |
| bmsql_new_order  |
| bmsql_oorder     |
| bmsql_order_line |
| bmsql_stock      |
| bmsql_warehouse  |
| datatypes1       |
| datatypes10      |
| datatypes11      |
| datatypes2       |
| datatypes3       |
| datatypes4       |
| datatypes5       |
| datatypes6       |
| datatypes7       |
| datatypes8       |
| datatypes9       |
+------------------+
21 rows in set (0.00 sec)

mysql>

我們發現指定的日誌目錄中，已經有了日誌文件，並且記錄了所有執行的SQL及輸出結果

[#9#root@Great81 ~ 20:51:43]9 cd /Great/logs/client/
[#10#root@Great81 /Great/logs/client 20:51:48]10 ll
總用量 8
-rw-r--r-- 1 root root  627 11月 16 20:48 2022-11-16_20-48.log
-rw-r--r-- 1 root root 3214 11月 16 20:50 2022-11-16_20-49.log
[#11#root@Great81 /Great/logs/client 20:51:48]11

notee(\t)

取消\T設置，不再記錄操作信息到日誌文件中。

use(\u)

切換當前連接的database。

warnings(\W)

在執行完SQL語句後，立即顯示warning信息，不需要再手動執行show warnings;了。

nowarnings(\w)

在執行完SQL語句後，不立即顯示warning信息，需要手動執行show warnings;才會顯示warning信息。

resetconnection(\x)

以新連接的狀態重新連接到伺服器，並且進行一些信息的清理及複位，不需要再次進行許可權驗證。主要影響如下信息：

回滾所有活動的事務，並重置自動提交模式。
所有DML鎖均已釋放。
所有TEMPORARY table 均已關閉(並刪除)。
會話系統變數將重新初始化。
用戶變數設置丟失。
準備好的語句被釋放。
HANDLER關閉。
LAST_INSERT_ID 置為 0。
用 GET_LOCK 釋放。

可以參考8.0.26的代碼libmysql.cc中4429~4444行

int STDCALL mysql_reset_connection(MYSQL *mysql) {
  DBUG_TRACE;
  if (simple_command(mysql, COM_RESET_CONNECTION, nullptr, 0, 0))
    return 1;
  else {
    mysql_detach_stmt_list(&mysql->stmts, "mysql_reset_connection");
    /* reset some of the members in mysql */
    mysql->insert_id = 0;
    mysql->affected_rows = ~(uint64_t)0;
    free_old_query(mysql);
    mysql->status = MYSQL_STATUS_READY;
    mysql_extension_bind_free(MYSQL_EXTENSION_PTR(mysql));
    return 0;
  }
}

query_attributes

通過query_attributes var1 value1 var2 value2來設置變數，然後通過mysql_query_attribute_string('var1')返回變數值value1，目前來看，並沒有發現特殊的使用方法，畢竟我們也可以通過set @var1='value1'來設置。

使用體驗

mysql Client Commands在某些場景下，可以極大的提高我們的操作效率，相對於完整的命令ego、pager、edit等，我更喜歡使用他們的簡寫命令\G、\P、\e，因為有時候完整命令不確定如何正確使用，會導致不生效，但是簡寫命令，是一定會生效的。

參考文章

MySQL 8.0 Reference Manual 4.5.1.2 mysql Client Commands

MySQL 8.0 Reference Manual 9.6 Query Attributes

Enjoy GreatSQL