当多个记录对“Order By”子句中定义的列具有相同的值时，是否存在从数据库返回的隐式行顺序？

I've been working mostly in Oracle and PostgreSQL databases specifically, but I am curious if there is any common standard. As the title suggests, I have been looking for answers to a question concerning the Order By clause of a SQL statement. For example, if I had some basic table Users:

我一直专注于Oracle和PostgreSQL数据库,但我很好奇是否有任何通用标准。正如标题所示,我一直在寻找有关SQL语句的Order By子句的问题的答案。例如,如果我有一些基本表用户:

-------------------------------------------
| id |     name     | birth_date | gender |
-------------------------------------------
|  1 |  xx_coolKid  | 12-DEC-1960|   M    |
-------------------------------------------
|  2 |  def@test.co | 24-JUN-1976|   F    |
-------------------------------------------
|  3 |    NULL?     | 30-AUG-1990|   M    |
-------------------------------------------
|  4 |    JeffR     | 12-DEC-1960|   M    |
-------------------------------------------
|  5 |    lol19     | 12-DEC-1960|   F    |
-------------------------------------------

and I were to run the query:

我要运行查询:

SELECT * FROM Users ORDER BY gender, birth_date;

Is there any particular order to the rows returned that match on both gender and birth_date? Or is the return order of those matching rows not guaranteed, as if no ORDER BY clause were defined?

对于返回的性别和birth_date匹配的行,是否有任何特定顺序?或者是否保证那些匹配行的返回顺序,就好像没有定义ORDER BY子句一样?

1 个解决方案

#1

The return order is not guaranteed. There is explicitly no standard in this area. The return order when keys have the same value is arbitrary, and may change between runs on the same database on the same data.

退货订单无法保证。这个领域没有明确的标准。当键具有相同值时的返回顺序是任意的,并且可以在相同数据上的相同数据库上的运行之间改变。

Some sort algorithms are stable, meaning that record order on the inputs is an implicit final order key. However, databases do not generally implement stable sorts.

某些排序算法是稳定的,这意味着输入上的记录顺序是隐式最终订单键。但是,数据库通常不会实现稳定的排序。

The Microsoft SQL documentation on order by actually explains this quite well:

订购时的Microsoft SQL文档实际上很好地解释了这一点:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

要使用OFFSET和FETCH在查询请求之间获得稳定的结果,必须满足以下条件:

(1) The underlying data that is used by the query must not change. That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation. For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

(1)查询使用的基础数据不得更改。也就是说,查询所触及的行不会更新,或者查询中的所有页面请求都是使用快照或可序列化事务隔离在单个事务中执行的。有关这些事务隔离级别的更多信息,请参阅SET TRANSACTION ISOLATION LEVEL(Transact-SQL)。

(2) The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

(2)ORDER BY子句包含保证唯一的列或列组合。

These conditions would be true in most databases.

在大多数数据库中都会出现这些情况。

#1