MariaDB上的“GROUP BY”行为与MySQL不同

8nuwlpux 于 2023-03-07 发布在 Mysql

关注(0)|答案(2)|浏览(171)

我已经被告知很多次，同样的查询MariaDB将工作就像它是如何在MySQL ...直到我遇到这个问题。
最近，我尝试将一个应用程序从MySQL（InnoDB）克隆到MariaDB（XtraDB）。虽然MariaDB运行MySQL查询时不需要任何更改，但我惊讶地发现，相同的查询在两个平台上的行为实际上非常不同，特别是在ORDER BY和GROUP BY中。
例如：

MyTable
    =======
    +----+----------+---------------------+-----------+
    | id | parentId | creationDate        | name      |
    +----+----------+---------------------+-----------+
    | 1  | 2357     | 2017-01-01 06:03:40 | Anna      |
    +----+----------+---------------------+-----------+
    | 2  | 5480     | 2017-01-02 07:13:20 | Becky     |
    +----+----------+---------------------+-----------+
    | 3  | 2357     | 2017-01-03 08:20:12 | Christina |
    +----+----------+---------------------+-----------+
    | 4  | 2357     | 2017-01-03 08:20:15 | Dorothy   |
    +----+----------+---------------------+-----------+
    | 5  | 5480     | 2017-01-04 09:25:45 | Emma      |
    +----+----------+---------------------+-----------+
    | 6  | 1168     | 2017-01-05 10:30:10 | Fiona     |
    +----+----------+---------------------+-----------+
    | 7  | 5480     | 2017-01-05 10:33:23 | Gigi      |
    +----+----------+---------------------+-----------+
    | 8  | 1168     | 2017-01-06 12:46:34 | Heidi     |
    +----+----------+---------------------+-----------+
    | 9  | 1168     | 2017-01-06 12:46:34 | Irene     |
    +----+----------+---------------------+-----------+
    | 10 | 2357     | 2017-01-07 14:58:37 | Jane      |
    +----+----------+---------------------+-----------+
    | 11 | 2357     | 2017-01-07 14:58:37 | Katy      |
    +----+----------+---------------------+-----------+

基本上，我希望从查询中获得的是每个GROUPing（即parentId）的最新记录。最新记录指的是MAX（creationDate）和MAX（id）
因此，对于上面的示例，由于只有三个不同的parentId值，我希望得到：

+----+----------+---------------------+-----------+
    | id | parentId | creationDate        | name      |
    +----+----------+---------------------+-----------+
    | 11 | 2357     | 2017-01-07 14:58:37 | Katy      |
    +----+----------+---------------------+-----------+
    | 9  | 1168     | 2017-01-06 12:46:34 | Irene     |
    +----+----------+---------------------+-----------+
    | 7  | 5480     | 2017-01-05 10:33:23 | Gigi      |
    +----+----------+---------------------+-----------+

最初，应用程序具有类似于以下方式的查询：

SELECT * FROM
  ( SELECT * FROM `MyTable` WHERE `parentId` IN (...)
    ORDER BY `creationDate` DESC, `id` DESC ) AS `t` 
  GROUP BY `parentId`;

在MySQL上，这是可行的，因为内部查询将排序，然后外部查询从内部查询的结果中获取每个GROUP的第一个，外部查询基本上遵循内部查询的排序。
但是在MariaDB上，外部查询将忽略内部查询结果的排序。

+----+----------+---------------------+-----------+
    | id | parentId | creationDate        | name      |
    +----+----------+---------------------+-----------+
    | 1  | 2357     | 2017-01-01 06:03:40 | Anna      |
    +----+----------+---------------------+-----------+
    | 2  | 5480     | 2017-01-02 07:13:20 | Becky     |
    +----+----------+---------------------+-----------+
    | 6  | 1168     | 2017-01-05 10:30:10 | Fiona     |
    +----+----------+---------------------+-----------+

为了在MariaDB上实现同样的行为，我提出了类似这样的方法（但不确定这是否准确）。

SELECT `t1`.* FROM `MyTable` `t1` LEFT JOIN `MyTable` `t2` ON (
        `t1`.`parentId` = `t2`.`parentId`
    AND `t2`.`parentId` IN (...)
    AND `t1`.`creationDate` <= `t2`.`creationDate`
    AND `t1`.`id` < `t2`.`id`)
  ) WHERE `t2`.`id` IS NULL;

现在的问题是...如果我要重写查询，我必须重写数百个查询...它们彼此之间多少有点不同。
我想知道在座各位是否有任何想法，可以让我尽可能少地做一些改变。
先谢谢大家。

mysql

来源：https://stackoverflow.com/questions/42312547/group-by-on-mariadb-behaves-differently-from-mysql

2条答案

按热度按时间

mwg9r5ms1#

是的，这是一个只有链接的答案。但是链接是到MariaDB网站的。
以下是对“不相容性”的另一种讨论：https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/
从技术上讲，MySQL实现了对Ansi标准的扩展。很久以后，它决定删除它，所以我想你会发现MySQL已经迁移到了MariaDB。
下面是“快速”实现分组最大化的方法列表，这可能是您正在尝试的方法：https://mariadb.com/kb/en/mariadb/groupwise-max-in-mariadb/

赞(0）回复(0）举报 2023-03-07

xvw2m8pv2#

您的第一个查询可能会在MySQL中工作，但其行为没有文档记录：您正在按groupid分组，但您选择了带有 * 的非聚合列，并且这些非聚合列中任何一个的值都是undefined-如果您得到的值是遇到的第一个值，那只是“运气问题”。
诚然，即使不能认为它是正确的，但在MySQL上我从未见过这种“技巧”失败（在stackoverflow上有大量投票赞成的答案建议您使用这种技巧），但MariaDB使用不同的优化引擎，您不能依赖MySQL未记录的行为。
您的第二个查询需要做一些调整：

and (
  `t1`.`creationDate` < `t2`.`creationDate`
  or (
    `t1`.`creationDate` = `t2`.`creationDate`
     and `t1`.`id` < `t2`.`id`
  )
)

因为首先你是按创建日期排序的，然后如果多于一个记录共享相同的创建日期，你将得到具有最高ID的记录。
还有其他方法可以编写相同的查询，例如：

select * from mytable
where id in (
  select max(m.id)
  from mytable m inner join (
    select parentID, max(creationDate) as max_cd
    from mytable
    group by ParentID
  ) t on m.parentID = t.parentID and m.creationDate = t.max_cd
  group by m.parentID, m.creationDate
)

但是每个查询都需要单独重写。
编辑
你的例子稍微复杂一点，因为你是按creationDate和id排序的。让我来解释一下。首先，对于每个parentID，你必须获取最后一个creationDate：

select parentID, max(creationDate) as max_cd
from MyTable
group by parentID

那么对于每个max creationDate你必须得到最大的id：

select t.parentID, t.max_cd, max(t.id) as max_id
from
  MyTable t inner join (  
    select parentID, max(creationDate) as max_cd
    from MyTable
    group by parentID
  ) t1 on t.parentID = t1.parentID and t.creationDate = t1.max_cd
group t.parentID, t.max_cd

那么你必须得到这个查询返回id的所有记录。在这个特定的上下文中，与表本身的LEFT JOIN应该更容易写，性能也更好。

赞(0）回复(0）举报 2023-03-07

我来回答

MariaDB上的“GROUP BY”行为与MySQL不同

2条答案

相关问题

热门标签

最新问答