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