mysql查询中定义变量

MySQL 数据查询中完全可以定义变量，核心用于临时存储中间结果、简化重复逻辑、实现动态条件等场景。根据作用域和使用场景，主要分为两类：用户自定义变量（会话级，最常用） 和 局部变量（仅存储过程/函数内有效），以下是详细用法和示例：

一、核心结论

MySQL 支持在查询中定义变量，其中：
● 用户变量（@变量名）：会话级（当前连接有效，断开则失效），无需声明类型，可直接在普通查询中使用（最常用）；
● 局部变量（DECLARE 变量名）：仅在 BEGIN...END 块内有效（存储过程/函数/触发器），需指定类型；
● 补充：系统变量（@@变量名）是MySQL内置的（如@@datadir），一般无需自定义，此处聚焦用户自定义变量。

二、用户自定义变量（普通查询首选）

1、 定义/赋值方式

赋值方式	语法	特点
SET 赋值（推荐）	SET @变量名 = 值; 或 SET @变量名 := 值;	语法清晰，支持多变量同时赋值
SELECT 赋值	SELECT @变量名 := 表达式/列名 FROM 表 [LIMIT 1];	直接从查询结果赋值，需用 :=（= 是比较符）
SELECT INTO 赋值	SELECT 列名 INTO @变量名 FROM 表 [LIMIT 1];	将查询结果直接存入变量，仅返回1行值

2、 基础示例（普通查询中使用）

-- 示例1：SET 定义变量，查询中使用
SET @target_id = 100; -- 定义变量并赋值
SET @min_age = 18;
-- 查询时引用变量（简化条件，避免重复写值）
SELECT id, name, age FROM user WHERE id = @target_id AND age >= @min_age;

-- 示例2：从查询结果赋值（临时存储中间结果）
SELECT @max_score := MAX(score) FROM student; -- 存储学生最高分数
SELECT @avg_age := AVG(age) FROM student;     -- 存储学生平均年龄
-- 用变量查询对应数据
SELECT * FROM student WHERE score = @max_score;

-- 示例3：SELECT INTO 赋值（更简洁的结果存储）
SELECT COUNT(*) INTO @total_user FROM user; -- 统计用户总数并存入变量
SELECT @total_user AS 总用户数; -- 直接查询变量值

-- 示例4：动态分页（简化分页逻辑）
SET @page_size = 10;  -- 每页条数
SET @page_num = 2;    -- 页码
SELECT * FROM goods 
LIMIT (@page_num - 1) * @page_size, @page_size; -- 第2页数据

3、 高级示例（变量实现复杂逻辑）
场景1：实现排名/连续编号

SET @rank = 0; -- 初始化排名变量
SELECT 
  @rank := @rank + 1 AS 排名,
  name,
  score 
FROM student 
ORDER BY score DESC; -- 按分数降序生成排名

场景2：关联查询简化（避免重复子查询）

-- 先存用户ID，再查该用户的订单（避免重复写子查询）
SELECT @user_id := id FROM user WHERE name = '张三';
SELECT * FROM order WHERE user_id = @user_id;

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

需用 DECLARE 声明，必须指定类型（如INT/VARCHAR），仅在 BEGIN...END 块内有效，适合复杂业务逻辑封装。

语法与示例

-- 创建存储过程，使用局部变量
DELIMITER // -- 临时修改语句结束符（避免与存储过程内的;冲突）
CREATE PROCEDURE get_student_stat()
BEGIN
    -- 定义局部变量（必须指定类型，可设默认值）
    DECLARE total_stu INT DEFAULT 0;
    DECLARE avg_score DECIMAL(5,2) DEFAULT 0.0;
    
    -- 给局部变量赋值
    SELECT COUNT(*) INTO total_stu FROM student;
    SELECT AVG(score) INTO avg_score FROM student;
    
    -- 使用变量返回结果
    SELECT total_stu AS 学生总数, avg_score AS 平均分数;
END //
DELIMITER ; -- 恢复语句结束符

-- 调用存储过程（执行局部变量逻辑）
CALL get_student_stat();

四、关键注意事项

1、 作用域差异：

• 用户变量（@xxx）：当前数据库连接有效，断开连接后变量失效；
• 局部变量（DECLARE）：仅在所属的BEGIN...END块内有效，存储过程执行完即销毁。

2、 赋值符注意：

• SET 语句中，= 和 := 均可用于赋值；
• SELECT 语句中，必须用 :=（= 会被识别为“等于”比较符），例如：

  SELECT @var = 10; -- 错误：这是比较（var是否等于10），不是赋值
  SELECT @var := 10; -- 正确：赋值
  

3、 类型特性：

• 用户变量是“弱类型”，自动适配赋值内容的类型（如数字、字符串、NULL）；
• 局部变量需显式指定类型，更严谨，适合精准控制。

4、 NULL 处理：
变量未赋值时默认是 NULL，运算时需注意（如 NULL + 1 = NULL），可通过 IFNULL 规避：

SET @num = NULL;
SELECT IFNULL(@num, 0) + 1; -- 结果为1，而非NULL

5、 性能提示：
变量仅临时存储数据，不会影响基础查询性能，但复杂嵌套查询中滥用变量可能增加维护成本，建议仅用于简化重复逻辑。

五、典型使用场景

场景	推荐变量类型	优势
普通查询简化条件/存储中间结果	用户变量（@xxx）	无需封装，直接在查询中使用
分页查询（动态页码/条数）	用户变量（@xxx）	避免硬编码，灵活调整参数
排名/连续编号	用户变量（@xxx）	无需子查询，高效生成序号
存储过程/函数封装业务逻辑	局部变量（DECLARE）	类型严谨，作用域隔离，适合复杂逻辑

总结：日常查询中优先使用 @xxx 类型的用户变量，可大幅简化重复逻辑；复杂业务（如批量处理、封装接口）则用存储过程+局部变量。