mysql中的用户变量

发表于 分类于 不明生物：

前言

LeetCode上有一道SQL练习题感觉很有必要备注一下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
【中等】180.编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

题解：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
/*
利用了ID连续，前后ID相差1的特性来确定，缺点也很明显, 数据必须保证前后ID是连续的且不断裂的
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT DISTINCT
	Num AS ConsecutiveNums
FROM
	(
		SELECT
			Num,
			CASE
		WHEN @prev = Num THEN
			@count := @count + 1
		WHEN (@prev := Num) IS NOT NULL THEN
			@count := 1
		END AS CNT
		FROM
			LOGS,
			(
				SELECT
					@prev := NULL ,@count := NULL
			) AS t
	) AS temp
WHERE
	temp.CNT >= 3
/*
利用用户变量实现对连续出现的值进行计数,与自关联或自连接相比，这种方法的效率更高，不受Logs表中的Id是否连续的限制，而且可以任意设定某个值连续出现的次数。
*/

MySQL的变量分类

MySQL变量一共分为两大类：用户自定义变量和系统变量。如下：

  • 用户自定义变量
    • 局部变量
    • 会话变量
  • 系统变量
    • 会话变量
    • 全局变量

局部变量

局部变量一般用于SQL的语句块中，比如存储过程中的begin和end语句块。其作用域仅限于该语句块内。生命周期也仅限于该存储过程的调用期间。

1
2
3
4
5
6
7
8
9
10
11
drop procedure if exists _procedure_test;
create procedure _procedure_test
(
    in a int,
    in b int
)
begin
    declare c int default 0;
    set c = a + b;
    select c as c;
end;

上述存储过程中定义的变量c就是局部变量。

会话变量

会话变量即为服务器为每个客户端连接维护的变量。在客户端连接时，使用相应全局变量的当前值对客户端的回话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量。其作用域与生命周期均限于当前客户端连接。

会话变量的赋值：

1
2
3
mysql> set session var_name = value;
mysql> set @@session.var_name = value;
mysql> set var_name = value;

会话变量的查询：

1
2
3
mysql> select @@var_name;
mysql> select @@session.var_name;
mysql> show session variables like "%var%";

全局变量

全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有SUPER权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件。

全局变量的设置：

1
2
mysql> set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION
mysql> set @@global.var_name = value; //同上

全局变量的查询：

1
2
mysql> select @@global.var_name;
mysql> show global variables like "%var%";

自定义变量

你可以利用SQL语句将值存储在用户自定义变量中，然后再利用另一条SQL语句来查询用户自定义变量。这样以来，可以再不同的SQL间传递值。

用户自定义变量的声明方法形如：@var_name，其中变量名称由字母、数字、“.”、“_”和“$”组成。当然，在以字符串或者标识符引用时也可以包含其他字符（例如：@’my-var’，@”my-var”，或者@my-var）。

用户自定义变量是会话级别的变量。其变量的作用域仅限于声明其的客户端链接。当这个客户端断开时，其所有的会话变量将会被释放。

用户自定义变量是不区分大小写的。

使用SET语句来声明用户自定义变量：

1
mysql> SET @var_name = expr[, @var_name = expr] ...

在使用SET设置变量时，可以使用 = 或者 := 操作符进行赋值。

当然，除了SET语句还有其他赋值的方式。比如下面这个例子，但是赋值操作符只能使用 := , 因为 = 操作符将会被认为是比较操作符。

1
2
3
4
5
6
7
mysql> SET @t1 = 1, @t2 = 2, @t3 := 4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1 + @t2 + @t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+

用户变量的类型仅限于：整形、浮点型、二进制与非二进制串和NULL。在赋值浮点数时，系统不会保留精度。其他类型的值将会被转成相应的上述类型。比如：一个包含时间或者空间数据类型（temporal or spatial data type）的值将会转换成一个二进制串。

如果用户自定义变量的值以结果集形式返回，系统会将其转换成字符串形式。

如果查询一个没有初始化的变量，将会以字符串类型返回NULL。

不要在同一个非SET语句中同时赋值并使用同一个用户自定义变量

用户自定义变量可以用于很多上下文中。但是目前并不包括那些显式使用常量的表达式中，比如SELECT中的LIMIT子句，或者LOAD DATA中的IGNORE N LINES的字句中。

通常来说，除了在SET语句中，不要再同一个SQL语句中同时赋值并使用同一个用户自定义变量。举个变量自增的例子，下面的是没问题的：

1
mysql> SET @a = @a + 1;

对于其他语句，比如SELECT，也许会得到期望的效果，但这真心不靠谱。比如下面的语句，也许你自然地会认为MySQL会先执行@a的值，然后再进行赋值操作：

1
mysql> SELECT @a, @a:=@a+1, ...;

然而，用户自定义变量表达式的计算顺序还没有定义呢。

除此之外，还有另一个问题。变量的默认返回类型由语句开始时的类型决定的，正如下面的例子：

1
2
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

上述的SELECT语句中，MySQL会报告给客户端第一列的字段类型为字符串，同时将所有对@a变量的使用均转换为字符串处理，尽管在SELECT语句中将@a变量设置为数字类型。在SELECT语句执行后，@a变量才会在下一个语句中识别为数字类型。

为了避免上述问题的发生，要么不在同一个语句中同时赋值并使用变量，要么在使用之前，将变量设置为0，0.0，或者’’，以确定它的数据类型。

变量的值是在SQL发送到客户端后才计算的

在SELECT语句中，在每一个select表达式被发送给客户端后，才会进行计算。这就意味着，在形如HAVING，GROUP BY和ORDER BY只句中有使用在当前select表达式定义的变量的情况下，该语句将不会得到如期的效果。

1
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

上述在HAVING只句中使用了在当前的select列表中定义的别名b，其使用了变量@aa。这条语句并不会得到如期的效果：@aa变量为上一次SQL语句执行的结果集中的ID值，并非当前的。

例 : 在MySQL中实现Rank高级排名函数

除了上面题目中的示例，这里给出另外一个常用的示例：

MySQL中没有Rank排名函数，当我们需要查询排名时，我们可以利用自定义变量来达到Rank函数一样的高级排名效果。

首先我们先创建一个我们需要进行高级排名查询的players表，

pid name age
1 Samual 25
2 Vino 20
3 John 20
4 Andy 22
5 Brian 21
6 Dew 24
7 Kris 25
8 William 26
9 George 23
10 Peter 19
11 Tom 20
12 Andre 20

实现Rank普通排名函数

在这里，我们希望获得一个排名字段的列，以及age的升序排列。所以我们的查询语句将是：

1
2
3
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (SELECT @curRank := 0) q
ORDER BY age
PID NAME AGE RANK
10 Peter 19 1
12 Andre 20 2
2 Vino 20 3
3 John 20 4
11 Tom 20 5
5 Brian 21 6
4 Andy 22 7
9 George 23 8
6 Dew 24 9
7 Kris 25 10
1 Samual 25 11
8 William 26 12

要在mysql中声明一个变量，你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化，而不需要单独的SET命令。当然，也可以使用SET，但它会处理两个查询：

1
2
3
4
SET @curRank := 0;
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players
ORDER BY age

实现Rank查询以降序排列

首要按age的降序排列，其次按name进行排列，只需修改查询语句加上ORDER BY和 DESC以及列名即可。

1
2
3
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (SELECT @curRank := 0) q
ORDER BY age DESC, name
PID NAME AGE RANK
8 William 26 1
7 Kris 25 2
1 Samual 25 3
6 Dew 24 4
9 George 23 5
4 Andy 22 6
5 Brian 21 7
12 Andre 20 8
3 John 20 9
11 Tom 20 10
2 Vino 20 11
10 Peter 19 12

实现Rank普通并列排名函数

现在，如果我们希望为并列数据的行赋予相同的排名，则意味着那些在排名比较列中具有相同值的行应在MySQL中计算排名时保持相同的排名(例如在我们的例子中的age)。为此，我们使用了一个额外的变量。

1
2
3
4
5
6
7
SELECT pid, name, age,
CASE
WHEN @prevRank = age THEN @curRank
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM players p, (SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY age
PID NAME AGE RANK
10 Peter 19 1
12 Andre 20 2
2 Vino 20 2
3 John 20 2
11 Tom 20 2
5 Brian 21 3
4 Andy 22 4
9 George 23 5
6 Dew 24 6
7 Kris 25 7
1 Samual 25 7
8 William 26 8

如上所示，具有相同数据和排行的两行或多行，它们都会获得相同的排名。玩家Andre, Vino, John 和Tom都有相同的age，所以他们排名并列第二。下一个最高age的玩家(Brian)排名第3。这个查询相当于MSSQL和ORACLE 中的DENSE_RANK()函数。

实现Rank高级并列排名函数

当使用RANK()函数时，如果两个或以上的行排名并列，则相同的行都会有相同的排名，但是实际排名中存在有关系的差距。

1
2
3
4
5
6
7
SELECT pid, name, age, rank FROM
(SELECT pid, name, age,
    @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
    @incRank := @incRank + 1,
    @prevRank := age
    FROM players p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
    ORDER BY age ) s

这是一个查询中的子查询。我们使用三个变量(@incRank，@prevRank，@curRank)来计算关系的情况下，在查询结果中我们已经补全了因为并列而导致的排名空位。我们已经封闭子查询到查询。这个查询相当于MSSQL和ORACLE中的RANK()函数。

PID NAME AGE RANK
10 Peter 19 1
12 Andre 20 2
2 Vino 20 2
3 John 20 2
11 Tom 20 2
5 Brian 21 6
4 Andy 22 7
9 George 23 8
6 Dew 24 9
7 Kris 25 10
1 Samual 25 10
8 William 26 12

在这里我们可以看到，Andre，Vino，John和Tom都有相同的age，所以他们排名并列第二。下一个最高年龄的球员(Brian)排名第6，而不是第3，因为有4个人并列排名在第2。

好的，通过本文加深对sql的运用，也希望你能从中获取收获。

参考链接:

深入MySQL用户自定义变量 胡小旭
在MySQL中实现Rank高级排名函数 风澈vio

关注作者公众号,获取更多资源!
赏作者一杯咖啡~