[Share] The full solution of the MySQL-based paging program (including ordinary pagination/sub-pagination/original paging/SINCE_ID class pagination of the microblog)

[Share] The full solution of the MySQL-based paging program (including general pagination/segmented paging/original paging/SINCE_ID class pagination for Weibo)
This blog address: http://blog.csdn.net/lgg201/article/details/7757494

The source code involved in this article can be downloaded in http://download.csdn.net/user/lgg201, or can be viewed in the corresponding blog post.

There are many shortcomings in SQL parsing, please correct me.

0. Download:
This procedure can be freely modified, freely distributed, can be downloaded in http://download.csdn.net/user/lgg201
1. Paging Requirements
The manipulation and retrieval of information is the main responsibility of the current Internet and enterprise information System. Information retrieval is to find qualified data from a large amount of data to be presented to the user in the user interface.
There are usually thousands of qualifying data, and the user's single message acceptance is small, so if all data that meets the user's criteria is presented to the user at once, most of the data is redundant for most scenarios.
Once the information retrieval is complete, it needs to be transferred (from storage media to application) and related calculations (business logic), so we need a segmented information retrieval mechanism to reduce this redundancy.
Paging was born.
2. Development of pagination
Basic paging program, the data according to the number of records per page (page_size) data divided into ceil (total_record/page_size) page, the first time for users to show the first paragraph of data, the subsequent interaction process, the user can choose to a page to review the data.
Later, mainly after the microblogging application appears, because its information changes quickly, and its characteristics are based on the time line to add data, so that the basic paging program can no longer meet the demand: a) When the next page, the data set may have undergone a lot of changes, the page can be repeated at any time may cause data duplication or jump; b) These applications use a number of user interfaces that display multiple pieces of data on a single screen, adding to the impact of data duplication/jumps on the user experience. As a result, programmers are beginning to use the since_id approach to record the next point of data acquisition, which has mitigated these drawbacks.
In the same user interface, it is really better to get the next paragraph/last piece of data through the user's reading behavior than to click on the "Next page" button, but it's also a disadvantage: a) When the user has gone to page 100th, he is not very easy to go back to the 5th page of interest, which is actually a rule of design application , we can not let the user interface too many single page screen, this will reduce the user experience; b) from a data point of view, the interval between our multiple reads is enough to make some changes in the data, it is difficult to find these problems (and therefore not affect the user experience) when we display only one screen at a time, but when the 100-screen data is displayed on a single page, the change of data repetition/jumping will reappear. ; c) from a program point of view, the large amount of data placed in the same user interface, will inevitably lead to the user interface program logic is affected. Based on the above considerations, the current application has begun to amend the page, the number of screens displayed on a page limit, while adding the concept of page numbers, in addition to the since_id approach, in order to achieve the best user experience, while ensuring the correctness of data logic (reduce error).
3. Discussion of pagination
Thanks to the discussions of the four colleagues in XP/JP/ZQ/LW, we analyzed the nature of the paging program based on several discussions. The main points of conclusion are as follows:
1) The purpose of paging is to read data in segments
2) data that can be paged must be orderly, even if he is dependent on the database storage order. (This is easier to understand: when the data set does not change, the same input, multiple executions, the resulting output order remains the same)
3) All segmented data read, to fully guarantee the consistency of the data set, must ensure the consistency of the data set order, that is, the snapshot
4) Traditional pagination, segmented pagination (divided into multiple segments per page) is the final analysis of the data set to do a cut, mapped to the SQL syntax of MySQL, is based on the input to obtain the limit clause, the applicable scenario for the data set change frequency low
5) since_id class paging, its essence is to assume that there is no change in the data set, the ID of a point of the dataset (in the dataset can absolutely locate the relevant field of the data) to the user side, each time carrying the ID to read the corresponding location of the data, to simulate the snapshot, the use of the scene for the data set historical data change frequency New data frequently
6) If there is a snapshot system that can generate a snapshot of the data set for each session initiation, then all the problems are solved
7) In the absence of a snapshot system, we can use since_id way to limit the data range, simulation snapshot system, can solve most problems
8) to simulate a snapshot using the Since_id method, its dataset collation must have a field (possibly composite) that uniquely identifies each of its data
4. Realization of Ideas
1) provides a conversion function for SQL
2) Support segmented paging (page, page_ping, Ping, ping_size), traditional paging (page, page_size), original paging (Offset-count), since_id paging (prev_id, next_id)
3) Segmented paging, traditional paging, the original paging is converted to the original paging processing at the bottom of the page
5. Implementation definition
Ping_to_offset
Input:
page# Request page number, range: [1, Total_page], over range with bounds, i.e. 0 fixed to 1, Total_page + 1 fixed to Total_page
ping# Request segment number, range: [1, page_ping], over range with bounds, i.e. 0 fixed to 1, page_ping + 1 fixed to page_ping
page_ping# number of segments per page, range: [1, Infinity]
count# the number of records to get, the current scenario means: Number of records per segment, range: [1, Infinity]
total_record# total number of records, Range: [1, Infinity]
Output:
offset# Offset
count# read number of bars
Offset_to_ping
Input:
offset# offset (must be aligned by count, i.e. divisible by count), range: [0, Infinity]
page_ping# number of segments per page, range: [1, Infinity]
count# read the number of bars, range: [1, Infinity]
Output:
page# Request page Number
ping# Request Segment Number
page_ping# number of segments per page
count# the number of records to get, the current scenario means: Number of records per segment
Page_to_offset
Input:
page# Request page number, range: [1, Total_page], over range with bounds, i.e. 0 fixed to 1, Total_page + 1 fixed to Total_page
total_record# total number of records, Range: [1, Infinity]
count# the number of records to get, the current scenario means: Number per page, range: [1, Infinity]
Output:
offset# Offset
count# read number of bars
Offset_to_page
Input:
offset# offset (must be aligned by count, i.e. divisible by count), range: [0, Infinity]
count# read the number of bars, range: [1, Infinity]
Output:
page# Request page Number
count# the number of records to get, the current scenario means: Number of pages per page
sql_parser# SQL statements that conform to the MySQL syntax specification are parsed to get the individual components
Input:
sql# the SQL statement to parse
Output:
Sql_components#sql the parsed field
sql_restore# converting SQL statement component sets to SQL statements
Input:
sql_components# the set of SQL statement components to restore
Output:
sql# the restored SQL statement
sql_to_count# Convert a SELECT statement that conforms to the MySQL syntax specification to a FETCH count
Input:
sql_components# the set of SQL statement components to convert to query count
Alias of the alias# count field
Output:
sql_components# converted query Count SQL statement component Set
Sql_add_offset
Input:
sql_components# to increase the set of SQL statement components that are offset, the limit component is not allowed
offset# offset (must be aligned by count, i.e. divisible by count), range: [0, Infinity]
count# number of records to get, range: [1, Infinity]
Output:
sql_components# the set of SQL statement components for the limit component has been added
sql_add_since# increase the range of the since_id type
Input:

