Write in front: the blogger is a real combat development after training into the cause of the “hill pig”, nickname from the cartoon “Lion King” in “Peng Peng”, always optimistic, positive attitude towards things around. My technical path from Java full stack engineer all the way to big data development, data mining field, now there are small achievements, I would like to share with you what I have learned in the past, I hope to help you on the way of learning. At the same time, the blogger also wants to build a perfect technical library through this attempt. Any anomalies, errors and matters needing attention related to the technical points of the article will be listed at the end, and everyone is welcome to provide materials in various ways.

• Please criticize any mistakes in the article and revise them in time.
• If you have any questions you would like to discuss or learn, please contact me at [email protected].
• The style of the published article varies from column to column, and all are self-contained. Please correct the deficiencies.

Use MySQL to achieve paging query

Key words: MySQL, paging query, true paging, false paging, LIMIT\

The article directories

• Use MySQL to achieve paging query
• • A, paging
  • • 1. What is paging
    • 2. True paging
    • 3. The false paging
    • 4. The buffer layer
  • MySQL implements paging
  • • 1. LIMIT usage
    • 2. Paging formula

A, paging

1. What is paging

2. True paging

True paging refers to the fact that only the data of the current page is queried each time the page is turned, which is characterized by a large number of interactions with the database. However, the amount of data queried each time is small and the data does not need to be kept in memory all the time. This method is applicable to the scenario where a large amount of data is detected and the data is not suitable for full detection.

3. The false paging

4. The buffer layer

Both real and fake pages interact with the database. For real pages, you don’t need to worry about data synchronization because you’re looking up to date each time, but the database can be heavily burdened, especially if there are large numbers of users.

Pseudo paging can relieve some of the strain on the database, but data cannot be synchronized in a timely manner unless re-requested or page refreshed.

Generally, there will be a cache layer in the enterprise, which can effectively reduce the pressure of the database and timely data synchronization. After modifying the data in the database, the changed data should be synchronized to the cache layer in time and obtained from the cache layer during data query.

MySQL implements paging

1. LIMIT usage

LIMIT appears at the end of the query and can be used with one or two parameters to LIMIT the data retrieved. The first parameter represents the offset (optional) and the second parameter represents the number of rows to be retrieved.

• Single-parameter usage

When specifying a parameter, the default is to omit the offset, that is, the offset is 0, starting with the first row and fetching rows.

/* Query the first 5 data */
SELECT * FROM Student LIMIT 5;
Copy the code

• Two-parameter usage

When specifying two parameters, note that the offset value starts from 0 and can be written in either of the following ways:

/* Select * from 1 to 10
SELECT * FROM Student LIMIT 0.10;
/* Query data from 11 to 20 */
SELECT * FROM Student LIMIT 10 OFFSET 10;
Copy the code

2. Paging formula

/* Total number of entries */
SELECT COUNT(*) FROM Student;
/* Assuming 10 entries per page, divide directly and round up */
SELECT CEIL(COUNT(*) / 10) AS pageTotal FROM Student;
Copy the code