使用order by和limit进行结果奇怪 [英] Strange results using order by and limit

问题描述

我正在尝试使用SQL设置分页.我希望每页3个结果，这是我已经完成的事情:

I'm trying to set up a pagination using SQL. I want 3 results per page and here is what I have done :

SELECT mot_cle.* FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 0; --Page 1

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 3; --Page 2

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 6; --Page 3

SELECT mot_cle.* 
FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 9; --Page 4

我检查了很多次，这不是很复杂，但是我的结果并不是我所期望的:

I checked many times and this is not very complicated but my results are not really what I expected :

第1页:

+-----+--------+------+
| id  |  mot   | hits |
+-----+--------+------+
|   2 | test   |   46 |
|   1 | blabla |    5 |
| 475 | intro  |    3 |
+-----+--------+------+

第2页:

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
| 478 | vrai  |    1 |
|  26 | ouest |    1 |
|  27 | serie |    1 |
+-----+-------+------+

第3页:

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
|  27 | serie |    1 |
|  26 | ouest |    1 |
| 478 | vrai  |    1 |
+-----+-------+------+

第4页:

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
|  27 | serie |    1 |
|  26 | ouest |    1 |
| 478 | vrai  |    1 |
+-----+-------+------+

如您所见，第2、3和4页的结果相同... 当我一次取回4页时:

As you can see, pages 2, 3 and 4 have the same results... When I fetch the 4 pages in one :

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 20 OFFSET 0;

结果:

+-----+-------------+------+
| id  |     mot     | hits |
+-----+-------------+------+
|   2 | test        |   46 |
|   1 | blabla      |    5 |
| 475 | intro       |    3 |
|  35 | acteurs     |    1 |
|  36 | milieu      |    1 |
|  37 | industriel  |    1 |
|  38 | plaire      |    1 |
|  39 | grandes     |    1 |
|  40 | ingenieries |    1 |
|  41 | francaises  |    1 |
|  34 | partenaire  |    1 |
|  33 | rthgyjhkj   |    1 |
|  32 | cool        |    1 |
|  31 | super       |    1 |
|  30 | vieux       |    1 |
|  29 | moteur      |    1 |
|  28 | yahoo       |    1 |
|  27 | serie       |    1 |
|  26 | ouest       |    1 |
| 478 | vrai        |    1 |
+-----+-------------+------+

也许我遗漏了一些东西或排序结果，并且使用极限/偏移量不兼容，我不知道出了什么问题.

Maybe I'm missing something or sorting results and using limit/offset are not compatible, I don't know what's wrong.

推荐答案

这里的问题是所有行的命中计数都为1，因此使用ORDER BY hits时它们的位置不确定.而且由于您每次访问页面时都会执行一个新查询，因此这些行将被重新加扰".

The problem here is that the rows all have a hit count of 1, therefore their position when using ORDER BY hits is non-deterministic. And since you execute a new query each time you access a page, the rows will be "scrambled" anew.

要保持页面的一致性，您还可以按其ID排序:

To keep your pages consistent, you could also order by their id:

SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC, id ASC LIMIT 3 OFFSET 0; --Page 1

这篇关于使用order by和limit进行结果奇怪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！