CN-116108103-B - Database table synchronization method and database statement optimization method

CN116108103BCN 116108103 BCN116108103 BCN 116108103BCN-116108103-B

Abstract

The specification provides a synchronization method of a database table and an optimization method of a database statement, wherein the database table comprises a source table and a target table, the method comprises the steps of obtaining the database statement, wherein the database statement is used for executing merging operation on the target table and the source table according to an external connection result of the source table and the target table, and performing anti-connection query on the source table and the target table to obtain data rows which exist in the source table and do not exist in the target table under the condition that the merging operation only comprises executing insertion operation on the target table according to the source table, and inserting the obtained data rows into the target table.

Inventors

Ding jincheng
ZHU TAO
WANG GUOPING

Assignees

北京奥星贝斯科技有限公司

Dates

Publication Date: 20260505
Application Date: 20221230

Claims (16)

1. A method of synchronizing database tables, the database tables including source tables and target tables, the method comprising: The method comprises the steps of obtaining a database statement, wherein the database statement is used for executing merging operation on a target table and a source table according to external connection results of the source table and the target table, the database statement is generated by adopting an external connection query statement, and a standard grammar structure of the external connection query statement at least comprises judging conditions and branch statements corresponding to the condition that the judging conditions are not met; Performing anti-join query on the source table and the target table to obtain a data row that exists in the source table and does not exist in the target table, in the case that the merge operation includes only performing an insert operation on the target table according to the source table; And inserting the obtained data line into the target table.
2. The method of claim 1, wherein the standard syntax structure of the externally connected query statement further includes a branch statement corresponding to when the determination condition is satisfied, the branch statement when the determination condition is satisfied being used to perform an update operation on the target table according to the source table, the method further comprising: in the case where the database statement includes only branch statements when the judgment condition is not satisfied, the judgment that the merge operation includes only performing an insert operation on the target table according to the source table.
3. The method of claim 1, the performing an anti-join query on the source table and the target table, comprising: Acquiring data ranges representing that the source table and the target table respectively participate in external connection query in the database statement; writing external connection relation sentences representing the source table and the target table in the database sentences into anti-connection relation sentences; And packaging the data range and the rewritten anti-connection relation statement into an anti-connection query statement, so as to perform anti-connection query on the source table and the target table by executing the anti-connection query statement.
4. A method according to claim 3, wherein in case execution conditions are included in the database statement, the encapsulated completed anti-join query statement includes execution conditions in the database statement.
5. The method of claim 3, the inserting the obtained data row into the target table comprising: assembling the packaged inverse connection query statement into a corresponding insertion statement; and executing the insertion statement to insert the query result of the anti-connection query into the target table.
6. The method of claim 1, the database statement comprising a merge into statement.
7. The method of claim 1, the method further comprising: and in the case that the merging operation comprises the step of executing the updating operation on the target table according to the source table, executing the updating operation on the source table according to the external connection result of the source table and the target table.
8. An optimization method of a database statement, the database statement is used for executing merging operation on a target table and a source table according to external connection results of the source table and the target table, wherein the database statement is generated by adopting an external connection query statement, a standard grammar structure of the external connection query statement at least comprises a judging condition and a branch statement corresponding to the condition that the judging condition is not met, the branch statement when the judging condition is not met is used for executing inserting operation on the target table according to the source table, the method comprises: In the case that the merging operation only includes performing an insert operation on the target table according to the source table, rewriting the database statement into an anti-join query statement, where the anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain a data row that exists in the source table and does not exist in the target table; and assembling the anti-connection query statement into a corresponding insertion statement so as to insert the obtained data row into the target table.
9. The method of claim 8, wherein the standard syntax structure of the externally connected query statement further includes a branch statement corresponding to when the determination condition is satisfied, the branch statement when the determination condition is satisfied being used to perform an update operation on the target table according to the source table, the method further comprising: in the case where the database statement includes only branch statements when the judgment condition is not satisfied, the judgment that the merge operation includes only performing an insert operation on the target table according to the source table.
10. The method of claim 8, the overwriting the database statement as an anti-join query statement, comprising: Acquiring data ranges representing that the source table and the target table respectively participate in external connection query in the database statement; writing external connection relation sentences representing the source table and the target table in the database sentences into anti-connection relation sentences; And packaging the data range and the rewritten anti-connection relation statement into an anti-connection query statement.
11. The method of claim 10, wherein, where execution conditions are included in the database statement, the encapsulated completed anti-join query statement includes execution conditions in the database statement.
12. The method of claim 8, the database statement comprising a merge into statement.
13. A synchronization apparatus for a database table, the database table comprising a source table and a target table, the apparatus comprising: the system comprises an acquisition unit, a source table, a target table, a source table, a database statement, a source table and a source table, wherein the database statement is used for executing merging operation on the target table and the source table according to an external connection result of the source table and the target table; A reverse connection unit, configured to perform a reverse connection query on the source table and the target table to obtain a data row that exists in the source table and does not exist in the target table, where the merging operation includes only performing an insert operation on the target table according to the source table; and the inserting unit is used for inserting the obtained data row into the target table.
14. An optimizing apparatus of a database statement for executing a merging operation on a target table and a source table according to an external connection result of the source table and the target table, wherein the database statement is generated by adopting an external connection query statement, and a standard syntax structure of the external connection query statement at least includes a judgment condition and a branch statement corresponding to when the judgment condition is not satisfied, and the branch statement when the judgment condition is not satisfied is used for executing an inserting operation on the target table according to the source table, the apparatus comprising: A rewrite unit configured to rewrite, in a case where the merge operation includes only performing an insert operation on the target table according to the source table, the database statement into an anti-join query statement for performing an anti-join query on the source table and the target table to obtain a data row that exists in the source table but does not exist in the target table; And the assembling unit is used for assembling the inverse connection query statement into a corresponding insertion statement so as to insert the obtained data row into the target table.
15. A computer readable storage medium having stored thereon a computer program which when executed by a processor performs the steps of the method according to any of claims 1 to 7 or 8 to 12.
16. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the steps of the method of any one of claims 1 to 7 or 8 to 12 when the program is executed.

Description

Database table synchronization method and database statement optimization method Technical Field The present disclosure relates to the field of database technologies, and in particular, to a method for synchronizing a database table and a method for optimizing a database statement. Background Relational databases, which are databases that employ a relational model to organize data, store data in rows and columns for ease of user understanding, a series of rows and columns of a relational database may be referred to as a database table. A user may query, update, and manage database tables in a relational database system by editing, executing a structured query language (Structured Query Language). The SQL (structured query language) can be further classified into 5 categories, namely a database query language (Data Query Language, DQL), a database operation language (Data Manipulation Language, DML), a database definition language (Data Denifition Language, DDL), a transaction control language (Trasactional Control Languag, TCL) and a data control language (Data Control Language, DCL). An external Join (Outer Join) query is a relatively common SQL statement, which is used to Join two tables or more than two database tables together under certain connection conditions, and returns not only records meeting the connection conditions, but also records partially not meeting the conditions. The operation principle is that each row of data of the source table is connected with the target table, if the data are matched, the matched data are output, and if the data are not matched, the data are output as Null. For example, the merge into statement is a database operation statement (DML) based on external connection, and when the source table and the target table are not matched, the insertion operation is performed on the target table if the data is that the source table has the target table or not, the update or deletion operation is performed on the target table if the data is that the source table does not have the target table, and the update or deletion operation is performed on the target table when the source table and the target table are matched. However, in the related art, when two or more database tables are connected by an external connection manner and a merge operation is performed, even if the merge operation includes only performing an insert operation on a target table, it still needs to match each row of data of a source table with the target table, which results in a large amount of connection data, long term of execution of the statement, and low efficiency in the execution process of the SQL statement. Disclosure of Invention In order to overcome the problems in the related art, the present specification provides a database table synchronization method and a database statement optimization method. According to a first aspect of one or more embodiments of the present specification, there is provided a method of synchronizing a database table, the database table including a source table and a target table, the method comprising: acquiring a database statement, wherein the database statement is used for executing merging operation on the target table and the source table according to the external connection result of the source table and the target table; Performing anti-join query on the source table and the target table to obtain a data row that exists in the source table and does not exist in the target table, in the case that the merge operation includes only performing an insert operation on the target table according to the source table; And inserting the obtained data line into the target table. According to a second aspect of one or more embodiments of the present specification, there is provided a method for optimizing a database statement for performing a merging operation on a source table and a target table according to an external connection result of the source table and the target table, the method comprising: In the case that the merging operation only includes performing an insert operation on the target table according to the source table, rewriting the database statement into an anti-join query statement, where the anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain a data row that exists in the source table and does not exist in the target table; and assembling the anti-connection query statement into a corresponding insertion statement so as to insert the obtained data row into the target table. According to a third aspect of one or more embodiments of the present specification, there is provided a synchronization apparatus of a database table, the database table including a source table and a target table, the apparatus comprising: The acquisition unit is used for acquiring database sentences, wherein the database sentences are used for executing merging operation on the target table and the source table according