mycli
BlackLivesMatter
We value the diversity of our community. We strive to amplify the voices of the oppressed to eradicate racism and xenophobia. We ask our community to stand together in support of the Black community.
MyCLI is a command line interface for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting.
- Source: https://github.com/dbcli/mycli
- Bug tracker: https://github.com/dbcli/mycli/issues
(注意, 可以直接输入密码)
mycli, 一款支持自动补全代码的命令工具, 支持ssh, 以及更多细节的优化, 如大量结果不会一次全部返回, 危险操作提示等.
一. 安装
需要python环境
pip install mycli
添加到配置, 即可在终端上轻松使用了.
二. 使用
@Lian ➜ ~\Desktop ( base 3.9.12) 19.498s mycli --help
Usage: mycli [OPTIONS] [DATABASE]
A MySQL terminal client with auto-completion and syntax highlighting.
Examples:
- mycli my_database
- mycli -u my_user -h my_host.com my_database
- mycli mysql://my_user@my_host.com:3306/my_database
Options:
-h, --host TEXT Host address of the database.
-P, --port INTEGER Port number to use for connection. Honors
$MYSQL_TCP_PORT.
-u, --user TEXT User name to connect to the database.
-S, --socket TEXT The socket file to use for connection.
-p, --password TEXT Password to connect to the database.
--pass TEXT Password to connect to the database.
--ssh-user TEXT User name to connect to ssh server.
--ssh-host TEXT Host name to connect to ssh server.
--ssh-port INTEGER Port to connect to ssh server.
--ssh-password TEXT Password to connect to ssh server.
--ssh-key-filename TEXT Private key filename (identify file) for the
ssh connection.
--ssh-config-path TEXT Path to ssh configuration.
--ssh-config-host TEXT Host to connect to ssh server reading from ssh
configuration.
--ssl-ca PATH CA file in PEM format.
--ssl-capath TEXT CA directory.
--ssl-cert PATH X509 cert in PEM format.
--ssl-key PATH X509 key in PEM format.
--ssl-cipher TEXT SSL cipher to use.
--ssl-verify-server-cert Verify server's "Common Name" in its cert
against hostname used when connecting. This
option is disabled by default.
-V, --version Output mycli's version.
-v, --verbose Verbose output.
-D, --database TEXT Database to use.
-d, --dsn TEXT Use DSN configured into the [alias_dsn]
section of myclirc file.
--list-dsn list of DSN configured into the [alias_dsn]
section of myclirc file.
--list-ssh-config list ssh configurations in the ssh config
(requires paramiko).
-R, --prompt TEXT Prompt format (Default: "\t \u@\h:\d> ").
-l, --logfile FILENAME Log every query and its results to a file.
--defaults-group-suffix TEXT Read MySQL config groups with the specified
suffix.
--defaults-file PATH Only read MySQL options from the given file.
--myclirc PATH Location of myclirc file.
--auto-vertical-output Automatically switch to vertical output mode
if the result is wider than the terminal
width.
-t, --table Display batch output in table format.
--csv Display batch output in CSV format.
--warn / --no-warn Warn before running a destructive query.
--local-infile BOOLEAN Enable/disable LOAD DATA LOCAL INFILE.
-g, --login-path TEXT Read this path from the login file.
-e, --execute TEXT Execute command and quit.
--init-command TEXT SQL statement to execute after connecting.
--charset TEXT Character set for MySQL session.
--password-file PATH File or FIFO path containing the password to
connect to the db if not specified otherwise.
--help Show this message and exit.
2.1 中文乱码的问题
使用环境:
os: win10, x64 pro, en
mysql: 8.0.30 MySQL Community Server - GPL |
powershell: 7.x (原生支持
utf-8, 不是win10预装的windows Powershell(5.x))
mycli, 默认的状态(mysql的默认的终端是不会出现这个乱码的问题), 查询中文返回结果乱码.
不管是修改my.ini设置, 还是添加这个--charset=utf8mb4, 均无效, 返回的查询内容依然是乱码
[client]
# manual add
default-character-set = utf-8
# 不管是utf8mb4, 还是utf-8, 打开还是乱码
# 无效, 依然还是乱码
mycli -u root -h localhost -P 3306 -pmysql2022 --charset=utf8mb4
# 不管是cmd还是powershell(7, 已经默认使用utf-8)
2.2 解决
由于Windows尚未全面支持utf-8, 该特性还是处于beta状态, 在win10上(不建议全局变更该特性, 不确定其潜在的问题).
@Lian ➜ ~\Desktop ( base 3.9.12) chcp
Active code page: 936
# 临时变更
chcp 65001
MySQL root@localhost:test_db> status;
--------------
mycli 1.25.0, running on CPython 3.9.12
+----------------------+-------------------------------------+
| Connection id: | 11 |
| Current database: | test_db |
| Current user: | root@localhost |
| Current pager: | System default |
| Server version: | 8.0.30 MySQL Community Server - GPL |
| Protocol version: | 10 |
| Connection: | localhost via TCP/IP |
| Server characterset: | utf8mb4 |
| Db characterset: | utf8mb4 |
| Client characterset: | utf8mb3 |
| Conn. characterset: | utf8mb3 |
| TCP port: | 3306 |
| Uptime: | 11 min 29 sec |
+----------------------+-------------------------------------+
Connections: 1 Queries: 38 Slow queries: 0 Opens: 240 Flush tables: 3 Open tables: 159 Queries per second avg:
0.055
-------------
只需要在打开powershell, cmd时自动执行chcp 65001
打开注册表 -> 找到Command Processor -> 添加autorun new string
但是这里又有引发另一个问题:
PostgreSQL会出现问题, 在powershell(7.x) 下会出现乱码, 但是这种问题在cmd下有所不同.
用户 postgres 的口令：
psql (15.2)
警告：来自 Windows 代码页 (65001) 的控制台代码页 (936) 的差异
8-bit 字符可能无法正常工作。请查阅 psql 参考
页 "Windows 用户注意事项" 的详细说明.
输入 "help" 来获取帮助信息.
chcp 936
# 执行后, 依然还是会出现乱码, 中文内容
- Add
--charsetoption for commandline by seamile - Pull Request #915 - dbcli/mycli (github.com) - How I should set my Terminal setting on windows 10 to make it work well? (Chinese garbled) - Issue #741 - dbcli/mycli (github.com)
- How to use unicode characters in Windows command line? - Stack Overflow
- Chinese can't display properly - Issue #303 - dbcli/mycli (github.com)
