A data updating method, device, apparatus, and storage medium
By generating and synthesizing target update statement blocks, the number of database accesses is reduced, solving the problem of long network transmission time in traditional database batch update methods and improving database performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- JINZHUAN INFORMATION TECHNOLOGY CO LTD
- Filing Date
- 2022-12-14
- Publication Date
- 2026-06-23
AI Technical Summary
Traditional methods for batch updating databases require frequent connections to the database, which increases network transmission time and impacts performance.
By obtaining at least two statements to be updated, generating a target update statement block, and sending it to the database all at once for data update, the number of accesses is reduced.
This reduces the number of database accesses, lowers network transmission time, and improves database performance.
Smart Images
Figure CN116186051B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database technology, and in particular to a data update method, apparatus, device, and storage medium. Background Technology
[0002] In real-world applications, most data in a database will undergo some degree of modification. Typically, the `UPDATE` statement is used to update data in the database. For updating batches of data in a database, a batch `UPDATE` method is used.
[0003] However, traditional batch update methods in databases require establishing a connection with the database for each update operation, which increases network transmission time and impacts database performance. Summary of the Invention
[0004] This application provides a data update method, apparatus, device, and storage medium to reduce the number of database accesses, thereby reducing network transmission time.
[0005] In a first aspect, embodiments of this application provide a data update method, including:
[0006] Retrieve at least two update statements to be used for inter-table updates;
[0007] Based on the inter-table query conditions and assignment clauses in the statement to be updated, generate the target update statement that is applied to the intra-table update.
[0008] Combine at least two target update statements into a target update statement block;
[0009] Send the target update statement block to the database to be updated to update the data.
[0010] Secondly, embodiments of this application also provide a data updating device, including:
[0011] The module for obtaining statements to be updated is used to obtain at least two statements to be updated for inter-table updates.
[0012] The target update statement generation module is used to generate target update statements that are applied to intra-table updates based on the inter-table query conditions and assignment clauses in the statement to be updated.
[0013] The statement block synthesis module is used to synthesize at least two target update statements into a target update statement block.
[0014] The data update module is used to send the target update statement block to the database to be updated for data update.
[0015] Thirdly, embodiments of this application also provide an electronic device, the electronic device comprising:
[0016] At least one processor; and
[0017] A memory that is communicatively connected to at least one processor; wherein,
[0018] The memory stores a computer program that can be executed by at least one processor, such that the at least one processor is able to perform the data update method of any embodiment of this application.
[0019] Fourthly, embodiments of this application also provide a computer-readable storage medium storing computer instructions that are used to cause a processor to execute and implement the data update method of any embodiment of this application.
[0020] This application obtains at least two update statements for inter-table updates; generates target update statements for intra-table updates based on the inter-table query conditions and assignment clauses in the update statements; combines the at least two target update statements into a target update statement block; and sends the target update statement block to the database to be updated for data updates. This technical solution combines at least two update statements into a single target update statement block and sends it to the database at once for data updates, reducing the number of database accesses and thus reducing network transmission time.
[0021] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of this application, nor is it intended to limit the scope of this application. Other features of this application will become readily apparent from the following description. Attached Figure Description
[0022] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0023] Figure 1 This is a flowchart of a data update method provided in Embodiment 1 of this application;
[0024] Figure 2 This is a flowchart of a data update method provided in Embodiment 2 of this application;
[0025] Figure 3 This is a schematic diagram of the structure of a data update device provided in Embodiment 3 of this application;
[0026] Figure 4 This is a schematic diagram of the structure of an electronic device that implements the data update method of the embodiments of this application. Detailed Implementation
[0027] The present application will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the application and not intended to limit it. Furthermore, it should be noted that, for ease of description, the accompanying drawings show only the parts relevant to the present application, not the entire structure.
[0028] Example 1
[0029] Figure 1 This is a flowchart illustrating a data update method provided in Embodiment 1 of this application. This embodiment is applicable to the execution of batch update statements in a distributed database. The method can be executed by a data update device, which can be implemented in hardware and / or software and configured within a distributed database system. Figure 1 As shown, the method includes:
[0030] S101. Obtain at least two update statements to be used for inter-table updates.
[0031] Inter-table updates refer to updating data in a current table based on data from another table. The statement to be updated refers to an unprocessed update statement (i.e., an UPDATE statement) in the database to be updated, such as "update t2 joint1 on t2.id1=t1.id1 set t2.id2=t1.id2 where id3='a';". The database to be updated can be the database that needs to be updated, such as a distributed database. Here, t1 and t2 are tables, with t1 serving as the reference table for the update and t2 as the table to be updated. Both tables t1 and t2 have four fields, id1-id4, and id2 is the primary key in both tables. For ease of description, "t2.id1=t1.id1" can be called the primary query condition between tables t1 and t2, "t2.id2=t1.id2" can be called the assignment statement, and "id3='a'" can be called the secondary query condition for table t1. The above update statement is used to assign the value of the id2 field in the row data of data table t1 that satisfies the main query conditions and the secondary query conditions to the id2 field in data table t2. That is, it determines the row data where the id1 field value in data table t1 is the same as the id1 field value in data table t2, and the id3 field value in data table t1 is 'a', and assigns the id2 value of that row data to the id2 field in data table t2.
[0032] Furthermore, the data table corresponding to the update statement is located in the same data shard of the database to be updated. A data shard is used to store the data in the data table and can be obtained by partitioning the database to be updated. Database partitioning refers to splitting the database to be updated according to certain distribution rules and adding the split results to different data shards. The database to be updated can include at least two data shards. The distribution rules can be duplicate distribution, hash distribution, range distribution, or list distribution, etc. It should be noted that the distribution rules can be pre-defined by technical personnel when creating the data table.
[0033] The above operations ensure that the data table corresponding to the update statement is located in the same data shard of the database to be updated, reducing the number of accesses between different data shards, thereby reducing network transmission time.
[0034] S102. Based on the inter-table query conditions and assignment clauses in the statement to be updated, generate the target update statement to be applied to the intra-table update.
[0035] Inter-table query conditions can refer to query conditions obtained by describing the fields of at least two data tables; intra-table updates can refer to updating the data of the current data table without relying on the field descriptions of other data tables. Assignment clauses are used to assign values to the fields to be updated in the table to be updated within the update statement.
[0036] For example, if the database to be updated executes two insert statements, as follows:
[0037] insert into t1values('1','2','a','1');
[0038] insert into t2values('1','1','1','1');
[0039] That is, in data table t1, the field value of field id1 is 1, the field value of field id2 is 2, the field value of field id3 is 'a', and the field value of field id4 is 1; correspondingly, in data table t2, the field values of fields id1, id2, id3, and id4 are 1.
[0040] If the statement to be updated is "update t2join t1on t2.id1=t1.id1 set t2.id2=t1.id2where id3='a'", then the inter-table query conditions are "t2.id1=t1.id1" and "id3='a'", which means querying data in tables t1 and t2 where the value of field id1 in t2 is equal to the value of field id1 in t1, and the value of field id3 in t1 is 'a'. The assignment clause is "t2.id2=t1.id2", which means assigning the value 2 of field id2 in t1 to field id2 in t2. Here, t2 is the table to be updated, and t1 is the reference table. Based on the inter-table query conditions and assignment clauses in the statement to be updated, a target update statement is generated for the intra-table update of table t2: "UPDATE test.t2 SET t2.GTID=4296682,`id2`='2'WHERE`id2`='1'". This target update statement indicates that the value of the field id2 in t2 will be updated from 1 to 2. GTID (Global Transaction ID) is used to enhance the database's master-slave consistency, fault recovery, and fault tolerance. The GTID can be used to determine which instance initially committed the transaction.
[0041] S103. Combine at least two target update statements into a target update statement block.
[0042] Specifically, the target update statements are sequentially combined to generate a target update statement block. At least two target update statements for the same data table on the same data shard can be arranged in ascending order of their generation time, and then combined into a single target update statement block according to this arrangement.
[0043] S104. Send the target update statement block to the database to be updated for data update.
[0044] Specifically, the target update statement block is sent to the database to be updated all at once; the database to be updated executes each target update statement in the order of the target update statements in the target update statement block to complete the update of the data in the database to be updated; after the target update statement block is executed, the database to be updated returns a summary at once, reducing the number of database accesses.
[0045] This application obtains at least two update statements for inter-table updates; generates target update statements for intra-table updates based on the inter-table query conditions and assignment clauses in the update statements; combines the at least two target update statements into a target update statement block; and sends the target update statement block to the database to be updated for data updates. This technical solution combines at least two update statements into a single target update statement block and sends it to the database at once for data updates, reducing the number of database accesses and thus reducing network transmission time.
[0046] Example 2
[0047] Figure 2 This is a flowchart of a data update method provided in Embodiment 2 of this application. Based on the above embodiments, this embodiment further refines the process of "generating a target update statement for intra-table updates based on the inter-table query conditions and assignment clauses in the statement to be updated" into "locking the update-related data in the corresponding statement to be updated that satisfies the corresponding inter-table query conditions based on the inter-table query conditions and assignment clauses; wherein, the update-related data includes update reference data in the reference table and data to be updated in the table to be updated; generating a target update statement for intra-table updates based on the update-related data, assignment clauses, and inter-table query conditions," thus providing a specific method for generating a target update statement. It should be noted that parts not detailed in this embodiment can be referred to in other embodiments.
[0048] like Figure 2 The data update methods shown specifically include:
[0049] S201. Obtain at least two update statements to be used for inter-table updates.
[0050] S202. Based on the inter-table update conditions and assignment clauses in the statement to be updated, lock the update-related data in the corresponding statement that meets the corresponding inter-table query conditions; wherein, the update-related data includes the update reference data in the reference table and the data to be updated in the table to be updated.
[0051] In this context, "updating related data" refers to the data involved in the inter-table query conditions within the statement to be updated. "Referencing table" refers to the data table used as a reference when updating the table to be updated. "Updating reference data" refers to the data in the reference table used to update the table to be updated. "Data to be updated" refers to the data in the table to be updated that needs to be updated.
[0052] Specifically, if the statement to be updated is "update t2join t1on t2.id1=t1.id1 set t2.id2=t1.id2 where id3='a'", then the inter-table query conditions are "t2.id1=t1.id1" and "id3='a'", and the assignment clause is "t2.id2=t1.id2". Here, table t1 is the reference table, and table t2 is the table to be updated. The update reference data in table t1 includes "t1.id1", "t1.id2", and "id3='a'", while the data to be updated in table t2 is "t2.id2". Based on the inter-table query conditions and assignment clause in the statement to be updated, the update-related data in the corresponding statement that satisfy the corresponding inter-table query conditions are locked, including "t1.id1", "t1.id2", "id3='a'", "t2.id1", and "t2.id2".
[0053] Optionally, based on the assignment clause in the statement to be updated and the main query condition in the inter-table query conditions, determine the fields to be locked in the statement to be updated; take the data in the reference table and the table to be updated that contain fields to be locked and satisfy the main query condition and auxiliary query condition in the inter-table query conditions as the update related data; lock the field values corresponding to the fields to be locked in the update related data.
[0054] In this context, the primary query condition refers to the main query condition among the inter-table query conditions, while the secondary query condition refers to the secondary query condition among the inter-table query conditions, used to assist the primary query condition in retrieving data. The field to be locked refers to the field in the update statement that needs to be locked to prevent write operations on the corresponding field values before data updates are performed.
[0055] Based on the example above, in the statement to be updated, “update t2join t1on t2.id1=t1.id1sett2.id2=t1.id2 where id3='a'”, the main query condition in the inter-table query is “t2.id1=t1.id1”, the secondary query condition is “id3='a'”, and the assignment clause is “t2.id2=t1.id2”. If t1 and t2 contain fields id1, id2, id3, and id4 respectively, based on the assignment clause in the update statement and the main query condition in the inter-table query, the fields to be locked in the update statement include "id1" and "id2". The data in the reference table t1 and the table to be updated t2 that contain fields to be locked and satisfy the main query condition "t2.id1 = t1.id1" and the auxiliary query condition "id3 = 'a'" in the inter-table query are used as the update related data. This update related data includes the fields "t1.id1", "t1.id2", "t1.id3", "t2.id2", and "t2.id1", as well as their corresponding values. The values of the fields to be locked in the update related data are locked. The following statement can be used to lock the values of the fields to be locked in the update related data:
[0056] SELECT`t1`.`id2`,`t2`.`id2`,`t2`.`id1`FROM t2join t1on t2.id1=t1.id1WHERE(`id3`='a')FOR UPDATE;
[0057] The above operations provide a method for locking the update-related data that meets the corresponding inter-table query conditions in the statement to be updated. This locks the data that meets the main and secondary query conditions in the inter-table query conditions of the statement to be updated, ensuring the safety of this data and preventing it from being overwritten. At the same time, it can quickly locate the data in the database to be updated that meets the main and secondary query conditions in the inter-table query conditions of the statement to be updated, so that the statement to be updated can be transformed and adjusted in the future.
[0058] S203. Based on the updated related data, assignment clauses, and inter-table query conditions, generate the target update statement to be applied to the intra-table update.
[0059] Specifically, based on the field values corresponding to the updated related data, the field values corresponding to the fields in the assignment clause, and the inter-table query conditions, a target update statement is generated for the intra-table update applied to the table to be updated.
[0060] Optionally, the assignment result of the assignment clause is determined based on the update reference data; the inter-table query conditions are transformed into intra-table query conditions based on the data to be updated and the update reference data; and the target update statement for intra-table updates is generated based on the assignment result and the intra-table query conditions.
[0061] The assignment result can refer to the specific value that needs to be assigned to the field to be updated in the table to be updated; the query condition within the table can refer to the query condition that only involves the data in the table to be updated.
[0062] For example, if two insert statements were executed on the data to be updated, as follows:
[0063] insert into t1values('1','2','a','1');
[0064] insert into t2values('1','1','1','1');
[0065] In table t1, the values of fields id1, id2, id3, and id4 are all 1; similarly, in table t2, the values of fields id1, id2, id3, and id4 are all 1. If the update statement is "update t2 join t1on t2.id1=t1.id1set t2.id2=t1.id2 where id3='a';", then the inter-table query conditions are "t2.id1=t1.id1" and "id3='a'"; the assignment clause is "t2.id2=t1.id2"; where table t1 is the reference table and table t2 is the table to be updated. Specifically, based on the field value 2 corresponding to the update reference data t1.id2 in t1, the assignment result of the assignment clause "t2.id2=t1.id2" is determined to be "t2.id2=2". Based on the data to be updated t2.id2 and the update reference data: "t1.id1", "t1.id2", and "id3='a'", the inter-table query conditions "t2.id1=t1.id1" and "id3='a'" are transformed into intra-table query conditions, i.e., "`id2`='1'". Based on the assignment result t2.id2=2 and the intra-table query condition "`id2`='1'", the target update statement for the intra-table update is generated, i.e., "UPDATE test.t2 SET t2.GTID=4296682,`id2`='2'WHERE`id2`='1';". Here, t2.GTID is used to determine whether the data in the target update statement is active when executing the SELECT statement. If so, then only the active target update statements will be executed sequentially in the database to be updated.
[0066] The above operations, based on the updated reference data and the data to be updated, obtain the assignment result of the assignment clause and the query conditions in the table; and based on the assignment result and the query conditions in the table, generate the target update statement to be applied to the table update, which improves the accuracy of the generated target update statement and facilitates the subsequent data update in the table to be updated.
[0067] As an optional embodiment of this application, the step of converting inter-table query conditions into intra-table query conditions based on the data to be updated and the update reference data can be: determining the query field values of the inter-table query conditions based on the update reference data; and converting the inter-table query conditions into intra-table query conditions based on the query field values and the primary key of the data to be updated.
[0068] The query field value can refer to the field value corresponding to the field involved in the inter-table query conditions. The primary key is used to uniquely identify a piece of data to be updated.
[0069] Specifically, based on the example above, the inter-table query conditions are "t2.id1 = t1.id1" and "id3 = 'a'". If the query field value of the corresponding field in the updated reference data is "t1.id1 = 1", "t1.id2 = 2", and "id3 = 'a'", the query field value of the inter-table query condition is determined to be "t2.id1 = 1". If the primary key of the data to be updated is the field t2.id2, based on the query field value t2.id1 = 1 and the primary key t2.id2 of the data to be updated, the inter-table query condition for t1 and t2 is transformed into an intra-table query condition for only t2, i.e., "`id2` = '1'.
[0070] The above operation provides a specific method for converting inter-table query conditions into intra-table query conditions, thereby ensuring the consistency of the conversion method of intra-table query conditions in the generated target update statement. This reduces the occurrence of situations where the target update statement cannot be executed or the execution result is incorrect, and thus improves the accuracy of the execution result of the target update statement.
[0071] In an optional embodiment, if the data to be updated does not have an explicit primary key, then the step of converting the inter-table query conditions into intra-table query conditions based on the data to be updated and the update reference data can be: determining the field that satisfies the index condition as the implicit primary key based on the field values of different data to be updated under the same field; and converting the inter-table query conditions into the intra-table query conditions based on the query field value and the implicit primary key of the data to be updated.
[0072] In this context, an explicit primary key can refer to a pre-defined primary key in the data to be updated, used to uniquely identify a piece of data to be updated in the table and its corresponding update reference data in the reference table. Conversely, an implicit primary key can refer to a non-nullable field selected when there is no pre-defined primary key in the data to be updated, which can also act as an index, uniquely identifying a piece of data to be updated in the table and its corresponding update reference data in the reference table.
[0073] Specifically, if there are data A and data B to be updated, and both involve reference table t1 and table t2 to be updated; wherein, the fields and corresponding field values in t1 and t2 can be referred to the relevant descriptions in other embodiments; the field that satisfies the index condition can be determined as the implicit primary key based on the field values of different data to be updated in t2 under the same field; the query field value of the inter-table query condition can be determined based on the update reference data in t1; and the inter-table query condition can be transformed into an intra-table query condition based on the query field value and the implicit primary key of the data to be updated.
[0074] The above operations, when the data to be updated does not have an explicit primary key, improve the method of converting inter-table query conditions into intra-table query conditions, enhance the flexibility of the data update method, expand its applicable scope, and enable the data update method to meet the needs of more business scenarios.
[0075] S204. Combine at least two target update statements into a target update statement block.
[0076] S205. Send the target update statement block to the database to be updated for data update.
[0077] The technical solution of this application embodiment locks the update-related data that meets the corresponding inter-table update conditions in the corresponding update statement based on the inter-table query conditions and assignment clauses in the statement to be updated; and generates a target update statement for intra-table updates based on the update-related data, assignment clauses, and inter-table query conditions. This technical solution, by locking the update-related data that meets the corresponding inter-table update conditions in the corresponding update statement, ensures the security of the update-related data and prevents it from being rewritten. Simultaneously, it provides a method for generating the target update statement, ensuring that the target update statement is generated according to certain rules, guaranteeing the uniformity of the format of all target update statements, and facilitating the subsequent execution of the target update statement by the database to be updated.
[0078] Example 3
[0079] Figure 3 This is a schematic diagram of a data update device provided in Embodiment 3 of this application. This embodiment is applicable to the execution of batch update statements in a distributed database. The device can be implemented in hardware and / or software and can be configured in a distributed database system. Figure 3 As shown, the device includes:
[0080] The module 301 for obtaining statements to be updated is used to obtain at least two statements to be updated for inter-table updates.
[0081] The target update statement generation module 302 is used to generate a target update statement that is applied to the intra-table update based on the inter-table query conditions and assignment clauses in the statement to be updated.
[0082] Statement block synthesis module 303 is used to synthesize at least two target update statements into a target update statement block;
[0083] The data update module 304 is used to send the target update statement block to the database to be updated for data update.
[0084] This application obtains the statements to be updated through a statement acquisition module; generates target update statements for table updates through a target update statement generation module; combines at least two target update statements into a target update statement block through a statement block synthesis module; and sends the target update statement block to the database to be updated for data update through a data update module. This technical solution combines at least two statements to be updated into a single target update statement block and sends it to the database at once for data update, reducing the number of database accesses and thus reducing network transmission time.
[0085] Optionally, the target update statement generation module 302 includes:
[0086] The associated data locking unit is used to lock the update associated data in the corresponding update statement that meets the corresponding inter-table query conditions based on the inter-table query conditions and assignment clauses in the update statement; wherein, the update associated data includes the update reference data in the reference table and the data to be updated in the table to be updated;
[0087] The update statement generation unit is used to generate target update statements that are applied to the table update based on the update related data, assignment clauses, and inter-table query conditions.
[0088] Optionally, the update statement generation unit includes:
[0089] The assignment result determination sub-unit is used to determine the assignment result of the assignment clause based on the updated reference data;
[0090] The query condition transformation sub-unit is used to transform inter-table query conditions into intra-table query conditions based on the data to be updated and the reference data to be updated.
[0091] The update statement generation sub-cell is used to generate the target update statement to be applied to the table update based on the assignment result and the query conditions in the table.
[0092] Optionally, the query condition transformation subunit is specifically used for:
[0093] Based on the updated reference data, determine the query field values for the inter-table query conditions; based on the query field values and the primary key of the data to be updated, convert the inter-table query conditions into intra-table query conditions.
[0094] Optionally, if the data to be updated does not have an explicit primary key, the device further includes:
[0095] The implicit primary key determination module is used to determine the field that meets the index conditions as the implicit primary key based on the field values of different data to be updated under the same field.
[0096] The intra-table query condition conversion module is used to convert inter-table query conditions into intra-table query conditions based on the query field values and the implicit primary key of the data to be updated.
[0097] Optionally, the associated data locking unit is specifically used for:
[0098] Based on the assignment clause in the statement to be updated and the main query condition in the inter-table query conditions, determine the fields to be locked in the statement to be updated; select the data in the reference table and the table to be updated that contain fields to be locked and satisfy the main query condition and the auxiliary query condition in the inter-table query conditions as the update related data; lock the field values corresponding to the fields to be locked in the update related data.
[0099] Optionally, the data table corresponding to the statement to be updated is located in the same data shard of the database to be updated.
[0100] The data update apparatus provided in this application embodiment can execute the data update method provided in any embodiment of this application, and has the corresponding functional modules and beneficial effects for executing each data update method.
[0101] Example 4
[0102] Figure 4 A schematic diagram of an electronic device 400 that can be used to implement embodiments of this application is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the application described and / or claimed herein.
[0103] like Figure 4 As shown, the electronic device 400 includes at least one processor 401 and a memory, such as a read-only memory (ROM) 402 and a random access memory (RAM) 403, communicatively connected to the at least one processor 401. The memory stores computer programs executable by the at least one processor. The processor 401 can perform various appropriate actions and processes based on the computer program stored in the ROM 402 or loaded into the RAM 403 from storage unit 408. The RAM 403 can also store various programs and data required for the operation of the electronic device 400. The processor 401, ROM 402, and RAM 403 are interconnected via a bus 404. An input / output (I / O) interface 405 is also connected to the bus 404.
[0104] Multiple components in electronic device 400 are connected to I / O interface 405, including: input unit 406, such as keyboard, mouse, etc.; output unit 407, such as various types of displays, speakers, etc.; storage unit 408, such as disk, optical disk, etc.; and communication unit 409, such as network card, modem, wireless transceiver, etc. Communication unit 409 allows electronic device 400 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.
[0105] Processor 401 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 401 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 401 performs the various methods and processes described above, such as data update methods.
[0106] In some embodiments, the data update method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 408. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 400 via ROM 402 and / or communication unit 409. When the computer program is loaded into RAM 403 and executed by processor 401, one or more steps of the data update method described above may be performed. Alternatively, in other embodiments, processor 401 may be configured as the data update method by any other suitable means (e.g., by means of firmware).
[0107] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.
[0108] Computer programs used to implement the methods of this application may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.
[0109] In the context of this application, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium can be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.
[0110] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).
[0111] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.
[0112] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.
[0113] It should be understood that the various forms of processes shown above can be used to rearrange, add, or delete steps. For example, the steps described in this application can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this application can be achieved, and this is not limited herein.
[0114] The specific embodiments described above do not constitute a limitation on the scope of protection of this application. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this application should be included within the scope of protection of this application.
Claims
1. A data update method, characterized in that, include: Retrieve at least two update statements to be used for inter-table updates; Based on the inter-table query conditions and assignment clauses in the statement to be updated, lock the update-related data in the corresponding statement that meets the corresponding inter-table query conditions; The updated associated data includes the updated reference data in the reference table and the data to be updated in the table to be updated. Based on the updated related data, the assignment clause, and the inter-table query conditions, generate the target update statement applied to the intra-table update; Combine at least two target update statements into a target update statement block; The target update statement block is sent to the database to be updated for data update.
2. The method according to claim 1, characterized in that, The step of generating a target update statement for the intra-table update based on the updated related data, the assignment clause, and the inter-table query conditions includes: Based on the updated reference data, determine the assignment result of the assignment clause; Based on the data to be updated and the reference data for updating, the inter-table query conditions are transformed into intra-table query conditions; Based on the assignment result and the table query conditions, generate the target update statement to be applied to the table update.
3. The method according to claim 2, characterized in that, The step of converting the inter-table query conditions into intra-table query conditions based on the data to be updated and the update reference data includes: Based on the updated reference data, determine the query field values for the inter-table query conditions; Based on the query field value and the primary key of the data to be updated, the inter-table query conditions are transformed into intra-table query conditions.
4. The method according to claim 3, characterized in that, The step of converting the inter-table query conditions into intra-table query conditions based on the query field values and the primary key of the data to be updated includes: If the data to be updated does not have an explicit primary key, the method further includes: Based on the field values of different data to be updated under the same field, determine the field that meets the index conditions as the implicit primary key; Based on the query field values and the implicit primary key of the data to be updated, the inter-table query conditions are transformed into intra-table query conditions.
5. The method according to claim 1, characterized in that, The step of locking the update-related data in the corresponding statement that satisfies the corresponding inter-table query conditions based on the inter-table query conditions and assignment clauses in the statement to be updated includes: Based on the assignment clause in the statement to be updated and the main query condition in the inter-table query conditions, determine the fields to be locked in the statement to be updated; The data in the reference table and the table to be updated that contain the field to be locked and satisfy the main query condition and the auxiliary query condition in the inter-table query conditions are used as the update association data; Lock the field value corresponding to the field to be locked in the updated associated data.
6. The method according to any one of claims 1-5, characterized in that, The data table corresponding to the statement to be updated is located in the same data shard of the database to be updated.
7. A data update device, characterized in that, include: The module for obtaining statements to be updated is used to obtain at least two statements to be updated for inter-table updates. The target update statement generation module is used to lock the update-related data that meets the corresponding inter-table query conditions in the statement to be updated, based on the inter-table query conditions and assignment clauses in the statement to be updated; the update-related data includes update reference data in the reference table and data to be updated in the table to be updated. Based on the updated related data, the assignment clause, and the inter-table query conditions, generate the target update statement applied to the intra-table update; The statement block synthesis module is used to synthesize at least two target update statements into a target update statement block; The data update module is used to send the target update statement block to the database to be updated for data update.
8. An electronic device, characterized in that, include: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the data update method as described in any one of claims 1-6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the data update method as described in any one of claims 1-6.