mysql - 用mysql实现分页(限制和偏移)

Question

我正在实现一个 RESTful api，它有一个需要支持分页的 GET 端点。为此，我实现了标准限制和偏移查询参数。当我到达 (mysql) 数据库时，我需要以有序、一致的方式获取数据。我试图在我的查询中结合使用“order by”和“limit x,y”，但没有返回预期的结果。
DDL/DML
这是用于创建表/索引 (ddl) 和插入数据 (dml) 的(唯一)脚本:https://gitlab.com/connorbutch/reading-comprehension/-/blob/9-list-all-assessments/reading-comprehension-server-quarkus-impl/src/main/resources/db/migration/V1.0.0__CreateAssessment.sql .该文件的内容也如下所示:

CREATE TABLE ASSESSMENT(
  ISBN BIGINT NOT NULL
, AUTHOR_FIRST_NAME VARCHAR(32)
, AUTHOR_LAST_NAME VARCHAR(32)
, TITLE VARCHAR(128) NOT NULL
, NUMBER_OF_POINTS FLOAT NOT NULL
, IS_VERIFIED BOOLEAN DEFAULT FALSE
, READING_LEVEL FLOAT NOT NULL
, CREATED_TEACHER_ID MEDIUMINT
) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE UNIQUE INDEX ASSESSMENT_X01 ON ASSESSMENT(
    ISBN
);

ALTER TABLE ASSESSMENT
ADD CONSTRAINT ASSESSMENT_PK PRIMARY KEY(
    ISBN
);

CREATE INDEX ASSESSMENT_X02 ON ASSESSMENT(
    TITLE
);

CREATE INDEX ASSESSMENT_X03 ON ASSESSMENT(
    CREATED_TEACHER_ID
);

INSERT INTO ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(9781976530739, 'Herman', 'Melleville', 'Moby Dick', 65, FALSE, 10.8, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(0486282112, 'Mary', 'Shelley', 'Frankenstein', 22, FALSE, 12.0, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(1503275922, 'Joseph', 'Conrad', 'Heart of Darkness', 36, FALSE, 12.5, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(0679732764, 'Ralph', 'Ellison', 'Invisible Man', 30, FALSE, 9.8, 1);

SQL查询
查询运行以从评估表中检索条目可以在以下位置找到: https://gitlab.com/connorbutch/reading-comprehension/-/blob/9-list-all-assessments/reading-comprehension-server-quarkus-impl/src/main/resources/sql.properties .为方便起见，我还在下面列出了它

GET_ALL_ASSESSMENTS=SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC
GET_ASSESSMENT_LIMIT_OFFSET=SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT :LIMIT, :OFFSET
GET_ASSESSMENT_COUNT=SELECT COUNT(1) FROM ASSESSMENT

预期结果
我的预期结果如下:

limit=1,offset=0 -> 返回大小为 1 且 isbn 最低的列表

limit=1,offset=1 -> 返回大小为 1 的列表，返回第二低的 isbn

limit=1,offset=2 -> 返回大小为 1 的列表，返回第三低的 isbn

limit=1,offset=3 -> 返回大小为 1 的列表，返回第四低的 isbn

limit=1,offset=x (x>=4) -> 返回一个空列表

limit=2,offset=0 -> 返回大小为 2 的列表，返回两个最低的 isbn

limit=2,offset=1 -> 返回大小为 2 的列表，返回第二/第三低的 isbn

limit=2,offset=2 -> 返回大小为 2 的列表，返回第三/第四个 isbn

limit=2,offset=3 -> 返回大小为 1 的列表，返回第四低的 isbn

limit=2,offset=x (x>=4) -> 返回空列表

limit=3,offset=0 -> 返回具有三个最低 isbn 的大小为 3 的列表

limit=3,offset=1 -> 返回大小为三的列表，返回第二/第三/第四低的 isbn

limit=3,offset=2 -> 返回大小为 2 的列表，返回第三/第四低的 isbn

limit=3,offset=3 -> 返回大小为 1 的列表，返回第四低的 isbn

limit=3,offset=x (x>=4) -> 返回空列表

limit=x,offset=0 (x>=4) -> 返回大小为 4 的列表，其中包含所有 isbn

limit=x,offset=1 (x>=4) -> 返回大小为 3 的列表，返回第二/第三/第四低的 isbn

limit=x,offset=2 (x>=4) -> 返回大小为 2 的列表，返回第三/第四低的 isbn

limit=x,offset=3 (x>=4) -> 返回大小为 1 且 isbn 倒数第四小的列表

实际结果

limit=1,offset=0 -> 返回空列表(sql 运行 SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 1, 67 | 67 |

limit=1,offset=1 -> 大小为 1 的列表(sql 运行 SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 1915|16

limit=1,offset=2 -> 大小二列表

限制=1，偏移=3 ->

limit=1,offset=x (x>=4) ->

限制=2，偏移=0 ->

限制=2，偏移=1 ->

限制=2，偏移=2 ->

限制=2，偏移=3 ->

limit=2,offset=x (x>=4) ->

limit=3,offset=0 -> 空列表(sql 运行 * SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 3, 171|6* |

限制=3，偏移=1 ->

限制=3，偏移=2 ->

限制=3，偏移=3 ->

limit=3,offset=x (x>=4) ->

limit=x,offset=0 (x>=4) ->

limit=x,offset=1 (x>=4) ->

limit=x,offset=2 (x>=4) ->

limit=x,offset=3 (x>=4) ->

问题:

我是否正确创建了 sql？

如果我是，这是 mysql 的问题/我将如何解决这个问题以产生预期的结果？

如果不是，我如何更正我的查询以产生预期的结果？是否像翻转我的限制/偏移一样简单？

欢迎任何有关如何实现此功能的一般性意见。

谢谢，
康纳

Answer 1

备考满分，
如果我看到问题并且理解正确，那么问题出在您下的订单 limit和 offset有两种方式，
要么你明确提到 limit和 offset作为，

SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID 
FROM ASSESSMENT 
ORDER BY ISBN ASC LIMIT 1 OFFSET 0;

或者
如果你不想提这个词 offset明确我们需要传递 offset先取值，然后 limit值(value)为

SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID 
FROM ASSESSMENT 
ORDER BY ISBN ASC LIMIT 0,1;

附言我没有像您提到的那样测试每个案例，但我认为如果我的理解正确，您现在知道该怎么做。