使用LIMIT在MySQL查询中对结果进行分页

I want to fetch my results a 'page' at a time; I want the page number to be a parameter (in a JDBC prepared statement). Consider the following snippet

我希望一次将结果作为“页面”获取;我希望页码是一个参数(在JDBC预处理语句中)。请考虑以下代码段

SELECT * FROM thread t ORDER BY t.id LIMIT ((? - 1) * 20), 20

So ideally, this would result, for page 1, to LIMIT 0, 20.

理想情况下,对于第1页,这将导致LIMIT 0,20。

When I test

当我测试

SELECT * FROM thread t ORDER BY t.id LIMIT ((1 - 1) * 20), 20

I am told I have a syntax error. I don't see what it could be, though - it's just some simple math. All it tells me is

我被告知我有语法错误。我不知道它可能是什么 - 它只是一些简单的数学。它告诉我的全部是

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((1 - 1) * 20), 20' at line 1

错误1064(42000):您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在第1行'((1 - 1)* 20),20'附近使用正确的语法

What am I doing wrong with my LIMIT clause, and how can I fix it?

我的LIMIT子句出了什么问题,我该如何解决?

3 个解决方案

#1

MySQL requires numeric constants for that LIMIT syntax.

MySQL需要LIMIT语法的数字常量。

From http://dev.mysql.com/doc/refman/5.7/en/select.html:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

LIMIT子句可用于约束SELECT语句返回的行数。 LIMIT需要一个或两个数字参数,它们都必须是非负整数常量,但有以下例外:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

在准备好的语句中,可以使用?指定LIMIT参数?占位符标记。

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

在存储的程序中,可以使用整数值例程参数或局部变量指定LIMIT参数。

Compute the constant on the Java side.

计算Java端的常量。

#2

This cannot be done.

这是不可能做到的。

See solution here: MySQL Math and COUNT(*) in LIMIT

请参阅此处的解决方案:LIM Math中的MySQL Math和COUNT(*)

I would recommend using javascript or something to handle the first parameter (i.e. offset) such as: limit 0,20 on first page and limit 21,20 on second...

我建议使用javascript或其他东西来处理第一个参数(即偏移量),例如:首页限制0,20,第二页限制21,20 ...

For example if your first page has a get variable in the url www.example.com?page=1

例如,如果您的第一页在网址www.example.com?page=1中有一个get变量

offset = (page - 1)*20 ;
row_count = 20;
select * from table limit (offset, row_count);

#3

Define Offset for the query.

定义查询的偏移量。

For example

if you want page-1 (records 1-10) offset=0,limit=10; for page-2 (records 11-20) offset =20,limit =10; and use the following query :

如果你想要page-1(记录1-10)offset = 0,limit = 10;对于第2页(记录11-20)偏移= 20,极限= 10;并使用以下查询:

SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};

example:

SELECT column FROM table
LIMIT 10 OFFSET 10;

#1