Select * from table name order by limt M,N from limt M,N But there is a deeper use trap in this way of writing. In the case of repeated data in the sorting field, it is easy to appear that the sorting result is inconsistent with the expected problem.
For example, we have a user table with the following structure and data:
Select * from user order by create_time limit pageNo,2; select * from user order by create_time limit pageNo,2;
1. When querying the data on the first page:
2. When querying the data on page 4:
Table user has a total of 8 data, with 4 pages of data, but in the actual query process, the first page and the fourth page unexpectedly appeared the same data.
What’s going on here? SQL > select table (s), table (s), table (s), table (s), table (s), table (s);
The actual execution results above have shown that there is often a gap between reality and imagination, and the actual SQL execution does not perform as described above. Here is actually the Mysql can do to Limit optimization, specific optimization way to see the official document: dev.mysql.com/doc/refman/… This is the description of version 5.7, extract a few points directly related to the problem to illustrate.
If you mix Limit row_count with order BY, mysql will find the ordered row_count row and return it immediately, instead of sorting the entire query and returning it. If you sort by index, it’s very fast; If it is a file sort, all rows (without Limit) matching the query are selected, and most or all of the selected rows are sorted until the row_count requested by Limit is found. If the row_count row requested by limit is found, Mysql will not sort the remaining rows in the result set.
Here we look at the corresponding SQL execution plan:
You can confirm that the file sort is used, and the table does not add additional indexes. So we can be sure that the SQL execution will return the query result immediately after finding the row requested by limit.
But even if it returns right away, why isn’t the pagination wrong?
The official documents are as follows:
If multiple rows of the order by field have the same value, mysql will return the query results in a random order, depending on the corresponding execution plan. That is, if the sorted columns are unordered, the order of the resulting rows is also uncertain.
This gives us a pretty good idea of why pagination is wrong, because when we sort create_time, there are several rows with the same value, and the order of the rows returned is uncertain. In this case, the first page returns a row with name 8, and the fourth page returns a row with name 8, so the fourth page appears again.
So how can this situation be resolved?
The official solution:
If you want to ensure the same sort results in the presence or absence of limits, you can add an additional sort condition. For example, if the ID field is unique, consider adding an extra ID to the sort field to ensure the order is stable.
In the above case, you can add a sorting field to the SQL, such as the ID field of fund_flow, so that the paging problem is solved. SELECT * FROM user ORDER BY create_time desc LIMIT 1,2;
Test the problem again!!