MySQL 区间变量和子查询符号

Question

我继承了这个 MySQL 查询作为一些遗留代码:

       SELECT
        HardwareAddress, CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm'
        FROM ( SELECT @prev := '') init
        JOIN
            ( SELECT HardwareAddress != @prev AS first,
                @prev := HardwareAddress,
                HardwareAddress, EventValue, ID
                FROM Events   
                    WHERE Time > {unixtime}
                    AND EventType = 104
                    AND HardwareAddress IN ({disps})
                    ORDER BY
                        HardwareAddress,
                        ID DESC
            ) x
       WHERE first;

{unixtime} 和 {disps} 是用 Python String.format() 方法填充的变量。

我很难从这个查询中创建新功能，因为没有人了解它是如何工作的，而且我也找不到足够的文档。我所知道的是，该查询从 IoT 设备发送的长十六进制字符串中提取了一个名为“算法”的值列表。

subselect和interval变量的工作原理我大概明白了，但是还有很多不明白。 FROM (SELECT @prev := '') init 行如何工作？查询的最后两行也让我感到困惑。为什么子查询别名为 x 而没有任何内容引用它，WHERE first 到底是什么意思？

如果有人能够向我介绍这段代码的作用，我将不胜感激。

Answer 1

让我们将 SQL 分成几部分。整体的核心是 JOINed 子查询:

SELECT
  HardwareAddress != @prev AS first,
  @prev := HardwareAddress,
  HardwareAddress,
  EventValue,
  ID
FROM Events
WHERE
    Time > {unixtime}
AND EventType = 104
AND HardwareAddress IN ({disps})
ORDER BY HardwareAddress, ID DESC

1. 第 1 列:(还)不知道 @prev 是什么，我们看到运算符是 !=。这意味着无论其操作数是什么，第 1 列都将是一个二进制值。在 MySQL 中，1 或 0。

2. 第 2 列:将 SQL 变量 @prev 设置为当前匹配行的值。下面进行讨论，但就查询而言，结果将始终为 NULL。

3. 第 3、4、5 列:我假设不言自明。

4. 限制条件 1、2、3:我假设不言自明。

5. ORDER BY:值得注意的是，查询按第 3 列 HardwareAddress 对结果进行排序，然后按 ID 降序。

然后第一列是一个 bool 值，表示此行的 HardwareAddress 列是否与上一行的相同。在上下文中，1 表示这是 HardwareAddress 的第一行，给定 ORDER BY。

因此，查询将返回如下结果:

+-------+--------------------------+-------------------+------------+-----+
| first | @prev := HardwareAddress | HardwareAddress   | EventValue | ID  |
+-------+--------------------------+-------------------+------------+-----+
|     1 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |  10 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   9 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   8 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   7 |
|     1 |                     NULL | ff:ff:9d:5f:f5:02 | ...        | 200 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  37 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  24 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  23 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  22 |
|     1 |                     NULL | ff:ff:9d:5f:f5:03 | ...        | 152 |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
+-----+----------------------------+-------------------+------------+-----+

将其与外部查询的约束放在一起，WHERE first，最终结果为:

+-------+--------------------------+-------------------+------------+-----+
| first | @prev := HardwareAddress | HardwareAddress   | EventValue | ID  |
+-------+--------------------------+-------------------+------------+-----+
|     1 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |  10 |
|     1 |                     NULL | ff:ff:9d:5f:f5:02 | ...        | 200 |
|     1 |                     NULL | ff:ff:9d:5f:f5:03 | ...        | 152 |
+-----+----------------------------+-------------------+------------+-----+

换句话说，整个查询尝试获取给定顺序中每个 HardwareAddress 的第一个。神奇的 FROM (SELECT @prev := '') init？这只是初始化 SQL 变量 @prev 以便在随后的子查询中使用。尾部 ... ID DESC) x 部分将内部查询别名为 x。人们可能会利用这些别名进一步连接更复杂的查询，但在这种情况下，它们是出于 MySQL 语法原因。您可以忽略它们。

总而言之，这是一种获取与每个 HardwareAddress 关联的最大 ID 的非常低效的方法。如果查询需要每列的最大值，那么直接用MAX询问即可。考虑:

SELECT
  HardwareAddress,
  CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm',
  MAX(ID) AS ID
FROM Events
WHERE
    Time > {unixtime}
AND EventType = 104
AND HardwareAddress IN ({disps})
GROUP BY 1, 2;

您的输出中将有一个额外的 ID；如果代码太脆弱而无法处理新列，您可以类似地将其屏蔽掉，就像原始查询对子选择所做的那样:

SELECT HardwareAddress, CONV(...) AS 'Algorithm'
FROM (SELECT ...) x

尽管 AND EventType 和 AND HardwareAddress 的选择性如何，这应该是一个更有效的查询。如果 ID 列有索引就更好了。