MySQL 5.6中的变量

olga

　　这篇文章介绍的是MySQL  5.6中的变量，基本是我以前学习MySQL 5.6手册时整理而来。
基础概念
　　MySQL中的变量可分为以下几种：
　　1、 MySQL系统变量：该类型变量反映了MySQL服务器是如何配置的。每一个系统变量都有一个默认值。系统变量可以在MySQL配置文件中进行设置，或使用MySQL启动选项进行设置。大多数系统变量都可以在MySQL运行时使用SET命令进行动态修改。
　　2、 MySQL状态变量：该类型变量反映了MySQL服务器的运行状态。
　　变量按作用范围可分为：
　　1、 全局变量（global）：对MySQL服务器全局生效。
　　2、 会话变量（session）：仅对单个客户端会话有影响。
　　有些MySQL选项只有全局变量或会话变量，而有些则同时有全局变量和会话变量。全局和会话变量的关联是这样的：
　　1、 当MySQL服务启动时，它会初始每一个全局变量为默认值。这个默认值可以在配置文件中或使用MySQL启动选项进行修改。
　　2、 MySQL也会为每一个客户端连接维护一个会话变量集。当客户端连接MySQL服务时，它所有的会话变量都会初始化为相应全局变量的当前值。对于少数系统变量，它的会话值可能不会被初始化为相应全局变量的值，具体可查询MySQL官方手册。
　　系统变量根据是否可动态修改（dynamic），分为：

• 　　动态变量：可动态修改变量的值。修改后无需重启MySQL服务即可生效。
  
• 　　非动态变量：不可动态修改变量的值。修改后需重启MySQL服务才可生效。
  

　　完整的变量属性列表可以在MySQL参考手册中查询得到。比如，可以了解到某一个变量是系统变量还是状态变量，是全局变量还是还是变量还是两种都有，是动态变量还是非动态变量。
查看变量的值
　　要查看MySQL的所有默认系统变量值（忽略配置文件中的设置），使用命令：
　　[root@gw ~]# mysqld --no-defaults --verbose --help
　　要查看MySQL的所有默认系统变量值（读取配置文件中的设置后的），使用命令：
　　[root@gw ~]# mysqld --verbose --help
　　SHOW命令查看系统变量的值，语法格式为：
　　mysql> SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

• 　　当使用关键字GLOBAL时，是查看所有全局变量的当前值：
  

　　mysql> SHOW GLOBAL VARIABLES;

• 　　当不使用关键字或使用关键字SESSION时，是查看所有会话变量的当前值（有会话变量的则显示当前会话变量的值，没有的则显示全局变量的值）：
  

　　mysql> SHOW VARIABLES;
　　mysql> SHOW SESSION VARIABLES;

• 　　可以加上LIKE语句并使用通配符 % 或 _ 来匹配特定的变量。百分号 % 匹配任意数量字符，下划线 _ 匹配单个字符。示例：
  

　　mysql> SHOW VARIABLES LIKE 'innodb_buffer%';
　　mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer%';
　　另外，也可以使用 SELECT 语句来查看系统变量的值。比如：
　　mysql> SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
　　对于在表达式中使用 @@var_name 这种形式引用变量的值（而不是使用 @@global. 或 @@session.），MySQL会返回会话变量的值（如果存在），否则会返回全局变量的值。有 @@global. 前缀的是显式指定查看全局变量的值，有 @@session. 前缀的是显式指定查看会话变量的值。
　　SHOW命令查看状态变量的值，语法格式为：
　　mysql> SHOW [GLOBAL | SESSION] STATUS [like_or_where]

• 　　当使用关键字GLOBAL时，显示的是全局状态值。一个全局状态变量可能表示的是服务器自身的一些状态（比如，Aborted_connects），或者是所有连接会话的聚合状态（比如，Bytes_received 和 Bytes_sent）。如果一个变量没有全局状态值，显示的就是会话状态值。
  

　　mysql> SHOW GLOBAL STATUS;

• 　　当不使用关键字或使用关键字SESSION时，显示的是会话状态值。如果一个变量没有会话值，显示的就是全局状态值。LOCAL关键字含义与SESSION相同。
  

　　mysql> SHOW STATUS;
　　mysql> SHOW SESSION STATUS;
　　mysql> SHOW LOCAL STATUS;
　　使用FLUSH STATUS命令可以将一些状态变量的值重置为0：
　　mysql> FLUSH STATUS;
设置变量的值
　　要修改系统变量的值，有几种方式：在MySQL配置文件中修改，使用MySQL启动选项修改，或在MySQL启动后使用SET命令动态修改。每一个变量具体支持哪几种方式，见MySQL官方手册中的列表。
　　在设置系统变量的值时，变量值为容量大小的，默认单位为字节(bytes)，或者也可以显式指定单位为K、M、G(大小写均可，分别表示1024、10242、10243字节)。变量值为文件名的，可以使用绝对路径名，但如果使用相对路径，那么路径是相对于MySQL数据目录。
　　动态变量的值可以在MySQL运行时使用SET命令进行修改：

• 　　要指明所修改的是一个全局变量，使用 GLOBAL 关键字或加上 @@global. 修饰符。示例：
  

　　SET GLOBAL max_connections = 1000;
　　SET @@global.max_connections = 1000;

• 　　要指明所修改的是一个会话变量，使用 SESSION 关键字或加上 @@session. 或 @@ 修饰符。一个客户端只能修改它自己的会话变量，而不能修改其它客户端的。示例：
  

　　SET SESSION sql_mode = 'TRADITIONAL';
　　SET @@session.sql_mode = 'TRADITIONAL';
　　SET @@sql_mode = 'TRADITIONAL';

• 　　LOCAL 和 @@local. 与 SESSION 和 @@session. 同义，作用相同。
  
• 　　如果未提供修饰符，SET命令修改的就是会话变量。但如果那个变量没有会话值，就会报错：
  

　　mysql> SET max_connections = 1000;
　　ERROR 1229 (HY000): Variable 'max_connections' is a
　　GLOBAL variable and should be set with SET GLOBAL

• 　　SET命令可以同时设置多个变量的值，使用逗号分隔开来。示例：
  

　　SET @x = 1, SESSION sql_mode = '';
　　同时设置多个变量时，最接近的GLOBAL或SESSION修饰符会应用于后面没有修饰符的变量。示例：
　　SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
　　SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
　　SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
　　如果SET命令中的某一个变量设置失败了，那么整个语句都会失败，其它变量也就不会被修改。

• 　　SET命令修改会话变量的值后，修改对当前会话是即时生效的，直到你将它改为另一个值或当前会话终止。修改不会对其它会话造成影响。修改全局变量的值后，修改也是即时生效的，新的值会被记住并应用于新的会话，直到你将它改为另一个值或MySQL服务关闭。全局变量的值只会影响相应的会话变量的值，也因此只会对新建会话造成影响，不会对现有会话，甚至不会对执行SET GLOBAL语句的当前会话造成影响。为了让全局变量的值永久生效，你还应该在MySQL配置文件中设置相应选项的值。
  
• 　　要设置一个全局变量值为MySQL的默认值，或设置一个会话变量值为当前对应的全局变量的值，可以将变量的值设为关键字DEFAULT。比如，下面两个语句是相同的，都是将会话变量max_join_size的值设置为当前相应的全局变量的值：
  

　　SET @@session.max_join_size=DEFAULT;
　　SET @@session.max_join_size=@@global.max_join_size;
　　当然，并不是所有系统变量的值都可以被设置为DEFAULT，那样会报错。不允许将值DEFAULT分配给用户自定义变量、存储过程或函数参数、或存储程序的本地变量。

• 　　要在表达式中引用系统变量的值，使用其中一个 @@ 修饰符。比如，要在 SELECT 语句中获取变量的值，可以：
  

　　SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
　　对于在表达式中使用 @@var_name 这种形式引用变量的值（而不是使用 @@global. 或 @@session.），MySQL会返回会话变量的值（如果存在），否则会返回全局变量的值。这与语句 SET @@var_name = expr 是不相同的，那个语句总是设置的会话变量的值。

• 　　MySQL启动选项中，变量值可以显式地指定单位K、M或G（不区分大小写），但使用SET命令时不行；另一方面，MySQL启动选项中，变量值不能通过表达式计算活动，而SET命令中则可以。比如，下面第一个是可以的，第二个则不行：
  

　　[root@gw ~]# mysql --max_allowed_packet=16M                              //启动选项中，这种用法可以
　　[root@gw ~]# mysql --max_allowed_packet=16*1024*1024                     //启动选项中，这种用法不行
　　相对地，下面第二个是可以的，第一个则不行：
　　mysql> SET GLOBAL max_allowed_packet=16M;                                //SET命令中，这种用法不行
　　mysql> SET GLOBAL max_allowed_packet=16*1024*1024;                       //SET命令中，这种用法可以
自定义变量
　　SET命令还可以用于设置自定义变量。在变量名前加上 @ 修饰符，就是表示设置自定义变量。变量名称可以由字母、数字、"."、"_"和"$"组成。当然，在以字符串或者标识符引用时也可以包含其他字符（例如：@'my-var'，@"my-var"，或者@`my-var`）。
　　mysql> SET @x = 1;
　　自定义变量是会话级别的变量，作用域仅限于当前会话。当前会话终止时，其所有的会话变量将会被释放，包括自定义变量。使用SELECT命令可以查看自定义变量的值：
　　mysql> SELECT @x;