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}是用PythonString.format()方法填充的变量。

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

我大致理解了子选择和间隔变量是如何工作的，但是有很多我不明白。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. 注意，查询通过第3列HardwareAddress对结果排序，然后由ID降序。

第一列是布尔值，表示该行的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列有索引，则更好。

Answer 2

所有子查询都必须别名。init子查询所做的全部工作就是初始化会话/@变量(它等同于在运行查询之前只执行SET @prev := ''; )。

Answer 3

FROM ( SELECT ... ) init
JOIN ( SELECT ... ) x

可以写

FROM ( SELECT ... ) AS init
JOIN ( SELECT ... ) AS x

后一种语法有助于暗示init和x是子查询的“别名”。这些别名通常在查询的其他地方需要，但在本例中不需要，特别是当有ON子句时。(不过，它们是强制性的。)实际使用的名称并不重要。

SELECT @prev := ''

只返回一行；它不是真正使用的。它的副作用是将“用户变量”@prev分配给空字符串。外部查询取决于在其他子查询之前执行的子查询。

顺便说一下，这两个子查询称为“派生”表，因为它们是在FROM或JOIN之后。

SELECT HardwareAddress != @prev AS first,  -- Outputs 1 or 0
       @prev := HardwareAddress,           -- Outputs HA, and sets @prev
    ...
    ORDER BY
        HardwareAddress,                   -- to go thru in order
        ID DESC

将1显示为first，每当HardwareAddress中发生“更改”时，目的是让1位于第一行，然后是一堆带有0的行，然后返回到1以获取不同的地址。这两个表达式形成了一个“模式”来实现这个目标。

HardwareAddress, EventValue, ID

最后，您可以看到HardwareAddress (以及其他一些东西)。

最好是在应用程序代码中格式化输出，而不是尝试在SQL中进行@prev游戏。

WHERE first

注意，0表示FALSE，其他任何东西都表示TRUE。first是上面讨论的列的别名。因此，当它是1时，它会显示；否则它会被跳过。

其效果是显示每组的第一组，按HardwareAddress分组。顺便说一句，这在简单的GROUP BY中是不可能的；需要某种形式的欺骗。(参见我对http://mysql.rjweb.org/doc.php/groupwise_max的讨论。)

SELECT
    HardwareAddress,

第二个派生表必然有一些额外的列。有了这个外部查询，您就可以扔掉这些额外的内容，专注于所需的输出--即HardwareAddress，而不是first，而不是HardwareAddress的额外副本。

SELECT ...
    CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm'

这将EventValue的一部分从十六进制转换为十进制。表达式本可以在派生表中完成；它没有多大区别。

, ID

这似乎是虚假的信息，后来被扔掉。

              WHERE Time > {unixtime}
                AND EventType = 104
                AND HardwareAddress IN ({disps})

(我假设您理解WHERE条款。)我推荐以下方法来提高性能，特别是在表很大的情况下：

INDEX(EventType, Time),
INDEX(EventType, HardwareAddress)