MySQL 什么是查询偏移量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1197603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is a query offset?
提问by Andrew Moore
I saw this at the Kohana documentation:
我在 Kohana 文档中看到了这一点：
$content = new View('pages/items');
$items = new Items_Model;
$content->items = $items->get_items($page_no, 10); // page to get starting at offset, number of items to get
As you can see, we can assume that we have get_items method for Items model that receives 2 parameters, $page_no and 10 (as shown here). I know that 10 is the number of items to get and $page_no is the page to get starting at offset
如您所见，我们可以假设我们有 Items 模型的 get_items 方法，它接收 2 个参数，$page_no 和 10（如下所示）。我知道 10 是要获取的项目数，$page_no 是从偏移量开始的页面
I can probably implement a limit sql statement for the 10 parameter, but what sql statement will correspond to $page_no? What does Kohana mean regarding "page to get starting at offset"
我大概可以为10个参数实现一个limit sql语句，但是$page_no对应什么sql语句呢？Kohana 关于“从偏移开始的页面”是什么意思
回答by Andrew Moore
It corresponds to a LIMITstatement:
它对应于一个LIMIT语句：
SELECT something FROM table LIMIT $limit OFFSET $offset;
//or alternatively
SELECT something FROM table LIMIT $offset,$limit;
In other words, select something from table, but only give me $limitrecords starting from record $offset.
换句话说，从 table 中选择一些东西，但只给我$limit从 record 开始的记录$offset。
$offset = ($page_no - 1) * $limit$page_nois 1 based.
$offset = ($page_no - 1) * $limit$page_no是基于 1。
More information in the MySQL documentation:
MySQL 文档中的更多信息：
DISCLAMER:$limitand $offsetis used in this question only to aid understanding. Of course, you do not want to build a query without proper value escaping.
免责声明：$limit并且$offset在这个问题中仅用于帮助理解。当然，您不希望在没有正确值转义的情况下构建查询。
回答by eswald
That particular comment unfortunately confuses two common ways to consider pagination or grouping in queries. The SELECT syntax, as Andrew describes, allows an OFFSET parameter, the number of items to skip before returning anything. However, it is most often used with pagination, as in the pagination libraryfrom which your quotation was taken. In this case, it is more useful to ask for a specific page number.
不幸的是，该特定评论混淆了在查询中考虑分页或分组的两种常见方法。正如 Andrew 所描述的，SELECT 语法允许一个OFFSET 参数，即在返回任何内容之前要跳过的项目数。但是，它最常与分页一起使用，就像在引用您的引文的分页库中一样。在这种情况下，询问特定页码会更有用。
To compare the two, consider a case where you've made a search and have gone to page 3, with 10 items per page. Items 1-20 were on the first two pages; therefore, the OFFSET parameter will be 20:
要比较这两者，请考虑这样一个案例：您进行了搜索并转到了第 3 页，每页有 10 个项目。第 1-20 项在前两页；因此，OFFSET 参数将为 20：
SELECT * FROM table WHERE searched LIMIT 10 OFFSET 20
or
或者
SELECT * FROM table WHERE searched LIMIT 20,10
Unfortunately, the $page_noparameter in the cited example probably wants to be 3, the page number. In that case, the SQL offset will have to be calculated. Given that get_itemsdoes not seem to be a standard model method, it's probably calculated in there; alternatively, the pagination library seems to have a property called sql_offsetwhich probably calculates it for you. Either way, the calculation is easy enough:
不幸的是，$page_no引用示例中的参数可能想要 3，即页码。在这种情况下，必须计算 SQL 偏移量。鉴于这get_items似乎不是标准的模型方法，它可能是在那里计算的；或者，分页库似乎有一个名为的属性sql_offset，它可能会为您计算它。无论哪种方式，计算都很简单：
$offset = max($page_no - 1, 0) * $limit;
