我正在尝试使用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进行结果奇怪
发布评论