CN114036241A - Data increment synchronization method based on multi-table association query - Google Patents
Data increment synchronization method based on multi-table association query Download PDFInfo
- Publication number
- CN114036241A CN114036241A CN202111472567.5A CN202111472567A CN114036241A CN 114036241 A CN114036241 A CN 114036241A CN 202111472567 A CN202111472567 A CN 202111472567A CN 114036241 A CN114036241 A CN 114036241A
- Authority
- CN
- China
- Prior art keywords
- data
- tables
- base
- synchronization
- unique
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 19
- 230000001360 synchronised effect Effects 0.000 claims description 3
- 238000011084 recovery Methods 0.000 abstract description 2
- 238000012790 confirmation Methods 0.000 description 2
- 230000005540 biological transmission Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 238000012544 monitoring process Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/275—Synchronous replication
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the technical field of data processing, in particular to a data increment synchronization method based on multi-table association query, which comprises the steps of selecting a unique key of a base table, calling one or more tables at the source of data synchronization as the base table, and calling a target table of the data synchronization as the target table; defining a target table, wherein the target table additionally incorporates unique keys of all base tables except for the establishment of normal fields required by synchronization; establishing a data synchronization module, automatically storing data before and after the change of the base table when the data of the base table changes, and then updating the data into a target table in an asynchronous mode according to the association logic between the base tables; the method for multi-table association query data increment synchronization solves the problem of multi-table association increment data synchronization, also provides a cross-database synchronization scheme of multi-table association increment data, has higher efficiency, does not have a large number of filing logs, and obviously reduces the data synchronization pressure of a disk and the same city disaster recovery.
Description
Technical Field
The invention relates to the technical field of data processing, in particular to a data increment synchronization method based on multi-table association query.
Background
The existing data synchronization method comprises increment synchronization based on a trigger and increment synchronization based on a time stamp, but the existing technology is single-table synchronization without processing logic.
There is a published chinese patent: a management method and device of a database, the disclosure number: CN101382949A, which judges whether there is duplicate data in a plurality of database tables through a unique index or primary key. However, the patent is a single table increment synchronization, and the single table increment synchronization scenario is relatively simple.
The method can simply and effectively work when the data volume is not large, but the data synchronization is time-consuming and a large amount of filing logs are generated when the relational tables with large data volume are subjected to relational query, so that the system performance is easily influenced greatly. The method of the common view is finally converted into the base table query, and certain loss is caused to the query performance.
Disclosure of Invention
The invention provides a data increment synchronization method based on multi-table association query, which is used for solving the problem of data association increment synchronization under the condition that the stock data base number of a plurality of tables is large but the updating number is not large.
In order to achieve the above object, a method for data increment synchronization based on multi-table association query is designed, where the method specifically includes:
A. selecting a unique key of a base table, and calling one or more tables of a source of data synchronization as the base table, and calling a target table of the data synchronization as a target table;
B. defining a target table, wherein the target table additionally incorporates unique keys of all base tables except for the establishment of normal fields required by synchronization;
C. establishing a data synchronization module, automatically storing data before and after the change of the base table when the data of the base table changes, and then updating the data into a target table in an asynchronous mode according to the association logic between the base tables, wherein the specific logic is as follows:
a. when any base table data is newly added, the synchronous program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, generates new incremental data based on the value of the unique key or the value combination of the unique keys of the plurality of tables according to the correlation logic between the base tables, and inserts the new incremental data into the target table;
b. when any base table data is deleted, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table;
c. when any base table data is updated, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table; and simultaneously generating new incremental data based on the value of the unique key or the combination of the values of the unique keys of the plurality of tables according to the association logic between the base tables, and inserting the new incremental data into the target table.
The invention also has the following preferable technical scheme:
1. the base table unique key is either the primary key field or the unique index field.
2. In step B, simultaneously establishing query index for target table
3. The data synchronization module is used for providing configuration entries of the base table, the target table and the base table association logic and can automatically establish an adding trigger, a deleting trigger and a changing trigger on the base table.
4. The data synchronization module provides a message confirmation mechanism and a failure retransmission mechanism after data synchronization.
Compared with the prior art, the invention has the advantages that: under the condition that the stock data base number of a plurality of tables is large but the updating number is small, the multi-table association query data increment synchronization method solves the problem of multi-table association increment data synchronization, and simultaneously provides a cross-library synchronization scheme of multi-table association increment data. Compared with full-scale synchronization, the method has higher efficiency, does not have a large amount of filing logs, and obviously reduces the data synchronization pressure of the disk and the same city disaster recovery.
When multiple tables are associated, the multiple tables of data in the base need to be subjected to Cartesian product, a new recording result set is inevitably generated, the new recording result set does not have an absolute comparison standard in a target table, the processing modes of adding, deleting and updating operations are different, the data synchronization is the synchronization of processing logic, and the aim is achieved by combining the unique joint identifiers of the multiple tables with the logic.
Drawings
FIG. 1 is a flow chart of the present invention.
Detailed Description
The present invention is further described below in conjunction with the following drawings, the structure and principle of which will be apparent to those skilled in the art. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
The invention relates to a data increment synchronization method based on multi-table association query, which comprises the following steps:
A. selecting a unique key (which can be a main key field or a unique index field) of the base table, and calling one or more tables at the source of data synchronization as the base table, and calling a target table of the data synchronization as the target table, and the following steps are performed.
B. A destination table is defined that additionally incorporates a unique key for all base tables, in addition to the normal synchronization required fields being established. Meanwhile, the query index can be established for the target table so as to improve the query speed of the target table.
C. Establishing a data synchronization program, wherein the data synchronization program can realize the following functions:
1. providing configuration entries of a base table, a target table and base table association logic;
2. an adding trigger, a deleting trigger and a changing trigger can be automatically established on the base table;
3. when the data of the base table changes, the data before and after the change of the base table can be automatically stored, and then the data is updated into the target table in an asynchronous mode according to the association logic between the base tables. The specific logic is as follows:
a. when any base table data is newly added, the synchronous program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, generates new incremental data based on the value of the unique key or the value combination of the unique keys of the plurality of tables according to the correlation logic between the base tables, and inserts the new incremental data into the target table;
b. when any base table data is deleted, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table;
c. when any base table data is updated, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table; and simultaneously generating new incremental data based on the value of the unique key or the combination of the values of the unique keys of the plurality of tables according to the association logic between the base tables, and inserting the new incremental data into the target table.
D. The program provides a message confirmation mechanism after data synchronization based on a client side mode and a service side mode, ensures data loss caused by network jitter, and can perform retransmission according to pre-stored data when transmission fails.
In a preferred embodiment, the following are specified:
the system comprises:
s0: and selecting the unique key of the base table. Primary keys A _ id, B _ id as in Table A and Table B;
s1: a destination table is defined. Adding auxiliary fields A _ id and B _ id as shown in a table C;
and S2, inputting the base table, the destination table and the associated logic. Such as select a.a., b.b.. from a, B where a _ id = B _ id and …;
s3: automatically establishing a trigger on the base table and monitoring data change of the base table;
s4: performing data synchronization according to data change, wherein the specific logic refers to step C, if the data synchronization is successfully performed, turning to S6, otherwise, performing retransmission, and turning to S5;
s5: according to the recorded old data, checking the target table and retransmitting;
s6: and (6) ending.
Claims (5)
1. A data increment synchronization method based on multi-table association query is characterized by comprising the following steps:
A. selecting a unique key (which can be a main key field or a unique index field) of a base table, calling one or more tables at the source of data synchronization as the base table, and calling a target table of the data synchronization as the target table;
B. defining a target table, wherein the target table additionally incorporates unique keys of all base tables except for the establishment of normal fields required by synchronization;
C. establishing a data synchronization module, automatically storing data before and after the change of the base table when the data of the base table changes, and then updating the data into a target table in an asynchronous mode according to the association logic between the base tables, wherein the specific logic is as follows:
when any base table data is newly added, the synchronous program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, generates new incremental data based on the value of the unique key or the value combination of the unique keys of the plurality of tables according to the correlation logic between the base tables, and inserts the new incremental data into the target table;
when any base table data is deleted, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table;
when any base table data is updated, the synchronization program captures the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables, and deletes all data records associated with the value of the unique key of the base table or the value combination of the unique keys of the plurality of tables in the destination table; and simultaneously generating new incremental data based on the value of the unique key or the combination of the values of the unique keys of the plurality of tables according to the association logic between the base tables, and inserting the new incremental data into the target table.
2. The method of claim 1, wherein the base table unique key is a primary key field or a unique index field.
3. The method according to claim 1, wherein the step B is performed by simultaneously building query indexes for the destination tables.
4. The method according to claim 1, wherein the data synchronization module is configured to provide configuration entries for the base table, the destination table, and the base table association logic, and is capable of automatically creating an add, delete, and change trigger on the base table.
5. The method according to claim 1, wherein the data synchronization module provides a message acknowledgement mechanism and a failure retransmission mechanism after data synchronization.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111472567.5A CN114036241A (en) | 2021-12-06 | 2021-12-06 | Data increment synchronization method based on multi-table association query |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111472567.5A CN114036241A (en) | 2021-12-06 | 2021-12-06 | Data increment synchronization method based on multi-table association query |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114036241A true CN114036241A (en) | 2022-02-11 |
Family
ID=80140004
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111472567.5A Pending CN114036241A (en) | 2021-12-06 | 2021-12-06 | Data increment synchronization method based on multi-table association query |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114036241A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117171262A (en) * | 2023-08-09 | 2023-12-05 | 飞算数智科技(深圳)有限公司 | Data synchronization method and device, storage medium and electronic equipment |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108376154A (en) * | 2018-02-07 | 2018-08-07 | 平安普惠企业管理有限公司 | System base table synchronous method, device, computer equipment and storage medium |
-
2021
- 2021-12-06 CN CN202111472567.5A patent/CN114036241A/en active Pending
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108376154A (en) * | 2018-02-07 | 2018-08-07 | 平安普惠企业管理有限公司 | System base table synchronous method, device, computer equipment and storage medium |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117171262A (en) * | 2023-08-09 | 2023-12-05 | 飞算数智科技(深圳)有限公司 | Data synchronization method and device, storage medium and electronic equipment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8688659B2 (en) | Method for indexed-field based difference detection and correction | |
| JP4856344B2 (en) | System and method for displaying and maintaining redundant data sets utilizing DNA transmission (transmission) and transcription techniques | |
| CN101064630B (en) | Data synchronization method and system | |
| US7690000B2 (en) | Metadata journal for information technology systems | |
| KR100825720B1 (en) | File management method in file system and metadata server for the same | |
| CN106462592A (en) | Systems and methods to optimize multi-version support in indexes | |
| US20100179954A1 (en) | Quick Mass Data Manipulation Method Based on Two-Dimension Hash | |
| CN101004744A (en) | System and method for synchronizing indexes of remote files at multiple points | |
| KR102038529B1 (en) | System for processing real-time data modification of in-memory database | |
| US20070282878A1 (en) | System and method for online reorganization of a database using flash image copies | |
| CN106815326A (en) | A kind of system and method for detecting dereliction key data table uniformity | |
| CN114036241A (en) | Data increment synchronization method based on multi-table association query | |
| CN107124479A (en) | A kind of domain name multi-line intelligently parsing method based on radix tree | |
| US20080097971A1 (en) | Peer-to-peer based secondary key search method and system for cluster database | |
| CN107169003B (en) | Data association method and device | |
| WO2022127866A1 (en) | Data processing method and apparatus, and electronic device and storage medium | |
| CN109246102B (en) | System and method for supporting large-scale authentication data rapid storage and retrieval | |
| JP5287071B2 (en) | Database management system and program | |
| CN113901131A (en) | Index-based on-chain data query method and device | |
| US7016906B1 (en) | Data processing method and apparatus employing OLE DB and having dual schema and auto update features | |
| CN116010359A (en) | Method and system for storing and inquiring data based on log | |
| CN114003660B (en) | Method and device for efficiently synchronizing real-time data to click House based on flash | |
| CN109800233A (en) | A kind of big data fusion searching method | |
| CN113032368A (en) | Data migration method and device, storage medium and platform | |
| US20060238801A1 (en) | System and method for transmitting data quickly between a client and a server |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |