mysql中使用show variables同时查询多个参数值?show variables的使用?
需求描述:
今天在查mysq关于连接数的问题,想要通过一个show variables命令同时查出来多个值.在此记录下.
操作过程:
1.通过show variables语句的like可以匹配多个值或者某个值
mysql> show variables like 'max_connections'; #这里默认的就是对Variable_name进行匹配,这里是准确匹配. +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'socket'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | socket | /tmp/mysql.sock | +---------------+-----------------+ 1 row in set (0.00 sec)
2.通过%通配符进行匹配
mysql> show variables like '%connec%'; #通过百分号(%)这个通配符进行匹配,可以匹配多项. +-----------------------------------------------+-------------------+ | Variable_name | Value | +-----------------------------------------------+-------------------+ | character_set_connection | latin1 | | collation_connection | latin1_swedish_ci | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 100 | | max_connections | 151 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 512 | +-----------------------------------------------+-------------------+ 9 rows in set (0.00 sec)
mysql> show variables like 'innodb_thread%'; #%通配符在结束处.
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like '%version'; #%通配符在开始处.
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| innodb_version | 5.7.21 |
| protocol_version | 10 |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.21-log |
+------------------+---------------+
4 rows in set (0.00 sec)
3.使用where子句进行匹配查询
mysql> show variables where variable_name = 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.7.21-log | +---------------+------------+ 1 row in set (0.01 sec) mysql> show variables where variable_name in ('version','innodb_version'); +----------------+------------+ | Variable_name | Value | +----------------+------------+ | innodb_version | 5.7.21 | | version | 5.7.21-log | +----------------+------------+ 2 rows in set (0.00 sec) mysql> show variables where value like '5.7%'; +----------------+------------+ | Variable_name | Value | +----------------+------------+ | innodb_version | 5.7.21 | | version | 5.7.21-log | +----------------+------------+ 2 rows in set (0.00 sec) mysql> show variables where variable_name = 'version' and value = '5.7'; Empty set (0.00 sec) mysql> show variables where variable_name = 'version' and value like '5.7%'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.7.21-log | +---------------+------------+ 1 row in set (0.00 sec)
备注:使用where子句的方式就和在SQL语句中使用where的方式是一样的.
4.使用_(下划线)匹配单个字符及将下划线当做普通字符使用
mysql> show variables like 'versio_\_c%'; #第一个是通配符,匹配一个字符,第二个下划线当做普通字符使用,所以在前面加上了转义字符\,将其转义为普通字符. +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.12 | +-------------------------+------------------------------+ 3 rows in set (0.00 sec)
show variables语法:
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]
小结:
- show variables主要是用来查看系统变量的值.
- 执行SHOW VARIABLES命令不需要任何权限,只要求能够连接到服务器就可以.
- 使用like语句表示用variable_name进行匹配.
- %百分号通配符可以用在匹配模式中的任何位置
文档创建时间:2018年7月17日11:33:27
