LIMIT OFFSET，LENGTH是否需要ORDER BY才能进行分页? [英] Does LIMIT OFFSET,LENGTH require ORDER BY for pagination?

问题描述

我有一个MyISAM表，整个表有28,900个.我正在按1500的块进行处理，这是这样的查询:

I have a MyISAM table with 28,900 entires. I'm processing it in chunks of 1500, which a query like this:

SELECT * FROM table WHERE id>0 LIMIT $iStart,1500

然后我循环遍历此操作，并每次将$ iStart递增1500.

Then I loop over this and increment $iStart by 1500 each time.

问题是查询在某些情况下返回相同的行.例如，LIMIT 0,1500查询返回的行数与LIMIT 28500,1500查询相同.

The problem is that the queries are returning the same rows in some cases. For example the LIMIT 0,1500 query returns some of the same rows as the LIMIT 28500,1500 query does.

如果我不对行进行排序，难道我不希望使用LIMIT进行分页吗?

If I don't ORDER the rows, can I not expect to use LIMIT for pagination?

(发生这些查询时，该表是静态的，没有其他查询在进行，这会改变其行).

推荐答案

几乎与其他所有SQL引擎一样，除非指定ORDER BY子句，否则MySQL MyISAM表根本无法保证行的返回顺序. .通常，它们返回的顺序将是它们从文件系统中读取的顺序，根据更新，删除甚至是缓存的选择的状态，查询的顺序可能会因查询而异.

Like pretty much every other SQL engine out there, MySQL MyISAM tables make no guarantees at all about the order in which rows are returned unless you specify an ORDER BY clause. Typically the order they are returned in will be the order they were read off the filesystem in, which can change from query to query depending on updates, deletes, and even the state of cached selects.

如果要避免同一行返回不止一次，则必须按某种顺序排序，主键是最明显的候选者.

If you want to avoid having the same row returned more than once then you must order by something, the primary key being the most obvious candidate.

这篇关于LIMIT OFFSET，LENGTH是否需要ORDER BY才能进行分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！