1、概述
mysql有用户变量和系统变量，系统变量又分全局(global)和会话(session)
查看set命令
root@localhost: 01:23 [7308][(none)]>help set;
Name: 'SET'
Description:
Syntax:
SET variable = expr [, variable = expr] ...
variable: {
user_var_name
| param_name
| local_var_name
| {GLOBAL | @@GLOBAL.} system_var_name
| [SESSION | @@SESSION. | @@] system_var_name
}
SET ONE_SHOT system_var_name = expr
SET syntax for variable assignment enables you to assign values to
different types of variables that affect the operation of the server or
clients:
o User-defined variables. See
https://dev.mysql.com/doc/refman/5.6/en/user-variables.html.
o Stored procedure and function parameters, and stored program local
variables. See
https://dev.mysql.com/doc/refman/5.6/en/stored-program-variables.html
.
o System variables. See
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html.
System variables also can be set at server startup, as described in
https://dev.mysql.com/doc/refman/5.6/en/using-system-variables.html.
URL: https://dev.mysql.com/doc/refman/5.6/en/set-variable.html
root@localhost: 01:24 [7308][(none)]>
2、用户变量
@是用户变量
设置用户变量set @addr='chengdu'
查询用户变量select @addr或 select @addr from dual
3、系统变量
@@是系统变量
查看系统全局变量select @@global.变量名，查看系统会话变量select @@session.变量名。 直接查询@@变量名默认查会话变量，如果只有全局变量就是查的全局变量
4、查看系统参数变量
使用show和select
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
root@localhost: 05:08 [7308][(none)]>show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost: 05:08 [7308][(none)]>select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>select @@global.sql_log_bin;
+----------------------+
| @@global.sql_log_bin |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost: 05:09 [7308][(none)]>select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>select @@global.sql_log_bin;
+----------------------+
| @@global.sql_log_bin |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
root@localhost: 05:09 [7308][(none)]>
设set global sql_log_bin=0报错说明只支持设置会话自己的参数变量值
root@localhost: 05:09 [7308][(none)]>set global sql_log_bin=0;
ERROR 1231 (42000): Variable 'sql_log_bin' can't be set to the value of '0'
root@localhost: 05:10 [7308][(none)]>
查询global_variables和session_variables表
root@localhost: 05:16 [7308][(none)]>show tables from information_schema like '%variables%';
+--------------------------------------------+
| Tables_in_information_schema (%variables%) |
+--------------------------------------------+
| GLOBAL_VARIABLES |
| SESSION_VARIABLES |
+--------------------------------------------+
2 rows in set (0.00 sec)
root@localhost: 05:16 [7308][(none)]>
root@localhost: 05:19 [7308][(none)]>select * from information_schema.session_variables where variable_name like '%sql_log_bin%';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| SQL_LOG_BIN | OFF |
+---------------+----------------+
1 row in set (0.00 sec)
root@localhost: 05:19 [7308][(none)]>
root@localhost: 05:19 [7308][(none)]>select * from information_schema.global_variables where variable_name like '%sql_log_bin%';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| SQL_LOG_BIN | ON |
+---------------+----------------+
1 row in set (0.00 sec)
root@localhost: 05:19 [7308][(none)]>
5、查看状态值
使用show系统状态值
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
root@localhost: 05:13 [7308][(none)]>show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 44342 |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost: 05:13 [7308][(none)]>show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 44344 |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost: 05:13 [7308][(none)]>show global status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 44356 |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost: 05:14 [7308][(none)]>show session status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 44365 |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost: 05:14 [7308][(none)]>
系统状态值不支持select方式
root@localhost: 05:14 [7308][(none)]>select @@uptime;
ERROR 1193 (HY000): Unknown system variable 'uptime'
root@localhost: 05:14 [7308][(none)]>
查询global_status和session_status表
root@localhost: 05:16 [7308][(none)]>show tables from information_schema like '%status%';
+-----------------------------------------+
| Tables_in_information_schema (%status%) |
+-----------------------------------------+
| GLOBAL_STATUS |
| SESSION_STATUS |
+-----------------------------------------+
2 rows in set (0.00 sec)
root@localhost: 05:16 [7308][(none)]>
root@localhost: 05:27 [7308][(none)]>select * from information_schema.global_status where variable_name like 'uptime';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME | 45168 |
+---------------+----------------+
1 row in set (0.00 sec)
root@localhost: 05:27 [7308][(none)]>
root@localhost: 05:27 [7308][(none)]>select * from information_schema.session_status where variable_name like 'uptime';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME | 45187 |
+---------------+----------------+
1 row in set (0.00 sec)
root@localhost: 05:27 [7308][(none)]>
标签：none,set,05,--,7308,global,session,root,localhost From： https://www.cnblogs.com/hsjz-xinyuan/p/16919635.html