METHOD OF AND DEVICE FOR ENQUIRING, BY PAGINATION, SUB-TABLES
DIVIDED FROM DATABASE, AND COMPUTER EQUIPMENT
BACKGROUND OF THE INVENTION
Technical Field [0001] The present application relates to the field of database technology, and more particularly to a method of and a device for enquiring, by pagination, sub-tables divided from a database, a computer equipment and a storage medium.
Description of Related Art
[0002] With the increase in business data volumes, the single table processing efficiency of conventional relational databases (such as MySQL database) has reached its bottleneck.
However, due to business requirements, such data are still required to be stored in the relational databases. In order to enhance efficiency, the data can be classified according to certain rules, as shown in Fig. 1, the data are respectively stored in n number of tables starting from table 001, table 002, ..., table (n), where the value of n is decided by the data volume. These n number of tables are classified according to certain rules, and are respectively administered by m number of databases DB 01, DB 01,..., DB (m).
[0003] Based on the aforementioned scheme of dividing into sub-tables whereby one table is divided into n number of tables and the n number of tables are distributed in m number of different databases, a problem is engendered thereby that the conventional query by pagination is no longer applicable. Taking the MySQL database for example, the query by pagination relies on LIMIT keywords, and the parameters required thereby are the two parameters of the starting line number and the size of pagination. However, due to the division into sub-tables, there might be the circumstance in which data of a certain page Date Recue/Date Received 2022-02-15 might be stored in plural tables, under such a circumstance, it is impossible to realize query by pagination only in dependence of the two parameters transmitted from the front end. The traditional solving scheme is to enquire out all data of the sub-tables that conform to conditions, and query by pagination is performed after all data have been summarized, but this scheme consumes relatively great memory overhead, and is relatively low in enquiring efficiency.
SUMMARY OF THE INVENTION
[0004] In view of the above technical problems, there is an urgent need to provide a method of and a device for enquiring by pagination, corresponding computer equipment and storage medium capable of economizing on memory overhead and enhancing enquiring efficiency.
[0005] There is provided a method of enquiring, by pagination, sub-tables divided from a database, and the method comprises:
[0006] obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0007] determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0008] performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
Date Recue/Date Received 2022-02-15
[0009] In one of the embodiments, the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination includes:
[0010] when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of paginated data of the current query by pagination and the number of pieces of data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0011] In one of the embodiment, the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination includes:
[0012] when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, obtaining the number of pieces of front paginated data and the number of pieces of data in a current query result set; and
[0013] determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of front paginated data and the number of pieces of data in the current query result set as the number of pieces of paginated data of the next query by pagination.
[0014] In one of the embodiments, the step of determining a starting line number of the next Date Recue/Date Received 2022-02-15 query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination includes:
[0015] when the number of pieces of data of the current query by pagination is zero, determining a difference between the starting line number of the current query by pagination and the number of pieces of data of the current sub-table as the starting line number of the next query by pagination, and determining the number of pieces of paginated data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0016] In one of the embodiments, when a condition for performing the next query by pagination is satisfied, the next query by pagination is performed in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination; wherein
[0017] the condition for performing the next query by pagination includes: the last sub-table has not been completely enquired, the starting line number of the next query by pagination is not smaller than the starting line number of the next sub-table, the number of pieces of paginated data of the next query by pagination is greater than zero, and the number of pieces of data in the current query result set has not satisfied the number of pieces of front paginated data.
[0018] In one of the embodiments, the method further comprises:
[0019] temporarily storing data in the current query result set;
[0020] when the number of pieces of the temporarily stored data satisfies the number of pieces of front paginated data, returning the temporarily stored data as a result of the next query by pagination; alternatively,
[0021] when the last sub-table has been completely enquired, returning the temporarily stored data as a result of the next query by pagination.
Date Recue/Date Received 2022-02-15
[0022] In one of the embodiments, the data in the current query result set is temporarily stored in a memory.
[0023] There is provided a device for enquiring, by pagination, sub-tables divided from a database, and the device comprises:
[0024] an obtaining module, for obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0025] a processing module, for determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0026] an enquiring module, for performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0027] There is provided a computer equipment that comprises a memory, a processor and a computer program stored on the memory and operable on the processor, and the following steps are realized when the processor executes the computer program:
[0028] obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0029] determining a starting line number of the next query by pagination and the number of Date Recue/Date Received 2022-02-15 pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0030] performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0031] There is provided a computer-readable storage medium storing a computer program thereon, and the following steps are realized when the computer program is executed by a processor:
[0032] obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0033] determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0034] performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0035] In the aforementioned method of and device for enquiring, by pagination, sub-tables divided from a database, corresponding computer equipment and storage medium, the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current Date Recue/Date Received 2022-02-15 query by pagination are obtained, a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination are determined according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, and the next query by pagination is performed in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination. In the solutions of the embodiments of the present application, two parameters (namely the starting line number of the query by pagination and the number of pieces of paginated data of the query by pagination) required for query by pagination can be dynamically calculated in the query by pagination, so that, under the circumstance of division into sub-tables, the query by pagination can be performed like a single datasheet, thusly, even if the data of a certain page are stored in plural tables, cross-datasheets query by pagination can also be achieved; since the two parameters are dynamically calculated, once any data required by a designated page at the front has been enquired, a result can be returned, without having to enquire out all condition-conforming data of the sub-tables, whereby memory is saved, enquiring operation is simplified, and enquiring efficiency is enhanced.
BRIEF DESCRIPTION OF THE DRAWINGS
[0036] Fig. 1 is a view schematically illustrating the architecture of sub-tables;
[0037] Fig. 2 is a view illustrating an application environment for the method of enquiring, by pagination, sub-tables divided from a database in an embodiment;
[0038] Fig. 3 is a flowchart schematically illustrating the method of enquiring, by pagination, sub-tables divided from a database in an embodiment;
[0039] Fig. 4 is a flowchart schematically illustrating the step of determining the starting line Date Recue/Date Received 2022-02-15 number of the next query by pagination and the number of pieces of paginated data of the next query by pagination in an embodiment;
[0040] Fig. 5 is a view schematically illustrating the division of a single table into three sub-tables in an embodiment;
[0041] Fig. 6 is a view schematically illustrating logic summary in an embodiment;
[0042] Fig. 7 is a view schematically illustrating query by pagination of the logic summary in an embodiment;
[0043] Fig. 8 is a block diagram illustrating the structure of the device for enquiring, by pagination, sub-tables divided from a database in an embodiment; and
[0044] Fig. 9 is a view illustrating the internal structure of the computer equipment in an embodiment.
DETAILED DESCRIPTION OF THE INVENTION
[0045] To make more lucid and clear the objectives, technical solutions and advantages of the present application, the present application is described in greater detail below with reference to accompanying drawings and embodiments. As should be understood, the specific embodiments as described here are merely meant to explain the present application, rather than to restrict the present application.
[0046] The method of enquiring, by pagination, sub-tables divided from a database as provided by the present application is applicable to the application environment as shown in Fig.
2, in which terminal 202, server 204 and data storage device 206 intercommunicate via network.
Date Recue/Date Received 2022-02-15
[0047] Terminal 202 can be, but is not restricted to be, any of a personal computer, a notebook computer, a smart mobile phone, a panel computer and a portable wearable device (such as smart goggles, smart watch, etc.). During the process of operation, terminal 202 can operate a certain application program, so as to send to server 204 a query by pagination request that may carry therewith an initial value of the starting line number of the query by pagination and an initial value of the number of pieces of paginated data of the query by pagination, and receive a query by pagination result as returned by server 204.
[0048] Server 204 can be embodied as an independent server or a server cluster consisting of a plurality of servers. Server 204 serves as a data processing device. During the process of operation, server 204 can receive from terminal 202 a query by pagination request carrying therewith an initial value of the starting line number of the query by pagination and an initial value of the number of pieces of paginated data of the query by pagination, and, after the current query by pagination has been executed, can obtain the number of pieces of data of the current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of the current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination, determine a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, and perform the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination, so as to thusly realize cross-datasheets query by pagination.
[0049] Data storage device 206 can include one or more database device(s) each usually storing Date Recue/Date Received 2022-02-15 a plurality of datasheets, while according to requirements, it is also possible for one or more database device(s) to respectively store just one datasheet. When server performs query by pagination, data is enquired from data storage device 206.
It is also possible to base on actual requirements to embody server 204 and data storage device 206 as a single computer equipment, such as a server with a database.
[0050] In one embodiment, as shown in Fig. 3, there is provided a method of enquiring, by pagination, sub-tables divided from a database, and this method is explained with an example of its being applied to the server in Fig. 2, to comprise the following steps.
[0051] Step 302 - obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination.
[0052] The starting line number of the query by pagination and the number of pieces of paginated data of the query by pagination are two parameters required for the query by pagination, the starting line number of the query by pagination indicates from which line to start to enquire the data, the number of pieces of paginated data of the query by pagination indicates the pagination size of the query by pagination, and the initial values of these two parameters are usually transmitted from the front end (namely the terminal). In the embodiments of the present application, one round of data query performed with these two parameters serves as one query by pagination. The current query by pagination is the latest query by pagination already performed. The starting line number of the current query by pagination and the number of pieces of paginated data of the current query by pagination are two parameters required for the current query by pagination.
[0053] Specifically, the server can record the starting line number of the current query by Date Recue/Date Received 2022-02-15 pagination and the number of pieces of paginated data of the current query by pagination, determine and record the number of pieces of data of the current query by pagination after the current query by pagination has been performed to completion, and obtain the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination and the number of pieces of paginated data of the current query by pagination from the recorded data before the next query by pagination is performed. As understandable to persons skilled in the art, the modes of obtaining the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination and the number of pieces of paginated data of the current query by pagination are not restricted thereby.
[0054] Step 304 - determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination.
[0055] Here, the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination are two parameters required for the next query by pagination. The next query by pagination usually indicates the query by pagination that immediately follows the current query by pagination.
[0056] In the solution of this embodiment, the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination are dynamically determined by the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, whereby is realized dynamic readjustment of the two parameters required for the query by pagination.
Date Recue/Date Received 2022-02-15
[0057] By the same token, the starting line number of the current query by pagination and the number of pieces of paginated data of the current query by pagination can be determined according to the number of pieces of data of the previous query by pagination, the starting line number of the previous query by pagination, and the number of pieces of paginated data of the previous query by pagination. The starting line number of the current query by pagination can also be equal to the initial value of the starting line number of the query by pagination, and the number of pieces of paginated data of the current query by pagination can also be equal to the initial value of the number of pieces of paginated data of the query by pagination.
[0058] Step 306 - performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0059] The next sub-table is a sub-table immediately adjacent to the current sub-table. The current sub-table and the next sub-table in this embodiment can be two immediately adjacent sub-tables in the same sub-database, and can also be two immediately adjacent sub-tables in different sub-databases, for instance, the current sub-table is the last sub-table in the current sub-database, and the next sub-table is the first sub-table in the next sub-database. As understandable to persons skilled in the art, the wording "immediately adjacent" here means being immediately adjacent in the arrangement sequence of sub-tables or sub-databases.
[0060] In the aforementioned method of enquiring, by pagination, sub-tables divided from a database, the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination are obtained, a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination are determined according to the number of pieces of data of the current query by Date Recue/Date Received 2022-02-15 pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, and the next query by pagination is performed in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination. Through such mode, two parameters (namely the starting line number of the query by pagination and the number of pieces of paginated data of the query by pagination) required for query by pagination can be dynamically calculated, so that, under the circumstance of division into sub-tables, the query by pagination can be performed like a single datasheet, thusly, even if the data of a certain page are stored in plural tables, cross-datasheets query by pagination can also be achieved; since the two parameters are dynamically calculated, once any data required by a designated page at the front has been completely enquired, a result can be returned, without having to enquire out all condition-conforming data of the sub-tables, whereby memory is saved, enquiring operation is simplified, and enquiring efficiency is enhanced.
[0061] As should be noted, the above wordings "current" and "next" are meant to describe two rounds of queries by pagination that are related and sequentially adjacent, and are not meant to restrict which two specific rounds of queries by pagination they are;
any wordings capable of describing such sequentially adjacent relation can be regarded as conceptually equivalent to the wordings "current" and "next", for instance, the above "current" can be replaced with "previous", and the above "next" can be replaced with "current".
[0062] As should be additionally noted, in the solution of this embodiment, only the steps relevant to the solution of the present application are involved, namely the steps relevant to cross-datasheets query by pagination; with respect to the circumstance in which the data of a certain page or plural pages does not require cross-datasheets query by pagination in the entire process of the query by pagination, that is to say, the data enquired in one datasheet has already satisfied one pagination size, it is then possible to process Date Recue/Date Received 2022-02-15 with the mode of query by pagination according to a single table. At the same time, in the embodiments of the present application, one round of data query performed with the starting line number of the query by pagination and the number of pieces of paginated data of the query by pagination as two parameters required for the query by pagination serves as one query by pagination.
[0063] In one of the embodiments, step 304 can include the step of: judging whether data has been completely enquired out in the current query by pagination according to the number of pieces of data of the current query by pagination, obtaining a judging result, and basing on the judging result and a preset query by pagination parameter readjusting policy to determine the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0064] The query by pagination parameter readjusting policy can include that the number of pieces of paginated data of the next query by pagination satisfies the still lacking number of pieces of the number of pieces of front paginated data, and can further include that, when the judging result is that data has been enquired out, the starting line number of the next query by pagination is the starting line number of the next sub-table;
generally, the starting line number is 0 or 1, for instance, with respect to the MySQL
database, the line number subscript counts from 0, so the starting line number is 0; when the judging result is that no data has been enquired out, the starting line number of the next query by pagination is the difference between the starting line number of the current query by pagination and the number of pieces of data in the current sub-table; as understandable to persons skilled in the art, if there is a data enquiring condition, the number of pieces of data in the current sub-table is the number of pieces of data that satisfies the data enquiring condition in the current sub-table.
[0065] In one of the embodiments, step 304 can include the following step:
when the number of pieces of data of the current query by pagination is greater than zero and smaller than the Date Recue/Date Received 2022-02-15 number of pieces of paginated data of the current query by pagination, determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of paginated data of the current query by pagination and the number of pieces of data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0066] In this embodiment, the two parameters required for the next query by pagination can be determined according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, whereby low cost is achieved.
[0067] In one of the embodiments, as shown in Fig. 4, step 304 can include the following steps.
[0068] Step 402 - when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, obtaining the number of pieces of front paginated data and the number of pieces of data in a current query result set.
[0069] The number of pieces of front paginated data means the number of pieces of data contained in one pagination at the front end.
[0070] Step 404 - determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of front paginated data and the number of pieces of data in the current query result set as the number of pieces of paginated data of the next query by pagination.
[0071] What the current query result set stores is data that has been currently enquired out and not returned to the front end (namely the terminal).
Date Recue/Date Received 2022-02-15
[0072] For instance, the number of pieces of front paginated data is 10, and the number of pieces of data in the current query result set is 6, then the number of pieces of paginated data of the next query by pagination will be 4.
[0073] The realizing cost for determining the two parameters required for the next query by pagination in this embodiment is also relatively low.
[0074] In one of the embodiments, step 304 can include the following step:
when the number of pieces of data of the current query by pagination is zero, determining a difference between the starting line number of the current query by pagination and the number of pieces of data of the current sub-table as the starting line number of the next query by pagination, and determining the number of pieces of paginated data of the current query by pagination as the number of pieces of paginated data of the next query by pagination. As understandable to persons skilled in the art, if there is a data enquiring condition, the number of pieces of data in the current sub-table is the number of pieces of data that satisfies the data enquiring condition in the current sub-table.
[0075] In this embodiment, the two parameters required for the next query by pagination can be determined according to the number of pieces of data of the current query by pagination, the number of pieces of data of the current sub-table, and the number of pieces of paginated data of the current query by pagination, whereby low cost is achieved.
[0076] As should be noted, since the number of pieces of data of the current query by pagination is not greater than the number of pieces of paginated data of the current query by pagination, so the circumstance in which the number of pieces of data of the current query by pagination is greater than the number of pieces of paginated data of the current query by pagination is not involved in the solution of the aforementioned embodiment. Besides, one piece of data is recorded in one line of each sub-table.
Date Recue/Date Received 2022-02-15
[0077] In one of the embodiments, when a condition for performing the next query by pagination is satisfied, the next query by pagination is performed in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination; the condition for performing the next query by pagination includes: the last sub-table has not been completely enquired, the starting line number of the next query by pagination is not smaller than the starting line number of the next sub-table, the number of pieces of paginated data of the next query by pagination is greater than zero, and the number of pieces of data in the current query result set has not satisfied the number of pieces of front paginated data.
[0078] When the starting line number of the next sub-table is zero, the starting line number of the next query by pagination is greater than or equal to zero. It is further possible to set more or less conditions for performing the next query by pagination according to requirements.
[0079] In one of the embodiments, the method of enquiring, by pagination, sub-tables divided from a database according to the present application can further comprise the following step: after the current query by pagination, detecting whether the number of pieces of data in the current query result set satisfies the number of pieces of front paginated data, namely whether the former is equal to the number of pieces of front paginated data, if not, the process entering the aforementioned step 302, if yes, returning the data in the current query result set to the terminal.
[0080] In one of the embodiments, the method of enquiring, by pagination, sub-tables divided from a database according to the present application can further comprise the following step: after the next query by pagination, detecting whether the number of pieces of data in the current query result set satisfies the number of pieces of front paginated data, namely whether the former is equal to the number of pieces of front paginated data, if not, taking the number of pieces of data enquired from the next sub-table during the next query Date Recue/Date Received 2022-02-15 by pagination as new number of pieces of data of the current query by pagination, taking the starting line number of the next query by pagination as a new starting line number of the current query by pagination, taking the number of pieces of paginated data of the next query by pagination as new number of pieces of paginated data of the current query by pagination, taking the next sub-table as a new current sub-table, and returning to step 304, if yes, returning the data in the current query result set to the terminal.
[0081] In the solutions of the above two embodiments, the data in the current query result set can be returned to the terminal once the current query result set satisfies the number of pieces of front paginated data, without having to return to the front end after all data that conforms to the enquiring condition has been enquired out, whereby memory overhead can be reduced, and enquiring efficiency can be enhanced.
[0082] In one of the embodiments, the method of enquiring, by pagination, sub-tables divided from a database according to the present application can further comprise the following step: temporarily storing data in the current query result set; when the number of pieces of the temporarily stored data satisfies the number of pieces of front paginated data, returning the temporarily stored data as a result of the next query by pagination;
alternatively, when the last sub-table has been completely enquired, returning the temporarily stored data as a result of the next query by pagination.
[0083] Preferably, the data in the current query result set is temporarily stored in a memory.
[0084] In the solution of this embodiment, the number of pieces of the temporarily stored data is smaller than or equal to the number of pieces of front paginated data, rather than all the data that satisfies the enquiring condition, whereby memory overhead can be reduced.
[0085] The solution of the above embodiment is applicable to the circumstances both with data enquiring condition and without data enquiring condition, when there is no data enquiring Date Recue/Date Received 2022-02-15 condition, query by pagination is performed according to the original line numbers of the various sub-tables, and when there is no data enquiring condition, query by pagination is performed according to line numbers sequentially determined anew on the basis of line numbers of the various sub-tables satisfying the data enquiring condition. For instance, there are twelve lines of data 0, 1, 2..., 11, in sub-table Table 002, and the data in the four lines of 3, 8, 9 and 10 satisfies the condition, so the line numbers of these four lines of data are sequentially determined anew as 0, 1, 2, 3.
[0086] The initial value of the aforementioned starting line number of the query by pagination and the initial value of the number of pieces of paginated data of the query by pagination can be determined according to the number of pieces of front paginated data and the pagination number of a target pagination. The pagination number of the target pagination is a pagination number to be displayed at the front end.
[0087] Specifically, the initial value of the starting line number of the query by pagination can be determined according to F=(t-1)*p, where F represents the initial value of the starting line number of the query by pagination, p represents the number of pieces of front paginated data, t represents the pagination number of the target pagination, and the number of pieces of front paginated data is determined as the initial value of the number of pieces of paginated data of the query by pagination. On receiving the query by pagination request from the terminal, the server can base on the number of pieces of front paginated data and the pagination number of the target pagination carried with the query by pagination request to determine the initial value of the starting line number of the query by pagination and the initial value of the number of pieces of paginated data of the query by pagination. Generally, when the query by pagination request is received, data is enquired from the first sub-table.
[0088] In order to facilitate comprehension of the solution of the present invention, a specific embodiment is made below to explain the solution of the present invention. As shown in Date Recue/Date Received 2022-02-15 Fig. 5, suppose a single table is divided into three sub-tables, respectively named as table 001, table 002 and table 003, in which are respectively stored some data, these data are numbered to be ten pieces of data altogether, of which seven pieces of data are stored in table 001, two pieces of data are stored in table 002, and one piece of data is stored in table 003. In the following description LIMIT keywords are taken to serve as query by pagination statements in a MySQL database, and the starting line number of each sub-table is 0 by way of example, but none of these constitutes any restriction to the solution of the present invention.
[0089] The core of the method of enquiring, by pagination, sub-tables divided from a database in the embodiments of the present application is to regard the sub-tables as one logic summary no matter how many they are divided, and this logic summary is connected by all the sub-tables head to end. As shown in Fig. 6, it is now supposed that a front-end user requires query by pagination, the query starts from the first page, and three pieces of data are displayed in each page, until all data have been enquired to completion.
Then the conversion to query by pagination is as shown in Fig. 7. The query by pagination relies on the LIMIT keywords, and the parameters required thereby are 0 starting line number and 0 pagination size. From the query by pagination based on the logic summary can be obtained the correspondence relation in the following Table 1.
Table 1 Sql Statement Page Number Table Involved Enquired Data LIMIT (0,3) 1 table 001 1, 2, 3 LIMIT (3,3) 2 table 001 4, 5, 6 LIMIT (6,3) 3 table 001, table 002 7, , 8, 9 LIMIT (9,3) 4 table 001, table 002, , 10 table 003
[0090] The four queries by pagination (SQL) statements are analyzed one-by-one below.
[0091] LIMIT (0,3):
Date Recue/Date Received 2022-02-15
[0092] Table 001 serves as a starting table, on which is performed LIMIT (0,3) to enable the returning of three pieces of data 1,2, 3 to the current query result set, at this time, the data of the current query result set satisfies the front pagination size (corresponding to the aforementioned number of pieces of front paginated data), it is then possible to return the query result: 1, 2, 3 to the front end (namely the terminal). When the data in the current query result set is returned to the front end, the data in the current query result set is usually also correspondingly deleted, to reduce occupation of the memory.
[0093] LIMIT (3,3):
[0094] Table 001 serves as a starting table, on which is performed LIMIT (3,3) to enable the returning of three pieces of data 4, 5, 6 to the current query result set, at this time, the data of the current query result set satisfies the front pagination size, it is then possible to return the query result: 4, 5, 6 to the front end.
[0095] The query so far merely involves table 001. However, due to the division into sub-tables, there might be the circumstance in which data of a certain page might be stored in plural tables, and this circumstance of query by pagination is enunciated as follows.
[0096] LIMIT (6,3):
[0097] Table 001 serves as a starting table, on which is performed LIMIT (6,3) to enable the returning of one piece of data 7, while two pieces are further needed; since the query to table 001 gets satisfying data, LIMIT (0,2) is performed on table 002. The "0"
here is because there is satisfying data in the query to table 001, so it suffices to start the query from the first piece of data with respect to table 002, and the line number of the first piece of data of table 002 is 0. The "2" here is because two pieces of data are further required for this query before returning to the front end. This mode of determining (0,2) can be referred to as query by pagination parameter readjusting policy 1. After LIMIT
(0,2) has been performed on table 002, the two pieces of data 8, 9 can be returned to the current query result set, at this time, the data of the current query result set satisfies the front pagination size, it is then possible to return the query result: 7, 8, 9 to the front end.
Date Recue/Date Received 2022-02-15
[0098] LIMIT (9,3):
[0099] Table 001 serves as a starting table, on which is performed LIMIT
(9,3), while no data is returned; since there is no returning data from the query to table 001, LIMIT
(2,3) is performed on table 002. The "2" here is because there is no data returning from the query to table 001, so a starting location calculation formula of table 002 is: 9-count (table 001) = 2, where count (table 001) is a counting operation performed on the data of table 001.
The "3" is because three pieces of data are further required before returning.
This mode of determining (2,3) can be referred to as query by pagination parameter readjusting policy 2.
[0100] There is still no data returned after performing LIMIT (2,3) on table 002. Then, by the same token, LIMIT (0,3) is continued to be performed on table 003 to enable the return of one piece of data 10. If there is further a table 004, calculation can be carried on according to the query by pagination parameter readjusting policy 1. However, since the entire three sub-tables have been traversed, this query returns the result:
10.
[0101] The results of comparison between the method of enquiring, by pagination, sub-tables divided from a database according to the embodiments of the present application and the conventional method are shown in the following Table 2, the test of this comparison makes use of a total amount of 10 million data, with 1000 sub-tables. Each piece of data occupies approximately 1KB of the memory, and each pagination size P (namely the number of pieces of front paginated data) is 10. There are altogether 1024 pieces of data that satisfy the enquiring condition.
Date Recue/Date Received 2022-02-15 Table 2 Method Total Amount of Memory Query Hit Ratio of Data Enquired Occupied (M) Rounds (C) Query (P/A) (A) Conventional 1024 1MB 1000 0.009765625 Method The Method 10 10KB 1-1000, 1 according to the dependent Embodiments of on data the present distribution Application
[0102] As should be understood, although the various steps in the flowcharts of Figs. 3-4 are sequentially displayed as indicated by arrows, these steps are not necessarily executed in the sequences indicated by arrows. Unless otherwise explicitly noted in this paper, execution of these steps is not restricted by any sequence, as these steps can also be executed in other sequences (than those indicated in the drawings). Moreover, at least partial steps in Figs. 3-4 may include plural sub-steps or multi-phases, these sub-steps or phases are not necessarily completed at the same timing, but can be executed at different timings, and these sub-steps or phases are also not necessarily sequentially performed, but can be performed in turns or alternately with other steps or with at least some of sub-steps or phases of other steps.
[0103] In one embodiment, as shown in Fig. 8, there is provided a device for enquiring, by pagination, sub-tables divided from a database, and the device comprises an obtaining module 802, a processing module 804, and an enquiring module 806, of which
[0104] the obtaining module 802 is employed for obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
Date Recue/Date Received 2022-02-15
[0105] the processing module 804 is employed for determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0106] the enquiring module 806 is employed for performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0107] In one of the embodiments, when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, the processing module 804 can determine a starting line number of the next sub-table as the starting line number of the next query by pagination, and determine a difference between the number of pieces of paginated data of the current query by pagination and the number of pieces of data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0108] In one of the embodiments, when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, the processing module 804 can obtain the number of pieces of front paginated data and the number of pieces of data in a current query result set, determine a starting line number of the next sub-table as the starting line number of the next query by pagination, and determine a difference between the number of pieces of front paginated data and the number of pieces of data in the current query result set as the number of pieces of paginated data of the next query by pagination.
[0109] In one of the embodiments, when the number of pieces of data of the current query by pagination is zero, the processing module 804 can determine a difference between the starting line number of the current query by pagination and the number of pieces of data Date Recue/Date Received 2022-02-15 of the current sub-table as the starting line number of the next query by pagination, and determine the number of pieces of paginated data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0110] In one of the embodiments, when a condition for performing the next query by pagination is satisfied, the enquiring module 806 can perform the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination; the condition for performing the next query by pagination includes: the last sub-table has not been completely enquired, the starting line number of the next query by pagination is not smaller than the starting line number of the next sub-table, the number of pieces of paginated data of the next query by pagination is greater than zero, and the number of pieces of data in the current query result set has not satisfied the number of pieces of front paginated data.
[0111] In one of the embodiments, the enquiring module 806 can further temporarily store data in the current query result set, when the number of pieces of the temporarily stored data satisfies the number of pieces of front paginated data, return the temporarily stored data as a result of the next query by pagination, or, when the last sub-table has been completely enquired, return the temporarily stored data as a result of the next query by pagination.
Preferably, the data in the current query result set is temporarily stored in a memory.
[0112] Specific definitions relevant to the device for enquiring, by pagination, sub-tables divided from a database may be inferred from the aforementioned definitions to the method of enquiring, by pagination, sub-tables divided from a database, while no repetition is made in this context. The various modules in the aforementioned device for enquiring, by pagination, sub-tables divided from a database can be wholly or partly realized via software, hardware, and a combination of software with hardware. The various modules can be embedded in the form of hardware in a processor in a computer equipment or Date Recue/Date Received 2022-02-15 independent of any computer equipment, and can also be stored in the form of software in a memory in a computer equipment, so as to facilitate the processor to invoke and perform operations corresponding to the aforementioned various modules.
[0113] In one embodiment, a computer equipment is provided, the computer equipment can be a server, and its internal structure can be as shown in Fig. 9. The computer equipment comprises a processor, a memory, a network interface and a database connected to each other via a system bus. The processor of the computer equipment is employed to provide computing and controlling capabilities. The memory of the computer equipment includes a nonvolatile storage medium, and an internal memory. The nonvolatile storage medium stores therein an operating system, a computer program and a database. The internal memory provides environment for the running of the operating system and the computer program in the nonvolatile storage medium. The database of the computer equipment employs the mode of sub-tables to store data. The network interface of the computer equipment is employed to connect to an external terminal via network for communication.
The computer program realizes a method of enquiring, by pagination, sub-tables divided from a database when it is executed by a processor.
[0114] As understandable to persons skilled in the art, the structure illustrated in Fig. 9 is merely a block diagram of partial structure relevant to the solution of the present application, and does not constitute any restriction to the computer equipment on which the solution of the present application is applied, as the specific computer equipment may comprise component parts that are more than or less than those illustrated in Fig. 9, or may combine certain component parts, or may have different layout of component parts.
[0115] In one embodiment, there is provided a computer equipment that comprises a memory, a processor and a computer program stored on the memory and operable on the processor, and the following steps are realized when the processor executes the computer program:
[0116] obtaining the number of pieces of data of a current query by pagination, a starting line Date Recue/Date Received 2022-02-15 number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0117] determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination; and
[0118] performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0119] In one of the embodiments, when the processor executes the computer program to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized: when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of paginated data of the current query by pagination and the number of pieces of data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0120] In one of the embodiment, when the processor executes the computer program to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the Date Recue/Date Received 2022-02-15 number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized:
[0121] when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, obtaining the number of pieces of front paginated data and the number of pieces of data in a current query result set; and
[0122] determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of front paginated data and the number of pieces of data in the current query result set as the number of pieces of paginated data of the next query by pagination.
[0123] In one of the embodiments, when the processor executes the computer program to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized: when the number of pieces of data of the current query by pagination is zero, determining a difference between the starting line number of the current query by pagination and the number of pieces of data of the current sub-table as the starting line number of the next query by pagination, and determining the number of pieces of paginated data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0124] In one of the embodiments, when the processor executes the computer program to realize the step of performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination, the following step is specifically realized:
performing the next query by pagination in the next sub-table according to the starting Date Recue/Date Received 2022-02-15 line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination when a condition for performing the next query by pagination is satisfied; the condition for performing the next query by pagination includes:
the last sub-table has not been completely enquired, the starting line number of the next query by pagination is not smaller than the starting line number of the next sub-table, the number of pieces of paginated data of the next query by pagination is greater than zero, and the number of pieces of data in the current query result set has not satisfied the number of pieces of front paginated data.
[0125] In one of the embodiments, when the processor executes the computer program, the following steps are further realized: temporarily storing data in the current query result set; when the number of pieces of the temporarily stored data satisfies the number of pieces of front paginated data, returning the temporarily stored data as a result of the next query by pagination; alternatively, when the last sub-table has been completely enquired, returning the temporarily stored data as a result of the next query by pagination.
Preferably, the data in the current query result set is temporarily stored in a memory.
[0126] In one embodiment, there is provided a computer-readable storage medium storing thereon a computer program, and the following steps are realized when the computer program is executed by a processor:
[0127] obtaining the number of pieces of data of a current query by pagination, a starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, wherein the number of pieces of data of a current query by pagination is the number of pieces of data enquired from a current sub-table during the current query by pagination;
[0128] determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by Date Recue/Date Received 2022-02-15 pagination; and
[0129] performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination.
[0130] In one of the embodiments, when the computer program is executed by a process to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized: when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, determining a starting line number of the next sub-table as the starting line number of the next query by pagination, and determining a difference between the number of pieces of paginated data of the current query by pagination and the number of pieces of data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0131] In one of the embodiment, when the computer program is executed by a process to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized:
[0132] when the number of pieces of data of the current query by pagination is greater than zero and smaller than the number of pieces of paginated data of the current query by pagination, obtaining the number of pieces of front paginated data and the number of pieces of data in a current query result set; and
[0133] determining a starting line number of the next sub-table as the starting line number of the Date Recue/Date Received 2022-02-15 next query by pagination, and determining a difference between the number of pieces of front paginated data and the number of pieces of data in the current query result set as the number of pieces of paginated data of the next query by pagination.
[0134] In one of the embodiments, when the computer program is executed by a process to realize the step of determining a starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination according to the number of pieces of data of the current query by pagination, the starting line number of the current query by pagination, and the number of pieces of paginated data of the current query by pagination, the following steps are specifically realized: when the number of pieces of data of the current query by pagination is zero, determining a difference between the starting line number of the current query by pagination and the number of pieces of data of the current sub-table as the starting line number of the next query by pagination, and determining the number of pieces of paginated data of the current query by pagination as the number of pieces of paginated data of the next query by pagination.
[0135] In one of the embodiments, when the computer program is executed by a process to realize the step of performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination, the following step is specifically realized:
performing the next query by pagination in the next sub-table according to the starting line number of the next query by pagination and the number of pieces of paginated data of the next query by pagination when a condition for performing the next query by pagination is satisfied; the condition for performing the next query by pagination includes:
the last sub-table has not been completely enquired, the starting line number of the next query by pagination is not smaller than the starting line number of the next sub-table, the number of pieces of paginated data of the next query by pagination is greater than zero, and the number of pieces of data in the current query result set has not satisfied the number of pieces of front paginated data.
Date Recue/Date Received 2022-02-15
[0136] In one of the embodiments, when the computer program is executed by a process, the following steps are further realized: temporarily storing data in the current query result set; when the number of pieces of the temporarily stored data satisfies the number of pieces of front paginated data, returning the temporarily stored data as a result of the next query by pagination; alternatively, when the last sub-table has been completely enquired, returning the temporarily stored data as a result of the next query by pagination.
Preferably, the data in the current query result set is temporarily stored in a memory.
[0137] As comprehensible to persons ordinarily skilled in the art, the entire or partial flows in the methods according to the aforementioned embodiments can be completed via a computer program instructing relevant hardware, the computer program can be stored in a nonvolatile computer-readable storage medium, and the computer program can include the flows as embodied in the aforementioned various methods when executed. Any reference to the memory, storage, database or other media used in the various embodiments provided by the present application can all include nonvolatile and/or volatile memory/memories. The nonvolatile memory can include a read-only memory (ROM), a programmable ROM (PROM), an electrically programmable ROM (EPROM), an electrically erasable and programmable ROM (EEPROM) or a flash memory. The volatile memory can include a random access memory (RAM) or an external cache memory. To serve as explanation rather than restriction, the RAM is obtainable in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM
(SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM
(RDRAM), direct Rambus dynamic RAM (DRDRAM), and Rambus dynamic RAM
(RDRAM), etc.
[0138] Technical features of the aforementioned embodiments are randomly combinable, while all possible combinations of the technical features in the aforementioned embodiments Date Recue/Date Received 2022-02-15 are not exhausted for the sake of brevity, but all these should be considered to fall within the scope recorded in the Description as long as such combinations of the technical features are not mutually contradictory.
[0139] The foregoing embodiments are merely directed to several modes of execution of the present application, and their descriptions are relatively specific and detailed, but they should not be hence misunderstood as restrictions to the inventive patent scope. As should be pointed out, persons with ordinary skill in the art may further make various modifications and improvements without departing from the conception of the present application, and all these should pertain to the protection scope of the present application.
Accordingly, the patent protection scope of the present application shall be based on the attached Claims.
Date Recue/Date Received 2022-02-15