MySQL变量的使用

根据变量的创建方式和作用域可以将其分为以下几种类型：

• 系统变量（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）的如下

---

更多技术干货，请持续关注程序员大佬超。
原创不易，转载请注明出处。