根据变量的创建方式和作用域可以将其分为以下几种类型:
- 系统变量(System Variable),由 MySQL 系统定义和维护的变量。系统变量可能包含全局值(全局变量)和会话值(会话变量),全局变量在服务器启动时通过命令行参数或者选项文件进行设置,在服务关闭时销毁;当客户端连接到服务器时,MySQL 会将大部分的全局变量复制一份作为客户端的会话变量,会话变量在连接断开时销毁。
- 用户变量(User-Defined Variable),由用户自定义的变量,在连接会话期间有效,可以用于在不同 SQL 语句之间传递数据。
- 局部变量(Local Variable),在存储过程或者函数中定义的变量,通常用于存储中间结果。局部变量的作用域为存储过程/函数内或者某个语句块之内。
1、系统变量
系统变量分为全局变量和会话变量。
1.1 查看系统变量
语法如下:
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]
GLOBAL 表示查看全局变量,SESSION 表示查询会话变量(默认选项,也可以使用 LOCAL 替代),LIKE 用于查找特定名字的变量,WHERE 用于查找满足指定条件的变量。
例如:
SHOW SESSION VARIABLES; -- 查询当前会话中的所有会话变量,可简写为SHOW VARIABLES
SHOW GLOBAL VARIABLES; -- 查询所有全局变量
SHOW GLOBAL VARIABLES LIKE 'max%'; -- 使用LIKE返回所有名字以max开头的全局变量
除了使用 SHOW VARIABLES 命令之外,也可为通过 SELECT 语句查询系统变量的值。
例如:
SELECT @@GLOBAL.back_log; -- 查询全局变量 back_log 的值
-- 查询会话变量 sql_mode 的值
select @@session.sql_mode;
select @@local.sql_mode;
select @@sql_mode;
注:引用系统变量时,需要在变量名前加上两个 @ 符号;使用一个 @ 符号引用的是用户变量。
1.2 设置系统变量
使用 SET
命令动态修改某些系统变量。SET 命令的完整语法如下:
SET variable = expr [, variable = expr] ...
variable: {
user_var_name
| param_name
| local_var_name
| {GLOBAL | @@GLOBAL.} system_var_name
| {PERSIST | @@PERSIST.} system_var_name
| {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
| [SESSION | @@SESSION. | @@] system_var_name
}
SET 命令可以用于设置各种变量,user_var_name 表示用户定义变量,param_name 是存储过程/函数的参数变量,local_var_name 是存储过程/函数的局部变量,system_var_name 是系统变量。
更改全局变量的值,在变量前加上 GLOBAL 关键字或者 @@GLOBAL 限定符。例如:
set global max_connections = 1000;
set @@global.max_connections = 1000;
更改会话变量的值,则如下所示:
ps:修改会话变量只对当前会话有效,不会影响其他连接会话的设置。
set session varname = value;
-- 或者
set @@session.varname = value;
如果要将值设为编译时的默认初始值,利用default值即可,如下
set @@session.max_join_size = default; -- 将会话变量 max_join_size 的值设为默认
set @@session.max_join_size = @@global.max_join_size; -- 将会话变量 max_join_size 的值设置为当前全局变量 max_join_size 对应的数值
2、用户变量
当我们需要在不同的 SQL 语句之间传递数据时,可以定义一个变量,然后在某个语句中进行赋值并且在其他语句中进行引用。作用范围在当前会话之内,关闭连接时自动销毁,不会被其他会话看到或者使用。
2.1 定义用户变量
使用 @var_name 格式进行定义,其中变量名 var_name 由字母数字、点号(.)、下划线(_)以及美元符号($)组成,最大长度为 64 个字符。例如,以下都是有效的用户变量:
@id
@123
@.user_var
@$a_very_long_variable_name
用户变量的赋值方法有两种:SET 命令或者 SELECT 语句。
(1)SET 命令
SET @var_name = expr [, @var_name = expr] ...
用户变量支持以下数据类型:integer、decimal、float、double、binary、char、varchar、text 或者 NULL 值。例如:
set @employee_count = (select count(*) from employee); -- 变量的类型是整数,通过一个子查询进行赋值
set @name = 'chaodev'; -- 变量的类型是字符串
除了使用等号=
之外,也可以使用 :=
进行变量赋值,如下
set @num=1;
set @num:=1;
(2)SELECT 语句
select @count := 80;
select @user_name := 'dalaochao';
select @num := 字段名 from 表名 where ...
推荐使用 SET 命令进行赋值。另外,SELECT 方式只能使用 := 进行赋值,因为 SELECT 语句中的 = 是一个比较运算符。
如果查询语句返回多行数据,变量只保存最后一行中的数据。例如
select @name:=nick_name from users;
select @name; -- name将是users表最后一条数据的nick_name
2.2 访问用户变量
大多数情况下,用户变量都可以当作表达式用于 SELECT 列表、WHERE 子句、INSERT 或者 UPDATE 等语句中,例如:
set @count=100;
select @count;
set @user_id='chaodev';
select * from users where user_id=@user_id;
3、局部变量
通常用于存储中间结果,一般用在存储过程的begin/end语句块内,该语句块执行完毕后,局部变量就消失了。
declare语句专门用于定义局部变量,可以使用default来说明默认值。
declare var_name [, var_name]... data_type [ DEFAULT value ];
set语句既可以用于局部变量的赋值。
set var_name=expr [, var_name=expr]...;
或者用select …. into…形式赋值
select col_name[,...] into var_name[,...] table_expr [where...];
示例:
declare v_employee_name varchar(100);
declare v_employee_salary decimal(8,4);
select employee_name, employee_salary
into v_employee_name, v_employee_salary
from employees
where employee_id=1;
4、变量使用示例
首先表结构和数据如下:
CREATE TABLE weather(
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
record_date date NULL DEFAULT NULL COMMENT '日期',
temperature int(10) NULL DEFAULT NULL COMMENT '温度',
PRIMARY KEY (id) USING BTREE
);
INSERT INTO weather VALUES (1, '2023-03-01', 10);
INSERT INTO weather VALUES (2, '2023-03-02', 25);
INSERT INTO weather VALUES (3, '2023-03-03', 20);
INSERT INTO weather VALUES (4, '2023-03-04', 30);
INSERT INTO weather VALUES (5, '2023-03-05', 35);
INSERT INTO weather VALUES (6, '2023-03-05', 35);
INSERT INTO weather VALUES (7, '2023-03-06', 20);
INSERT INTO weather VALUES (8, '2023-03-07', 40);
示例:
获得一个排名字段的列,temperature升序排列。
set @sort=0;
select A.*,@sort:=@sort+1 sort from weather A order by temperature;
或者如下:
-- 获得一个排名字段的列,temperature的升序排列
select id,record_date,temperature,@sort := @sort+1 as sort from weather,
(
select @sort := 0
) init
order by temperature;
并列数据的行赋予相同的排名
-- 并列数据的行赋予相同的排名
select id,record_date,temperature,
case when @temp = temperature then @sort
when @temp := temperature then @sort := @sort+1
end as sort
from weather,
(
select @sort := 0,@temp := null
) init
order by temperature;
将并列的排名产生的空位补齐
-- 将并列的排名产生的空位补齐
select id,record_date,temperature,sort from
(select id,record_date,temperature,
if(@temp = temperature,@sort,@incSort) as sort,
@incSort := @incSort + 1,
@temp := temperature,
@sort := @sort +1
from weather,
(
select @sort := 0,@temp := null,@incSort :=1
) init
order by temperature ) res;
查询与昨天的温度相比温度更高的所有日期
-- 查询与昨天的温度相比温度更高的所有日期
select id,record_date,temperature,t.r
from
(select w.*,
-- 变量按顺序赋值
@curd := w.record_date,
@curt := w.temperature,
@isH := if(datediff(@curd,@pred) = 1 and @curt > @pret,1,0) as r,
-- 判断完毕后才会改变pret和pred的值
@pret := @curt,
@pred := @curd
from weather w,
(select
@curd := null,
@pred := null,
@curt := 0,
@pret := 0,
@isH := 0
) init
order by w.record_date
) t
where t.r = 1
每天与昨天对比的结果如下
比昨天温度高(t.r=1)的如下
更多技术干货,请持续关注程序员大佬超。
原创不易,转载请注明出处。