增强MySQL CLI的可读性和灵活性

最新推荐文章于 2022-10-19 15:57:36 发布

weixin_34102807

最新推荐文章于 2022-10-19 15:57:36 发布

阅读量180

点赞数

文章标签：数据库开发工具 python

原文链接：https://my.oschina.net/u/1440383/blog/213450

版权

2019独角兽企业重金招聘Python工程师标准>>>

MySQL的命令行其实也能像ORACLE的sql/plus一样灵活多变

一、将mysql下执行的内容导出成文件（非into outfile）

tee /path/file 指定导出文件名称

notee 停止导出

root@localhost:wm_ztcj> tee /tmp/user.log
Logging to file '/tmp/user.log'
root@localhost:wm_ztcj> select user,host,password from mysql.user;
+-----------------+-----------------------+-------------------------------------------+
| user            | host                  | password                                  |
+-----------------+-----------------------+-------------------------------------------+
| root            | localhost             | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | localhost.localdomain | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | 127.0.0.1             | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | ::1                   | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| repl            | %                     | *50C610800D1B52DF4C81430612A0A4DF1B5487DE |
| wg_user         | %                     | *C91B3E8D9994614CDC307B3DA6E04616A780E21E |
| wm_sns_user     | %                     | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| wm_sns_user     | 192.168.0.%           | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| wm_sns_user     | localhost             | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| checker         | %                     | *9A85346FF112526836DB4101A6794463E03D8727 |
| zabbix          | localhost             | *9CBC0CDFECC6EDB55518791A797EAEE799141804 |
| wm_ztcj         | %                     | *E1CBA20F460E6A92300A2D40049990345A449EEA |
| wm_develop_test | %                     | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| wm_develop_test | localhost             | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| wm_ztcj         | localhost             | *E1CBA20F460E6A92300A2D40049990345A449EEA |
| wm_develop_test | 192.168.0.%           | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| root            | 192.168.0.%           | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
+-----------------+-----------------------+-------------------------------------------+
17 rows in set (0.01 sec)
root@localhost:wm_ztcj> notee
Outfile disabled.
root@localhost:wm_ztcj> exit
Bye
[mysql@WM_ZTCJ ~]$ more /tmp/user.log 
root@localhost:wm_ztcj> select user,host,password from mysql.user;
+-----------------+-----------------------+-------------------------------------------+
| user            | host                  | password                                  |
+-----------------+-----------------------+-------------------------------------------+
| root            | localhost             | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | localhost.localdomain | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | 127.0.0.1             | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| root            | ::1                   | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
| repl            | %                     | *50C610800D1B52DF4C81430612A0A4DF1B5487DE |
| wg_user         | %                     | *C91B3E8D9994614CDC307B3DA6E04616A780E21E |
| wm_sns_user     | %                     | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| wm_sns_user     | 192.168.0.%           | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| wm_sns_user     | localhost             | *14A720739D63BA762B59848FBB67436EEA6F1D46 |
| checker         | %                     | *9A85346FF112526836DB4101A6794463E03D8727 |
| zabbix          | localhost             | *9CBC0CDFECC6EDB55518791A797EAEE799141804 |
| wm_ztcj         | %                     | *E1CBA20F460E6A92300A2D40049990345A449EEA |
| wm_develop_test | %                     | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| wm_develop_test | localhost             | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| wm_ztcj         | localhost             | *E1CBA20F460E6A92300A2D40049990345A449EEA |
| wm_develop_test | 192.168.0.%           | *2B084359A06856B7758E41D33BE6D0DD7AB79CCB |
| root            | 192.168.0.%           | *728A5A63A7262B3CDCF9F016C5C8922964944607 |
+-----------------+-----------------------+-------------------------------------------+
17 rows in set (0.01 sec)

二、使用edit编辑

有时候数据一整条很长的命令之后，希望重新编辑一下，如果光靠光标左右移动，效果很差，可以直接edit编辑

root@localhost:wm_ztcj_develop> select * from wm_user_dynamic2 limi 1\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
root@localhost:wm_ztcj_develop> edit

这样就进入了VIM（我将默认的VI编辑器变成了vim）中编译命令编辑完成之后退出

可以手动指定 set @editor='vim';

      1 select * from wm_user_dynamic2 limit 1
      ~                                                                                                                                                                                                    
/tmp/sql183PTC [+]                                                                                                                                                                                   
"/tmp/sql183PTC" 1L, 39C written

    -> \G --输入；或者\G继续执行之前的命令
    *************************** 1. row ***************************
               ID: 2800626
              UID: 1
             OUID: 1
             CUID: NULL
              BID: NULL
              CID: NULL
             BCID: NULL
             HDID: NULL
             PCID: NULL
             CDID: NULL
              RID: 2222
      ADDTERMINAL: 4
            STATE: NULL
          DISPLAY: 0
             TYPE: 1
          ADDTIME: 2014-03-26 15:04:38
       ADDADMINID: NULL
            ADDIP: NULL
   LASTUPDATETIME: NULL
     LASTUPDATEIP: NULL
LASTUPDATEADMINID: NULL

三、MySQL的Pager（页面显示风格）

有时数据量大一次显示完不方便查阅，希望能像more一样读取：

root@localhost:wm_ztcj_develop> pager more
PAGER set to 'more'

root@localhost:wm_ztcj_develop> show status;
+-----------------------------------------------+----------------+
| Variable_name                                 | Value          |
+-----------------------------------------------+----------------+
| Aborted_clients                               | 1666           |
| Aborted_connects                              | 28776          |
| Binlog_cache_disk_use                         | 4              |
| Binlog_cache_use                              | 339021         |
| Binlog_stmt_cache_disk_use                    | 11387          |
| Binlog_stmt_cache_use                         | 67796          |
| Bytes_received                                | 643            |
| Bytes_sent                                    | 17685          |
| Com_admin_commands                            | 0              |
| Com_assign_to_keycache                        | 0              |
| Com_alter_db                                  | 0              |
| Com_alter_db_upgrade                          | 0              |
| Com_alter_event                               | 0              |
| Com_alter_function                            | 0              |
| Com_alter_procedure                           | 0              |
| Com_alter_server                              | 0              |
| Com_alter_table                               | 0              |
| Com_alter_tablespace                          | 0              |
| Com_drop_server                               | 0              |
| Com_drop_table                                | 0              |
| Com_drop_trigger                              | 0              |
| Com_drop_user                                 | 0              |
| Com_drop_view                                 | 0              |
--More--

如果希望从结果中过滤关键字，也可将pager替换成grep工具：

例如想查看innodb中checkponit位置变化

root@localhost:wm_ztcj_develop> pager grep checkpoint
PAGER set to 'grep checkpoint'

root@localhost:wm_ztcj_develop> show engine innodb status\G select sleep(3);show engine innodb status\G
Last checkpoint at  437227579829
1 row in set (0.05 sec)

1 row in set (3.00 sec)

Last checkpoint at  437243479330
1 row in set (0.05 sec)

为了不影响下次查询的使用，需要将pager 设置成标准输出，或者之前的配置

root@localhost:wm_ztcj_develop> pager more
PAGER set to 'more'
root@localhost:wm_ztcj_develop> \n  --设置成标准输出
PAGER set to stdout
root@localhost:wm_ztcj_develop> \P  --设置成之前的输出方式
PAGER set to 'more'

四、给命令行上色

需要下载一个工具grc，并使用配置文件color-mysql-console

[mysql@WM_ZTCJ ~]$ wget http://korpus.juls.savba.sk/~garabik/software/grc/grc_1.5.tar.gz
[mysql@WM_ZTCJ ~]$ tar zvxf grc_1.5.tar.gz 
[mysql@WM_ZTCJ ~]$ cd grc-1.5/
[mysql@WM_ZTCJ ~]$ ./install.sh /usr/local/grc  --指定你希望安装的路径
安装完毕之后，上传color-mysql-console的配置文件
下载地址：
[mysql@WM_ZTCJ ~]$ wget https://codeload.github.com/nitso/colour-mysql-console/zip/master
解压后将配置文件放在当前用户的家目录下即可
[mysql@WM_ZTCJ ~]$ echo "alias mysql=$(echo -e 'mysql --prompt="\x1B[31m\\u\x1B[34m@\x1B[32m\\h\x1B[0m:\x1B[36m\\d>\x1B[0m "')" >> .bash_profile

再次登录mysql：

是不是要比枯燥的黑白色好多了？

五、pager md5sum 校验

如果在改写SQL之后希望校验下改写的SQL执行结果是否与之前的一直，可以如下使用：

root@localhost:wm_ztcj> pager md5sum
PAGER set to 'md5sum'
root@localhost:wm_ztcj> select * from wm_user_info order by id desc limit 1;select * from wm_user_info where id=(select max(id) from wm_user_info);
125138b7769186a6834ad030ad928f74  -
1 row in set (0.41 sec)

125138b7769186a6834ad030ad928f74  -
1 row in set (0.00 sec)

这样就可以避免逐条比较记录了，直接获取结果。

转载于:https://my.oschina.net/u/1440383/blog/213450