相同记录集上相同的UPDATE SQL似乎与MariaDB不一致。

Question

MariaDB ：逻辑似乎不符合MariaDB的行为。

目标：创建一个自引用ID列值，以引用直接的前一条记录。

Description：我们有一个非常大的记录表，其中ID作为记录的主键和时间，分布在终端之间。我们希望创建一个列来保存同一终端当前记录的当前记录的前一条记录的ID值，具体取决于记录的时间，我们将使用该记录进行自联接，以便在同一记录集中并排选择当前记录的前一条记录，以提高查询性能，而不是在每次查询期间动态选择先前的记录。

Expectation：了解实际发生了什么，以及跨数据库引擎(至少MySQL & MariaDB)兼容的具有最佳性能的更新查询。

UPDATE tst_terminaldata SET PreviousID = NULL;

UPDATE tst_terminaldata AS TD
SET TD.PreviousID =
    (SELECT TDP.ID FROM
        (SELECT TDP2.ID, TDP2.Time, TDP2.TerminalID
                         FROM tst_terminaldata AS TDP2
                         ORDER BY 
  TDP2.Time DESC, TDP2.ID DESC /* Works for- Windows: MySQL 8.0.16, 5.7.11; Linux: MySQL 8.0.13, 8.0, 5.7.20 */
  TDP2.Time ASC, TDP2.ID ASC /* Works for- Windows: MariaDB 10.4.7; Linux: MariaDB 10.2.25, 10.1.40 */
        ) AS TDP
        WHERE TDP.TerminalID = TD.TerminalID
          AND TDP.Time < TD.Time
        LIMIT 1
    ) 
WHERE TD.PreviousID IS NULL;

SELECT TD.* FROM tst_terminaldata AS TD
      ORDER BY TD.TerminalID ASC, TD.Time DESC, TD.ID DESC;

Case：MySQL SQL在我看来是合乎逻辑的，而不是MariaDB SQL。但这两种方法都适用于各自的情况(如SQL中的注释)，但不应该这样做。

样本数据

ID  TerminalID  Time    PreviousID
1   1   2019-07-29 13:56:37 NULL
2   1   2019-07-29 13:56:52 NULL
3   2   2019-07-29 13:57:01 NULL
4   1   2019-07-29 13:57:02 NULL
5   2   2019-07-29 13:57:08 NULL
6   1   2019-07-29 13:57:17 NULL
7   2   2019-07-29 13:57:23 NULL
8   1   2019-07-29 13:57:32 NULL

Answer 1

• 派生表中的ORDER BY被MySQL和MariaDB (至少在您尝试过的版本中)忽略。
• 优化器可以按任何顺序显示行。
• MySQL和MariaDB的优化程序代码在这方面的差异约为5.6 / 10.0。

这应该可以解释为什么您会根据ORDER BY和版本得到不同的结果。

如果您正在每秒插入1700行，我希望您正在对它们进行批处理并使用SSD。

我不知道查询的目标是什么，但我建议在数据到达时消化数据，而不是挣扎于“滞后”类型的查询。

Answer 2

好像你需要迟滞函数-

UPDATE tst_terminaldata AS TD
SET TD.PreviousID = (SELECT TerminalID, LAG(TerminalID) OVER(PARTITION BY TerminalID ORDER BY TIME
                     FROM tst_terminaldata) T
WHERE T.TerminalID = TD.TerminalID

虽然我还没有试过，但我认为这应该是可行的。

Answer 3

可以将join与lag()结合使用。

update tst_terminaldata td join
       (select td2.*, lag(id) over (partition by TerminalID order by time) as prev_id
        from tst_terminaldata td2
       ) td2
       on td.id = td2.id
    set previousid = prev_id;