Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The method of realizing paging query by MySQL

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of paging query by MySQL, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

Implement paging query in MySQL: when the amount of data is small, you can use limit query to achieve paging query, when the amount of data is large, you can use the establishment of primary key or unique index to achieve it, and you can sort it through order by.

Admin management is always unavoidable in back-end projects, and paging is needed when background management needs to present data.

In general, when paging queries in MySQL, limit queries are used, and order by is used for sorting in queries. Next, we will describe in detail how MySQL implements paging queries.

Paging requirements:

Client by passing start (page number), limit (the number of bars displayed per page) two parameters to paging query database table data, MySql database to provide paging functions have limit mline n, but the use of this function and our needs are not the same, so we need to rewrite their own paging statements according to the actual situation. Example

The sql for querying the data from articles 1 to 10 is:

Select * from table limit 0pl 10

Our requirement is to query the data on the first page:

Select * from table limit (1-1) * 10pm 10

From the above analysis, we can conclude that the format of the paging sql is:

Select * from table limit (start-1) * limit,limit

Where start is the page number and limit is the number of bars displayed per page.

Create a primary key or unique index

When the amount of data is small, using limit for data paging will not be significantly slow in performance, but when the amount of data reaches ten thousand or millions of levels, the performance of sql statements will affect the return of data. It is necessary to use the primary key or the only index instead of limit for data paging.

Example: return data between 10 and 50

Set the primary key or unique index to demo_id

Select * from table where demo_id > (pageNo-1) * pageSize limit pageSize

Reorder based on data

When the information needs to be returned in order or reverse order, the above data needs to be sorted. Order by ASC can be used to represent the order, order byDESC represents the reverse order, generally speaking, the default is the order

Example: the returned data is arranged in the order of demo_id

Select * from table where demo_id > (pageNo-1) * pageSize order by demo_id limit pageSize; above are all the contents of MySQL's paging query method. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report