MySQL变量

MySQL 变量详解：系统变量与自定义变量的使用

MySQL 中的变量是存储数据的容器，分为系统变量（MySQL 内置，控制数据库行为）和自定义变量（用户定义，用于查询或存储过程中传递数据）。合理使用变量可简化操作、提升脚本灵活性。本文详细解析两类变量的定义、使用及区别。

系统变量：控制 MySQL 运行行为

系统变量是 MySQL 预定义的变量，用于配置数据库的运行参数（如连接数、缓存大小等），分为全局变量（影响所有会话）和会话变量（仅影响当前会话）。

系统变量的分类与作用

类型	作用范围	生效时机	示例
全局变量	所有会话（连接）	重启 MySQL 或通过 `SET GLOBAL` 修改后生效	`max_connections`（最大连接数）
会话变量	当前会话（连接）	会话启动时继承全局变量，`SET SESSION` 修改后立即生效	`autocommit`（自动提交开关）

查看系统变量

（1）查看所有系统变量

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

-- 查看所有会话变量（默认显示当前会话）
SHOW SESSION VARIABLES;  -- 或简写为 SHOW VARIABLES;

（2）过滤查询特定变量

使用 LIKE 匹配变量名（支持通配符 %）：

-- 查看所有包含“timeout”的全局变量
SHOW GLOBAL VARIABLES LIKE '%timeout%';

-- 查看当前会话的字符集变量
SHOW SESSION VARIABLES LIKE 'character_set_%';

（3）查询单个变量的值

通过 @@ 符号访问变量：

-- 查询全局变量
SELECT @@GLOBAL.max_connections;  -- 查看最大连接数

-- 查询会话变量（可省略 SESSION 关键字）
SELECT @@SESSION.autocommit;  -- 查看当前会话自动提交状态
SELECT @@autocommit;  -- 等价于上面的语句（默认会话级别）

修改系统变量

（1）修改全局变量

-- 方式 1：使用 SET GLOBAL
SET GLOBAL max_connections = 1000;

-- 方式 2：使用 @@GLOBAL
SET @@GLOBAL.max_connections = 1000;

注意：
- 全局变量修改后，新会话会生效，已有会话不受影响。
- 部分变量需重启 MySQL 才能生效（如 innodb_buffer_pool_size），可通过 SHOW VARIABLES 确认是否生效。

（2）修改会话变量

-- 方式 1：使用 SET SESSION
SET SESSION autocommit = 0;  -- 关闭当前会话自动提交

-- 方式 2：使用 @@SESSION
SET @@SESSION.autocommit = 0;

-- 方式 3：省略 SESSION（默认会话级别）
SET autocommit = 0;

注意：会话变量修改仅对当前连接有效，断开连接后失效。

自定义变量：用户定义的数据容器

自定义变量是用户在查询或存储过程中定义的变量，用于临时存储数据，分为用户变量（会话级别）和局部变量（存储过程 / 函数内有效）。

用户变量（会话级别）

用户变量在当前会话内有效，可跨 SQL 语句使用，声明时需加 @ 前缀。

（1）声明与赋值

用户变量无需提前声明类型，赋值时自动确定类型，支持三种赋值方式：

-- 方式 1：使用 SET 语句（= 或 := 均可）
SET @user_var1 = 100;
SET @user_var2 := 'hello';

-- 方式 2：使用 SELECT 语句（必须用 :=）
SELECT @user_var3 := 3.14;

-- 方式 3：从查询结果中赋值
SELECT COUNT(*) INTO @user_var4 FROM users;  -- 将用户总数存入变量

（2）使用用户变量

-- 直接查询变量值
SELECT @user_var1, @user_var2;

-- 在查询中使用变量
SELECT * FROM orders WHERE total > @user_var1;  -- 查询金额大于100的订单

-- 变量参与计算
SET @sum_var = @user_var1 + @user_var3;  -- 100 + 3.14 = 103.14

（3）特点

作用范围：当前会话（连接），其他会话不可见。
生命周期：会话结束后自动销毁。
无需声明类型，灵活但缺乏类型检查。

局部变量（存储过程 / 函数内）

局部变量仅在定义它的 BEGIN...END 代码块（如存储过程、函数）中有效，需显式声明类型。

（1）声明与赋值

-- 在存储过程中定义局部变量
DELIMITER //
CREATE PROCEDURE test_local_var()
BEGIN
  -- ① 声明局部变量（必须在代码块开头）
  DECLARE var1 INT;  -- 声明整数变量
  DECLARE var2 VARCHAR(50) DEFAULT 'default';  -- 声明字符串变量并设默认值
  
  -- ② 赋值
  SET var1 = 200;
  SET var2 := 'local variable';
  
  -- ③ 使用变量
  SELECT var1, var2;
END //
DELIMITER ;

-- 调用存储过程，查看局部变量值
CALL test_local_var();  -- 输出：200, 'local variable'

（2）特点

作用范围：仅在声明它的 BEGIN...END 块内有效（如存储过程执行结束后失效）。
必须声明类型（如 INT、VARCHAR），有类型检查。
声明位置：必须在代码块的最开头（DECLARE 语句需放在所有 SQL 语句之前）。

用户变量与局部变量的区别

特性	用户变量	局部变量
声明前缀	需加 `@`（如 `@var`）	不加前缀（如 `var`）
声明位置	会话中任意位置	仅在 `BEGIN...END` 块开头
作用范围	当前会话	所在的代码块（如存储过程）
类型检查	无（自动推断）	有（需显式声明类型）
使用场景	跨语句传递数据	存储过程 / 函数内临时计算

变量使用的注意事项

系统变量的权限：
- 修改全局变量需要 SUPER 权限，普通用户只能查看。
- 会话变量通常无需特殊权限，用户可修改自己会话的变量。
自定义变量的性能：
- 用户变量在复杂查询中可能导致优化器无法有效优化，建议仅用于简单场景。
- 局部变量在存储过程中性能较好，适合复杂逻辑中的数据传递。
变量名冲突：
- 避免自定义变量与系统变量同名（如 @max_connections 可能与全局变量混淆）。
- 局部变量名避免与表字段名重名，否则可能导致查询歧义