如何在 MySQL 中声明和使用变量

MySQL 有三种变量机制。当同一连接中的多条 SQL 语句需要共享一个值时，使用 @limit 这样的用户定义会话变量。在存储过程、函数、触发器或事件中，使用不带 @ 的局部变量。需要检查或配置服务器或单个连接时，使用 @@SESSION.sql_mode 这样的系统变量。

最重要的区别是，DECLARE 不是可以在普通 SQL 窗口中直接执行的通用语句。它只能出现在 BEGIN ... END 复合语句中，并且必须位于该块的其他语句之前。对于快速脚本，请从 SET @name = value; 开始。SQL 示例中的关键字 DECLARE、SET、SELECT ... INTO、BEGIN ... END、SESSION 和 GLOBAL 保持不变。

选择正确的 MySQL 变量

这些形式不能互换。DECLARE @name INT 不是局部变量语法，而在存储程序外单独执行 DECLARE name INT 会产生语法错误。

在 SQL 会话中使用用户定义变量

用户定义变量以 @ 开头，使用前不需要声明。使用 SET 分配值或表达式，然后在后续语句中引用它：

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

变量只属于当前连接。其他连接无法使用它，连接结束后变量也会消失。未初始化的用户变量为 NULL，因此可复用脚本应在开头初始化变量。

SET 是最清晰的赋值方式。MySQL 也接受在 SET 语句中使用 :=：

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

变量类型由赋予的值决定，而不是由类型子句声明。它可以保存数字、小数、字符串、二进制字符串或 NULL；跨越类型边界时应使用显式转换。

使用 SELECT ... INTO 加载查询结果

当值来自查询时，使用 SELECT ... INTO。目标变量的数量必须与所选表达式的数量一致：

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

查询应设计为只返回一行。COUNT(*)自然会返回一行，按唯一键查询也适合。如果可能返回多行，请使用确定的 ORDER BY 和 LIMIT 1 选择目标行。

用户变量不能替代表名或列名。SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; 不会替换表名。如果确实需要动态 SQL，请创建供 PREPARE 使用的语句，并验证应用程序提供的标识符。

MySQL 9.7 为了兼容性仍允许在 SET 之外赋值用户变量，但该行为计划在未来移除。应使用 SET 和 SELECT ... INTO，不要使用 SELECT @n := n 这样的表达式。

在存储程序中声明局部变量

局部变量使用不带 @ 的 DECLARE，并且需要 MySQL 数据类型。可以指定 DEFAULT，否则初始值为 NULL。请在 BEGIN ... END 块的开头、可执行语句之前声明变量。它们只在该块和嵌套块中可见。

下面的过程使用输入参数、局部变量和输出参数。DELIMITER 命令属于 mysql 客户端，使包含分号的过程体可以作为一条 CREATE PROCEDURE 语句发送。

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

local_count 只在过程执行期间存在。OUT 参数通过传给 CALL 的会话变量返回。如果查询返回多个列，请为每个表达式准备一个目标，并处理没有匹配行的情况。

可以在一条语句中声明多个相同类型的变量，但分开声明通常更易维护：

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE 还可以定义条件、游标和处理程序。顺序很重要：变量和条件在游标之前，游标在处理程序之前。因此，在 SELECT 或 SET 后声明会产生语法错误。

理解 SELECT ... INTO 的行数边界

标量目标只能保存一个值，不能保存任意结果集。下面的查询故意返回三行，因此会产生错误1172：

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

请将查询缩小到已知的一行、聚合结果，或让结果保持为行集合。只有配合有意的排序时，LIMIT 1 才适用：

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

同样的一行规则也适用于局部变量。如果例程可能找不到行，请明确初始化变量，并在需要时使用 NOT FOUND 处理程序或存在性检查。

检查或设置 MySQL 系统变量

系统变量用于配置服务器或客户端连接。可以使用 SHOW VARIABLES 或 @@ 限定符查看：

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

不指定作用域时，SHOW VARIABLES 使用当前会话。SESSION 值只影响该连接；GLOBAL 值通常会初始化新连接。更改 GLOBAL 变量需要适当权限，并且变量必须允许更改。

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

注释中的 GLOBAL 语句没有放入可运行 fixture，因为是否成功取决于账户权限以及变量的作用域和可变性。

排查无法使用的变量

• 如果 DECLARE 被拒绝，请确认它在 BEGIN ... END 内，并且位于所有可执行语句之前。普通 SQL 脚本使用 SET @name = value。
• 如果变量意外变成 NULL，请初始化它并检查来源表达式或查询。
• 如果 SELECT ... INTO 报错1172，请让查询只返回一行，或将结果保留为行集合。
• 其他连接看不到 @name 是正常的，因为用户变量只属于当前会话。
• 如果 @@GLOBAL.name 修改失败，请检查变量是否可变更以及管理权限。

成功 fixture 会赋值会话变量、在筛选中重复使用它们、在过程中声明局部变量并返回输出参数。边界 fixture 会验证多行 SELECT ... INTO 的诊断；两者都使用临时 MySQL 服务器。

完整语法请参阅 MySQL 用户定义变量参考、局部 DECLARE 参考 和 SET 变量赋值参考。