MySQL 'LIMIT' 参数如何在 sql 中工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6736483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does 'LIMIT' parameter work in sql?
提问by Luis
I have 4000 rows for example, and I define X limit.
例如，我有 4000 行，我定义了 X 限制。
The query stops after it finds X rows? or the query finds all the rows and then takes X rows from the found rows?
查询在找到 X 行后停止？或者查询找到所有行，然后从找到的行中取出 X 行？
Thank you.
谢谢你。
回答by Yuck
From MySQL Reference Manual:
来自MySQL 参考手册：
If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.
如果将 LIMIT row_count 与 ORDER BY 一起使用，MySQL 会在找到排序结果的第一个 row_count 行后立即结束排序，而不是对整个结果进行排序。如果使用索引完成排序，则速度非常快。如果必须进行文件排序，则必须选择与没有 LIMIT 子句的查询匹配的所有行，并且必须对其中的大部分或全部进行排序，然后才能确定已找到第一个 row_count 行。在任何一种情况下，在找到初始行之后，都不需要对结果集的任何剩余部分进行排序，而 MySQL 不会这样做。
So it looks like it's possiblethat the entire result set is known before the LIMITis applied. But MySQL will try everything it can not to do so. And you can help it by providing useful indexes that match your queries.
因此，它看起来像它的可能，之前的整个结果集被称为LIMIT应用。但是 MySQL 会尝试它不能这样做的一切。您可以通过提供与您的查询匹配的有用索引来帮助它。
EDIT: Furthermore, if the set is not sorted it terminates the SELECToperation as soon as it's streamed enough rows to the result set.
编辑：此外，如果集合未排序，它会在将SELECT足够多的行流式传输到结果集后立即终止操作。
回答by ErickBest
SELECT * FROM your_tableLIMIT 0, 10
SELECT * FROM your_tableLIMIT0, 10
This will display the first 10 resultsfrom the database.
这将显示数据库中的前 10 个结果。
SELECT * FROM your_tableLIMIT 5, 5
SELECT * FROM your_tableLIMIT5, 5
This will show records 6, 7, 8, 9, and 10
这将显示记录6、7、8、9 和 10
It's like telling MySql;I want you to start counting from 5+1 or the 6th record, but Select only upto 5 records
这就像告诉MySql;我要你从5+1 或第 6 条记录开始计数，但最多只能选择5 条记录
回答by Dirk
I'm assuming you're thinking about MySQL, in which according to the documentation, the answer is it depends. If you're using a LIMIT(without a HAVING), then:
我假设你正在考虑 MySQL，根据文档，答案是它取决于。如果您使用的是LIMIT（没有HAVING），则：
- If you are selecting only a few rows with
LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan. - As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using
SQL_CALC_FOUND_ROWS.
- 如果你只用 选择几行
LIMIT，MySQL 在某些情况下会使用索引，而通常它更喜欢进行全表扫描。 - 一旦 MySQL 向客户端发送了所需数量的行，它就会中止查询，除非您使用
SQL_CALC_FOUND_ROWS.
There are a few other cases which you should read about in the documentation.
还有一些其他情况您应该在文档中阅读。
回答by Renato Dinhani
It stops after it found the number of rows specified in the LIMIT clause. This can be verified with a large amount of data. It retrieves the result in a time that is not possible if it is getting all the rows of the table and filtering after that.
它在找到 LIMIT 子句中指定的行数后停止。这可以通过大量数据来验证。如果它正在获取表的所有行并在此之后进行过滤，则它会在不可能的时间内检索结果。
回答by vamyip
If you are using MS SQL Server, then you can write it as given below.
如果您使用的是 MS SQL Server，那么您可以按照下面给出的方式编写它。
Select TOP [x]
*
From MyTable
Hope it helps.
希望能帮助到你。
Vamyip
瓦米普
