主页 / dba / 问题 / 82765
Accepted
Cnkt
Asked: 2014-11-17 04:31:04 +0800 CST

没有偏移量的Mysql分页

  • 772

假设我有一张包含这些数据的表格：

表数据

我想通过 createdAt 列 DESC 和 id 列 DESC 来订购它

select id, createdAt from post order by createdAt desc, id desc

现在看起来像这样：

ORDER BY createdAt DESC, id DESC

我想用每页 2 个项目对其进行分页并且不想使用offset，只是limit：

select id, createdAt from post order by createdAt desc, id desc limit 2

在此处输入图像描述

要获取接下来的 2 项，我使用此查询：

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

在此处输入图像描述

我可以这样继续下去。获取最后一项createdAt，id然后将其用于next页面查询。

但我试图制定previous page查询近两天，但还没有找到办法。

这是我已经尝试过的：

从当前结果中获取第一项（而不是最后一项）使用它的id和createdAt字段并反转查询（createdAt >=, id >, created at >）中的条件。但是这个查询总是给我前两个结果（这是正常的，因为提供这个条件的行是前两个）。

我已经没有想法了。我需要帮助。谢谢。

mysql pagination

1 个回答

  1. Best Answer

    这是一种方法：

    mysql> #select all in reverse
mysql> select id, createdAt from post order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
|   10 | 2014-11-13 18:33:08 |
|    9 | 2014-11-13 18:33:01 |
|    8 | 2014-11-13 17:51:21 |
|    7 | 2014-11-13 17:40:30 |
|    6 | 2014-11-12 18:37:44 |
|    5 | 2014-11-12 18:21:44 |
|    4 | 2014-11-12 18:17:30 |
|    3 | 2014-11-12 17:31:31 |
|    2 | 2014-11-11 18:37:40 |
|    1 | 2014-11-11 17:28:41 |
+------+---------------------+
16 rows in set (0.01 sec)

mysql> #select last two
mysql> select id, createdAt from post order by createdAt desc, id desc limit 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button again
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:10:05' AND (id < '13' OR createdAt < '2014-11-16 09:10:05') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #undo next button (just to get back conceptually)
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #we already saw 'next' from here
mysql> #now let's click the previous button
mysql> #change the direction of the inequalities and order by statement
mysql> SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   15 | 2014-11-16 09:11:03 |
|   16 | 2014-11-16 09:11:18 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #and if you want them sorted in desc:
mysql> select id, createdAt from (SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2 ) a order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)
    • 2

相关问题