SQL for Spring Web Site Paging using Maria DB

my web site was made by Spring framework.
and also using Mybatis.

in my web site, has bulletin board page.
and here is the board table.

TB_BOARD

seq(pk), user_seq, title, category, content, reg_time

now i want apply paging.
so i checked Maria DB document.
they said use pk instead of offset.
okay, i understand.. but there is problem.

if my bulletin board page has only the previous and next buttons, there is no problem.
i will write my SQL like this:

next

select * from TB_BOARD where seq < #{last_seq} order by seq desc limit 5

previos

select * from TB_BOARD where seq >= #{last_seq} order by seq desc limit 5

but if bulletin page has a numbered paging button?
[1][2][3][4][5]

and someone clicked [4] button..how do i get the records for [4] button?
in my think, records can be changed(update, delete), seq will be no longer sequential.
so it is impossible to predict.

is not there any way to do this without "select @rownum"?
thanks.

Solution

The 'previos' query needs ASC, not DESC if I understand seq correctly.

If you are going to have [1][2]... with all pages possible, you are asking for trouble. I will assume you list only the next and previous, say, 5 pages. That is, if you are on page 13, the buttons will show 8..18. (Plus, perhaps, [first] and [last].

You must not use a hard-coded "sequence" number. Instead, use a datetime or other thing that has the same effect

As for INSERTs and DELETEs, "next" and "previous" can be made to work "correctly", but jumping to an arbitrary page is ambiguous because of changes that could have happened.

3 pages head of the current one is seq < $this ORDER BY seq DESC LIMIT 15,5 (or something like that.