Database table processing method, device, equipment and medium

Abstract

The present invention provides a database table processing method, apparatus, device, and medium, the method comprising: obtaining a first database table and a second database table, both of which have a target field; determining one of the first database table and the second database table as a main table and the other as an appendix based on an association condition; converting the data in the appendix into a first key-value pair set; matching each row of data in the main table with the first key-value pair set based on the same value of the target field; assembling one or more new key-value pair sets from a row of data in the main table and a matching key-value pair in the first key-value pair set, and placing the new key-value pair sets into a final result set. The present invention solves the problems of high time complexity and extremely low efficiency in efficient multi-table association queries in relational databases when indexes cannot be used, and greatly improves the performance of multi-table association queries without indexes.

Description

Technical Field

[0001] The present invention relates to the field of data query technology, and in particular to a database table processing method, device, equipment and medium. Background Art

[0002] A relational database is a database that organizes data in a two-dimensional table model and is widely used by various application systems. In a relational database table, an index is a data structure used by the storage engine to quickly find records. Query efficiency is generally improved by creating appropriate indexes. Especially when the amount of data in the database table increases, the impact of indexes on performance becomes increasingly important.

[0003] In actual project implementations, we've discovered database tables with no or inappropriate indexes, and excessive indexes that lead to excessively large index files, increasing system query times. This leads to inefficient queries and even inability to obtain results when data volumes increase and indexes can't be used for multi-table join queries, making it difficult to meet business data processing and processing requirements. Summary of the Invention

[0004] In view of the above problems, embodiments of the present invention provide a database table processing method, apparatus, device, and medium to overcome the above problems or at least partially solve the above problems.

[0005] A first aspect of an embodiment of the present invention discloses a method for processing a database table, the method comprising:

[0006] Obtain a first database table and a second database table, wherein the first database table and the second database table both have a target field;

[0007] According to the association condition, one of the first database table and the second database table is determined as a main table, and the other is determined as a supplementary table;

[0008] Convert the data in the appendix into a first key-value pair set, wherein one key-value pair in the first key-value pair set corresponds to one row of data or multiple rows of data in the appendix, the key in one key-value pair is: a value of the target field in the appendix, and the value in one key-value pair is: the values ​​of other fields in one row of data or multiple rows of data where the value of the target field in the appendix is ​​located;

[0009] Based on the matching principle of the same value of the target field, each row of data in the main table is matched with the first key-value pair set;

[0010] Assemble one or more new key-value pair sets by combining a row of data in the main table with a key-value pair that matches in the first key-value pair set, and put them into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is each field name in the row of the matched main table and the appendix, and the value in a key-value pair is each value corresponding to each field name in the row of the matched main table and the appendix.

[0011] Optionally, according to the association condition, determining one of the first database table and the second database table as a main table and the other as a supplementary table includes:

[0012] When the association condition is that the first database table left-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table;

[0013] When the association condition is that the first database table right-joins the second database table, the first database table is determined as an attached table, and the second database table is determined as a main table;

[0014] When the association condition is that the first database table inner-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table.

[0015] Optionally, converting the data in the appendix into a first key-value pair set includes:

[0016] Reading the data in the appendix and determining each value of the target field in the appendix;

[0017] For each value of the target field in the appendix, add the values ​​of other fields in the row of data or multiple rows of data where the value is located into a list;

[0018] Taking each value of the target field in the appendix as a key and a list corresponding to the value as a value, a key-value pair in the first key-value pair set is obtained.

[0019] Optionally, matching each row of data in the main table with the first key-value pair set based on the same value of the target field as a matching principle includes:

[0020] Read the data in the main table and determine each value of the target field in the main table;

[0021] Compare each value of the target field in the main table with the key of each key-value pair in the first key-value pair set to see whether they are the same.

[0022] Optionally, assembling a row of data in the main table and a key-value pair that matches the first key-value pair set into one or more new key-value set pairs includes:

[0023] When a value of the target field in the main table and a key of a key-value pair in the first key-value pair set are both the first target value, assembling the values ​​of other fields in each row of data in the main table and the appendix table whose value of the target field is the target value, to obtain one or more key-value pair sets;

[0024] The key-value pair set has each field name in the main table and the appendix row where the value of the target field is the first target value as the key of the key-value pair, and each field name in the main table and the appendix row where the value of the target field is the first target value as the value of the key-value pair.

[0025] Optionally, the method further includes:

[0026] If a value of the target field in the primary table is a second target value, which is different from the key of each key-value pair in the first key-value pair set, determining whether the association condition is an inner-join of the first database table to the second database table;

[0027] When the association condition is inner-joining the first database table to the second database table, the values ​​of other fields in one or more rows of data in the main table containing the target field having the second target value are discarded.

[0028] Optionally, the method further includes:

[0029] If a value of the target field in the primary table is a second target value, which is different from the key of each key-value pair in the first key-value pair set, determining whether the association condition is an inner-join of the first database table to the second database table;

[0030] If the join condition is not inner-joining the first database table to the second database table, a key-value pair is obtained by taking the value of the target field as the second target value and the values ​​of other fields in the row of data in the main table containing the target field having the second target value as the value, and adding the obtained key-value pair to the new key-value pair set;

[0031] When the association condition is not inner-joining the first database table to the second database table, each field name in the row where the target field in the main table has the second target value is used as key, and each field name in the row where the target field in the main table has the second target value is used as value. These are put into a new key-value pair set one by one, and each new key-value pair set is put into the final result set.

[0032] A second aspect of an embodiment of the present invention discloses a database table processing device, the device comprising:

[0033] An acquisition module, configured to obtain a first database table and a second database table, wherein both the first database table and the second database table have a target field;

[0034] a determination module, configured to determine, based on an association condition, one of the first database table and the second database table as a main table and the other as a supplementary table;

[0035] a conversion module, configured to convert the data in the appendix into a first key-value pair set, wherein a key-value pair in the first key-value pair set corresponds to a row of data or multiple rows of data in the appendix, a key in a key-value pair is: a value of the target field in the appendix, and a value in a key-value pair is: values ​​of other fields in a row of data or multiple rows of data where a value of the target field in the appendix is ​​located;

[0036] a matching module, configured to match each row of data in the main table with the first set of key-value pairs based on the same value of the target field as a matching principle;

[0037] An assembly module is used to assemble a row of data in the main table and a key-value pair that matches in the first key-value pair set into one or more new key-value pair sets, and put them into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is: each field name of the row where the matching main table and the appendix are located, and the value in a key-value pair is: the value corresponding to each field name of the row where the matching main table and the appendix are located.

[0038] According to a third aspect of an embodiment of the present invention, an electronic device is disclosed, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor. When executed by the processor, the method for processing a database table as described in the first aspect of the present invention is implemented.

[0039] A fourth aspect of an embodiment of the present invention discloses a computer-readable storage medium having a computer program / instruction stored thereon. When the computer program / instruction is executed by a processor, the database table processing method described in the first aspect of the present invention is implemented.

[0040] The embodiments of the present invention include the following advantages:

[0041] In an embodiment of the present invention, in order to solve the problems of high time complexity and extremely low efficiency in efficient multi-table association queries when indexes cannot be used in relational databases, a first database table and a second database table are first obtained, and then, based on the association conditions, one of the first database table and the second database table is determined as the main table and the other is determined as the appendix, and the data in the appendix is ​​converted into a first key-value pair set. Based on the matching principle of the same value of the target field, the data in the main table is matched with the first key-value pair set, and a row of data in the main table is assembled with a key-value pair that matches in the first key-value pair set to form one or more new key-value pair sets, and put into the final result set. Since the key-value pairs in the new key-value pair set are all obtained by associating the main table and the appendix, efficient query of multiple database tables without indexes is achieved, thereby improving the performance of multi-table association queries without indexes. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments of the present invention. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0043] Figure 1 This is a flowchart of a database table processing method provided by an embodiment of the present invention;

[0044] Figure 2 This is a database table example 1 provided in an embodiment of the present invention;

[0045] Figure 3 This is a database table example 2 provided in an embodiment of the present invention;

[0046] Figure 4 This is a flowchart of a database table processing method provided by an embodiment of the present invention;

[0047] Figure 5 This is a schematic diagram of an implementation process of a database table processing method provided by an embodiment of the present invention;

[0048] Figure 6 It is a schematic diagram of the structure of a database table processing device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0049] To make the above-mentioned objects, features, and advantages of the present invention more clearly understood, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0050] The embodiment of the present invention provides a database table processing method, such as Figure 1 As shown, Figure 1 A flowchart of a database table processing method provided by an embodiment of the present invention includes steps S101 to S105:

[0051] Step S101: obtaining a first database table and a second database table, wherein both the first database table and the second database table have a target field.

[0052] In this embodiment, the first database table and the second database table are database tables that need to be associated with each other, and there is no index relationship between the first database table and the second database table. That is, in this embodiment, an associated query is performed on the two database tables when there is no index relationship between the first database table and the second database table. The target field refers to the connection field between the first database table and the second database table for the associated query. For example, for Figure 2 and Figure 3 The database table shown (denoted by t1 Figure 2 The database table of the example is represented by t2 Figure 3 In the example database table, use Java ListMap to implement select t1.*,t2.c5,t2.c6,t2.c7,t2.c8 from user_info t1 LEFT JOIN user_department t2 on t1.user_id=t2.user_id. (The code means: associate and match the same values ​​in the user_id columns in t1 and t2). In this case, the user_id column is the target field, and each value in the user_id column is the corresponding value of the target field.

[0053] Step S102: According to the association condition, one of the first database table and the second database table is determined as a main table, and the other is determined as a supplementary table.

[0054] In this embodiment, the association condition refers to the association query method between two database tables, that is, the connection method between the two data lists can be known based on the association condition. Generally, the connection methods of database tables include: left-join, right-join, inner-join, etc.

[0055] The primary table is the query basis for database table association queries, while the appendix is ​​the queried object for database table association queries. That is, when querying two database tables, the appendix data is read first. Then, based on the target fields in the primary table, the appendix is ​​matched sequentially to retrieve data records corresponding to the same target fields in the appendix. In actual applications, the primary and appendix tables, as well as the processing order, are determined based on the association conditions between the database tables. During processing, the appendix data is organized into a map-type collection, and the primary table data is organized into a list-type collection to ensure the integrity of the primary table data.

[0056] In an optional embodiment, determining one of the first database table and the second database table as a main table and the other as a supplementary table according to an association condition includes:

[0057] When the association condition is that the first database table left-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table;

[0058] When the association condition is that the first database table right-joins the second database table, the first database table is determined as an attached table, and the second database table is determined as a main table;

[0059] When the association condition is that the first database table inner-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table.

[0060] In this embodiment, three different join methods exist, with three association conditions. A left-join of the first database table with the second database table returns all data in the first database table and data where the target field in the second database table is equal, i.e., the first database table serves as the query basis (primary table) and the second database table is the queried object (supplementary table). A right-join of the first database table with the second database table returns all data in the second database table and data where the target field in the first database table is equal, i.e., the second database table serves as the query basis (primary table) and the first database table is the queried object (supplementary table). An inner-join of the first database table with the second database table returns only data where the target field in the two database tables is equal. In this case, the process is treated as a left-join, i.e., the first database table serves as the query basis (primary table) and the second database table is the queried object (supplementary table).

[0061] In actual applications, the connection method between two data lists is determined according to the different associated query codes of the database tables. For example, Figure 2 and Figure 3 The database table shown in the figure has the following associated query code: select t1.*,t2.c5,t2.c6,t2.c7,t2.c8 from user_info t1 LEFT JOIN user_department t2 on t1.user_id=t2.user_id. Then, according to the left join, it can be confirmed that user_info is the main table and user_department is the supplementary table, that is, Figure 2 For the main table, Figure 3 See the attached table.

[0062] Step S103: Convert the data in the appendix into a first key-value pair set, wherein a key-value pair in the first key-value pair set corresponds to a row of data or multiple rows of data in the appendix, a key in a key-value pair is: a value of the target field in the appendix, and a value in a key-value pair is: the values ​​of other fields in a row of data or multiple rows of data where a value of the target field in the appendix is ​​located.

[0063] In this embodiment, the appendix is ​​the object to be queried, and all the data in the appendix must first be converted into a first key-value pair set, that is, the first key-value pair set includes all the data in the appendix. In subsequent steps, query matching is performed based on the main table based on the first key-value pair set.

[0064] A key-value pair in the first key-value pair set corresponds to a row of data or multiple rows of data in the appendix, which means that the data constituting the key-value pair is composed of a row of data in the appendix, or is constructed from multiple rows of data in the appendix. Specifically, when the value of the target field of the row of data in the appendix is ​​different from the value of the target field of other rows of data, the data in the row constitutes a key-value pair. When there are multiple rows of data in the appendix with the same target field value, the multiple rows of data corresponding to the same target field value constitute a key-value pair, wherein the value of the target field of one or more rows of data is the key of the key-value pair, and the other data of one or more rows of data except the target field constitutes the value of the key-value pair.

[0065] For example, for Figure 3 In the database table shown as an example, the user_id column is the target field. The values ​​of the target field are: 1, 4, 5, 6, 7, and 8, respectively. For the key with a value of 1, the data corresponding to its value includes two rows with a user_id value of 1. Similarly, for the key with a value of 4, the data corresponding to its value includes one row with a user_id value of 4.

[0066] In an optional embodiment, converting the data in the appendix into a first key-value pair set includes steps A1 to A3:

[0067] Step A1: Read the data in the appendix and determine each value of the target field in the appendix.

[0068] Step A2: For each value of the target field in the appendix, add the values ​​of other fields in the row of data or multiple rows of data where the value is located into a list.

[0069] Step A3: Using each value of the target field in the appendix as a key and a list corresponding to the value as a value, a key-value pair in the first key-value pair set is obtained.

[0070] In this embodiment, each row of data in the appendix is ​​read in sequence, and then the value of the target field corresponding to each row of data is determined, and then each value of the target field is obtained. The values ​​of the target fields corresponding to different rows may be the same. When the value of the target field of a row of data is different from the values ​​of the target fields of other rows, the remaining data of the row of data excluding the target field is added to a list. When there are multiple rows of data with the same target field value, the multiple rows of data with the same target value excluding the remaining data of the target field are added to the same list. After determining the value of each target field, the value of each target field is used as a key, that is, as the key in the key-value pair, and then the list corresponding to the target field value is used as the value, that is, as the value of the key-value pair, and then multiple key values ​​are obtained to form a first key-value pair set.

[0071] For example, Figure 4 As shown, step ① processes the data in the appendix into the first key-value pair set. First, the value 1 of the target field user_id in the appendix is ​​used as the key of the first key-value pair (key=1), and then the first three rows of data with key=1 are processed into three Map data and added to a list List as the value of the first key-value pair (value), thereby obtaining the first key-value pair. Similarly, the value 2 of the target field user_id is used as the key of the second key-value pair (key=2), and then the fourth row of data with key=2 is processed into a Map data and added to a list List as the value of the second key-value pair (value), thereby obtaining the second key-value pair. All the data in the appendix are processed in sequence using the same processing method, and finally the first key-value pair set is obtained.

[0072] Step S104: Based on the matching principle of the same value of the target field, each row of data in the main table is matched with the first key-value pair set.

[0073] In this embodiment, the matching principle based on the same value of the target field means matching the target fields with the same value in the main table and the supplementary table, that is, according to the value of each target field in the main table, one-to-one matching is performed with the key of each key-value pair in the first key-value pair set to obtain the key-value pair in the first key-value pair set that has the same value as the target field in the main table.

[0074] In an optional embodiment, matching each row of data in the main table with the first key-value pair set based on the same value of the target field as a matching principle includes:

[0075] Read the data in the main table and determine each value of the target field in the main table; compare each value of the target field in the main table with the key of each key-value pair in the first key-value pair set to see whether they are the same.

[0076] In this embodiment, each row of data in the main table is read in sequence, and the target field value corresponding to each row of data is determined, thereby obtaining each target field value. The value of each target field in the main table is then compared with the key of each key-value pair in the first key-value pair set. If the target field value in the main table matches the key of the key-value pair, a match is successful.

[0077] Optionally, before matching each row of data in the primary table with the first key-value pair set, each row of data in the primary table needs to be converted into a second key-value pair set. In this second key-value pair set, the key is the field name of the primary table row, and the value is the value corresponding to the field name of the primary table row. During matching, the key corresponding to the target field in the second key-value pair set is matched with the key of the key-value pair in the first key-value pair set.

[0078] For example, Figure 4 As shown, step 2 traverses the data in the main table and converts each row of data into a key-value pair set. The key of the key-value pair set is the field name, and the value is the field value. For example, the key-value pair set corresponding to the data item user_id = 1 is: [key = user_id, value = 1], [key = c1, value = content], [key = c2, value = content]. Step 3 matches key = 1 with the key of each key-value pair in the first key-value pair set. When the key values ​​are the same, it indicates a successful match. Then, the matching key-value pairs in the first key-value pair set and the second key-value pair set are assembled, that is, the matching data in the main table and the appendix are assembled.

[0079] Step S105: Assemble a row of data in the main table and a key-value pair that matches in the first key-value pair set into one or more new key-value pair sets, and put them into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is: each field name of the row where the matching main table and the appendix are located, and the value in a key-value pair is: the value corresponding to each field name of the row where the matching main table and the appendix are located.

[0080] In this embodiment, when a target field value in the main table matches a key-value pair in the first key-value pair set, the row of data corresponding to the target value in the main table is combined with one or more data items in the first key-value pair, thereby obtaining one or more new key-value pair sets. Specifically, when there is only one piece of data in the matching first key value, the matching data in the main table is combined with one piece of data in the first key-value pair to obtain a new key-value pair set. When there are N pieces of data in the first key-value pair, the matching data in the main table is combined with the N pieces of data in the first key-value pair to obtain N new key-value pair sets.

[0081] It should be noted that the new key-value pair set in this embodiment is a Map, which is used to store multiple key-value pairs. The data in each new key-value pair set corresponds to a piece of data after the main table and the appendix are associated. A data value refers to a row of data in the main table that matches a row of data in the appendix. Figure 4 As shown in the figure, taking a piece of data with key = 1 in the main table and a piece of data with key = 1 in the appendix table as an example, the data in the new key-value pair set is: [key = user_id, value = 1], [key = c1, value = content], [key = c2, value = content], [key = c3, value = content], [key = c4, value = content]. The placement method in the new key-value pair set is: map.put("user_id","1"); map.put("c1","content"); map.put("c2","content"); map.put("c3","content"); map.put("c4","content").

[0082] The final result set in this embodiment is a list list, which is used to store new key-value pair sets. The new key-value pair sets are stored in the final result set through List.add(Map). The final result set contains N new key-value pair sets, and each new key-value pair set contains M key-value pairs.

[0083] In an optional embodiment, assembling a row of data in the main table and a key-value pair that matches the first key-value pair set into one or more new key-value pair sets includes:

[0084] When a value of the target field in the main table and a key of a key-value pair in the first key-value pair set are both the first target value, assembling the values ​​of other fields in each row of data in the main table and the appendix table whose value of the target field is the target value, to obtain one or more key-value pair sets;

[0085] The key-value pair set has each field name in the main table and the appendix row where the value of the target field is the first target value as the key of the key-value pair, and each field name in the main table and the appendix row where the value of the target field is the first target value as the value of the key-value pair.

[0086] For example, Figure 4 As shown, in step ④, when the key of the main table is 1 (that is, the value of the target field is 1), it matches the key-value pair of the first key-value pair set key=1, wherein the value of the key-value pair of key=1 in the first key-value pair set is the list list, and the list list contains three data Maps. At this time, a row of data corresponding to key=1 in the main table is added and combined with the three data in the list list to generate three new Maps, which are then put into the final result set List. The main table data of user_id=2 and user_id=3 are processed, matched, and assembled in the same manner and put into the final result set List. Since the key-value pairs in the new key-value pair set are all derived from the association between the main table and the appendix, efficient query of multiple database tables is achieved without an index, which improves the performance of multi-table association query without an index.

[0087] In actual applications, traversing the main table data to match it with the appendix table and assembling the matching data into a new key-value pair set includes the following process:

[0088] Step 1: Convert a row of data in the main table into a second key-value pair set (Map) in the format of [key=field name, value=field value]. The second key-value pair set contains all the data in this row.

[0089] Step 2: Use the specific value of the association condition as the key to match in the appendix (the first key-value pair set). If the key values ​​are the same, it means the match is successful.

[0090] Step 3: The matched data in the main table is a Map, and the data in the appendix is ​​a List (the List contains one or more Map data). It is necessary to traverse the List, take out all the Maps in it, and add them to the Map generated by the main table data to generate a new key-value pair set Map. The new key-value pair set is placed in the final result set List.

[0091] In an optional embodiment, when the value of the target field in the main table does not match any key-value pair in the first set, processing needs to be performed according to the type of the association condition, specifically:

[0092] When a value of the target field in the main table is a second target value, which is different from the key of each key-value pair in the first key-value pair set, it is determined whether the association condition is the first database table inner-joining the second database table.

[0093] When the association condition is inner-joining the first database table to the second database table, the values ​​of other fields in one or more rows of data in the main table containing the target field having the second target value are discarded.

[0094] When the association condition is not inner-joining the first database table to the second database table, each field name in the row where the target field in the main table has the second target value is used as key, and each field name in the row where the target field in the main table has the second target value is used as value. These are put into a new key-value pair set one by one, and each new key-value pair set is put into the final result set.

[0095] In this embodiment, when the association condition is inner-joining the first database table to the second database table, only data with identical target fields in the first and second database tables (the main and supplementary tables) are returned. Therefore, when the value of the target field in the main table does not match any key-value pair in the first set, the data corresponding to the target field value in the main table is directly discarded.

[0096] If the join condition is not inner-joining the first database table to the second database table, that is, the join condition is left-joining the first database table to the second database table or right-joining the first database table to the second database table, these two join conditions indicate that all data in the main table and data in the appendix table with the same value as the target field in the main table will be returned. Therefore, if the value of the target field in the main table does not match any key-value pairs in the first set, the key-value pair set generated from the main table data is directly added to the final result set List.

[0097] In this embodiment, in order to solve the problems of high time complexity and extremely low efficiency in efficient multi-table association queries when indexes cannot be used in relational databases, a first database table and a second database table are first obtained. Then, based on the association condition, one of the first database table and the second database table is determined as the main table and the other as the supplementary table. The data in the supplementary table is converted into a first key-value pair set. Based on the matching principle of the same value of the target field, the data in the main table is matched with the first key-value pair set. Two or more matching rows of data in the main table and the supplementary table are assembled into a new key-value pair set. Since the key-value pairs in the new key-value pair set are all derived from the association between the main table and the supplementary table, efficient multi-table association queries are achieved without indexes, thereby improving the performance of multi-table association queries without indexes.

[0098] like Figure 5 As shown, Figure 5 This is a schematic diagram of the implementation process of a database table processing method provided in this embodiment. In actual applications, when performing a correlation query on two index-free database tables, the main table and the appendix table are first determined based on the correlation conditions. Then the appendix table is processed: the appendix table data is read, and one or more rows of data in the appendix table are converted into key-value pairs to obtain a key-value pair set recordsMap. Specifically, a Map is created. <String,List<Map<String,String> >> A collection, named recordsMap, is used to store the data in the appendix. The appendix data is then read one by one, parsing the key (target field) value. All keys (target fields) and the corresponding row of data for the target field are assembled into data1. A check is performed to determine whether a key-value pair with the same key exists in the first key-value pair collection, recordsMap. If not, a collection, baseDataList1, is created to store multiple data items. Data1 is placed in baseDataList1, and the baseDataList1 collection is then placed into recordsMap as a key-value pair. If so, the existing baseDataList1 in the first key-value pair collection, recordsMap, is removed, the new data is added, and the key-value pair, baseDataList1, with the new data, is placed back into the first key-value pair collection.

[0099] After obtaining the first key-value pair set, recordsMap, the main table is processed. The main table data is read one by one, the key (target field) is parsed, and the key value of the target field in each row of data is determined based on the join condition. All field names and the corresponding data of the field names in the row are assembled to obtain data2 (a key-value pair set). Then, based on the key value in the main table, a check is performed to determine whether the same key value exists in the first key-value pair set, recordsMap. In other words, the value of each target field in the main table is matched with each key in the first key-value pair.

[0100] When there is a key-value pair baseDataList1 in the recordsMap of the first key-value pair set that matches the main table, take out the key-value pair baseDataList1 from the first key-value pair set recordsMap, then loop through the key-value pair baseDataList1 to take out each data data1, assemble data2 with each data1 into a new key-value pair set, and put the new key-value pair set into the final result set resultList. When there is no key-value pair baseDataList1 in the recordsMap of the first key-value pair set that matches the key value of the main table, determine whether the association method is inner join. If the association method is inner join, directly discard the data2 corresponding to the key value and do not put it into resultList; if the association method is not inner join, directly put data2 as a new key-value pair set into the final combined set resultList.

[0101] For example, for Figure 2 and Figure 3 The database table shown uses Java ListMap to implement select t1.*,t2.c5,t2.c6,t2.c7,t2.c8 from user_info t1 LEFT JOIN user_department t2 on t1.user_id=t2.user_id, completing the complete process of the associated query:

[0102] 1. According to the left join, confirm that user_info is the main table ( Figure 2 ), user_department is a schedule ( Figure 3 )

[0103] 2. Read the attached table data and create a new Map <String,List<Map<String,String> >>A collection named recordsMap is used to store the schedule data, specifically:

[0104] 1) Read each row of data, for example, the first row of data, according to the association condition t1.user_id = t2.user_id, it is necessary to set the value 1 of user_id (target field) as the key of recordsMap, and create a collection Map<String,Object> Name it data1 and take out the value of each column of this row of data and put it into the set data1, data1.put("user_id",1); data1.put("c5","bed number_1"); data1.put("c6","work number_1"); data1.put("c7","feature_1"); data1.put("c8","description_1"), where data1 is a key-value pair.

[0105] 2) Determine whether there is already an element with the same target field value in recordMap. Specifically, use recordMap.get(1) to determine whether there is a corresponding element in recordMap. If the key of the data in this loop does not exist in recordsMap, create a collection List <Map<String,String> , named baseDataList1, put the data data1 into baseDataList1 through baseDataList1.add(data1), and then put baseDataList1 into recordsMap through recordsMap.put(1,baseDataList1).

[0106] 3) In the next loop, the value of user_id in the second row is still 1, and a set Map is created.<String,Object> Name it data1 and take out the value of each column of this row of data and put it into the collection data1, data1.put("user_id",1); data1.put("c5","Bed number_3"); data1.put("c6","Work number_3"); data1.put("c7","Feature_3"); data1.put("c8","Description_3"). Use recordMap.get(1) to determine whether there is a corresponding element in recordMap. Because the previous data has already placed an element with key=1 in recordMap, the key of this data exists in recordMap in this loop. Use recordsMap.get(1) to take out baseDataList1, use baseDataList1.add(data1) to put this data into baseDataList1, and then use recordsMap.put(1,baseDataList1) to put baseDataList1 back. The same method is repeated until all the appendix data are processed and the final recordsMap is generated (that is, the first key-value pair set is obtained).

[0107] 3. Create a List<Map<String, String>> collection named resultList to store the final result. Traverse the data in the main table, read each row of data. For example, for the first row of data, according to the association condition t1.user_id = t2.user_id, it is also necessary to use the user_id value 1 as the key. Create a collection Map<String, Object> named data2 and put the values of each column of this row of data into the collection data2. data2.put("user_id", 1); data2.put("c1", "Zhang_1"); data2.put("c2", "ID_No_1"); data2.put("c3", "Address_1"); data2.put("c4", "Code_1"); Determine whether there is a corresponding element in the supplementary table collection recordsMap through recordsMap.get(1). In this loop, the user_id of this row of data is 1 and it exists in recordsMap. Retrieve baseDataList1 through recordsMap.get(1), loop through baseDataList1, retrieve data1 from baseDataList1 and generate a new collection newMap with data2 through data2.putAll(data1), and put newMap into resultList through resultList.add(newMap). Keep repeating according to the same processing method until all the data in the main table is processed to generate the final resultList (i.e., the new key-value pair collection).

[0108] It should be noted that the algorithm main body of the method provided in this embodiment is based on the list-map built in Java, does not need to rely on other packages or install other component services, and the readability of the program of this method is very strong and will not increase the learning cost of users. Although the method of this embodiment is mainly used for relational databases, by modifying it, it can be used to process data from different sources or non-relational databases, such as processing csv data and redis data.

[0109] In this embodiment, starting from the query time complexity, the query performance is improved by reducing the time complexity. Cleverly design the collection structure Map<String, List<Map<String, String>>> to convert one of the tables into a map, and design the collection structure List<Map<String, String>> to convert the final result into a list. Implement and fully fit the left-join, right-join, and inner-join in the mysql database.

[0110] The embodiment of the present invention also provides a database table processing device, such as Figure 6 As shown, Figure 6 A schematic diagram of the structure of a database table processing device provided in an embodiment of the present invention, the device comprising:

[0111] An acquisition module 61 is configured to obtain a first database table and a second database table, wherein both the first database table and the second database table have a target field;

[0112] A determination module 62, configured to determine, based on an association condition, one of the first database table and the second database table as a main table and the other as a supplementary table;

[0113] a conversion module 63 configured to convert the data in the appendix into a first key-value pair set, wherein one key-value pair in the first key-value pair set corresponds to one row of data or multiple rows of data in the appendix, a key in a key-value pair is a value of the target field in the appendix, and a value in a key-value pair is the values ​​of other fields in one row of data or multiple rows of data containing the value of the target field in the appendix;

[0114] A matching module 64 is configured to match each row of data in the main table with the first key-value pair set based on the same value of the target field as a matching principle;

[0115] The assembly module 65 assembles a row of data in the main table with a key-value pair that matches in the first key-value pair set into one or more new key-value pair sets, and puts the new key-value pair sets into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is: each field name of the row where the matching main table and the appendix are located, and the value in a key-value pair is: the value corresponding to each field name of the row where the matching main table and the appendix are located.

[0116] In an optional embodiment, the determining module includes:

[0117] A first determining submodule is configured to, when the association condition is that the first database table left-joins the second database table, determine the first database table as a primary table and determine the second database table as a supplementary table;

[0118] a second determining submodule, configured to, when the association condition is that the first database table right-joins the second database table, determine the first database table as an appendix table and determine the second database table as a main table;

[0119] The third determining submodule is configured to, when the association condition is that the first database table inner-joins the second database table, determine the first database table as the main table and the second database table as the supplementary table.

[0120] In an optional embodiment, the conversion module includes:

[0121] A first conversion submodule is configured to read the data in the appendix and determine each value of the target field in the appendix;

[0122] A second conversion submodule is configured to add, for each value of the target field in the appendix, values ​​of other fields in a row of data or multiple rows of data where the value is located into a list;

[0123] The third conversion submodule is configured to use each value of the target field in the appendix as a key and a list corresponding to the value as a value to obtain a key-value pair in the first key-value pair set.

[0124] In an optional embodiment, the matching module includes:

[0125] A first matching submodule is configured to read the data in the main table and determine each value of the target field in the main table;

[0126] The second matching submodule is used to compare each value of the target field in the main table with the key of each key-value pair in the first key-value pair set to see whether they are the same.

[0127] In an optional embodiment, the assembly module includes:

[0128] A first assembling submodule is configured to assemble, when a value of the target field in the main table and a key of a key-value pair in the first key-value pair set are both the first target value, the values ​​of other fields in each row of data in the main table and the appendix whose value of the target field is the target value, respectively, to obtain one or more key-value pair sets;

[0129] The second assembly submodule, the key-value pair set is a key of the key-value pair with each field name of the main table and the appendix in the row where the value of the target field is the first target value, and the value of the key-value pair is a value of the main table and the appendix in the row where the value of the target field is the first target value.

[0130] In an optional embodiment, the device further includes:

[0131] A condition judgment module, configured to determine whether the association condition is to inner-join the first database table to the second database table when a value of the target field in the main table is a second target value that is different from the key of each key-value pair in the first key-value pair set;

[0132] The discarding module is used to discard the values ​​of other fields in one or more rows of data in the main table where the target field with the second target value is located when the association condition is that the first database table inner-joins the second database table.

[0133] In an optional embodiment, the device further includes:

[0134] A condition judgment module, configured to determine whether the association condition is to inner-join the first database table to the second database table when a value of the target field in the main table is a second target value that is different from the key of each key-value pair in the first key-value pair set;

[0135] An adding module is used for, when the association condition is not the inner-join of the first database table to the second database table, taking each field name of the row where the target field in the main table is located and whose value is the second target value as key, and taking each field name of the row where the target field in the main table is located and whose value is the second target value as value, putting them into a new key-value pair set one by one, and putting each of the new key-value pair sets into the final result set.

[0136] An embodiment of the present invention further provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the database table processing method described in the embodiment of the present invention when executing the computer program.

[0137] An embodiment of the present invention further provides a computer-readable storage medium having a computer program / instruction stored thereon. When the computer program / instruction is executed by a processor, the database table processing method described in the embodiment of the present invention is implemented.

[0138] The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referenced to each other.

[0139] The embodiments of the present invention are described with reference to the flowcharts and / or block diagrams of the methods, apparatuses, electronic devices, and computer program products according to the embodiments of the present invention. It should be understood that each process and / or block in the flowcharts and / or block diagrams, as well as the combination of processes and / or blocks in the flowcharts and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing terminal device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing terminal device generate instructions for implementing the processes in the flowcharts and / or block diagrams. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0140] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing terminal device to operate in a specific manner, so that the instructions stored in the computer readable memory produce a manufactured product including an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0141] These computer program instructions can also be loaded onto a computer or other programmable data processing terminal device so that a series of operating steps are executed on the computer or other programmable terminal device to produce a computer-implemented process, thereby providing instructions for executing on the computer or other programmable terminal device to implement the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0142] Although the preferred embodiments of the present invention have been described, those skilled in the art may make additional changes and modifications to these embodiments once they become aware of the basic creative concepts. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the embodiments of the present invention.

[0143] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or terminal device that includes a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or terminal device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or terminal device that includes the element.

[0144] The above is a detailed introduction to the database table processing method, device, equipment and medium provided by the present invention. Specific examples are used herein to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method of the present invention and its core ideas. At the same time, for those skilled in the art, according to the ideas of the present invention, there may be changes in the specific implementation methods and application scopes. In summary, the contents of this specification should not be understood as limiting the present invention.

Claims

1. A database table processing method, characterized in that: The method comprises: Obtain a first database table and a second database table, wherein the first database table and the second database table both have a target field; According to the association condition, one of the first database table and the second database table is determined as a main table, and the other is determined as a supplementary table; Convert the data in the appendix into a first key-value pair set, wherein one key-value pair in the first key-value pair set corresponds to one row of data or multiple rows of data in the appendix, the key in one key-value pair is: a value of the target field in the appendix, and the value in one key-value pair is: the values ​​of other fields in one row of data or multiple rows of data where the value of the target field in the appendix is ​​located; Based on the matching principle of the same value of the target field, each row of data in the main table is matched with the first key-value pair set; Assemble one or more new key-value pair sets by combining a row of data in the main table with a key-value pair that matches in the first key-value pair set, and put them into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is each field name in the row of the matched main table and the appendix, and the value in a key-value pair is each value corresponding to each field name in the row of the matched main table and the appendix.

2. The method according to claim 1, characterized in that According to the association condition, one of the first database table and the second database table is determined as a main table, and the other is determined as a supplementary table, including: When the association condition is that the first database table left-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table; When the association condition is that the first database table right-joins the second database table, the first database table is determined as an attached table, and the second database table is determined as a main table; When the association condition is that the first database table inner-joins the second database table, the first database table is determined as the main table, and the second database table is determined as the supplementary table.

3. The method according to claim 1, characterized in that Convert the data in the appendix into a first key-value pair set, including: Reading the data in the appendix and determining each value of the target field in the appendix; For each value of the target field in the appendix, add the values ​​of other fields in the row of data or multiple rows of data where the value is located into a list; Taking each value of the target field in the appendix as a key and a list corresponding to the value as a value, a key-value pair in the first key-value pair set is obtained.

4. The method according to claim 1, wherein Based on the matching principle of the same value of the target field, each row of data in the main table is matched with the first key-value pair set, including: Read the data in the main table and determine each value of the target field in the main table; Compare each value of the target field in the main table with the key of each key-value pair in the first key-value pair set to see whether they are the same.

5. The method according to claim 4, characterized in that Assembling a row of data in the main table with a key-value pair that matches the first key-value pair set into one or more new key-value pair sets, including: When a value of the target field in the main table and a key of a key-value pair in the first key-value pair set are both the first target value, assembling the values ​​of other fields in each row of data in the main table and the appendix table whose value of the target field is the target value, to obtain one or more key-value pair sets; The key-value pair set has each field name in the main table and the appendix row where the value of the target field is the first target value as the key of the key-value pair, and each field name in the main table and the appendix row where the value of the target field is the first target value as the value of the key-value pair.

6. The method according to claim 4, characterized in that The method further comprises: If a value of the target field in the primary table is a second target value, which is different from the key of each key-value pair in the first key-value pair set, determining whether the association condition is an inner-join of the first database table to the second database table; When the association condition is inner-joining the first database table to the second database table, the values ​​of other fields in one or more rows of data in the main table containing the target field having the second target value are discarded.

7. The method according to claim 4, characterized in that The method further comprises: If a value of the target field in the primary table is a second target value, which is different from the key of each key-value pair in the first key-value pair set, determining whether the association condition is an inner-join of the first database table to the second database table; When the association condition is not inner-joining the first database table to the second database table, each field name in the row where the target field in the main table has the second target value is used as key, and each field name in the row where the target field in the main table has the second target value is used as value. These are put into a new key-value pair set one by one, and each new key-value pair set is put into the final result set.

8. A database table processing device, characterized in that: The device comprises: An acquisition module, configured to obtain a first database table and a second database table, wherein both the first database table and the second database table have a target field; a determination module, configured to determine, based on an association condition, one of the first database table and the second database table as a main table and the other as a supplementary table; a conversion module, configured to convert the data in the appendix into a first key-value pair set, wherein a key-value pair in the first key-value pair set corresponds to a row of data or multiple rows of data in the appendix, a key in a key-value pair is: a value of the target field in the appendix, and a value in a key-value pair is: values ​​of other fields in a row of data or multiple rows of data where a value of the target field in the appendix is ​​located; a matching module, configured to match each row of data in the main table with the first set of key-value pairs based on the same value of the target field as a matching principle; An assembly module is used to assemble a row of data in the main table and a key-value pair that matches in the first key-value pair set into one or more new key-value pair sets, and put them into the final result set. Each new key-value pair set corresponds to a row of data that matches in the main table and the appendix. The key in a key-value pair is: each field name of the row where the matching main table and the appendix are located, and the value in a key-value pair is: the value corresponding to each field name of the row where the matching main table and the appendix are located.

9. An electronic device, characterized in that: The method comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the method for processing a database table according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium having a computer program / instruction stored thereon, characterized in that: When the computer program / instruction is executed by a processor, the database table processing method according to any one of claims 1 to 7 is implemented.

Images