MySQL系统变量、自定义变量和存储程序变量使用小结

     MySQL一共有三种变量：系统变量（system variable）、自定义变量（user-defined variable）和存储程序变量（stored program variable）。系统变量是MySQL内置的；自定义变量是用户自己定义；存储程序变量包括存储程序创建时定义的参数和内部的本地变量。普通用户只是仅仅写写简单SQL的话，可能仅仅会接触到自定义变量；系统变量和存储程序变量对于MySQL管理员和运维人员来说，就会经常接触了。有鉴于此，并且因为自定义变量最简单易述，具体写的时候就从自定义变量开始写起。三种变量都遵循一个通用的定义形式：SETvariable_assignment [, variable_assignment] ...

①赋值命令

     SET

②赋值符

     “=”或者“:=”（两者皆可，个人习惯使用后者，在某本书上作者推荐的，不过官方文档并没有刻意的推荐说明）

③赋值内容

     符合变量作用环境即可

一、自定义变量

     自定义变量就是简单的数据容器，需要注意的是，变量名需要以一个“@”符号开头，少了“@”不会识别，多了“@”可能会报错（为什么是“可能”留待系统变量再说），定义形式如下，其中“expr”从简单到复杂可以是普通字符值也可以是查询语句的返回值。

SET @var_name = expr;

两个简单的栗子：

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);

     自定义变量即定义即使用，基本上没有其他额外限制，但作用范围是session级别的，也就是会话关闭后就不能再引用，除非再次定义。

二、系统变量

     系统变量就是MySQL自带的那一堆服务运行控制选项，只要MySQL在运行中，这些变量就在工作着，并且只要你愿意，随时可以拿出来看看（可以简单地通过“SHOW VARIABLES ”查看）。这是一个庞大的部分，所以一点一点分开来说吧。

①很多系统变量都有默认值，因此赋值为“DEFAULT”是有效的。

SET @@session.max_join_size=DEFAULT;

②系统变量有全局（GLOBAL）和会话（SESSION / LOCAL）之分，修改全局是服务级别的而修改会话只是修改当前的连接会话。

SET GLOBAL sort_buffer_size = 1000000;
SET SESSION sort_buffer_size = 1000000;

③系统变量有多种修改方式：修改配置文件、带系统变量选项启动MySQL以及MySQL运行中的指令修改。其中，修改配置文件是最直截了当的。

④系统变量名有多种引用方式：直接变量名引用或者“@@”修饰符引用（对比前文自定义变量是一个“@”）。所以，在修改session级别的系统变量时，以下命令是等价的。

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

⑤修改全局变量需要“ SUPER ”超级权限，并且命令中的"GLOBAL"不可省略。

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

⑥修改会话级别的系统变量一般不需要特殊权限，并且命令中的“SESSION”是可以省略的（默认会话级别）。

SET sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';

⑦“LOCAL”=“SESSION”以及“@@local.”=“@@session.”。所以以下全部等价。

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

⑧混合设置多个系统变量时，最近的一个“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;

三、存储程序变量

     存储程序变量的作用范围仅仅局限在定义它们的那个存储程序中。存储程序变量包括存储函数和存储过程定义的参数以及在存储程序（存储函数、存储过程、事件以及触发器等）中定义的本地变量。

①存储程序变量需要事先声明：参数变量在存储程序定义语句头部声明；本地变量在存储程序体中通过“DECLARE”声明。

DECLARE var_name [, var_name] ... type [DEFAULT value]

②定义的存储程序变量数据类型需要与事先声明保持一致。

③存储程序变量声明需要置于“CURSOR”和“HANDLE”声明之前。

     以上内容简单总结了MySQL的三种变量类型。实际使用中，三种变量并非是独立的，也可以有相互的关联关系，甚至于一条“SET”语句可以进行多变量类型操作。

SET @seed = 1024, GLOBAL max_connections = 1024;
SET @seed = @@global.max_connections + 9527;

参考MySQL官方文档：

https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

https://dev.mysql.com/doc/refman/5.7/en/stored-program-variables.html