英文:
Mariadb: Pagination using OFFSET and LIMIT is skipping one row
问题
我有这个MariaDB表格：
id，名称
id列具有以下属性：主键，自动递增，唯一。
表格有40,000行。
我正在使用PHP和MariaDB从这个表中加载行。这是PHP代码：
$get_rows = $conn->prepare("SELECT * FROM my_table where id > 0 ORDER BY id ASC LIMIT 30 OFFSET ?");
$get_rows->bind_param('i', $offset);
//等等。
第一次查询返回了正确的结果，但在下一次查询（通过AJAX进行），我收到了下一组30行，当前结果和下一组之间有一行的间隙。这种情况一直持续下去。
在表中，第一行已被删除。因此，我恢复了它，现在查询可以正常工作。但是，将来我肯定还会删除更多的行。（我没有软删除的选项）。是否有办法可以继续删除行，并使这些查询返回正确的结果（不跳过任何行）？
编辑
这里是前两次查询中id的范围示例：
查询1：
247--276
查询2：
278--307
（277 缺失）
备注：我询问了ChatGPT，但它无法提供帮助。 :'(
英文:
I have this MariaDB table:
id, name
The id column has these attributes: Primary, auto_increment, unique.
The table has 40,000 rows.
I'm using this PHP & MariaDB to load rows from this table.
This is the PHP code:
$get_rows = $conn->prepare("SELECT * FROM my_table where id> 0 ORDER BY id ASC LIMIT 30 OFFSET ?");
$get_rows->bind_param('i', $offset);
//etc.
The query returned everything correctly at the first time, but in the next query (made through AJAX), I received the next 30 rows with a gap of one row between the current result and the next one. And this goes on and on.
In the table, the row #1 had been deleted. So, I restored it, and now the query works. However, I will definitely have to delete more rows in the future. (I don't have the option of soft-deleting).
Is there any way I can keep deleting rows, and have these queries return correct results (without skipping any row)?
EDIT
Here's an example of the range of the ids in the first 2 queries:
Query 1:
247--276
Query 2:
278--307
(277 is missing)
NB I asked ChatGPT, but it couldn't help. :')
答案1
得分: 1
LIMIT和OFFSET查询是根据位置而不是数值来筛选行。所以如果你删除了第一个“页面”的一行，那么所有后续行的位置都会向下移动一个。
确保不错过行的一种解决方案是通过在前一页的最大id值来定义页面，而不是通过偏移来定义。
$get_rows = $conn->prepare("
SELECT * FROM my_table where id > ?
ORDER BY id ASC LIMIT 30");
$get_rows->bind_param('i', $lastId);
这只适用于您之前的查询浏览了前一页，这样您可以保存该页面上最后一个id的值。
英文:
LIMIT and OFFSET query rows by position, not by value. So if you deleted a row in the first "page," then the position of all subsequent rows moves down by one.
One solution to ensure you don't miss a row is to define pages by the greatest id value on the preceding page, instead of by the offset.
$get_rows = $conn->prepare("
SELECT * FROM my_table where id> ?
ORDER BY id ASC LIMIT 30");
$get_rows->bind_param('i', $lastId);
This only works if your previous query viewed the preceding page, so you can save the value of the last id in that page.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论