MySQL客户端连接工具 mysql

醉生萌死

修改于 2018-12-10 04:57:18

10.1K0

修改于 2018-12-10 04:57:18

文章被收录于专栏：醉生梦死

MySQL 版本信息：

[root@db02 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

Usage: mysql [OPTIONS] [database]
选项 -u, --user=name 指定用户名 -p, --password[=pwd] 指定密码 -h, --host=ip 指定服务器IP或者域名 -P, --port=3306 指定连接端口

默认为连接本机（localhost）上的3306端口

[root@db02 ~]# mysql -uroot -p123

[root@db02 ~]# mysql -uroot -p
Enter password: 

[root@db02 ~]# mysql --user=root --password=123

[root@db02 ~]# mysql --user=root --password
Enter password:

查看当前登录的用户信息 root@localhost

[root@db02 ~]# mysql -uroot -p123

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)

指定连接哪台服务器上的mysql，指定端口号

[root@db02 ~]# mysql -uroot -p123 -hlocalhost -P3306

[root@db02 ~]# mysql -uroot -p123 -h10.0.0.52 -P3306

查看当前登录的用户信息 root@10.0.0.%

[root@db02 ~]# mysql -uroot -p123 -h10.0.0.52 -P3306

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@10.0.0.%  |
+----------------+
1 row in set (0.00 sec)

设定客户端字符集 --default-character-set=gbk

[root@db02 ~]# mysql -uroot -p123 

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.6.36/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

加上参数连接

[root@db02 ~]# mysql -uroot -p123 --default-character-set=gbk

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | gbk                                       |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.6.36/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

执行选项-e

[root@db02 ~]# mysql -uroot -p123 -e 'show databases'
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| world              |
+--------------------+

多个sql语句中间用英文分号（;）隔开

[root@db02 ~]# mysql -uroot -p123 -e 'show tables from mysql;select host,user from mysql.user;'
Warning: Using a password on the command line interface can be insecure.
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
.............................
.............................
| user                      |
+---------------------------+
+-----------+------+
| host      | user |
+-----------+------+
| 10.0.0.%  | root |
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+

格式化选项

-E, --vertical	将输出方式按照字段顺序竖着显示
-s, --silent	去掉mysql中的线条框显示

[root@db02 ~]# mysql -uroot -p123 -e 'show databases' -E

*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: test
*************************** 5. row ***************************
Database: world

[root@db02 ~]# mysql -uroot -p123 -e 'show databases' -s

Database
information_schema
mysql
performance_schema
test
world

[root@db02 ~]# mysql -uroot -p123 -e 'select user,host from mysql.user' -s

user	host
root	10.0.0.%
root	127.0.0.1
root	localhost

mysql -uroot -p123 -s

[root@db02 ~]# mysql -uroot -p123 -s

mysql> select user,host from mysql.user;
user	host
root	10.0.0.%
root	127.0.0.1
root	localhost

错误处理选项

-f, --force	强制执行sql
-v, --verbose	显示更多信息
--show-warnings	显示警告信息

这三个经常是一起使用的

如果执行脚本有错，可以使用 -f 强制执行，而不是在遇到错误时直接终止

sql测试文本
[root@db02 ~]# cat a.sql 
insert into stu values(1);
insert into stu values(2aa);
insert into stu values(3);

遇到错误直接终止运行

[root@db02 ~]# mysql -uroot -p123 test < a.sql 
ERROR 1054 (42S22) at line 2: Unknown column '2aa' in 'field list'

[root@db02 ~]# mysql -uroot -p123 test -e 'select * from stu'
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|    1 |
+------+

使用 -f 参数

[root@db02 ~]# mysql -uroot -p123 test -f < a.sql 
ERROR 1054 (42S22) at line 2: Unknown column '2aa' in 'field list'

[root@db02 ~]# mysql -uroot -p123 test -e 'select * from stu'
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|    1 |
|    1 |
|    3 |
+------+

使用-f -v参数

[root@db02 ~]# mysql -uroot -p123 test -f -v < a.sql 
--------------
insert into stu values(1)
--------------
--------------
insert into stu values(2aa)
--------------
ERROR 1054 (42S22) at line 2: Unknown column '2aa' in 'field list'
--------------
insert into stu values(3)
--------------

使用-f -v --show-warnings参数

[root@db02 ~]# cat a.sql 
insert into stu values(1);
insert into stu values(222222222222222222222);
insert into stu values(3);

[root@db02 ~]# mysql -uroot -p123 test -f -v --show-warnings < a.sql 
--------------
insert into stu values(1)
--------------
--------------
insert into stu values(222222222222222222222)
--------------
ERROR 1264 (22003) at line 2: Out of range value for column 'id' at row 1
Error (Code 1264): Out of range value for column 'id' at row 1
Error (Code 1264): Out of range value for column 'id' at row 1
--------------
insert into stu values(3)
--------------