CN113220785A - Database change record synchronization method and device based on primary key comparison - Google Patents
Database change record synchronization method and device based on primary key comparison Download PDFInfo
- Publication number
- CN113220785A CN113220785A CN202110555446.0A CN202110555446A CN113220785A CN 113220785 A CN113220785 A CN 113220785A CN 202110555446 A CN202110555446 A CN 202110555446A CN 113220785 A CN113220785 A CN 113220785A
- Authority
- CN
- China
- Prior art keywords
- database
- record
- primary key
- sql statement
- synchronization
- 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
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/273—Asynchronous replication or reconciliation
-
- 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
- G06F16/235—Update request formulation
-
- 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
- G06F16/2358—Change logging, detection, and notification
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a database change record synchronization method and device based on primary key comparison, wherein the method comprises the following steps: acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database; and acquiring an incremental record of the source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in the target database. Compared with the prior art, the method and the system have the advantages that the main key records of the source database and the target database are obtained, the synchronization of deleted records between the databases is realized on the basis of the main key comparison, and the synchronization of the incremental records between the databases is realized by using the conversion SQL sentences after the incremental records of the source database are obtained, so that the data synchronization between heterogeneous databases is realized.
Description
Technical Field
The invention relates to the technical field of database synchronization, in particular to a database change record synchronization method and device based on primary key comparison.
Background
The principle of database synchronization is to capture changes in the source database and then synchronize the changes to the destination database. At present, methods for capturing source database changes mainly include a log method, a snapshot method, a trigger method, an API method, a shadow table method, a timestamp method, a state marking method and the like, and all the methods have respective advantages and disadvantages.
The log method is to directly obtain the object change sequence by analyzing the log of the source database. The method is convenient and quick, but many database manufacturers do not disclose the format of the log, and cannot directly access the log, and different databases have great difference in the details of the recorded log, thereby bringing great difficulty to the synchronization of heterogeneous databases.
The snapshot method defines an instant image of a source database object at a certain moment as a snapshot, and directly copies and updates the snapshot into a target database. The method is the simplest, does not depend on a special mechanism, does not occupy additional system resources, is very easy to manage and operate, and has the defects that specific changes cannot be known, and the efficiency is low because the table with large data volume needs to transmit the full data.
The trigger method is to create an add/delete DML (data Manipulating language) trigger for the database object, and the trigger is automatically activated whenever the DML operation is performed on the database object, and the SQL statement of the operation main part transmits the change to the target database. The method needs to set a very complicated trigger for a more complicated synchronization task, has great influence and dependence on a source database, and is difficult to maintain.
The API method is characterized in that an API middleware is introduced between an application program and a database, and when the operation of the application program causes the change of a source database, the middleware records a change sequence and transmits the change sequence to a target database. The method relies on middleware, and has a great defect that the method cannot be used for the change caused by directly operating the source database through a database terminal without an application program.
The shadow table method is to establish a copy (shadow table) for the object to be synchronized, when the change occurs, the change sequence can be obtained only by comparing the current table with the copy, and then the change sequence is synchronized to the target database. The method needs to create a shadow table, has large influence on a source database, and needs to scan the whole source table and the shadow table every time the change is acquired, so that the efficiency is very low.
The time stamp method is that a time stamp field is added to the object to be synchronized, and the change data is obtained according to the time stamp. According to the method, a timestamp field needs to be added, a timestamp needs to be maintained every time data change, the application of a source database is greatly influenced, and true deletion data cannot be captured.
And the state marking method is to add a state marking field to the object to be synchronized and obtain the changed data according to the state marking. According to the method, a state mark field needs to be added, the state mark needs to be maintained every time data change, the application of a source database is greatly influenced, and true deletion data cannot be captured.
Disclosure of Invention
The invention aims to overcome the defects of the prior art and provide a database change record synchronization method and device based on main key comparison, which are used for acquiring main key records of a source database and a target database, realizing deletion record synchronization between the databases based on the main key comparison, and realizing increase record synchronization between the databases by using a conversion SQL statement after acquiring increase record of the source database, thereby realizing data synchronization between heterogeneous databases.
The purpose of the invention can be realized by the following technical scheme:
a database change record synchronization method based on primary key comparison comprises the following steps:
acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database;
and acquiring an incremental record of a source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in a target database.
Further, the user-defined main key of the source database is used for obtaining the SQL statement and obtaining the main key record from the source database, the user-defined main key of the destination database is used for obtaining the SQL statement and obtaining the main key record from the destination database, and the source database and the destination database may be heterogeneous databases, so that the user-defined main key is used for obtaining the SQL statement and obtaining the main key record respectively.
Further, the primary key records of the source database are stored in sequence in the SA structure, and the primary key records of the destination database are stored in sequence in the TA structure, so that the time consumed for searching objects in the SA and the TA is shortened by the sequential storage.
Further, the SA structure and the TA structure are array structures.
Further, a user-defined change screening SQL statement is used for obtaining the change records of the source database.
Furthermore, a conversion SQL statement template is preset based on the formats of the source database and the target database, and the incremental records are substituted into the conversion SQL statement template to obtain the conversion SQL statement.
Further, the steps of checking the objects in the SA and the TA, and generating the delete SQL statement based on the objects existing in the TA and not existing in the SA are specifically as follows:
obtaining an object from the SA, searching whether an object same as the object exists in the TA, if so, deleting the object same as the object in the TA, otherwise, repeating the step until the search of all the objects in the SA is completed;
and if the TA is empty, not generating a deletion SQL statement, otherwise, respectively generating the deletion SQL statement based on each object left in the TA, and executing the deletion SQL statement in the target database.
Furthermore, based on the format of the target database, a deletion SQL statement template is preset, and the object is substituted into the deletion SQL statement template to obtain a deletion SQL statement.
A database change record synchronization device based on primary key comparison comprises a synchronization module, a source database and a destination database, wherein the synchronization module is used for synchronizing database change records of the source database and the destination database and executing the following steps:
acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database;
and acquiring an incremental record of a source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in a target database.
The SA structure and the TA structure are stored in the memory of the server where the synchronization module is located, the primary key comparison process is carried out in the memory of the server where the synchronization module is located, and both the source database and the target database are non-invasive.
The synchronization module comprises a memory and a processor, and the database change record synchronization method based on the primary key comparison is stored in the memory of the synchronization module in the form of a computer program and is executed by the processor.
Compared with the prior art, the method and the system have the advantages that the main key records of the source database and the target database are obtained, the synchronization of deleted records between the databases is realized on the basis of the main key comparison, and the synchronization of the added records between the databases is realized by using the conversion SQL sentences after the added records of the source database are obtained, so that the data synchronization between the heterogeneous databases is realized quickly and efficiently.
Drawings
FIG. 1 is a flow chart of a database change record synchronization method;
fig. 2 is a schematic structural diagram of a database change record synchronization device.
Detailed Description
The invention is described in detail below with reference to the figures and specific embodiments. The present embodiment is implemented on the premise of the technical solution of the present invention, and a detailed implementation manner and a specific operation process are given, but the scope of the present invention is not limited to the following embodiments.
Example 1:
a database change record synchronization method based on primary key comparison is disclosed, as shown in FIG. 1, and includes the following steps:
acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database;
and acquiring an incremental record of the source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in the target database.
A database change record synchronizing device based on primary key comparison is disclosed, as shown in FIG. 2, and comprises a synchronizing module, a source database and a destination database, wherein the synchronizing module comprises a memory and a processor, and a database change record synchronizing method based on primary key comparison is stored in the memory of the synchronizing module in the form of a computer program and executed by the processor, and the database change record synchronizing method based on primary key comparison is realized when the database change record synchronizing method is executed.
For a source database and a target database, all the adding, deleting and modifying operations of the target database are carried out according to the source database, if the deleting operations are carried out in the source database, the data which are really deleted cannot be captured by using the change screening SQL statements, therefore, the objects which exist in TA and do not exist in SA are considered to be the objects deleted in the source database through the comparison of the main keys, the SQL statements are generated and deleted based on the objects and executed in the target database, and the synchronization of the deleted records of the source database and the target database can be realized.
In the embodiment, a synchronization program is used for realizing the synchronization of the change records of the source database and the target database, the synchronization program runs on a server, and when the synchronization program is executed, the database change record synchronization method based on the primary key comparison is realized, and in the synchronization program, a source database object is defined as sdb and a target database object is defined as tdb. Defining json object keyNodes as the main key array of the source database, namely SA, and defining extNodes as the main key array of the destination database, namely TA.
The method comprises the steps of pre-storing a self-defined source database main key to obtain SQL statement keySQL, pre-defining a self-defined target database main key to obtain SQL statement extSQL, pre-defining a deleted SQL statement template delSQL, pre-defining a change screening SQL statement selectSQL, defining data nodes to store obtained enhancement records of a source database, and pre-defining a conversion SQL statement template targetSQL.
After finding out the object which exists in the TA and does not exist in the SA, substituting the object into a deleted SQL statement template delSQL to obtain a deleted SQL statement which can be executed in a target database; after the incremental records of the source database are obtained, each incremental record is substituted into the conversion SQL statement template targetSQL, so that the conversion SQL statement which can run in the target database is obtained, all SQL databases can be supported, and data synchronization among heterogeneous databases is realized.
The following is an example of a C + + language implementation:
in this embodiment, the primary key records are stored in the array, the primary key records in the SA and the TA are arranged in sequence, the SA is traversed, and for each object in the SA, if the same object is found in the TA, the same object in the TA is deleted.
Firstly, deleting the objects existing in the SA and the TA at the same time in the TA, so that the objects remaining in the TA are the objects existing in the TA and not existing in the SA, and the process of generating the delete SQL statement based on the objects remaining in the TA is as follows:
through the steps, the deletion record synchronization of the source database and the target database is completed, and the modification records of the source database and the target database are synchronized, wherein the modification records comprise newly added records and updated records.
// incremental record synchronization
dataNodes ═ sdb. querysql (selectSQL); modification records for// select SQL screening source database for (int r ═ 0; r < datanodes. size (); r + +)
{
Json::Value row＝dataNodes[r]；
for(auto it＝row.begin()；it！＝row.end()；++it)
{
targetSQL＝replace(targetSQL,string("$")+it.name()+string("$"),row[it.name()].asString())；
}
tdb.ExeSQL(targetSQL)；
}
By the method and the device, data synchronization between heterogeneous databases can be realized quickly and efficiently, the primary key comparison is that the primary key records of the databases are stored in the SA and the TA, and the source database and the target database are all non-invasive.
The foregoing detailed description of the preferred embodiments of the invention has been presented. It should be understood that numerous modifications and variations could be devised by those skilled in the art in light of the present teachings without departing from the inventive concepts. Therefore, the technical solutions available to those skilled in the art through logic analysis, reasoning and limited experiments based on the prior art according to the concept of the present invention should be within the scope of protection defined by the claims.
Claims (10)
1. A database change record synchronization method based on primary key comparison is characterized by comprising the following steps:
acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database;
and acquiring an incremental record of a source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in a target database.
2. The primary key comparison-based database change record synchronization method according to claim 1, characterized in that a self-defined primary key of a source database is used to obtain SQL statements and primary key records from the source database.
3. The primary key comparison-based database change record synchronization method according to claim 1, wherein a user-defined destination database primary key is used to obtain SQL statements to obtain primary key records from a destination database.
4. The primary key comparison-based database change record synchronization method according to claim 1, wherein the primary key records of the source database are stored in sequence in the SA structure, and the primary key records of the destination database are stored in sequence in the TA structure.
5. The primary key comparison-based database change record synchronization method as claimed in claim 4, wherein the SA structure and the TA structure are array structures.
6. The primary key comparison-based database change record synchronization method according to claim 1, characterized in that custom change screening SQL statements are used to obtain change records of the source database.
7. The method according to claim 1, wherein the step of checking the objects in the SA and the TA, and the step of generating the delete SQL statement based on the objects existing in the TA and not existing in the SA comprises:
obtaining an object from the SA, searching whether an object same as the object exists in the TA, if so, deleting the object same as the object in the TA, otherwise, repeating the step until the search of all the objects in the SA is completed;
and if the TA is empty, not generating a deletion SQL statement, otherwise, respectively generating the deletion SQL statement based on each object left in the TA, and executing the deletion SQL statement in the target database.
8. A database change record synchronization device based on primary key comparison, characterized in that, based on the database change record synchronization method as claimed in any one of claims 1 to 7, the device comprises a synchronization module, a source database and a destination database, the synchronization module is used for synchronizing the database change records of the source database and the destination database, and the following steps are executed:
acquiring and storing a main key record of a source database in an SA structure, acquiring and storing a main key record of a target database in a TA structure, checking objects in the SA and the TA, generating a deleted SQL statement based on the objects existing in the TA and not existing in the SA, and executing the deleted SQL statement in the target database;
and acquiring an incremental record of a source database, wherein the incremental record comprises a newly added record and an updated record, generating a conversion SQL statement based on the incremental record, and executing the conversion SQL statement in a target database.
9. The primary key comparison-based database change record synchronization device according to claim 8, wherein the SA structure and the TA structure are stored in a memory of a server where the synchronization module is located.
10. The primary key comparison-based database change record synchronization device according to claim 8, wherein the synchronization module comprises a memory and a processor, and the primary key comparison-based database change record synchronization method is stored in the memory of the synchronization module in the form of a computer program and executed by the processor.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110555446.0A CN113220785A (en) | 2021-05-21 | 2021-05-21 | Database change record synchronization method and device based on primary key comparison |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110555446.0A CN113220785A (en) | 2021-05-21 | 2021-05-21 | Database change record synchronization method and device based on primary key comparison |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113220785A true CN113220785A (en) | 2021-08-06 |
Family
ID=77093585
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110555446.0A Pending CN113220785A (en) | 2021-05-21 | 2021-05-21 | Database change record synchronization method and device based on primary key comparison |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113220785A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113760910A (en) * | 2021-08-31 | 2021-12-07 | 中国银联股份有限公司 | Data synchronization method and device |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102346775A (en) * | 2011-09-26 | 2012-02-08 | 苏州博远容天信息科技有限公司 | Method for synchronizing multiple heterogeneous source databases based on log |
| CN104750755A (en) * | 2013-12-31 | 2015-07-01 | 阿里巴巴集团控股有限公司 | Method and system for recovering data after switching between main database and standby database |
| CN105930493A (en) * | 2016-05-04 | 2016-09-07 | 北京思特奇信息技术股份有限公司 | Method and system for data synchronization between different databases |
| CN106815326A (en) * | 2016-12-28 | 2017-06-09 | 中国民航信息网络股份有限公司 | A kind of system and method for detecting dereliction key data table uniformity |
| CN109033193A (en) * | 2018-06-16 | 2018-12-18 | 温州职业技术学院 | A kind of database structure and its method of data synchronization |
| CN111143470A (en) * | 2019-12-27 | 2020-05-12 | 中国银行股份有限公司 | Cross-platform database data synchronization comparison method and device |
| CN111597257A (en) * | 2020-05-09 | 2020-08-28 | 远光软件股份有限公司 | Database synchronization method and device, storage medium and terminal |
| CN111930850A (en) * | 2020-09-24 | 2020-11-13 | 腾讯科技（深圳）有限公司 | Data verification method and device, computer equipment and storage medium |
| WO2020259149A1 (en) * | 2019-06-26 | 2020-12-30 | 中兴通讯股份有限公司 | System and method for implementing incremental data comparison |
-
2021
- 2021-05-21 CN CN202110555446.0A patent/CN113220785A/en active Pending
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102346775A (en) * | 2011-09-26 | 2012-02-08 | 苏州博远容天信息科技有限公司 | Method for synchronizing multiple heterogeneous source databases based on log |
| CN104750755A (en) * | 2013-12-31 | 2015-07-01 | 阿里巴巴集团控股有限公司 | Method and system for recovering data after switching between main database and standby database |
| CN105930493A (en) * | 2016-05-04 | 2016-09-07 | 北京思特奇信息技术股份有限公司 | Method and system for data synchronization between different databases |
| CN106815326A (en) * | 2016-12-28 | 2017-06-09 | 中国民航信息网络股份有限公司 | A kind of system and method for detecting dereliction key data table uniformity |
| CN109033193A (en) * | 2018-06-16 | 2018-12-18 | 温州职业技术学院 | A kind of database structure and its method of data synchronization |
| WO2020259149A1 (en) * | 2019-06-26 | 2020-12-30 | 中兴通讯股份有限公司 | System and method for implementing incremental data comparison |
| CN111143470A (en) * | 2019-12-27 | 2020-05-12 | 中国银行股份有限公司 | Cross-platform database data synchronization comparison method and device |
| CN111597257A (en) * | 2020-05-09 | 2020-08-28 | 远光软件股份有限公司 | Database synchronization method and device, storage medium and terminal |
| CN111930850A (en) * | 2020-09-24 | 2020-11-13 | 腾讯科技（深圳）有限公司 | Data verification method and device, computer equipment and storage medium |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113760910A (en) * | 2021-08-31 | 2021-12-07 | 中国银联股份有限公司 | Data synchronization method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109241175B (en) | Data synchronization method and device, storage medium and electronic equipment | |
| CN109800222B (en) | HBase secondary index self-adaptive optimization method and system | |
| JP6668442B2 (en) | Method and system for detecting data replication and synchronization errors of heterogeneous database through SQL packet analysis | |
| CN110262929B (en) | Method for ensuring consistency of copying affairs and corresponding copying device | |
| CN111209344A (en) | Data synchronization method and device | |
| CN112286941B (en) | Big data synchronization method and device based on Binlog + HBase + Hive | |
| CN109947796B (en) | Caching method for query intermediate result set of distributed database system | |
| CN111324607B (en) | SQL statement multiplexing method and device | |
| CN114036119A (en) | Data synchronization method based on button and database log | |
| CN112231321B (en) | Oracle secondary index and index real-time synchronization method | |
| CN114691704A (en) | Metadata synchronization method based on MySQL binlog | |
| CN112231407A (en) | DDL synchronization method, device, equipment and medium of PostgreSQL database | |
| CN112000649B (en) | Method and device for synchronizing incremental data based on map reduce | |
| CN113220785A (en) | Database change record synchronization method and device based on primary key comparison | |
| CN109145155A (en) | High-concurrency warehousing processing method for mass remote sensing image metadata | |
| CN110222121A (en) | A kind of SQL Server database increment synchronization realization method and system based on CDC mode | |
| CN111858181A (en) | Method and tool for realizing mirror synchronization of cross-region containers | |
| CN111159020A (en) | Method and device applied to synchronous software test | |
| CN113792026A (en) | Deployment method and device of database script and computer readable storage medium | |
| CN112416944A (en) | Method and equipment for synchronizing service data | |
| CN116501700B (en) | APP formatted file offline storage method, device, equipment and storage medium | |
| CN112685431B (en) | Asynchronous caching method, device, system, electronic equipment and storage medium | |
| CN114840497B (en) | Line migration preprocessing method, system and device for database and storage medium | |
| CN111753045B (en) | Hive two-level full-text index technical method and system based on elastic search | |
| CN115658815A (en) | CDC (control data center) -based data synchronization method |
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 |