澳门金沙vip深入MySQL用户自定义变量:使用详解及其使用场景案例

一、前言

在前段工作中,曾几次收到超级话题积分漏记的用户反馈。通过源码的阅读分析后,发现问题出在高并发分布式场景下的计数器上。计数器的值会影响用户当前行为所获得积分的大小。比如,当用户在某超级话题下连续第n(n即计数器的值)次进行转发帖子时,将会获得与n相关的分数。然而,在第一次改进后问题依然存在。所以,这次在之前的基础上,通过使用MySQL变量的途径来解决该问题。

深入MySQL用户自定义变量:使用详解及其使用场景案例,mysql使用详解

二、到底MySQL的变量分哪几类?

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

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

本文涉及的内容为用户自定义会话变量,若对其他分类无感,请点击这里。

PS:用户定义的会话变量和系统定义的会话变量有什么区别?

一、前言

在前段工作中,曾几次收到超级话题积分漏记的用户反馈。通过源码的阅读分析后,发现问题出在高并发分布式场景下的计数器上。计数器的值会影响用户当前行为所获得积分的大小。比如,当用户在某超级话题下连续第n(n即计数器的值)次进行转发帖子时,将会获得与n相关的分数。然而,在第一次改进后问题依然存在。所以,这次在之前的基础上,通过使用MySQL变量的途径来解决该问题。

局部变量

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

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

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

二、到底MySQL的变量分哪几类?

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

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

本文涉及的内容为用户自定义会话变量,若对其他分类无感,请点击这里。

PS:用户定义的会话变量和系统定义的会话变量有什么区别?

会话变量

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

会话变量的赋值:

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

会话变量的查询:

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

局部变量

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

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

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

全局变量

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

全局变量的设置:

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

全局变量的查询:

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

会话变量

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

会话变量的赋值:

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

会话变量的查询:

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

三、MySQL用户自定义变量详解

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

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

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

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

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

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

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

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

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。

全局变量

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

全局变量的设置:

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

全局变量的查询:

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

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

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

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

1 SET @a = @a + 1;

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

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

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

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

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

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

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

三、MySQL用户自定义变量详解

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

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

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

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

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

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

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

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

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @[email protected][email protected];
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @[email protected][email protected] |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+

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

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

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

变量的值是在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值,并非当前的。

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

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

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

1 SET @a = @a + 1;

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

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

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

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

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

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

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