[Share] mysql-based paging program full solution (including common paging/segmented paging/original paging/Weibo since_id paging)

[Share] mysql-based paging program full solution (including normal paging/segmented paging/original paging/Weibo since_id paging) this blog address: complete solution for mysql-based paging programs (including common paging/segmented paging/original paging/Weibo since_id paging)
Blog: http://blog.csdn.net/lgg201/article/details/7757494

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

SQL parsing has many shortcomings. please correct me.

0. download:
This program can be freely modified and distributed. it can be downloaded at http://download.csdn.net/user/lgg201
1. paging requirements
Information manipulation and retrieval are the primary responsibilities of the Internet and enterprise information systems. information retrieval is to find qualified data from a large amount of data and present it to the user interface.
There are usually tens of thousands of qualified data records, and the user's single-time acceptance of information is very small. Therefore, if all the data that meets the user's requirements is presented to the user at a time, for most scenarios, most of the data is redundant.
After information retrieval is complete, it must be transmitted (from storage medium to application) and related computing (business logic). therefore, we need a segmented information retrieval mechanism to reduce this redundancy.
Paging came into being.
2. paging development
The basic paging program divides the data into the ceil (total_record/page_size) page according to the number of records per page (page_size), and displays the data in the first segment for the user for the first time, in the subsequent interaction process, you can select a page to review the data.
Later, due to the rapid changes in information after the emergence of Weibo applications, the feature was to add data based on the timeline. in this way, the basic paging program could no longer meet the requirements:) when you obtain the next page, the dataset may have changed a lot. turning pages at any time may lead to repeated or jumping data. B) these applications use a lot of user interfaces that display multiple pieces of data on one screen, this increases the impact of data duplication/jumping on user experience. therefore, programmers began to use the since_id method to record the next data point, which has mitigated the above disadvantages.
In the same user interface, it is better to automatically obtain the next/previous data segment through user reading behaviors than to click the next page, but it also has the following drawbacks:) it is not easy for a user to return to the 100th page information he is interested in when he has reached the 5th page. this is actually a rule for designing applications, we cannot increase the number of single-page screens on the user interface, which will reduce the user experience. B. from the data perspective, the interval between multiple reads is sufficient to change the data, when only one screen is displayed at a time, it is difficult to find these problems (so it does not affect the user experience). However, when 100 screen data is displayed on one page, this change will be magnified, data duplication/jumping will occur again. c) from a program perspective, placing a large amount of data on the same user interface will inevitably affect the program logic of the user interface. based on the above considerations, the application has begun to modify pages, limit the number of screens displayed on one page, add the page number concept, and use the since_id method, in order to achieve optimal user experience, while ensuring the correctness of the data logic (reduce the error ).
3. paging discussion
I would like to thank four colleagues from xp/jp/zq/lw for their discussions. Based on these discussions, we have analyzed the nature of the paging program. The main conclusions are as follows:
1) The purpose of paging is to read data in segments.
2) data that can be paged must be ordered, even if it depends on the database storage order. (This is easier to understand: When the dataset does not change, the same input, multiple executions, and the output sequence remain unchanged)
3) to fully ensure the consistency of the dataset, ensure the consistency of the dataset sequence, that is, the snapshot.
4) in the traditional paging mode, segmented paging (divided into multiple segments per page) is essentially a cutting of the dataset and ing it to the SQL syntax of mysql. the limit clause is obtained based on the input, applicable scenarios: low frequency of dataset changes
5) The since_id class is paged. in essence, it is assumed that the existing data has not changed, and the id of a certain vertex of the dataset (the related fields of the data can be absolutely located in the dataset) provided to the user side. each time the id is carried to read data from the corresponding location, the snapshot is used to simulate the low frequency of historical data changes in the dataset, resulting in frequent new data changes.
6) If a snapshot system can generate a snapshot data for the dataset at the time of each session initiation, all problems will be solved.
7) when there is no snapshot system, we can use since_id to limit the data range and simulate the snapshot system to solve most problems.
8) to use the since_id method to simulate a snapshot, the dataset sorting rule must have a field that uniquely identifies each of its data (which may be composite)
4. implementation ideas
1) provides SQL conversion functions
2) supports segmented pages (page, page_ping, ping, ping_size), traditional pages (page, page_size), original pages (offset-count), and since_id pages (prev_id, next_id)
3) segmented paging, traditional paging, and original paging are converted to original paging at the underlying layer.
5. implementation definition
Ping_to_offset
Input:
Page # Request page number, range: [1, total_page]. if the range is exceeded, the value 0 is changed to 1, and the value total_page + 1 is changed to total_page.
Ping # Request Field number, range: [1, page_ping]. if the range is exceeded, the value 0 is changed to 1, and the value page_ping + 1 is changed to page_ping.
Page_ping # number of segments per page, range: [1, infinite]
Count # number of records to be obtained. The current application scenario indicates the number of records in each segment. The range is [1, infinite].
Total_record # total number of records, range: [1, infinite]
Output:
Offset # offset
Count # read count
Offset_to_ping
Input:
Offset # offset (the offset must be aligned according to count, that is, it can be divisible by count), range: [0, infinite]
Page_ping # number of segments per page, range: [1, infinite]
Count # Number of read entries, range: [1, infinite]
Output:
Page # Request page number
Ping # Request Field Number
Page_ping # number of segments per page
Count # number of records to be obtained. The current application scenario is as follows: number of records per segment
Page_to_offset
Input:
Page # Request page number, range: [1, total_page]. if the range is exceeded, the value 0 is changed to 1, and the value total_page + 1 is changed to total_page.
Total_record # total number of records, range: [1, infinite]
Count # number of records to be obtained. The current application scenario is: number of records per page, range: [1, infinite]
Output:
Offset # offset
Count # read count
Offset_to_page
Input:
Offset # offset (the offset must be aligned according to count, that is, it can be divisible by count), range: [0, infinite]
Count # Number of read entries, range: [1, infinite]
Output:
Page # Request page number
Count # number of records to be retrieved. The current application scenario is as follows: number of records per page
SQL _parser # parse SQL statements that comply with mysql syntax specifications to obtain various components
Input:
SQL # SQL statement to be parsed
Output:
SQL _components # fields parsed by SQL
SQL _restore # Convert an SQL statement component set to an SQL statement
Input:
SQL _components # set of SQL statement components to be restored
Output:
SQL # restored SQL statement
SQL _to_count # Convert SELECT statements that comply with mysql syntax specifications to get counts
Input:
SQL _components # SQL statement component set to be converted to query count
Alias # alias of the counting field
Output:
SQL _components # converted query count SQL statement component set
SQL _add_offset
Input:
SQL _components # the SQL statement component set for which the offset is to be added. The LIMIT component is not allowed.
Offset # offset (the offset must be aligned according to count, that is, it can be divisible by count), range: [0, infinite]
Count # number of records to be obtained, range: [1, infinite]
Output:
SQL _components # SQL statement component set of the LIMIT component added
SQL _add_since # increase the range of the since_id formula
Input: