时间戳

首页 > 解决方案 > mySQL 8 中带有 order by、limit 和括号的奇怪行为

mysql - mySQL 8 中带有 order by、limit 和括号的奇怪行为

问题描述

假设我有一个包含以下值的表

在此处输入图像描述

我执行以下查询并获得附加的结果

SELECT * FROM testvalues ORDER BY textval;

在此处输入图像描述

到目前为止一切都很好。我现在将添加一个 LIMIT 语句。

SELECT * FROM testvalues ORDER BY textval LIMIT 3;

在此处输入图像描述

仍然一切看起来都很好。但是请注意当我在查询中添加括号时会发生什么。

(SELECT * FROM testvalues ORDER BY textval) LIMIT 3;

在此处输入图像描述

关于括号为什么会导致顺序不正确的任何想法。似乎使用括号仅用于确保在应用限制之前完成内部查询并因此对其进行排序。但似乎它会导致应用限制并完全丢弃排序。这是一个错误吗？一个已知的错误？我没有看到任何报道。

这是因为最近从 MySQL5.5 升级到 MySQL8 以及它们如何处理包含 order by 和/或 limit 操作的 UNION 查询。无论它们是否是联合的一部分，都可能使用了太多括号。我们的一些 SQL 是生成的，因此在某些情况下，引擎可能会自动添加括号，以预测结果可能会用于更大的联合查询。反正。我偏离了核心问题。

编辑/更新：

正如 nbk 和 nick 所指出的，括号的使用，无论在这种情况下看起来多么无害，都会导致 MySQL 将其作为子查询来处理。但是，以下查询实际上有效，这似乎与给出的解释相反。

SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3;

在此处输入图像描述

现在这是子查询的完整形式，但是看起来它确实在子查询中应用了排序，然后限制了结果。

编辑2：

跟进尼克的回应。是的，这是一个实际查询的示例，为了清楚起见，它已经过简化，并使用模拟数据来防止泄露敏感的真实数据。

我想我对原始问题的回答基本上是“MySQL 已决定将其优化为子查询，因此忽略 ORDER BY”。这让我很头疼。为什么以下查询在功能上看起来相同时处理方式不同。

(SELECT * FROM testvalues ORDER BY textval) LIMIT 3;

SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3;

标签： mysqlsql-order-bylimit

解决方案

在子查询/派生表中排序不会影响最终输出；这些行仅按外部级别ORDER BY子句排序。由于您没有，因此最后一个查询中结果的顺序是不确定的。如果写成，它将正常工作

(SELECT * FROM testvalues) ORDER BY textval LIMIT 3;

MariaDB 知识库中有关于这种行为的描述。

请注意，当您尝试“真实”子查询时，即

SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3

优化器将传播该ORDER BY子句，因为它满足手册中描述的条件：

如果这些条件都为真，优化器将派生表或视图引用中的 ORDER BY 子句传播到外部查询块：

  • 外部查询未分组或聚合。

  • 外部查询未指定 DISTINCT、HAVING 或 ORDER BY。

  • 外部查询将此派生表或视图引用作为 FROM 子句中的唯一来源。

当这些条件不成立时：

否则，优化器会忽略 ORDER BY 子句。

推荐阅读