A data updating method, device, storage medium and equipment

Abstract

The specification discloses a data updating method, device, storage medium and equipment. The method comprises the following steps: obtaining an update statement to be executed, determining each subquery contained in the update statement, splitting the subquery into an operation clause and a source clause for each subquery, determining a query group composed of subqueries containing the same source clause, fusing the operation clauses of each subquery in the query group for each query group to obtain a fused operation clause, determining a merged subquery corresponding to the query group according to the fused operation clause and the source clause corresponding to the query group, and finally rewriting the update statement according to the determined merged subquery to execute the rewritten update statement to update data. The method can identify subqueries with the same source clause in the update statement, merge the subqueries to obtain a merged subquery to rewrite the update statement, reduce the number of subqueries executed when the update statement is executed, and improve the updating efficiency on the premise of obtaining the same updating result.

Description

Technical Field

[0001] This specification relates to the field of database technology, and in particular to a data update method, apparatus, storage medium and device. Background Technology

[0002] In databases, a single statement often involves operations on multiple fields. For example, an update statement may involve updating multiple fields in the target table.

[0003] Currently, databases suffer from low statement execution efficiency, leading to inefficient data updates. To improve data update efficiency in databases, this manual provides a data update method. Summary of the Invention

[0004] This specification provides a data updating method, apparatus, storage medium, and device to at least partially solve the problems existing in the prior art.

[0005] The following technical solution is adopted in this specification:

[0006] This specification provides a data update method, including:

[0007] Obtain the update statement to be executed, and determine the subqueries contained in the update statement;

[0008] For each subquery, break it down into an operation clause and a source clause;

[0009] Based on the source clauses of each subquery, determine the query group consisting of subqueries that contain the same source clause;

[0010] For each query group, the operation clauses of each subquery in the query group are merged to obtain a merge operation clause. Based on the merge operation clause and the source clause corresponding to the query group, the merged subquery corresponding to the query group is determined.

[0011] Rewrite the update statement based on the determined merged subquery;

[0012] Execute the rewritten update statement to update the data.

[0013] This specification provides a data update device, including:

[0014] The acquisition module is used to acquire the update statement to be executed and determine the subqueries contained in the update statement;

[0015] The splitting module is used to break down each subquery into an operation clause and a source clause;

[0016] The grouping module is used to determine the query group composed of subqueries that contain the same source clause, based on the source clause of each subquery.

[0017] The fusion module is used to fuse the operation clauses of each subquery in each query group to obtain a fusion operation clause, and determine the merged subquery corresponding to the query group based on the fusion operation clause and the source clause corresponding to the query group.

[0018] The rewrite module is used to rewrite the update statement based on the determined merged subquery;

[0019] The update module is used to execute rewritten update statements to update the data.

[0020] This specification provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described data update method.

[0021] This specification provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described data update method.

[0022] The above-mentioned technical solutions adopted in this specification can achieve the following beneficial effects:

[0023] In the above data update method, by obtaining the update statement to be executed, the subqueries contained in the update statement are determined. For each subquery, the subquery is split into operation clauses and source clauses. Based on the source clauses of each subquery, query groups consisting of subqueries containing the same source clause are determined. For each query group, the operation clauses of each subquery in the query group are merged to obtain a merge operation clause. Based on the merge operation clause and the source clause corresponding to the query group, the merged subquery corresponding to the query group is determined. Finally, based on the determined merged subquery, the update statement can be rewritten and executed to update the data.

[0024] As can be seen from the above, the data update method provided in this specification can identify subqueries with the same source clause in the update statement and merge them to obtain a merged subquery, thereby rewriting the update statement, reducing the number of subqueries executed when executing the update statement, and improving update efficiency while obtaining the same update result. Attached Figure Description

[0025] The accompanying drawings, which are included to provide a further understanding of this specification and form part of this specification, illustrate exemplary embodiments and are used to explain this specification, but do not constitute an undue limitation thereof. In the drawings:

[0026] Figure 1 This is a flowchart illustrating one data update method described in this specification.

[0027] Figure 2 This is a comparative diagram of an update statement provided in this specification;

[0028] Figure 3 This is a schematic diagram of a data update device provided in this specification;

[0029] Figure 4 This is a schematic diagram of an electronic device provided in this specification. Detailed Implementation

[0030] When updating data in a database, updates are typically performed using update statements. Currently, updating a database using update statements, especially when multiple fields are being updated, is time-consuming and inefficient.

[0031] To address the aforementioned issues, this specification provides a data update method.

[0032] Based on the data update method provided in this manual, update statements can be rewritten and their structure optimized to reduce the execution time of update statements, thereby improving update efficiency.

[0033] In one or more embodiments of this specification, the format of the update statement is: update target table set assignment statement;

[0034] The target table is the data table in the database that is being updated. `update` and `set` are fixed keywords in update statements, while the target table and assignment statements are customizable parts. The assignment statement is a block of statements that can be flexibly configured to assign values ​​to the fields to be updated in the target table.

[0035] For example, suppose the update statement is: update t1 set c1=5, c2=1, c3=(select avg(c1)fromt2), c4=(select sum(c2)from t2), c5=(select c2+c1 from t3 limit 1);

[0036] Where t1 represents the target table, and the part after set: c1=5, c2=1, c3=(select avg(c1)fromt2), c4=(select sum(c2)from t2), c5=(select c2+c1 from t3 limit 1) are assignment statements, and the semicolon indicates the end of an update statement.

[0037] c1, c2, c3, c4, and c5 are five fields in the target table that need to be updated. c1 = 5, c2 = 1, c3 = (select avg(c1) from t2), c4 = (select sum(c2) from t2), and c5 = (select c2 + c1 from t3 limit 1) are five assignment expressions, which are also assignment clauses in an assignment statement.

[0038] c1=5 and c2=1 are regular assignment expressions that do not contain subqueries.

[0039] In one or more embodiments of this specification, a subquery may be split into an operation clause and a source clause.

[0040] Taking Structured Query Language (SQL) as an example, subqueries can be in the form of select...from..., where select... is the operation clause (also called the select clause) and from... is the source clause (also called the from clause).

[0041] The ellipsis in the SELECT clause can be a field from the source table (the field containing the data retrieved from the source table), or a calculation function such as the AVG() function for calculating the mean or the SUM() function for calculating the sum. Therefore, the SELECT clause can be used to select fields and obtain the values ​​under those fields as the query results, or to perform calculations and obtain the calculated results as the query results.

[0042] The ellipsis in the FROM clause can contain at least the name of the source table, or a subquery, etc., to determine the source of the queried data.

[0043] It should be noted that in this manual, the form of subqueries is not limited to the select...from... format. As long as it can determine the value of a field based on the data source to obtain the query result, or perform calculations based on the data source to obtain the query result, it is acceptable.

[0044] In this context, the source table is the table from which the updated data originates. For example, when updating data table 1 based on data table 2, data table 1 is the target table, and data table 2 is the source table.

[0045] In the example update statement above, there are three assignment expressions containing subqueries: c3 = (select avg(c1) from t2), c4 = (select sum(c2) from t2), and c5 = (select c2 + c1 from t3 limit1). The subqueries in these three expressions are, respectively, `select avg(c1) from t2`, `select sum(c2) from t2`, and `select c2 + c1 from t3 limit 1`. `t2` and `t3` are the source tables, and `c1` and `c2` in the subqueries represent fields in the source tables. The three subqueries respectively represent: retrieving records from `t2` and calculating the mean of field `c1`; retrieving records from `t2` and calculating the sum of field `c2`; and retrieving one record from `t3` and calculating the sum of field `c2` and field `c1`.

[0046] Currently, when executing the UPDATE statement in the example above, the database needs to retrieve the update values ​​corresponding to the fields c1, c2, c3, c4, and c5 to be updated, i.e., the values ​​on the right side of the assignment operator "=" or the query results of subqueries. For assignment expressions containing subqueries, three subqueries need to be executed separately to obtain the query results of each subquery, even if some subqueries are queries on the same source table. This makes the execution of update statements inefficient, resulting in low database update efficiency.

[0047] In the data update method provided in this specification, in order to improve the efficiency of executing update statements, subqueries containing the same source clause are merged based on the similarity of subqueries and executed as a single subquery. In this way, the same result can be obtained by executing a single subquery as by executing multiple subqueries separately, but in a shorter time and with higher efficiency, thereby improving the update efficiency of the database.

[0048] In some scenarios, an update statement may involve a large number of subqueries, and the probability and number of similar subqueries (i.e., subqueries containing clauses from the same source) are higher. The data update methods provided in this manual can improve the optimization effect of this type of update statement and significantly improve execution efficiency.

[0049] To make the objectives, technical solutions, and advantages of this specification clearer, the technical solutions of this specification will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this specification, and not all of them. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this specification.

[0050] The technical solutions provided in the various embodiments of this specification are described in detail below with reference to the accompanying drawings.

[0051] Figure 1 This is a flowchart illustrating a data update method described in this specification, which specifically includes the following steps:

[0052] S100: Obtain the update statement to be executed and determine the subqueries contained in the update statement.

[0053] This data update method can be executed by the database.

[0054] First, the database obtains the update statement to be executed. To rewrite and optimize the update statement, the database can also identify the subqueries contained within it.

[0055] Since subqueries can exist within assignment expressions contained in update statements, when determining the subqueries contained in an update statement, the database can specifically identify the assignment statements within the update statement and then determine the assignment expressions from those statements. Subsequently, the subqueries can be extracted from the assignment expressions containing them.

[0056] Of course, since the update statement may contain assignment expressions that do not contain subqueries, the database can first identify the assignment expressions containing subqueries from the assignment statements after determining each assignment expression, and then split them to obtain the subqueries.

[0057] S102: For each subquery, split the subquery into an operation clause and a source clause.

[0058] As mentioned above, a subquery can be broken down into an operation clause and a source clause. To optimize an update statement, subqueries that contain the same source clause can be identified first, allowing for the merging of these subqueries to obtain a new subquery.

[0059] Therefore, after identifying the subqueries in the update statement, the first step is to break down each subquery into an operation clause and a source clause.

[0060] S104: Based on the source clauses of each subquery, determine the query group consisting of subqueries that contain the same source clause.

[0061] Since subqueries with the same source clause retrieve data from the same source—that is, the data used to update the target table comes from the same source—they are easy to merge for execution. Therefore, subqueries can be grouped based on the source clauses they contain, resulting in multiple query groups. This ensures that all subqueries within a query group are similar subqueries containing the same source clause.

[0062] Therefore, in one or more embodiments of this specification, the database can determine a query group consisting of subqueries containing the same source clause based on the source clause of each subquery.

[0063] S106: For each query group, merge the operation clauses of each subquery in the query group to obtain a merge operation clause. Based on the merge operation clause and the source clause corresponding to the query group, determine the merged subquery corresponding to the query group.

[0064] Once the query groups are determined, the database can merge and optimize the subqueries in each query group, combining multiple subqueries in a query group into one subquery. This allows the database to execute the merged subquery and obtain the query results corresponding to the original multiple subqueries by executing the merged subquery, thus improving update efficiency.

[0065] Since the source clauses are the same in a query group, while the operation clauses are diverse and complex and usually different, when the operation clauses of each subquery in a query group are different, the operation clauses can be merged to obtain new operation clauses.

[0066] Therefore, after identifying each query group, the database can merge the operation clauses of each subquery within that query group to obtain a merge operation clause. Then, based on this merge operation clause and the corresponding source clause for that query group, the merged subquery for that query group can be determined. This merged subquery is a new subquery obtained by merging multiple subqueries.

[0067] It should be noted that this query group contains multiple identical source clauses. One source clause can be selected from these as the source clause corresponding to this query group, which is then used to obtain a merged subquery with the merge operation clause.

[0068] Furthermore, during the merging process, it's crucial to ensure the correct format of the merged subqueries. Specifically, the resulting merged subqueries must follow the order of the source clauses in the merging operation, rather than the order of the source clauses in the merging operation. Of course, at every stage of obtaining the rewritten update statement, the syntax and format of the update statement must be guaranteed to be correct.

[0069] It should be noted that steps S100 to S106 can be specifically executed by the optimizer in the database.

[0070] S108: Rewrite the update statement based on the determined merge subquery.

[0071] After determining the merge subqueries corresponding to each query group, the database can rewrite the update statement based on each merge subquery to obtain the optimized update statement.

[0072] S110: Execute the rewritten update statement to update the data.

[0073] After the update statement is rewritten, the database can execute the rewritten update statement.

[0074] It should be noted that by rewriting and optimizing the update statement, only the form and structure of the non-fixed parts of the update statement are changed. The optimization is for the efficiency of executing the update statement. Executing the rewritten update statement yields the same update result as executing the original update statement; the result remains unchanged. In other words, the data update method provided in this specification optimizes both statement execution efficiency and update efficiency.

[0075] based on Figure 1 The data update method shown involves obtaining the update statement to be executed, identifying the subqueries contained in the update statement, and for each subquery, splitting it into operation clauses and source clauses. Based on the source clauses of each subquery, query groups consisting of subqueries containing the same source clause are identified. For each query group, the operation clauses of each subquery in the query group are merged to obtain a merge operation clause. This allows the merging subquery corresponding to the query group to be determined based on the merging operation clause and the source clause corresponding to the query group. Finally, based on the determined merging subquery, the update statement can be rewritten and executed to update the data.

[0076] As can be seen from the above method, this method can identify subqueries in update statements that contain clauses from the same source, and merge them to obtain merged subqueries, thereby rewriting the update statement, reducing the number of subqueries executed when executing the update statement, and improving update efficiency while obtaining the same update result.

[0077] In addition, in step S104, when determining the query group consisting of subqueries containing the same source clause based on the source clause of each subquery, specifically, the database can take any subquery as the target subquery, traverse other ungrouped subqueries, and determine the query group based on other subqueries that are consistent with the source clause of the target subquery.

[0078] Then, the database can determine whether there are any ungrouped subqueries that are not the target subquery.

[0079] If so, the target subquery can be determined from the ungrouped subqueries that are not used as the target subquery, and the process can continue to traverse other ungrouped subqueries to determine new query groups until there are no more subqueries that are not used as the target subquery.

[0080] If not, then the query groups that received the update statement can be determined.

[0081] When determining the target subquery, it can be determined randomly, or it can be determined according to the order in which it appears in the update statement.

[0082] In one or more embodiments of this specification, in order to avoid a query group containing too many subqueries, which would require merging a large number of subqueries into a single merged subquery, the database can also control the number of subqueries contained in a query group.

[0083] Therefore, the database can identify subqueries containing the same source clause as a group of undetermined subqueries, and when the number of undetermined subqueries in the group exceeds a preset threshold, it can determine a query group based on some of the subqueries in the group of undetermined subqueries, and determine another query group based on another part of the subqueries.

[0084] That is, several subqueries containing the same source clause can be divided into two query groups. For example, when the number of subqueries containing the same source clause is even, they can be divided into two query groups. When the number of subqueries containing the same source clause is odd, the number of subqueries contained in the two query groups can be 1 different.

[0085] In one or more embodiments of this specification, an operation clause may consist of an operation identifier and an operation field. For example, in the operation clause `select c1+10` in the subquery `(select c1+10 from t3 limit 1)`, `select` is the operation identifier, and the remaining part, `c1+10`, is the operation field. Since this operation field may contain only fields, or it may contain calculations such as functions, it may also be an operation string.

[0086] The source clause consists of a source identifier and a source string. For example, in the subquery (select c1+10fromt3 limit1), the source clause from t3 limit 1 is the source identifier, and the part other than the source identifier is the source string: t3 limit 1.

[0087] In one or more embodiments of this specification, in step S106, when the operation clauses of each subquery in the query group are merged to obtain the merged operation clause, specifically, the database can concatenate the operation fields of each subquery in the query group to obtain the merged operation field, and determine the merged operation clause corresponding to the query group based on the operation identifier and the merged operation field.

[0088] Then, based on the merge operation clause and the source clause corresponding to the query group, the merge subquery corresponding to the query group can be determined.

[0089] Furthermore, in one or more embodiments of this specification, an assignment expression containing a subquery may consist of a field to be updated, an assignment operator, and a subquery. The assignment operator is "=".

[0090] In step S108, when rewriting the update statement based on the determined merged subqueries, specifically, the database can determine the fields to be updated in each assignment expression corresponding to each query group, and determine the vector of the fields to be updated corresponding to the query group based on the determined fields to be updated.

[0091] Then, based on the vector of the field to be updated, the assignment operator, and the merge subquery corresponding to the query group, the optimized assignment expression corresponding to the query group can be determined, and the update statement can be rewritten based on the optimized assignment expression corresponding to each query group.

[0092] In one or more embodiments of this specification, when rewriting the update statement based on the optimized assignment expressions corresponding to each query group, specifically, the database can determine the original assignment expression corresponding to that query group in the update statement for each query group. Then, the original assignment expressions corresponding to each query group in the update statement can be deleted and replaced with the optimized assignment expressions corresponding to each query group, resulting in the rewritten update statement.

[0093] Taking the update statement as an example: update t1 set c1=(select c1+10from t3 limit 1), c2=1, c3=(select avg(c1)from t2), c4=(select sum(c2)from t2), c5=(select c2+c1 from t3 limit 1); the process of re-optimizing the update statement in this database will be explained.

[0094] After obtaining the update statement, the database can determine the assignment statements within it: c1 = (select c1+10 from t3 limit 1), c2 = 1, c3 = (select avg(c1) from t2), c4 = (select sum(c2) from t2), and c5 = (select c2+c1 from t3 limit 1). Then, five assignment expressions can be determined from these statements: c1 = (select c1+10 from t3 limit 1), c2 = 1, c3 = (select avg(c1) from t2), c4 = (select sum(c2) from t2), and c5 = (select c2+c1 from t3 limit 1). Finally, the assignment expressions containing subqueries can be determined, i.e., all assignment expressions except for c2 = 1.

[0095] Then, the database can extract subqueries from assignment expressions containing subqueries. For example, the assignment expression c1 = (select c1+10 from t3 limit 1) can be extracted into the subquery (select c1+10 from t3 limit 1), the assignment expression c3 = (select avg(c1) from t2) can be extracted into the subquery (select avg(c1) from t2), the assignment expression c4 = (select sum(c2) from t2) can be extracted into the subquery (select sum(c2) from t2), and the assignment expression c5 = (select c2+c1 from t3 limit 1) can be extracted into the subquery (select c2+c1 from t3 limit 1).

[0096] Next, the similarity relationships between subqueries can be determined, and subqueries containing the same source clause can be grouped into a query group. Since (select c1+10from t3 limit 1) and (select c2+c1 from t3 limit 1) have the same source clause, both being from t3 limit 1, and (select avg(c1)from t2) and (select sum(c2)from t2) have the same source clause, both being from t2, (select c1+10from t3 limit 1) and (select c2+c1 from t3 limit 1) are similar subqueries and form a query group (hereinafter referred to as the first query group), and (select avg(c1)from t2) and (select sum(c2)from t2) are similar subqueries and form a query group (hereinafter referred to as the second query group).

[0097] For the first query group, when merging the operation clauses of the subqueries in this query group, we can obtain the merge operation clause `select c1+10,c2+c1`, where `c1+10` and `c2+c1` are the merge operation fields corresponding to this query group. Based on the merge operation clause and the source clause `from t3 limit 1` of this query group, we can obtain the merged subquery of this query group (`select c1+10,c2+c1 from t3 limit 1`), which is a new subquery.

[0098] When merging the subquery operation clauses in the second query group, the merge operation clauses `select avg(c1),sum(c2)` and `avg(c1),sum(c2)` are obtained, which are the merge operation fields corresponding to this query group. Based on this merge operation clause and the source clause `from t2` of this query group, the merged subquery corresponding to this query group can be obtained (`select avg(c1),sum(c2)from t2`).

[0099] Furthermore, when rewriting the update statement based on the merged subqueries corresponding to each query group, the fields to be updated in the assignment expression c1 = (select c1+10 from t3 limit 1) and the assignment expression c5 = (select c2+c1 from t3 limit 1) corresponding to the first query group can be determined. Based on the determined fields to be updated c1 and c5 for this query group, the vector (c1, c5) of the fields to be updated for this query group can be determined. And based on the vector (c1, c5), the assignment operator "=", and the merged subquery (select c1+10, c2+c1 from t3 limit 1) for this query group, the optimized assignment statement (c1, c5) = (select c1+10, c2+c1 from t3 limit 1) can be determined.

[0100] Furthermore, the fields to be updated in the assignment expression c3 = (select avg(c1) from t2) and the assignment expression c4 = (select sum(c2) from t2) corresponding to the subquery of the second query group can be determined. Based on the determined fields to be updated c3 and c4 of the query group, the vector (c3, c4) of the fields to be updated corresponding to the query group can be determined. Based on the vector (c3, c4), the assignment operator "=", and the merged subquery of the query group (select avg(c1), sum(c2) from t2), the optimized assignment statement (c3, c4) = (select avg(c1), sum(c2) from t2) can be determined.

[0101] Then, the update statements can be rewritten based on the optimized assignment statements corresponding to each query group.

[0102] When rewriting the update statement based on the optimized assignment statements corresponding to each query group, the database can determine the original assignment expressions corresponding to the first query group: c1 = (select c1+10 from t3 limit 1) and c5 = (select c2+c1 from t3 limit 1), and delete them from the update statement, replacing them with the optimized assignment expression (c1,c5) = (select c1+10,c2+c1 from t3 limit 1) for that query group.

[0103] The original assignment expressions corresponding to the second query group are determined as: c3 = (select avg(c1) from t2) and c4 = (select sum(c2) from t2). These are then removed from the update statement and replaced with the optimized assignment expression (c3, c4) = (select avg(c1), sum(c2) from t2) for the second query group.

[0104] Then, we can get the rewritten update statement: update t1 set(c1,c5)=(select c1+10,c2+c1 from t3limit 1),c2=1,(c3,c4)=(select avg(c1),sum(c2)from t2);

[0105] In the example above, one query group corresponds to two original assignment expressions. Taking the first query group as an example, the two original assignment expressions are located as the first and last assignment expressions in the update statement, respectively, while there is only one optimized assignment expression. During replacement, the two original assignment expressions can be deleted, and the optimized assignment expression can be placed at the beginning of the original assignment expressions, or it can be placed at the end. This specification does not impose any restrictions on this.

[0106] As can be seen from the above examples, the query statement before rewriting optimization requires four subqueries to be executed when performing an update operation, while the optimized query statement only requires two subqueries to be executed when performing an update operation, which greatly reduces the amount of computation required for the update operation and effectively improves the performance of the update query.

[0107] In one or more embodiments of this specification, when deleting the original assignment expressions corresponding to each query group in an update statement and replacing them with optimized assignment expressions corresponding to each query group, the database can determine the position of the original assignment expression corresponding to that query group in the update statement for each query group. Then, the original assignment expression corresponding to that query group can be deleted from the update statement, and a priority position can be determined from the determined positions to add the optimized assignment expression corresponding to that query group to that priority position.

[0108] The priority position can be the position closest to the beginning of the update statement among the determined positions, or it can be the position closest to the end of the statement, which can be set as needed.

[0109] Furthermore, since there may be cases where the operation clauses of all subqueries in a query group are the same, in step S106, when the operation clauses of each subquery in the query group are merged to obtain a merge operation clause, and the merge operation clause and the source clause corresponding to the query group are used to determine the merged subquery corresponding to the query group, specifically, when the subqueries in the query group are the same, any subquery in the query group can be used as the merged subquery corresponding to the query group.

[0110] To aid understanding, this manual also provides comparison diagrams before and after the update statements are rewritten, such as... Figure 2 As shown.

[0111] Figure 2 This is a comparative diagram of an update statement provided in this specification. As shown in the figure, the update statement before rewriting is Update t1 set c1 = 5, c2 = (select avg(c1) from t2), c4 = (select sum(c2) from t2); after rewriting this update statement, the rewritten update statement is Update t1 set c1 = 5, (c2, c4) = (select avg(c1), sum(c2) from t2);

[0112] In the update statement before rewriting, `Update` and `Set` are fixed keywords in the update statement. `c1`, `c2`, and `c4` are the fields to be updated in the target table `t1`. The part after `Set`: `c1 = 5`, `c2 = (select avg(c1) from t2)`, `c4 = (select sum(c2) from t2)` are assignment statements, including three assignment expressions: `c1 = 5`, `c2 = (select avg(c1) from t2)`, and `c4 = (select sum(c2) from t2)`. The assignment expression `c2 = (select avg(c1) from t2)` contains a subquery `(select avg(c1) from t2)`, where `select` is the operation identifier in the subquery's operation clause, `avg(c1)` is the operation field, and `from t2` is the source clause. The assignment expression `c4 = (select sum(c2) from t2)` contains the subquery `(select sum(c2) from t2)`. Similarly, `select` is the operator identifier in the subquery's operator clause, `sum(c2)` is the operator field, and the source clause is also `from t2`. That is, the data source for both subqueries is the source table `t2`.

[0113] As seen in the rewritten update statement, the assignment statement is shorter: c1 = 5, (c2, c4) = (select avg(c1), sum(c2) from t2). The difference between the rewritten and unrewritten update statements lies in the part after the assignment expression c1 = 5. The optimized assignment expression (c2, c4) = (select avg(c1), sum(c2) from t2) in the rewritten update statement is obtained by merging the two assignment expressions containing subqueries in the unrewritten update statement. (c2, c4) is the vector of fields to be updated in the optimized assignment expression. It is evident that the optimized assignment expression contains only one subquery, namely, the merged subquery (select avg(c1), sum(c2) from t2). The merge subquery consists of the merge operation clauses select avg(c1) and sum(c2) and the source clause from t2, where avg(c1) and sum(c2) are the merge operation fields.

[0114] It should be noted that the English spelling of update statements in this manual is for illustrative purposes only and is not a limitation. For the same string in a statement, such as `update`, whether the string is uppercase, lowercase, or has its first letter capitalized is merely a difference in spelling and does not restrict the executable update statements. For example, Figure 2 In the update statement shown, all letters can be uppercase.

[0115] The above is one data update method provided in this specification. This specification also provides a corresponding data update device, such as... Figure 3 As shown.

[0116] Figure 3 This is a schematic diagram of a data update device provided in this specification. The device includes:

[0117] The acquisition module 200 is used to acquire the update statement to be executed and determine the subqueries contained in the update statement;

[0118] The splitting module 201 is used to split each subquery into an operation clause and a source clause;

[0119] The grouping module 202 is used to determine the query group composed of subqueries containing the same source clause based on the source clause of each subquery;

[0120] The fusion module 203 is used to fuse the operation clauses of each subquery in each query group to obtain a fusion operation clause, and determine the merged subquery corresponding to the query group based on the fusion operation clause and the source clause corresponding to the query group.

[0121] The rewrite module 204 is used to rewrite the update statement based on the determined merged subquery;

[0122] Update module 205 is used to execute the rewritten update statements to update the data.

[0123] Optionally, the acquisition module 200 is further configured to determine the assignment statements in the update statement, determine each assignment expression from the assignment statements, and split the subqueries from the assignment expressions containing subqueries.

[0124] Optionally, the grouping module 202 is further configured to take any subquery as the target subquery, traverse other ungrouped subqueries, determine query groups based on other subqueries that are consistent with the source clause of the target subquery, determine whether there are any ungrouped subqueries that are not used as target subqueries, if so, determine the target subquery from the ungrouped subqueries that are not used as target subqueries, and continue to traverse other ungrouped subqueries to determine new query groups until there are no subqueries that are not used as target subqueries, if not, determine the query groups of the update statement.

[0125] Optionally, the operation clause consists of an operation identifier and an operation field. The fusion module 203 is used to concatenate the operation fields of each subquery in the query group to obtain a fused operation field, and to determine the fused operation clause corresponding to the query group based on the operation identifier and the fused operation field.

[0126] Optionally, the assignment expression containing the subquery consists of the field to be updated, the assignment operator, and the subquery. The rewriting module 204 is further configured to, for each query group, determine the field to be updated in each assignment expression corresponding to the query group, determine the vector of the field to be updated corresponding to the query group based on the determined field to be updated, determine the optimized assignment expression corresponding to the query group based on the vector of the field to be updated, the assignment operator, and the merged subquery corresponding to the query group, and rewrite the update statement based on the optimized assignment expression corresponding to each query group.

[0127] Optionally, the rewriting module 204 is further configured to, for each query group, determine the original assignment expression corresponding to that query group in the update statement, delete the original assignment expressions corresponding to each query group in the update statement, and replace them with the optimized assignment expressions corresponding to each query group, thereby obtaining the rewritten update statement.

[0128] Optionally, the fusion module 203 is further configured to, when all subqueries in the query group are the same, use any subquery in the query group as the merged subquery corresponding to the query group.

[0129] This specification also provides a computer-readable storage medium storing a computer program that can be used to perform the above-described data update method.

[0130] This instruction manual also provides Figure 4 The diagram shows a schematic structural representation of the electronic device. Figure 4 At the hardware level, the electronic device includes a processor, an internal bus, a network interface, memory, and non-volatile memory, and may also include other hardware required for business operations. The processor reads the corresponding computer program from the non-volatile memory into memory and then runs it to implement the above data update method. Of course, in addition to software implementation, this specification does not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to individual logic units, but can also be hardware or logic devices.

[0131] In the 1990s, improvements to a technology could be clearly distinguished as either hardware improvements (e.g., improvements to the circuit structure of diodes, transistors, switches, etc.) or software improvements (improvements to the methodology). However, with technological advancements, many methodological improvements today can be considered direct improvements to the hardware circuit structure. Designers almost always obtain the corresponding hardware circuit structure by programming the improved methodology into the hardware circuit. Therefore, it cannot be said that a methodological improvement cannot be implemented using hardware physical modules. For example, a Programmable Logic Device (PLD) (such as a Field Programmable Gate Array (FPGA)) is such an integrated circuit whose logic function is determined by the user programming the device. Designers can program and "integrate" a digital system onto a PLD themselves, without needing chip manufacturers to design and manufacture dedicated integrated circuit chips. Furthermore, nowadays, instead of manually manufacturing integrated circuit chips, this programming is mostly implemented using "logic compiler" software. Similar to the software compiler used in program development, the original code before compilation must be written in a specific programming language, called a Hardware Description Language (HDL). There are many HDLs, such as ABEL (Advanced Boolean Expression Language), AHDL (Altera Hardware Description Language), Confluence, CUPL (Cornell University Programming Language), HDCal, JHDL (Java Hardware Description Language), Lava, Lola, MyHDL, PALASM, and RHDL (Ruby Hardware Description Language). Currently, the most commonly used are VHDL (Very-High-Speed ​​Integrated Circuit Hardware Description Language) and Verilog. Those skilled in the art should understand that by simply performing some logic programming on the method flow using one of these hardware description languages ​​and programming it into an integrated circuit, the hardware circuit implementing the logical method flow can be easily obtained.

[0132] The controller can be implemented in any suitable manner. For example, it can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro)processor, logic gates, switches, application-specific integrated circuits (ASICs), programmable logic controllers, and embedded microcontrollers. Examples of controllers include, but are not limited to, the following microcontrollers: ARC625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicon Labs C8051F320. A memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art will also recognize that, in addition to implementing the controller in purely computer-readable program code form, the same functionality can be achieved by logically programming the method steps to make the controller take the form of logic gates, switches, ASICs, programmable logic controllers, and embedded microcontrollers. Therefore, such a controller can be considered a hardware component, and the means included therein for implementing various functions can also be considered as structures within the hardware component. Alternatively, the means for implementing various functions can be considered as both software modules implementing the method and structures within the hardware component.

[0133] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, a computer can be, for example, a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or any combination of these devices.

[0134] For ease of description, the above devices are described in terms of function, divided into various units. Of course, in implementing this specification, the functions of each unit can be implemented in one or more software and / or hardware components.

[0135] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0136] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations 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, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0137] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0138] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0139] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0140] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0141] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0142] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0143] Those skilled in the art will understand that the embodiments of this specification can be provided as methods, systems, or computer program products. Therefore, this specification may take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this specification may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0144] This specification can be described in the general context of computer-executable instructions that are executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform a specific task or implement a specific abstract data type. This specification can also be practiced in distributed computing environments, where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0145] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to interchangeably. Each embodiment focuses on describing the differences from other embodiments. In particular, the system embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions in the method embodiments.

[0146] The above description is merely an embodiment of this specification and is not intended to limit this specification. Various modifications and variations can be made to this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of the claims of this specification.

Claims

1. A data update method, comprising: Obtain the update statement to be executed, determine the assignment expression containing subqueries from the assignment expression contained in the update statement, and split the subqueries from the assignment expression containing subqueries. The assignment expression of the subquery includes: the field to be updated and the assignment operator; The database optimizer breaks down each subquery into operation clauses and source clauses. Based on the source clauses of each subquery, determine the query group consisting of subqueries that contain the same source clause; For each query group, the operation clauses of each subquery in the query group are merged to obtain a merge operation clause. Based on the merge operation clause and the source clause corresponding to the query group, the merged subquery corresponding to the query group is determined. Based on the determined merged subqueries, the update statement is rewritten, including: for each query group, determining the fields to be updated in each assignment expression corresponding to that query group; determining a vector of the fields to be updated corresponding to that query group based on the determined fields to be updated; determining the optimized assignment expression corresponding to that query group based on the vector of the fields to be updated, the assignment operators, and the merged subqueries corresponding to that query group; and rewriting the update statement based on the optimized assignment expressions corresponding to each query group. Execute the rewritten update statement to update the data.

2. The method as described in claim 1, wherein determining a query group consisting of subqueries containing the same source clause based on the source clause of each subquery, specifically includes: Use any subquery as the target subquery; Iterate through the other ungrouped subqueries and determine the query group based on the other subqueries that are consistent with the source clause of the target subquery; Determine if there are any ungrouped subqueries that are not the target subquery; If so, then among the subqueries that have not been grouped and are not used as the target subquery, determine the target subquery, and continue to traverse other ungrouped subqueries to determine new query groups until there are no more subqueries that are not used as the target subquery; If not, then determine the query groups that received the update statement.

3. The method as described in claim 1, wherein the operation clause consists of an operation identifier and an operation field; The operation clauses of each subquery in the query group are merged to obtain the merged operation clause, which specifically includes: The operation fields of each subquery in the query group are concatenated to obtain the merged operation field; Based on the operation identifier and the fusion operation field, determine the fusion operation clause corresponding to the query group.

4. The method as described in claim 1, wherein the update statement is rewritten based on the optimized assignment expression corresponding to each query group, specifically including: For each query group, determine the original assignment expression corresponding to that query group in the update statement; The original assignment expressions corresponding to each query group in the update statement are deleted and replaced with the optimized assignment expressions corresponding to each query group, resulting in a rewritten update statement.

5. The method as described in claim 1, wherein the operation clauses of each subquery in the query group are merged to obtain a merge operation clause, and the merged subquery corresponding to the query group is determined based on the merge operation clause and the source clause corresponding to the query group, specifically including: When all subqueries in a query group are identical, any subquery in that query group is treated as the merged subquery corresponding to that query group.

6. A data updating device, comprising: The acquisition module is used to acquire the update statement to be executed, determine the assignment expression containing subqueries from the assignment expression contained in the update statement, and split the subqueries from the assignment expression containing subqueries. The assignment expression of the subquery includes: the field to be updated and the assignment operator; The splitting module is used by the database optimizer to split each subquery into operation clauses and source clauses; The grouping module is used to determine the query group composed of subqueries that contain the same source clause, based on the source clause of each subquery. The fusion module is used to fuse the operation clauses of each subquery in each query group to obtain a fusion operation clause, and determine the merged subquery corresponding to the query group based on the fusion operation clause and the source clause corresponding to the query group. The rewrite module is used to rewrite the update statement based on the determined merged subqueries, including: for each query group, determining the fields to be updated in each assignment expression corresponding to the query group; determining a vector of the fields to be updated corresponding to the query group based on the determined fields to be updated; determining the optimized assignment expression corresponding to the query group based on the vector of the fields to be updated, the assignment operator, and the merged subquery corresponding to the query group; and rewriting the update statement based on the optimized assignment expressions corresponding to each query group. The update module is used to execute rewritten update statements to update the data.

7. The apparatus of claim 6, wherein the grouping module is further configured to take any subquery as the target subquery, traverse other ungrouped subqueries, determine a query group based on other subqueries that are consistent with the source clause of the target subquery, determine whether there is an ungrouped subquery that is not a target subquery, and if so, determine the target subquery from the ungrouped subqueries that are not a target subquery, and continue to traverse other ungrouped subqueries to determine a new query group until there are no subqueries that are not a target subquery, and if not, determine each query group of the update statement.

8. The apparatus of claim 6, wherein the operation clause comprises an operation identifier and an operation field; The fusion module is further configured to concatenate the operation fields of each subquery in the query group to obtain a fusion operation field, and determine the fusion operation clause corresponding to the query group based on the operation identifier and the fusion operation field.

9. The apparatus of claim 8, wherein the rewriting module is further configured to, for each query group, determine the original assignment expression corresponding to that query group in the update statement, delete the original assignment expressions corresponding to each query group in the update statement, and replace them with the optimized assignment expressions corresponding to each query group, thereby obtaining the rewritten update statement.

10. The apparatus of claim 6, wherein the fusion module is further configured to, when all subqueries in the query group are the same, use any subquery in the query group as the merged subquery corresponding to the query group.

11. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the method described in any one of claims 1 to 5.

12. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in any one of claims 1 to 5.

Images