关于在SQL SELECT语句中使用变量的技术咨询及查询问题

我来帮你梳理下SQL（尤其是MySQL）里变量的正确用法，还有你现有查询存在的问题以及优化方案～

一、你的现有查询的核心问题 #

你想用@z变量把ticketing_ticket.id_ticket_category的值传递给交叉连接的子查询，用来递归获取分类标签，但这里有个致命问题：MySQL的子查询和连接的执行顺序是由优化器决定的，完全不固定。交叉连接的子查询很可能在主查询的子查询X之前执行，这时候@z还没被赋值；就算是之后执行，也只会取子查询X最后一行的id_ticket_category值，根本没法实现每行工单对应自己分类标签的需求。

另外你那个递归获取父分类的子查询看起来没写完，但先聚焦变量使用的核心问题。

二、SQL变量使用的核心规则（MySQL场景） #

• 别赌执行顺序：变量的赋值和读取顺序完全依赖优化器的执行计划，你没法保证子查询、连接的执行顺序符合你的预期，跨查询块传递变量基本都会踩坑。
• 会话级变量要小心：变量一旦赋值，会保留到整个数据库会话结束，如果不手动初始化，下一次查询可能会用到旧值，导致结果错误。
• 能用JOIN/CTE就别用变量：如果是关联数据、递归查询这类场景，用显式JOIN或者CTE（递归公共表表达式）比变量传递可靠得多，代码也更易维护。

三、针对你需求的修正方案 #

你的需求应该是：获取当天关闭的工单，显示工单邮箱、处理时长，同时递归获取该工单分类的所有父分类标签并拼接成字符串对吧？

方案1：MySQL 8.0+ 用CTE递归查询（推荐） #

CTE是MySQL 8.0开始支持的，递归写法清晰可靠，彻底避开变量的坑：

WITH RECURSIVE category_hierarchy AS (
    -- 第一步：先获取每个工单的直接分类
    SELECT 
        tc.id_category, 
        tc.label, 
        tc.id_parent,
        tt.id_ticket
    FROM ticketing_ticket tt
    JOIN ticketing_category tc ON tt.id_ticket_category = tc.id_category
    WHERE DATE(tt.date_close) = CURDATE()
    
    UNION ALL
    
    -- 第二步：递归向上找父分类
    SELECT 
        parent.id_category, 
        parent.label, 
        parent.id_parent,
        child.id_ticket
    FROM category_hierarchy child
    JOIN ticketing_category parent ON child.id_parent = parent.id_category
    WHERE parent.id_category IS NOT NULL -- 直到没有父分类为止
)
SELECT 
    tt.id_ticket_category AS id,
    tt.email,
    CONCAT(TIMESTAMPDIFF(day, tt.date_create, tt.date_close), ' jours ') AS 'temps de traitement',
    GROUP_CONCAT(ch.label SEPARATOR ';') AS 'Domaines'
FROM ticketing_ticket tt
JOIN category_hierarchy ch ON tt.id_ticket = ch.id_ticket
WHERE DATE(tt.date_close) = CURDATE()
GROUP BY tt.id_ticket, tt.email, tt.date_create, tt.date_close, tt.id_ticket_category;

方案2：MySQL 5.x 低版本兼容方案（用变量但严格控制） #

如果你的MySQL版本低于8.0，不支持CTE，那可以把变量的赋值限制在每行的子查询里，确保每行工单都重新初始化变量，避免会话级污染：

SELECT 
    tt.id_ticket_category AS id,
    tt.email,
    CONCAT(TIMESTAMPDIFF(day, tt.date_create, tt.date_close), ' jours ') AS 'temps de traitement',
    (
        SELECT GROUP_CONCAT(tc.label SEPARATOR ';')
        FROM (
            -- 初始化变量为当前工单的分类ID
            SELECT @r := tt.id_ticket_category AS _id, @l := 0
            UNION ALL
            -- 递归向上找父分类，直到没有父分类为止
            SELECT @r := (SELECT id_parent FROM ticketing_category WHERE id_category = @r), @l := @l + 1
            FROM ticketing_category
            WHERE @r IS NOT NULL
        ) AS recursion
        JOIN ticketing_category tc ON recursion._id = tc.id_category
        WHERE tc.id_category IS NOT NULL
    ) AS 'Domaines'
FROM ticketing_ticket tt
WHERE DATE(tt.date_close) = CURDATE();

四、变量使用的最佳实践 #

• 简单场景才用变量：比如累计求和、临时存储单个计算结果这类简单场景，复杂关联、递归优先用JOIN/CTE。
• 每次用前初始化：比如在查询开头加SET @z = NULL;，避免会话里的旧值干扰当前查询。
• 同一个SELECT列表里用变量才安全：比如SELECT @a := @a + 1, @a这种写法，MySQL能保证赋值和读取的顺序，跨查询块的变量传递尽量别碰。

内容的提问来源于stack exchange，提问作者Mercer