How to implement paging query in MySQL

SQL paging query:

background

In the company's system, there is a platform for configuration management, which is the so-called CRUD platform. However, after clicking on it, the first page you see is a query page (clicking on the page automatically triggers the query function). Later, your CRUD operations are performed by your operations colleagues. However, generally, for businesses with relatively small amounts of data, all data is queried and returned directly to the front end for paging. However, when the amount of data reaches tens of thousands, they cannot be allowed to mess around. Two more parameters must be added to them.

Workaround

It is very simple to add two parameters (1) the number of pages and (2) the number of queries per page (the backend has a default value to prevent it from being passed)

Processing required by the service

The processing method is very simple to extract in sql

Query all data without adding paging

​When adding paging, it is particularly important to note that:

The first query for paging is the number of pages. The number of pages here needs to be processed. There is a formula sql written page number = (page number - 1) * the number of pages

How is SQL processed?

• The first

SELECT * FROM test_user_info limit 0,4;

• The second

Sometimes paging can solve most of the problems, but we can predict where the data is, that is, where to start in a table with a large amount of data. We slightly change the SQL

Add the starting id and start getting 4 data each time

SELECT * FROM test_user_info WHERE id > 32 limit 4; 

Also, when you have a normal query and know that it is a piece of data, add limit 1 at the end to make the SQL execution faster.

This is an obvious contrast.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.