mysql - 具有 session 变量的类似 SQL 会产生不同的结果

Question

MariaDB 10.1.18

表P(id int AUTO_INCRMENT, rownum int, code int, s int, PRIMARY KEY(id));

select id, rownum, code, s from P order by id;
+----+--------+------+------+
| id | rownum | code | s    |
+----+--------+------+------+
|  1 |      1 |    5 |    1 |
|  2 |      2 |    5 |    1 | 
|  3 |      3 |    5 |    1 |
|  4 |      4 |    5 |    1 |
|  5 |      5 |    5 |    1 |
|  6 |      6 |    7 |    1 |
|  7 |      7 |    7 |    1 |
|  8 |      8 |    7 |    1 |
|  9 |      9 |    7 |    1 |
| 10 |     10 |    7 |    1 |
+----+--------+------+------+

问题:以下 2 个查询非常相似:第一个查询连接在 id 上，第二个查询连接在 rownum 上； id 和 rownum 列具有相同的值(见上表)，但计算列 N 中的查询结果不同:

查询 1:加入 id 列

SELECT P.id, P.rownum, P2.s,
IF(P2.s IS NULL, @val:=@val+1, @val) as N
FROM  P CROSS JOIN (SELECT @val:=0) init
LEFT JOIN P P2
ON (P.id+1=P2.id AND  P.s=1 AND  P2.s=1 AND P.code = P2.code)
ORDER BY P.id;

+----+--------+------+------+
| id | rownum | s    | N    |
+----+--------+------+------+
|  1 |      1 |    1 | 0    |
|  2 |      2 |    1 | 0    |
|  3 |      3 |    1 | 0    |
|  4 |      4 |    1 | 0    |
|  5 |      5 | NULL | 1    |
|  6 |      6 |    1 | 1    |
|  7 |      7 |    1 | 1    |
|  8 |      8 |    1 | 1    |
|  9 |      9 |    1 | 1    |
| 10 |     10 | NULL | 2    |
+----+--------+------+------+

查询 2:连接 rownum 列

SELECT P.id, P.rownum, P2.s,
IF(P2.s IS NULL, @val:=@val+1, @val) as N
FROM  P CROSS JOIN (SELECT @val:=0) init
LEFT JOIN P P2
ON (P.rownum+1=P2.rownum AND  P.s=1 AND  P2.s=1 AND P.code = P2.code)
ORDER BY P.id;

+----+--------+------+------+
| id | rownum | s    | N    |
+----+--------+------+------+
|  1 |      1 |    1 |    0 |
|  2 |      2 |    1 |    0 |
|  3 |      3 |    1 |    0 |
|  4 |      4 |    1 |    0 |
|  5 |      5 | NULL |    1 |
|  6 |      6 |    1 |    0 |
|  7 |      7 |    1 |    0 |
|  8 |      8 |    1 |    0 |
|  9 |      9 |    1 |    0 |
| 10 |     10 | NULL |    2 |
+----+--------+------+------+

Answer 1

正如 MariaDB knowledge base 中明确记录的那样和 MySQL manual ，您不应在同一语句中读取用户定义的变量并设置其值，除非该语句是SET。对于其他语句，它是不安全的，并且不能保证结果，正如您的示例清楚地表明的那样。

附加说明:您在问题中讨论的变量通常被称为“用户定义变量”或“用户变量”，而不是“系统变量”、“全局变量”和“ session ”变量”，每个变量都暗示系统定义的变量，可以是全部变量，也可以仅限于给定范围。