MySQL用户变量定义：set与select用法详解

用户变量

在实际开发中，经常会遇到需要在不同SQL语句之间传递某个值的情况。这时候，用户变量就是最直接的解决方案——它相当于一个临时存储的“便签”，你可以在一个地方把值写进去，然后在另一个地方读出来。

定义用户变量有两种方式：SET 和 SELECT。它们各有各的脾气，用不对就容易踩坑，下面一个一个说。

SET 的使用方法：

SET @变量名 {= | := } value [,@变量名 {= | := } value,…] ;

SET 的变量值可以是以下几种：

• 字面量，比如数字、字符串
• 系统表达式，比如 NOW()
• 来自一个 SQL 语句的结果（子查询）
• 系统函数，比如 RAND()

例1

定义用户变量 PI，初值为 3.1415926

SET @PI := 3.1415926;

注意一点：用户变量的数据类型完全由赋给它的值决定，你不需要提前声明类型，MySQL 会自动推断。

例2

定义一个变量，存放 1 号球队的队长号码

SET @anr := (SELECT playerno FROM teams WHERE teamno=1);

要获取变量的值，直接 select 它就行：

SELECT @变量名;

这里有个容易翻车的细节：

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=5,@b=@a;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;
+-----+
| @b  |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

看到没？@b 的值是 1，而不是 5。原因在于，当你在一条 SET 语句中同时给多个变量赋值时，MySQL 会先计算所有等号右边的值，然后再统一赋值。所以 @b=@a 时，右边的 @a 还是旧值 1，而不是刚赋上去的 5。这一点特别容易忽略，实战中要小心。

SELECT 设置用户变量：

在 SELECT 中给变量赋值，只能用 :=，不能用 =——因为在 SELECT 里 = 被当作比较操作符，而不是赋值符。这个写法和普通 SELECT 的区别在于：

• 一定会返回一行结果（即使你只是赋值）
• 只能用 :=

例3

创建用户变量 playerno 并赋值为 7

SELECT @playerno := 7;

例4

一次定义 3 个用户变量

SELECT @name := 'tom', @town := 'Inglewood', @postcode := '1234ab';

例5

查询 6 号球员的姓名、城市和邮编，并保存到 3 个变量中

SELECT @name := name, @town := town, @postcode := postcode
FROM players
WHERE playerno = 6;

注意：如果 SELECT 查询返回了多行，MySQL 会把最后一行的值赋给变量，而不会报错。这一点既是特性也是陷阱——如果查询结果不是你期望的，变量值可能跟你预想的不一样。

例6

定义变量 @playerno，将 players 表中最小的球员编号赋给它：6

SELECT @playerno := playerno
FROM players
ORDER BY playerno DESC;
SELECT @playerno;

重要提醒：当用户变量用在 WHERE 或 HA VING 子句中时，必须先用单独的语句定义好变量，否则会得到意外结果——因为 WHERE 子句的执行顺序在 SELECT 赋值之前。

例7

SELECT @pnr7 := 7
FROM players
WHERE playerno < @pnr7;

这条语句不会返回任何球员编号小于 7 的记录。为什么？因为 WHERE 子句在 SELECT 的赋值之前执行，此时 @pnr7 还没有被赋值，它的值是 NULL，所以 playerno < NULL 永远为假，自然查不出结果。正确的做法是：先单独用 SET 或 SELECT 给 @pnr7 赋好值，再执行带 WHERE 的查询。

用户变量的生命周期：

用户变量只在当前会话中有效。只要会话没有断开，你随时可以读写这个变量。但如果想跨会话共享变量，就得把它存到数据表里，用持久化方式传递。