SQL变量的使用（自定义变量）

一、系统变量

说明：变量由系统定义，不是用户定义，属于服务器层面

注意：

• 全局变量需要添加GLOBAL关键字，
• 会话变量需要添加SESSION关键字，如果不写，默认会话级别

使用步骤

查看所有系统变量

SHOW GLOBAL VARIABLES;#全局变量
SHOW SESSION VARIABLES;#会话变量

查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';

查看指定的系统变量的值

#默认会话变量
SELECT @@session.系统变量名;#会话
SELECT @@global.系统变量名;#全局

为某个系统变量赋值

方式一：
SET GLOBAL/【SESSION】 系统变量名=值;
方式二：
SET @@global/【SESSION】.系统变量名=值;

1.1 全局变量

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有会话（连接）有效，但不能跨重启

①查看所有全局变量
SHOW GLOBAL VARIABLES;

②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%g%';

③查看指定的系统变量的值
SELECT @@global.flush_time;

④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

1.2 会话变量

作用域：针对于当前会话（连接）有效

①查看所有会话变量
SHOW VARIABLES;#省略默认会话
SHOW SESSION VARIABLES;

②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%g%';

③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;

④为某个会话变量赋值
#隔离级别更改为读未提交
方式一
SET @@session.tx_isolation='read-uncommitted';
方式二
SET SESSION tx_isolation='read-committed';

二、自定义变量（重点）

引用：https://blog.csdn.net/qq_41081716/article/details/108428687

说明：变量由用户自定义，而不是系统提供的

使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）

2.1 用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量应用在任何地方，也就是BEGIN END里面或BEGIN END外面

赋值操作符：=或:=

①声明并初始化

方式一★
SET @变量名=值;

方式二
SET @变量名:=值;

方式三
SELECT @变量名:=值;

②赋值（更新变量的值）

方式一：★
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

例子：
SELECT 
	@row := row_no,
	@name := book_name 
FROM tb_book 
WHERE id = 1;
注意：使用SET赋值时可以用“=”或“:=”，但是使用SELECT语句赋值时必须用“:=赋值”。


方式二：
SELECT 字段 INTO @变量名
FROM 表
where 条件;
例如：
①
SELECT AVG(`salary`) INTO @avg_s
FROM `employees`;

②
SELECT row_no,book_name INTO @row,@name FROM tb_book WHERE id = 1;

③使用（查看变量的值）★

SELECT @变量名;
例如
①
SELECT @avg_s;

②
-- 输出结果
SELECT @row;
SELECT @name;


SET @m=1;
SET @n=1;
SET @sum-@m+@n; 
SELECT @sum;

2.2 局部变量

作用域：仅仅在定义它的BEGIN END块中有效

应用在 BEGIN END中的第一句话

例如

（1）声明两个局部变量m和n，并均赋值为1.
（2）把m和n的值加和并赋值给新声明的变量sum，显示sum的值.

三、案例

3.1 案例1

更多例子参见：

sql中变量的使用
https://blog.csdn.net/qq_41081716/article/details/108428687

如：

表：Scores

(Id, Score) 
('1', '3.5')
('2', '3.65')
('3', '4.0')
('4', '3.85')
('5', '4.0')
('6', '3.65')

排序

select 
	score ,
	(@i:=@i+1) as rank1 
from Scores,(select @i:=0) init  //定义变量一般写在from语句之后,相当于在末尾定义变量
order by score desc

3.2 案例2

select user_id as seller_id, if (r2.item_brand is null || r2.item_brand != favorite_brand, "no", "yes") as 2nd_item_fav_brand
from users
left join (
    select r1.seller_id, items.item_brand from (
        select 
            @rk := if (@seller = a.seller_id, @rk + 1, 1) as rank,
            @seller := a.seller_id as seller_id, 
            a.item_id
        from (
            select seller_id, item_id
            from orders 
            order by seller_id, order_date
        ) a, (select @seller := -1, @rk := 0) b) r1
    join items 
    on r1.item_id = items.item_id
    where r1.rank = 2
) r2 on user_id = r2.seller_id;

select id as seller_id, max(if(t.rk=2.0 and t.favorite_brand = t.item_brand, 'yes', 'no')) as 2nd_item_fav_brand
from 
    (select id, favorite_brand, order_date, item_brand,
    if(@pre_id = id, @num:=@num+1, @num:=1) as rk,
    @pre_id:=id
    from
        (select u.user_id as id, u.favorite_brand, o.order_date, i.item_brand
        from users as u left outer join orders as o
        on u.user_id = o.seller_id
        left outer join items as i
        on o.item_id = i.item_id
        order by u.user_id, o.order_date) as a, (select @pre_id:=null, @num:=1) as b) as t
group by id

四、用户变量和局部变量的对比

自定义变量	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	BEGIN END中	只能在BEGIN END的第一句话	一般不用加@,需要指定类型