Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to use limit to achieve paging, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

1. The basic implementation of limit

In general, the client passes two parameters, pageNo (page number) and pageSize (number per page), to paginate and query the data in the database, and uses the limit provided by MySQL to solve this problem when the amount of data is small (tuple 100 / 1000):

Receive client {pageNo:1,pagesize:10} select * from table limit (pageNo-1) * pageSize,pageSize; receive client {pageNo:5,pageSize:30} select * from table limit (pageNo-1) * pageSize,pageSize

Second, establish a primary key or unique index

When the amount of data is small, simply using limit for data paging will not be significantly slow in performance, but the performance of sql statements with data volume up to ten thousand to one million will affect the return of data. In this case, we need to use the primary key or unique index for data paging.

Suppose the primary key or unique index is good_id that receives client {pageNo:5,pagesize:10} select * from table where good_id > (pageNo-1) * pageSize limit pageSize;-returns data with a good_id between 40 and 50

Third, reorder based on data

When the information that needs to be returned is in order or reverse order, the above statements are reordered based on the data. Order by ASC/DESC order or reverse order defaults to order

Select * from table where good_id > (pageNo-1) * pageSize order by good_id limit pageSize;-returns data with good_id between 40 and 50, sorted in good_id order

IV. The best paging scheme

10 items per page: current 118 120125 reverse order: size 980 970 7 6 6 5 54 43 3221 19 98 next page: select * from tb1 where nid

< (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10;上一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid >

Current page maximum order by nid asc limit per page data * [current page-page number]) An order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid

< (select nid from (select nid from tb1 where nid >

980 order by nid asc limit 20) An order by A.nid desc limit 1) order by nid desc limit 10; Thank you for reading this article carefully. I hope the article "how to use limit to achieve paging in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.