MySQL中用户和系统变量设置获取使用@和@@ global和session--笔记

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)]>