PostgreSQL 或 MySQL 中的默认选择顺序是什么? [英] What is the default select order in PostgreSQL or MySQL?

问题描述

我在 PostgreSQL 文档中读到，如果没有 ORDER 语句，SELECT 将以未指定的顺序返回记录.

I have read in the PostgreSQL docs that without an ORDER statement, SELECT will return records in an unspecified order.

最近在一次采访中，我被问到如何在没有 PK 或 created_at 或其他可用于订单的字段的情况下，按照记录插入的顺序 SELECT 记录.采访我的高级开发人员坚持认为，如果没有 ORDER 语句，记录将按照插入的顺序返回.

Recently on an interview, I was asked how to SELECT records in the order that they inserted without an PK or created_at or other field that can be used for order. The senior dev who interviewed me was insistent that without an ORDER statement the records will be returned in the order that they were inserted.

这对于 PostgreSQL 是真的吗?MySQL 是真的吗?或者任何其他关系型数据库?

Is this true for PostgreSQL? Is it true for MySQL? Or any other RDBMS?

推荐答案

我可以回答 MySQL.我不知道 PostgreSQL.

I can answer for MySQL. I don't know for PostgreSQL.

默认的顺序不是插入的顺序，一般.

The default order is not the order of insertion, generally.

在 InnoDB 的情况下，默认顺序取决于为查询读取索引的顺序.您可以从 EXPLAIN 计划中获取此信息.

In the case of InnoDB, the default order depends on the order of the index read for the query. You can get this information from the EXPLAIN plan.

对于 MyISAM，它按照从表中读取的顺序返回订单.这可能是插入的顺序，但 MyISAM 会在你删除记录后重用间隙，所以新的行可能会更早地存储.

For MyISAM, it returns orders in the order they are read from the table. This might be the order of insertion, but MyISAM will reuse gaps after you delete records, so newer rows may be stored earlier.

这些都不能保证；这只是当前实现的副作用.MySQL 可以在下一个版本中更改实现，使结果集的默认顺序不同，而不会违反任何记录的行为.

None of this is guaranteed; it's just a side effect of the current implementation. MySQL could change the implementation in the next version, making the default order of result sets different, without violating any documented behavior.

因此，如果您需要特定顺序的结果，则应在查询中使用 ORDER BY.

So if you need the results in a specific order, you should use ORDER BY on your queries.

这篇关于PostgreSQL 或 MySQL 中的默认选择顺序是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！