在SQL中，LIMIT是否按时间顺序返回插入到最后一行的行? [英] In SQL, does the LIMIT returns the row which is inserted the last in chronological order?

问题描述

假设，如果以下行按时间顺序插入到表中:

Suppose, if following rows are inserted in chronological order into a table:

行1，行2，行3，行4，...，行1000，行1001.

row1, row2, row3, row4, ..., row1000, row1001.

过一会儿，我们删除/删除最新的 row1001 .

After a while, we delete/remove the latest row1001.

如本文所述:如何在SqLite中获得前5名记录?
如果运行以下命令:

As in this post: How to get Top 5 records in SqLite?
If the below command is run:

SELECT * FROM <table> LIMIT 1;

• 它肯定会提供"row1000"吗?
• 如果否，那么是否有任何有效的方法来获取最新行没有遍历所有行?-即不使用 ORDER BY 和 DESC 的组合.

[注意:目前我使用的是"SQLite"，但对我而言，一般来讲我也很感兴趣.]

[Note: For now I am using "SQLite", but it will be interesting for me to know about SQL in general as well.]

推荐答案

您误解了SQL的工作方式.您正在考虑 逐行 ，这是错误的.SQL 不是 您所关注的遍历行"；它对作为集合"的数据进行操作.

You're misunderstanding how SQL works. You're thinking row-by-row which is wrong. SQL does not "traverse rows" as per your concern; it operates on data as "sets".

其他人指出，关系数据库 假定具有任何特定的顺序，因此您 必须使用 ORDER BY 明确指定顺序.

Others have pointed out that relational database cannot be assumed to have any particular ordering, so you must use ORDER BY to explicitly specify ordering.

但是(尚未提及)，为了确保其有效运行，您需要创建一个适当的索引.

无论是否有索引，正确的查询是:

Whether you have an index or not, the correct query is:

SELECT  <cols>
FROM    <table>
ORDER BY <sort-cols> [DESC] LIMIT <no-rows>

请注意，如果没有索引，数据库将加载所有数据，并可能在内存中排序以找到TOP n.

Note that if you don't have an index the database will load all data and probably sort in memory to find the TOP n.

如果您确实拥有适当的索引，则数据库将使用可用的最佳索引来尽可能高效地检索TOP n行.

If you do have the appropriate index, the database will use the best index available to retrieve the TOP n rows as efficiently as possible.

请注意， sqllite文档非常清楚. ORDER BY 上的部分说明了排序是不确定的.并且 LIMIT 一节中没有任何与此矛盾的东西(它只是限制了返回的行数).

Note that the sqllite documentation is very clear on the matter. The section on ORDER BY explains that ordering is undefined. And nothing in the section on LIMIT contradicts this (it simply constrains the number of rows returned).

如果返回多行的SELECT语句没有ORDER BY子句，则返回行的顺序是不确定的.

此行为也符合ANSI标准和所有主要的SQL实现.请注意，任何保证任何排序方式的数据库供应商都必须牺牲性能，以损害试图检索数据但不关心顺序的查询.(不利于业务.)

This behaviour is also consistent with the ANSI standard and all major SQL implementations. Note that any database vendor that guaranteed any kind of ordering would have to sacrifice performance to the detriment of queries trying to retrieve data but not caring about order. (Not good for business.)

作为一个附带说明，关于排序的错误假设是一个容易犯的错误(类似于关于未初始化的局部变量的错误假设).

As a side note, flawed assumptions about ordering is an easy mistake to make (similar to flawed assumptions about uninitialised local variables).

RDBMS实现很可能使顺序 出现 保持一致.他们遵循某种算法来添加数据，某种算法来获取数据.结果，他们的操作具有很强的 可重复性 (这是我们对计算机的爱(或恨)).因此，事情重复地 外观 相同.

RDBMS implementations are very likely to make ordering appear consistent. They follow a certain algorithm for adding data, a certain algorithm for retrieving data. And as a result, their operations are highly repeatable (it's what we love (and hate) about computers). So things repeatably look the same.

理论示例:

• 插入行会导致该行被添加到下一个可用空间.因此，数据按顺序显示.但是，如果更新不再适合该行，则必须将其移动到新位置.
• DB引擎可能会从聚簇索引页面和 sem 顺序检索数据，以将聚簇索引用作自然排序" ...直到一天之内，页面拆分将其中一个页面放入一个不同的位置.*或者新版本的DMBS可能会缓存某些数据以提高性能，并突然更改订单.
• Inserting a row results in the row being added to the next available free space. So data appears sequential. But an update would have to move the row to a new location if it no longer fits.
• The DB engine might retrieve data sequentially from clustered index pages and seem to use clustered index as the 'natural ordering' ... until one day a page-split puts one of the pages in a different location. * Or a new version of the DMBS might cache certain data for performance, and suddenly order changes.

真实示例:

• GROUP BY 的MS SQL Server 6.5实现也具有按分组依据列进行排序的副作用.当MS(版本7或2000)实现一些性能改进时，默认情况下 GROUP BY 将以散列顺序返回数据.许多人指责MS中断了查询，而实际上他们做出了错误的假设并且未能根据需要对结果进行 ORDER BY .
• The MS SQL Server 6.5 implementation of GROUP BY had the side-effect of also sorting by the group-by columns. When MS (in version 7 or 2000) implemented some performance improvements, GROUP BY would by default, return data in a hashed order. Many people blamed MS for breaking their queries when in fact they had made false assumptions and failed to ORDER BY their results as needed.

这就是为什么特定顺序的唯一保证是使用 ORDER BY 子句.

这篇关于在SQL中，LIMIT是否按时间顺序返回插入到最后一行的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！