Pagination mysql to display a page with a specific line

Now I have a question on mysql paging. A user’s record is displayed on a table with many other user’s record. and the table is sorted/paged. Now i need to display the page that containing the user’s row directly after the user login. How can I achieve this? A simple thought would be firstly find out the rownum of the user, then do the paging accordingly, but I’m wondering if there are better ways to do it. thanks.

Sample table for discussion

create table t_users (id int auto_increment primary key, username varchar(100));
insert t_users(username) values
('jim'),('bob'),('john'),('tim'),('tom'),
('mary'),('elise'),('karl'),('karla'),('bob'),
('jack'),('jacky'),('jon'),('tobias'),('peter');

The query to show the page on which the user is on, not specifically putting the user to the top of the page (which would have been a lot easier)

select username, id
from
  (select count(*) pos
   from t_users
   where username <= 'tobias') pos,
  (select @row:[email protected]+1 row, u.*
   from (select @row:=0) initvars, t_users u
   order by u.username, u.id) numbered
where floor((numbered.row + 3)/4) = floor((pos.pos+3)/4);

Notes:

  1. The page size here is 4, the number 3 is the result of taking 1 off the page size
  2. The username of the user who just logged in is ‘tobias’, which is used in the first sub-query
  3. There had better be an index along the ORDER BY clause (in this case on username)
  4. This will cause a table scan to fully row-number the last subquery
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Pagination mysql to display a page with a specific line

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录