如何查询某些行的sqlite,即将其划分为页面(perl DBI) [英] how to query sqlite for certain rows, i.e. dividing it into pages (perl DBI)
问题描述
很抱歉,我的菜鸟问题,
我目前正在编写一个带sqlite数据库的perl Web应用程序。
我希望能够在我的应用程序中显示查询结果,该结果可能会获得成千上万的行-应该将这些行分成页面-路由应类似于/ webapp / N-其中N是页码。
使用DBI查询sqlite db的正确方法是什么,以便仅获取相关行。
sorry for my noob question, I'm currently writing a perl web application with sqlite database behind it. I would like to be able to show in my app query results which might get thousands of rows - these should be split in pages - routing should be like /webapp/N - where N is the page number. what is the correct way to query the sqlite db using DBI, in order to fetch only the relavent rows.
例如,如果我显示每行25行页面,因此我想在第一页中查询数据库中的1-25行,在第二页中查询26-50等...。
for instance, if I show 25 rows per page so I want to query the db for 1-25 rows in the first page, 26-50 in the second page etc....
推荐答案
使用 LIMIT
/ OFFSET
构造将显示页面,但 OFFSET
使查询效率低下,并在数据更改时使页面内容移动。
Using the LIMIT
/OFFSET
construction will show pages, but the OFFSET
makes the query inefficient, and makes the page contents move off when the data changes.
如果下一页开始,效率更高且更一致查询在最后一个查询结束的位置,例如:
It is more efficient and consistent if the next page starts the query at the position where the last one ended, like this:
SELECT *
FROM mytable
ORDER BY mycolumn
WHERE mycolumn > :lastvalue
LIMIT 25
这表示您的链接不是 / webapp?Page = N
但 / webapp?StartAfter = LastKey
。
This implies that your links are not /webapp?Page=N
but /webapp?StartAfter=LastKey
.
此在滚动光标页面中有详细说明。
这篇关于如何查询某些行的sqlite,即将其划分为页面(perl DBI)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!