How mysql paging queries are written

mysql paging method (several ways of mysql paging)

Paging implementation in MySQL







|product3|< /p>



This place is saying that all the pname’s are selected from the proct for a total of three records.

Paging in MySQL is very simple, we can use limit

For example:









Limit usage is as follows:

The first parameter is where to start, and the second parameter is how many pieces of data are displayed on each page; note: the first page is denoted by 0.

Combining MySQL database, how to realize the paging function

First step: we can use the $_GET method to get the value of a parameter, and get different dynamic parameters when the user clicks on the previous page, the next page, the first page, or the last page

Second step: according to MySQL’s LIMIT keyword, the dynamic parameters of the SQL statement stitching

We will define the number of data items to be displayed on each page, in the position of the first parameter of LIMIT, and according to the dynamic change of the address bar parameter to specify the data from which to start displaying data. p>The number of data items to be displayed on each page is defined in the position of the first parameter of the limit, and the dynamic change of the address bar parameter is used to specify the data to be displayed starting from the first data item

Mysql how to query a record in the paging page in detail


In practice, we will encounter such a problem, know the id of a record, and then need to determine this record if the sorting paging in accordance with the id of this record, this record in the first few pages. Today this article for you to provide an idea.

Without further ado, let’s take a look at the detailed implementation of the method

Query the paging position according to ID

Query the paging position according to ID, for example, according to the inverse order of ID, the following SQL query can be greater than the number of records of this ID:

selectcount(id) fromuserwhereid>5;

In the example, user is the table name, 5 ids to be matched. obviously, because it is the reverse order only need to find the number of records greater than this id can be, if it is the positive order, it is less than this id can be.

When checking out the value of count, how to calculate the current record is located in the first page of it, here to java code example calculation:


// Assuming that the results of the above check out for the count, the value of 11


/Calculate the current record is located in the pageNum. pageNum where the current record is

// By taking the mode and adding 1 to get the current page number is page 2


/// If you want to further get in a certain page in a certain position, then the remainder can be, that is, page 2 of the first record (starting from 0)


Multi-dimensional sorting and positioning

The above sorting by simple ID is still a good solution, then if you now query a record sorting dimension is not only ID, for example, first in accordance with the age of the inverse order, if the age is the same, then follow up with the ID for the reverse sorting. The basic sql statement is as follows:


At this point, we know that a certain id is 5, age is 18 records, how to determine the position of this record in the multiconditional sorting it.

First of all, the difficulty of multi-conditional sorting is the age of the same case, if the age is not the same, just the following sql can be like “according to the ID query paging location” as the location of the id:


This can be query out of this combination of sorting age is not the same when the location of this record, the specific location of the algorithm with the first case.

So how to deal with the case when age is repeated. Of course, can be achieved through complex correlation query or sub-table query, here through another way, is to query the age of the same, and id is greater than the number of records of the current user:


Above to get age In the same case, and id greater than 5 records, the first step and the second step of the statistics are added together, the problem is not back to “according to the ID query paging location” of the simple mode, or the same algorithm can be calculated in the current record is located in the first few pages.

This program, although querying the database twice, if a good indexing, than the correlation query or sub-query to be convenient, concise and efficient.


The above are two dimensions of thinking about similar problems encountered in practice, I hope to bring a breakthrough, but also hope that you provide a better program.