MySQL用户变量

原创于 2024-12-11 20:11:36 发布 · 1.9k 阅读

39 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

17 篇文章

订阅专栏

MySQL 用户变量简介

用户变量（User Variables）是 MySQL 中一种临时变量，它的作用域仅限于当前会话（Connection）。用户变量以 @ 开头，可以存储临时数据，用于计算或在查询中传递值。用户变量不需要事先声明，也没有严格的类型定义，系统会根据上下文推断类型。

用户变量的特点

作用域：仅在当前会话中有效，断开连接后变量会被清除。
命名规则：以 @ 开头，后面可以是字母、数字或下划线，例如 @var1。
类型推断：用户变量的类型根据其赋值内容动态决定。
使用限制：
- 用户变量在查询中使用时，取值和赋值的顺序未必是确定的，需要小心。
- 不能直接在 CREATE VIEW 或触发器中使用用户变量。
赋值和引用：
- 赋值：可以通过 SET 或 SELECT 语句赋值。
- 引用：在查询中直接使用 @变量名。

用户变量的声明和赋值

1. 使用 `SET` 语句

SET @var1 = 10;              -- 赋值为整数
SET @var2 = 'Hello';         -- 赋值为字符串
SET @var3 = 3.14;            -- 赋值为浮点数
SET @var4 = NULL;            -- 赋值为 NULL

2. 使用 `SELECT` 语句

SELECT @var1 := 100;         -- 将 100 赋值给 @var1
SELECT @var2 := CONCAT('Hi', ' MySQL');  -- 动态拼接字符串

3. 使用带查询结果的赋值

SELECT @max_salary := MAX(salary) FROM employees;  -- 将查询结果赋值给变量

用户变量的应用场景

1. 用于存储查询结果

用户变量可以在查询中存储计算的中间结果供后续使用。

SET @total_salary = 0;

SELECT @total_salary := @total_salary + salary 
FROM employees;   -- 计算总薪资
SELECT @total_salary;  -- 输出总薪资

2. 累计计算（Running Total）

用户变量适合用来实现累计求和等操作。

SELECT 
    employee_id, 
    salary, 
    @running_total := @running_total + salary AS cumulative_salary
FROM employees, (SELECT @running_total := 0) AS t
ORDER BY employee_id;

3. 条件判断

用户变量可以在 CASE 语句中搭配使用。

SELECT 
    employee_id,
    salary,
    @bonus := CASE 
        WHEN salary > 5000 THEN salary * 0.1
        ELSE salary * 0.05
    END AS bonus
FROM employees;

4. 递归计算（基于连续数据处理）

用于处理需要连续依赖上一行结果的场景，如连续计数、增长率等。

示例：计算连续重复出现的值的次数。

SELECT 
    Num,
    @count := CASE 
        WHEN @prev = Num THEN @count + 1
        ELSE 1
    END AS cnt,
    @prev := Num
FROM logs, (SELECT @prev := NULL, @count := 0) AS t;

5. 动态查询和控制

用户变量可以在存储过程或动态 SQL 中传递参数。

SET @table_name = 'employees';
SET @query = CONCAT('SELECT * FROM ', @table_name, ' WHERE salary > 5000');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项

计算顺序的问题：
- 用户变量在查询中被赋值和引用时，可能因优化器的执行顺序导致结果不确定。
- 推荐将赋值和查询分开处理，或使用子查询确保顺序。
示例：
```
SELECT (@var := salary) AS current_salary, @var + 1000 AS new_salary
FROM employees;
```
变量值的类型：
- MySQL 用户变量是无类型的，赋值后会动态转换。
- 在比较或计算时注意类型一致性，避免隐式转换。
NULL 值处理：
- 用户变量初始值为 NULL，若未赋值直接使用，可能导致意外的结果。

高级用法

1. 配合窗口函数

MySQL 8.0 引入窗口函数后，可以结合用户变量实现更复杂的逻辑。

WITH ranked_logs AS (
    SELECT 
        Num,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM Logs
)
SELECT 
    Num, 
    @gap := ROW_NUMBER() - @prev_row AS gap,
    @prev_row := ROW_NUMBER()
FROM ranked_logs, (SELECT @prev_row := 0) AS t;