mysql用户变量的应用：查询所有的节点或查询所有的子节点

用户变量:以"@"开始，形式为"@变量名"

创建用户定义的变量，请使用格式@variable_name，其中variable_name包含字母数字字符。从MySQL 5.7.5开始，用户定义变量的最大长度为64个字符

用户定义的变量不区分大小写。这意味着@id和@ID是一样的

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

1、用户变量是以"@"开头的。局部变量没有这个符号；

2、定义变量不同。用户变量使用set语句，局部变量使用declare语句定义；

3、作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

一、用户变量赋值

1、使用set进行赋值，此时可以用:=或=

语法：

SET @variable_name := value;
# or
SET @variable_name = value;

没有指定GLOBAL 或SESSION,那么默认将会定义用户变量。

example:

SET @num = 2;
SELECT @num as test;

结果：

2、select语句中赋值，必须使用 := 赋值运算符，因为在SELECT语句中，MySQL将=运算符视为等于运算符

语法：

SELECT @variable_name := value; 

example:

赋值之后，可以在允许表达式的后续语句中使用变量，例如，在WHERE子句，INSERT或UPDATE语句

举例

先创建一个logs表，如下所示：

1、获取表中最大的num并赋值@max

SELECT @max := MAX(num) FROM `logs`

结果：

2、根据@max筛选数据，@max 存储在临时表，关联查找最大值对应的行数据

select id,num from logs,(SELECT @max := MAX(num) FROM `logs`) t where num=@max

结果：

3、用户定义的变量只能包含单个值。如果SELECT语句返回多个值，则变量将采用结果中最后一行的值。

select @max := num from logs where num > 0

结果：

此时select @max as test

我们给logs表再增加一行数据来验证：

说明如果SELECT语句返回多个值，则变量将采用结果中最后一行的值。

用户变量在mysql客户端退出后,会自动消失,之后我打开客户端,使用"select @max;" 显示变了的值为null,说明,未定义的变量初始化是null.

我们先给@max赋值为5

关闭navicat后再次打开，执行select @max as test，结果为null。

二、用户变量的应用

先创建表t_user，如下所示：

通过自连接，形成一个树状结构

问：如何查询子节点所有的父节点，例如，如何查询5上面所有的父节点。

SELECT
@r AS _id,
(SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id,
@l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,
t_user h WHERE @r <> 0

@r表示声明了一个用户变量r，@r ：保存当前节点。（从叶节点往根节点找，@r 保存当前到哪个位置了）。@r 初始为要找的节点。即 @r := 5

_id：当前节点。@r <> 0 表示_id!=0，因为_id等于0是不合理的

结果如下:

lvl表示level，即：1=自身，2=父节点，3=父节点的父节点，以此类推。如果不需要，可以去掉。

如上图所示，只需要修改我标注的两个地方即可（当然，表明和字段名也要做响应修改）：

• 5：要查询的子节点
• 0: 如果该节点没有父节点(即是顶节点)，则会被置位 0

下面开始拆解SQL：

（1）、首先初始化变量@r和@l。即(SELECT @r := 5, @l := 0) vars
（2）、@r AS _id 将当前节点作为查询结果 _id
（3）、查找当前节点_id的父节点，将其赋给@r，然后作为查询结parent_id。即 (SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id
（4）、将@l加1，并作为查询结果lvl。即 @l := @l + 1 AS lvl
　　循环 2,3,4 步骤，得到如下结果：

去掉where条件就是上图的执行结果

（5）、执行where条件，过滤掉 @r <> 0 (_id!=0)，

SELECT
@r AS _id,
(SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id,
@l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,
t_user h WHERE   @r <> 0 

结果如下：

三、查询所有的父节点（包含自己）

<select id="selectTreeParentByNo" resultMap="resultMap">
        SELECT T2.* FROM (
        　　SELECT
        　　@R AS _id,
        　　( SELECT @R := parent_no FROM t_org WHERE org_no = _id ) AS PARENT_ID,
        　　@L := @L + 1 AS LVL
        　　FROM
        　　(SELECT @R := #{orgNo}, @L := 0) VARS, t_org h
        　　WHERE @R <![CDATA[>=]]> 0
        ) T1 INNER JOIN t_org T2 ON T1._id = T2.org_no
        ORDER BY T1.LVL DESC;
    </select>

四、查询所有的父节点（不包含自己）

<select id="selectTreeParentByNo" resultMap="resultMap">
        SELECT T2.* FROM (
        SELECT
        @R AS _id,
        ( SELECT @R := PARENT_no FROM t_org WHERE org_no = _id ) AS PARENT_ID,
        @L := @L + 1 AS LVL
        FROM
        (SELECT @R := #{orgNo}, @L := 0) VARS, t_org h
        WHERE @R <![CDATA[>=]]> 0
        ) T1 INNER JOIN t_org T2 ON T1._id = T2.org_no AND T2.org_no != #{orgNo}
        ORDER BY T1.LVL DESC;
    </select>

五、查询当前节点的所有的子节点（包含自己）

    select t3.* from (
              select t1.*,
                   if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), if (t1.id = #{orgNo},#{orgNo},0)) as ischild
               from (
                     select org_no as id, parent_no as parent_id,org_name as orgName,t.* from t_org t
                ) t1,
                (select @pids :=  #{orgNo}) t2
        ) t3 where ischild <![CDATA[<>]]> '0'
    </select>

if函数：IF(expr1,expr2,expr3) ：如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。

下面开始拆解SQL：

（1）、首先初始化变量@pids，将当前部门的orgNo赋值给@pids。即(select @pids := #{orgNo}) t2

（2）、查询t_org表的所有记录，将t_org表的org_no赋值给id，parent_no赋值给parent_id，org_name赋值给orgName。即(select org_no as id, parent_no as parent_id,org_name as orgName,t.* from t_org t) t

（3）、如果parent_id在@pids中，该部门为当前节点的子部门，即find_in_set(parent_id, @pids) > 0为true，则将该部门的id拼接到@pids后面，逗号相隔。如果该部门不是当前节点的子部门，但是该部门的id即为当前节点的orgNo，即该部门为当前部门，则if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), if (t1.id = #{orgNo},#{orgNo},0))的值为当前节点的orgNo；如果该部门不是当前节点的子部门，也不是当前节点，则通过where后面的条件（ischild != 0）去除。

六、查询当前节点的所有的子节点（不包含自己）

    select t3.* from (
              select t1.*,
                   if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
               from (
                     select org_no as id, parent_no as parent_id,org_name as orgName,t.* from t_org t
                ) t1,
                (select @pids :=  #{orgNo}) t2
        ) t3 where ischild <![CDATA[<>]]> '0'
    </select>