MySql分页结果，找到“page"具体结果 [英] MySql paginated results, find "page" for specific result

问题描述

我有一个使用 MySql 数据库的应用程序，它显示一个记录的分页列表，用户可以向其中添加新的记录.我知道如何使用 LIMIT 等获得分页结果，但问题是如何在添加新记录时进入特定页面.如果页面大小为 20，并且用户正在查看第一页，并且他们添加了第 23 条记录(比如 100 条记录)，我们如何确定向用户显示哪个页面.

I have an application using a MySql database which is displaying a paginated list of records, to which to which the user is able to add new ones. I know how to get paginated results using LIMIT etc, but the question is how to go a specific page when adding a new record. If the page size is 20, and the user is viewing the first page, and they add a record which is 23rd (out of say 100), how do we determine which page to show the user.

因此，本质上，给定页面大小和特定记录，我如何确定要在页面中显示哪个页面".总页数不包括它们所属表的所有记录，但获取它们的标准是静态的.

So, essentially, given a page size and a specific record, how do I determine which "page" to show out of the pages. The total pages do NOT include all of the records for the table they belong to, but the criteria for getting them is static.

我应该更具体.这些记录包含一个唯一 ID 字段和一个字符串名称字段.结果记录按名称字母顺序排序.另外，这是一个java应用程序.

I should have been more specific. The records contain a unique ID field and a string name field. The resultant records are sorted by the name alphabetically. Also, this is a java application.

我唯一能想到的就是选择所有按名称排序的所需记录，然后如何在特定记录的这些结果中找到位置.从那个位置可以很容易地计算出页码，因为我们知道页面大小，但我不确定它们是否是用于获取结果中记录位置的 MySql 语法.

The only thing I can think of is to select all the desired records sorted by name and then some how find the position in those results of the specific record. From that position the page number could be easily calculated since we know the page size, but I'm not sure if they is MySql syntax for getting the position of a record in the results.

愚蠢"的解决方案是将它们全部抓取，然后在应用程序代码 (java) 中，确定所有结果中的特定记录位置.但似乎他们必须是一种更有效的方式.

The "dumb" solution would be to just grab them all and then in the application code (java), determine the specific records position in all the results. But is seems like their must be a more efficient way.

推荐答案

SELECT COUNT(*) as CNT FROM tbl WHERE name < (SELECT name FROM tbl WHERE id = 42)

之后，您只需将 CNT 值除以每页的行数并舍入 (Math.floor())

After that you just divide CNT value to the amount rows per page and round (Math.floor()) it

或者你甚至可以在 sql 中做到这一点:

Or you can even do that in sql:

SELECT FLOOR(COUNT(*) / 20) AS page_number ...

这篇关于MySql分页结果，找到“page"具体结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！