在MySQL中限制SQL查询结果

I would like to limit the amount of rows I fetch in MySQL. Can you show me how?

我想限制我在MySQL中获取的行数。你能告诉我怎么样吗？

ex:

例如：

1st query I would like to retrieve only the first 10,000 records
第一个查询我想只检索前10,000条记录
2nd query I would like to retrieve only records from 10,000 - 20,000
第二个查询我想只检索10,000 - 20,000的记录

etc

等等

7 个解决方案

#1

The term you're looking for is "pagination." Unfortunately, this is done differently depending on the SQL engine.

你正在寻找的术语是“分页”。不幸的是，这取决于SQL引擎的不同。

For MS SQL Server, see this Stack Overflow question.

对于MS SQL Server，请参阅此Stack Overflow问题。

Since you mentioned MySQL, it's actually quite simple:

既然你提到了MySQL，它实际上非常简单：

SELECT [columns] FROM [a table] LIMIT 10000
SELECT [columns] FROM [a table] LIMIT 10000 OFFSET 10000

The first statement fetches results 1-10,000, and the second statement fetches results 10,001-20,000.

第一个语句获取结果1-10,000，第二个语句获取结果10,001-20,000。

#2

I think the following queries will give you the desired result

我认为以下查询将为您提供所需的结果

SELECT * FROM PERSON_TBL LIMIT 0, 10000

SELECT * FROM PERSON_TBL LIMIT 0,10000

@ 1st query I would like to retrieve only the first 10,000 records

@ 1st查询我想只检索前10,000条记录

SELECT * FROM PERSON_TBL LIMIT 10000,10000

SELECT * FROM PERSON_TBL LIMIT 10000,10000

@ 2nd query I would like to retrieve only records from 10,000 - 20,000

@ 2nd查询我想只检索10,000 - 20,000的记录

#3

select top x * from table in SQL Server

select * from table where ROWNUM < x in Oracle

select * from table limit x in MySQL

#4

MySQL and PostgreSQL support OFFSET that is usually used with a LIMIT clause.

MySQL和PostgreSQL支持OFFSET，它通常与LIMIT子句一起使用。

SELECT column FROM table
LIMIT 10000

SELECT column FROM table
LIMIT 10000 OFFSET 10000

#5

in mysql you do as follows

在mysql中你做如下

SELECT * FROM PERSON_TBL LIMIT 0, 1000 

SELECT * FROM PERSON_TBL LIMIT 1000, 1000

Query 1 will fetch first 1000 records,

查询1将获取前1000条记录，

Query 2 will fetch next 1000 records

查询2将获取下一个1000条记录

Syntax for limits clause

限制子句的语法

LIMITS OFFSET, ROWCOUNT

限额抵消，ROUCOUNT

Where ROWCOUNT give number of row to fetch

其中ROWCOUNT给出要获取的行数

OFFSET gives from which row to fetch more info here

OFFSET提供从哪一行获取更多信息

#6

TSQL

SELECT TOP 10000 ...

选择TOP 10000 ...

PL/SQL

PL / SQL

... WHERE ROWNUM < 10000 ...

......在哪里ROWNUM <10000 ...

#7

in MySQL :

在MySQL中：

SELECT * FROM `your_table` LIMIT 0, 10000

This will display the first 10000 results from the database.

这将显示数据库中的前10000个结果。

 SELECT * FROM `your_table` LIMIT 10000, 20000

This will show records 10001, 10002, ... ,20000

这将显示记录10001,10002，...，20000

#1