A cross-database data automatic synchronization method and system

Abstract

The present application relates to computer data processing technical field, especially to a kind of cross-library data automatic synchronization method and system, method includes the following steps: S1, parse synchronization configuration file and query checkpoint metadata table, generate and include source database connection address, source data table identification set, field mapping rule, primary key rule and the synchronization task instruction of the current breakpoint time stamp corresponding to each source data table;S2, according to the synchronization task instruction, establish connection with source database, respectively from the source data table identification set corresponding to each source data table Incremental record set is extracted, which update time stamp is greater than the corresponding current breakpoint time stamp.The present application, by using only the read-only authority of source database to obtain synchronization configuration and incremental data, then dispenses with source system architecture modification and high custom interface development, directly realizes the low-cost elimination of data island between heterogeneous systems and data automation circulation.

Description

Technical Field

[0001] This invention relates to the field of computer data processing technology, and in particular to a method and system for automatic cross-database data synchronization. Background Technology

[0002] As enterprises deepen their digital transformation, various business departments often establish relatively independent information management systems based on their own needs. Because these systems were built at different times and developed by different vendors, they typically use independent heterogeneous database architectures at the underlying level. This results in business data being scattered across isolated systems, forming "data silos" that make it difficult to meet the enterprise's business needs for cross-system data integration and centralized flow.

[0003] Currently, cross-system data synchronization typically involves manual periodic export and import or commissioning the original system vendor to develop customized data interfaces. However, manual operation is labor-intensive and data flow is delayed, making it prone to data omissions or entry errors. On the other hand, purchasing customized interfaces from the original vendor faces high secondary development costs and usually requires a certain degree of invasive modification to the source system architecture. This not only results in a long implementation cycle but also makes subsequent functional expansion and maintenance susceptible to dependence on external suppliers. Summary of the Invention

[0004] To overcome the above shortcomings, this invention provides a method and system for automatic cross-database data synchronization, aiming to improve the problem that cross-system data synchronization usually relies on manual periodic export and import or commissioning the original system manufacturer to develop customized data interfaces.

[0005] In a first aspect, the present invention provides the following technical solution: a method for automatic cross-database data synchronization, comprising the following steps: S1. Parse the synchronization configuration file and query the checkpoint metadata table to generate a synchronization task instruction containing the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. S2. Establish a connection with the source database according to the synchronization task instruction, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. S3. Calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint. Compare the change fingerprint with the synchronization fingerprint index, filter matching records and mark the change type of the remaining records to obtain a list of changes to be synchronized. S4. Obtain the primary and foreign key dependencies involved in the list of changes to be synchronized, and sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and the change type to generate an ordered batch processing queue. S5. According to the ordered batch processing queue, perform data transformation and cleaning on the records in the queue based on the field mapping rules to generate standardized data batches; S6. Write the standardized data batch into the target database and update the checkpoint metadata table and the synchronization fingerprint index.

[0006] Preferably, the generation of synchronization task instructions specifically includes the following steps: Parse the synchronization configuration file to extract the source database connection address, source data table identifier set, field mapping rules, and primary key rules; The checkpoint metadata table is queried based on each source data table identifier in the extracted source data table identifier set to obtain the breakpoint timestamp value corresponding to each source data table. Each breakpoint timestamp value is used as the corresponding current breakpoint timestamp, and combined with the extracted source database connection address, the source data table identifier set, the field mapping rule, and the primary key rule to generate a synchronization task instruction.

[0007] Preferably, the step of extracting the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set specifically includes the following steps: Initiate a session request to the source database based on the source database connection address; After establishing the session corresponding to the session request, for each of the source data tables, a query statement is generated using the current breakpoint timestamp corresponding to the source data table as the filtering condition. Execute the query statement and read the data results returned by the query statement, and summarize the data results as an incremental record set.

[0008] Preferably, the process of filtering and matching records and marking the change types of the remaining records to obtain the list of changes to be synchronized specifically includes the following steps: Traverse the incremental record set, extract the currently traversed record, calculate the primary key, the update timestamp, and the field content of the currently traversed record, and generate a change fingerprint; The changed fingerprint is compared with the synchronized fingerprint index. If the changed fingerprint already exists in the synchronized fingerprint index, the record corresponding to the changed fingerprint is filtered out. If the primary key of the currently traversed record exists in the synchronization fingerprint index but the corresponding change fingerprint is different, then the change type of the currently traversed record is marked as an update type; if the primary key of the currently traversed record does not exist in the synchronization fingerprint index, then the change type of the currently traversed record is marked as an addition type, and the marked records are summarized to generate a list of changes to be synchronized.

[0009] Preferably, the step of sorting and dividing the list of changes to be synchronized into blocks to generate an ordered batch processing queue specifically includes the following steps: A data table dependency graph is constructed based on the primary and foreign key dependencies, and a topology sorting algorithm is used to determine the synchronous write level. Within the same synchronous write level, newly added type records in the list of changes to be synchronized are arranged before updated type records; According to the preset batch capacity threshold, the sorted list of changes to be synchronized is divided into blocks to generate an ordered batch processing queue.

[0010] Preferably, the step of constructing a data table dependency graph based on the primary and foreign key dependencies and determining the synchronous write level using a topological sorting algorithm specifically includes the following steps: The data tables in the list of changes to be synchronized are used as graph nodes, and the primary and foreign key dependencies are used as directed edges to generate a data table dependency graph. Perform an in-degree traversal on the data table dependency graph, and identify the graph nodes that are not collected to the synchronous write level after the in-degree traversal as a set of circular dependency nodes. The ring break weight is calculated based on the number of foreign key fields, the nullable identifier of the foreign key fields, and the number of records corresponding to each directed edge in the ring dependency node set. The directed edges whose ring break weight reaches the preset ring break threshold are marked as delayed association edges. Separate the foreign key field corresponding to the delayed association edge from the first write field set, generate a primary key advance batch containing the primary key field and non-delayed fields, and generate a foreign key backfill batch containing the foreign key field corresponding to the delayed association edge. Perform a topological sort on the data table dependency graph after removing the delayed associated edges to generate a synchronous write hierarchy, and arrange the foreign key backfill batch after the primary key-first batch.

[0011] Preferably, in step S5, generating standardized data batches specifically includes the following steps: Extract the original data type of the records in the ordered batch processing queue according to the field mapping rules; The original data type is mapped to the standard data type corresponding to the target database, and default value filling and format cleaning are performed on null values ​​and non-standard formats; Based on the cleaned records, a standardized data batch corresponding to the target database table structure is generated.

[0012] Preferably, in step S6, writing the standardized data batch into the target database and updating the checkpoint metadata table and the synchronization fingerprint index specifically includes the following steps: The standardized data batch is submitted to the target database using conflict update syntax; After the submission is completed, extract the upper bound of the batch update timestamp in the standardized data batch; The upper bound of the batch update timestamp is overwritten to the checkpoint metadata table, and the change fingerprint corresponding to each record submitted this time is updated to the synchronization fingerprint index.

[0013] Secondly, the present invention provides the following technical solution: an automatic cross-database data synchronization system, the system comprising: The task generation module is used to parse the synchronization configuration file and query the checkpoint metadata table to generate synchronization task instructions that include the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. The data extraction module is used to establish a connection with the source database according to the synchronization task instruction, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. The change identification module is used to calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint, compare the change fingerprint with the synchronization fingerprint index, filter matching records and mark the change type of the remaining records to obtain a list of changes to be synchronized. The queue orchestration module is used to obtain the primary and foreign key dependencies involved in the list of changes to be synchronized, sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and the change type, and generate an ordered batch processing queue. The data conversion module is used to convert and clean the records in the ordered batch processing queue according to the field mapping rules, and generate standardized data batches. The data writing module is used to write the standardized data batches into the target database and update the checkpoint metadata table and the synchronization fingerprint index.

[0014] The present invention has the following beneficial effects: 1. In this invention, synchronous configuration and incremental data are obtained by using only read-only permissions of the source database, thus eliminating the need for source system architecture modification and expensive custom interface development, and directly realizing the low-cost elimination of data silos between heterogeneous systems and automated data flow.

[0015] 2. In this invention, incremental records are extracted based on breakpoint timestamps and redundant data that has not undergone substantial changes are filtered out by comparing hash change fingerprints. Then, cross-database format conversion and on-premises update operations are performed only on the content that has actually changed, which effectively reduces network transmission consumption and write load on the target database.

[0016] 3. In this invention, a data table dependency graph is constructed and the loop breaking weight is calculated to resolve complex primary and foreign key circular reference relationships. Then, the multi-table related data is arranged into an ordered batch processing queue that strictly conforms to the logical order, thereby avoiding constraint conflict errors when writing to the target database in batches and maintaining the structural integrity of the data. Attached Figure Description

[0017] Figure 1 This is a flowchart of a cross-database automatic data synchronization method proposed in this invention; Figure 2 This is an architecture diagram of an automatic cross-database data synchronization system proposed in this invention. Detailed Implementation

[0018] The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0019] Example 1:

[0020] In a first embodiment of the present invention, the present invention provides a method for automatic cross-database data synchronization, such as... Figure 1 As shown, it includes the following steps: S1. Parse the synchronization configuration file and query the checkpoint metadata table to generate a synchronization task instruction containing the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. Furthermore, in S1, generating the synchronization task instruction specifically includes the following steps: Parse the synchronization configuration file to extract the source database connection address, source data table identifier set, field mapping rules, and primary key rules; Based on the source data table identifiers in the extracted source data table identifier set, query the checkpoint metadata table to obtain the breakpoint timestamp value corresponding to each source data table; The timestamp values ​​of each breakpoint are used as the corresponding timestamps of the current breakpoint. These are then combined with the extracted source database connection address, source data table identifier set, field mapping rules, and primary key rules to generate a synchronization task instruction.

[0021] Specifically, in the initial stage of automatic cross-database data synchronization, the system first loads and parses a pre-written YAML-formatted synchronization configuration file. This configuration file, in text form, solidifies the static synchronization strategy between heterogeneous databases. The system parses this configuration file using a file reading component, extracting the source database connection address, source table identifier set, field mapping rules, and primary key rules. In one feasible implementation, the configuration file stores the source database connection address in the `source_host` field, the port number in the `source_port` field, access credentials in the `source_user` and `source_password` fields, and a table configuration array in the `tables` field. Each element in this array corresponds to a business table, containing a `table_name` subfield for recording the table name, a `primary_key` subfield for recording the primary key field name, and a `field_mapping` subfield for recording the field mapping relationship. After system parsing, the values ​​corresponding to `source_host` (e.g., 192.168.1.100) and `source_port` (e.g., 1433) are used as the source database connection addresses. The `table_name` values ​​in the `tables` array are aggregated into a set of source data table identifiers, such as the crew schedule table and the safety training record table. The `primary_key` values ​​are aggregated into primary key rules, and the `field_mapping` values ​​are aggregated into field mapping rules. These field mapping rules are stored in memory in a dictionary structure with source field names as keys and target field names and target data types as values, for retrieval and use in subsequent data transformation steps.

[0022] After extracting the aforementioned static configuration parameters, the system initiates a query request to the underlying storage engine of the target database based on the source table identifiers in the source table identifier set, and reads the checkpoint metadata table. The checkpoint metadata table is specifically used to persistently store the latest time state of each business table during its last successful write to the target database in historical synchronization tasks. In one feasible implementation, this table contains a `table_name` column and a `checkpoint_ts` column, where the `table_name` column stores the business table identifier and serves as the primary key, and the `checkpoint_ts` column stores the breakpoint timestamp value corresponding to that business table. The system performs a retrieval of the checkpoint metadata table using the source table identifier as a filter condition, retrieving the breakpoint timestamp value corresponding to each source table one by one. If a source table identifier has a corresponding record in the checkpoint metadata table, the value of its `checkpoint_ts` column is read directly. If the source table identifier does not have a corresponding record in the checkpoint metadata table, it means that the business table has not undergone any synchronization operation. In this case, the system assigns a preset initial time constant as the breakpoint timestamp value. In practice, this initial time constant is set to January 1, 1970, 00:00:00. A new record is created in the checkpoint metadata table with the source table identifier `table_name` and the initial time constant `checkpoint_ts`, to ensure that subsequent synchronization tasks can update it normally. Thus, the system obtains the dynamic starting time for incremental data filtering for each business table.

[0023] Subsequently, the system uses the timestamp values ​​of each breakpoint as the corresponding current breakpoint timestamp, and combines them in a structured manner with the source database connection address, source data table identifier set, field mapping rules, and primary key rules to generate a synchronization task instruction. This combination and conversion process is implemented through a task instruction construction algorithm model, the formula of which is defined as follows: In this algorithm formula, This represents a task instruction construction function, used to aggregate and encapsulate various input parameters into synchronous task instructions; This indicates the input source database connection address, which in this embodiment corresponds to the connection parameters of the SQL Server database. Specifically, the values ​​are address 192.168.1.100 and port 1433. This represents the set of identifiers for the input source data table, and ,in Indicates the first The identifier of Zhang's business form, , In this embodiment, the number of tables in the source data table identifier set is used to identify the number of tables. Corresponding crew schedule, Corresponding safety training record form; Indicates the mapping rules for the input fields; Indicates the primary key rule entered; This represents a mapping set consisting of table identifiers and breakpoint timestamps, and ,in Indicates table identifier The corresponding current breakpoint timestamp value, in this embodiment The value is the timestamp of the last successful synchronization of the crew schedule, such as 2024-05-01 02:30:00; This represents the final generated synchronization task instruction, which uses a dictionary structure to carry all the above parameters and passes them to subsequent data extraction steps. The system uses this algorithm to aggregate and encapsulate isolated static environment configuration parameters with dynamic synchronization watermark data, constructing a synchronization task instruction containing a complete execution context.

[0024] This step decouples business synchronization rules from core code and provides standardized parameter inputs for subsequent incremental data extraction by obtaining accurate breakpoint timestamps.

[0025] S2. Establish a connection with the source database according to the synchronization task instruction, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. Furthermore, in S2, extracting the set of incremental records whose update timestamps are greater than the corresponding current breakpoint timestamps from each source data table corresponding to the source data table identifier set specifically includes the following steps: Initiate a session request to the source database based on the source database connection address; After establishing the session corresponding to the session request, generate query statements for each source data table, using the current breakpoint timestamp of the source data table as the filtering condition. Execute the query statement and read the data results returned by the query statement, and summarize the data results as an incremental record set.

[0026] Specifically, after receiving the synchronization task instruction generated in the previous stage, the system first parses the instruction to extract the source database connection address. Based on the extracted source database connection address, the system's underlying communication component initiates a session request to establish a connection to the source database's listening port according to the standard communication protocol. To prevent system threads from being blocked for extended periods due to network jitter across network segments, the system introduces a connection timeout constant when initiating the session request. In one feasible implementation, this connection timeout constant is set to 30 seconds. Once the network-level handshake verification and credential verification are successful, the system successfully establishes the session corresponding to the session request and allocates a persistent database cursor object in memory, which serves as the communication channel for subsequent reading of underlying business data.

[0027] While maintaining the session connection, the system sequentially initiates a data scanning process for each source data table included in the source data table identifier set. The system iterates through the extracted source data table identifiers and generates query statements using the current breakpoint timestamp corresponding to each source data table as the core filtering condition. The update timestamp used for comparison originates from the record modification time field agreed upon in the source data table. In one feasible implementation, this field is the update_time column in the source data table. This column is automatically refreshed in the source business system with each record addition or modification, and its field name can be specified by the configuration parameters carried in the synchronization task instruction to adapt to the naming differences of different business tables. The system assembles the table identifier and the current breakpoint timestamp into a query statement. In this embodiment, the query statement uses SELECT as the main body and WHERE clauses where update_time is greater than the current breakpoint timestamp as the filtering condition. For example, for a flight attendant schedule table, a query statement is generated to find all records where update_time is greater than 2:30:00 AM on May 1, 2024.

[0028] The above data extraction process is implemented through an internally constructed data extraction algorithm model, the formula of which is defined as follows: In this algorithm formula, This represents the final set of incremental records output by the system. This represents the total number of tables in the source data table identifier set; For table traversal index and ; The union operator represents a set and is used to combine and aggregate data results from multiple independent business tables. This indicates the context parameters of the currently established source database session; Indicates the first In this embodiment, the identifier of Zhang Yuan's data table is specifically associated with the crew schedule in the field of subway crew management technology; Represents the source data table The corresponding current breakpoint timestamp parameter is specifically set to the time point 2:30:00 on May 1, 2024 in this embodiment; This represents the query execution function, whose internal logic is to parse the input table identifier and timestamp into the aforementioned structured query statement, and then send the statement to the source database for execution through the session context.

[0029] The system executes the above query statement through a database cursor and reads the data results returned by the query statement from the source database disk or cache. Considering that the cumulative incremental data volume of a single business table may exceed the system's running memory capacity, the system adopts a cursor-based streaming batch reading mechanism when reading data results. That is, the cursor requests a fixed batch of records from the source database each time instead of loading all the data at once. The system pre-sets a single batch fetching threshold constant. In one feasible implementation, this single batch fetching threshold is set to 5000 records. The system cyclically fetches network data packets at a scale of 5000 records each time until all data results in the current source data table that meet the breakpoint timestamp filtering condition have been completely read. The system repeats the above generation and reading operations for each table in the source data table identifier set. Finally, the extracted data results are structurally assembled in the memory buffer, and the data results are summarized into an incremental record set containing data from multiple tables.

[0030] Timestamp filtering enabled accurate incremental acquisition of business data, avoiding the waste of computing resources caused by full table scans of the source database, and providing a reliable source of basic data for subsequent heterogeneous data transformation.

[0031] S3. Calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint. Compare the change fingerprint with the synchronization fingerprint index, filter matching records and mark the change type of the remaining records to obtain a list of changes to be synchronized. Furthermore, in S3, filtering matching records and marking the change types of the remaining records to obtain the list of changes to be synchronized specifically includes the following steps: Traverse the incremental record set, extract the currently traversed record, calculate the primary key, update timestamp, and field content of the currently traversed record, and generate a change fingerprint; The changed fingerprint is compared with the synchronous fingerprint index. If the changed fingerprint already exists in the synchronous fingerprint index, the record corresponding to the changed fingerprint is filtered out. If the primary key of the currently traversed record exists in the synchronized fingerprint index but the corresponding change fingerprint is different, then the change type of the currently traversed record is marked as an update type; if the primary key of the currently traversed record does not exist in the synchronized fingerprint index, then the change type of the currently traversed record is marked as an addition type, and the marked records are summarized to generate a list of changes to be synchronized.

[0032] Specifically, after obtaining the incremental record set, the system initializes an empty list structure of changes to be synchronized in memory, and simultaneously loads the persistent synchronization fingerprint index in the target database. The synchronization fingerprint index is represented in memory as a key-value dictionary structure, where the key is the primary key of the business record, and the value is the historical change fingerprint generated during historical synchronization. Existing fingerprints in this index are continuously accumulated and written during the data writing phase of each synchronization task. In one feasible implementation, if the system is running for the first time, the synchronization fingerprint index is empty. In this case, the primary keys of all records in the incremental record set do not exist in this index and will be uniformly marked as new records. The system initiates a traversal mechanism based on the primary key rule, sequentially extracting the currently traversed records from the incremental record set. In one feasible implementation, the primary key rule specifies a unique identifier field for different business tables; for example, specifying the shift number field as the primary key for a flight attendant scheduling table. For the extracted currently traversed records, the system reads their corresponding primary key value, update timestamp, and all business field content. To eliminate comparison errors caused by differences in the underlying field order across various system platforms, the system sorts all business field content lexicographically by the first letter of the field name and converts them into a standard string format. During the conversion process, to ensure the reproducibility of results obtained when the same record is used in calculations at different times, the system uniformly replaces null fields with fixed placeholders such as NULL strings, uniformly formats date type fields into strings of year, month, day, hour, minute, and second, and uniformly converts numeric type fields into fixed-point strings with trailing zeros removed.

[0033] The system then concatenates the primary key value, update timestamp, and processed business field content to generate a change fingerprint. This generation process is implemented using a cryptographic hash operation model, and its operation formula is defined as follows: In this calculation formula, This indicates the currently extracted traversal record; Representing records The final generated change fingerprint feature value; This represents a one-way hash function, which in one feasible implementation uses the secure hash algorithm SHA-256, and its output is a fixed 64-bit hexadecimal string constant. The string concatenation operator is used to merge isolated fields in serialization. Representing records The primary key string extracted according to the primary key rule is specifically taken as an actual shift number in the crew shift schedule table, such as D20240501001, in this embodiment. Representing records The update timestamp string, in this embodiment, is taken as 20240501023000; Representing records The sorted and concatenated business field content string corresponds in this embodiment to the concatenated value of driver employee number and route name in the shift schedule table, such as E1088# Line 1. The system uses this algorithm to compress lengthy and unstructured business data into a unique fixed-length feature value, serving as the absolute benchmark for data comparison.

[0034] After generating the change fingerprint, the system compares the newly generated change fingerprint with the pre-loaded synchronous fingerprint index, performing filtering and marking logic. The system first checks if the primary key of the currently traversed record exists in the key space of the synchronous fingerprint index. If the primary key already exists, the system further compares the fingerprint values ​​of the two. If the newly generated change fingerprint is completely identical to the historical change fingerprint recorded in the synchronous fingerprint index, it indicates that the record in the source database was only accidentally timestamped, but the actual business content has not been substantially modified. In this case, the system directly filters out the record corresponding to the change fingerprint and discards it from the current processing pipeline. If the primary key of the currently traversed record exists in the synchronous fingerprint index but the corresponding change fingerprint is different, it indicates that this is historical data that has undergone actual content modification. The system marks the change type of the currently traversed record as an update type, and the newly generated change fingerprint of this record will be updated to the synchronous fingerprint index along with it in subsequent write steps to overwrite the original historical fingerprint. If the primary key of the currently traversed record does not exist in the synchronous fingerprint index at all, it indicates that this is incremental data newly entered into the source system. The system directly marks the change type of the currently traversed record as an add type. The system continuously loops the above calculation and comparison branch determination steps until the traversal is complete. Finally, it summarizes all records marked as update type and new type and loads them into the list of changes to be synchronized.

[0035] The fingerprint comparison mechanism accurately identifies the real changes in data, effectively intercepts redundant records without substantive business modifications, and reduces the write load on the target database.

[0036] S4. Obtain the primary and foreign key dependencies involved in the list of changes to be synchronized. Sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and change types to generate an ordered batch processing queue. Furthermore, in S4, sorting and dividing the list of changes to be synchronized into blocks to generate an ordered batch processing queue specifically includes the following steps: A data table dependency graph is constructed based on the primary and foreign key dependencies, and a topological sorting algorithm is used to determine the synchronous write level. Within the same synchronous write level, newly added type records in the list of changes to be synchronized are arranged before updated type records; According to the preset batch capacity threshold, the sorted list of changes to be synchronized is divided into blocks to generate an ordered batch processing queue.

[0037] Furthermore, constructing a data table dependency graph based on primary and foreign key dependencies and using a topological sorting algorithm to determine the synchronous write hierarchy specifically includes the following steps: Use the data tables in the list of changes to be synchronized as graph nodes and the primary and foreign key dependencies as directed edges to generate a data table dependency graph. Perform an in-degree traversal on the data table dependency graph and identify the graph nodes that are not collected into the synchronous write level after the in-degree traversal as a set of circular dependency nodes. The ring break weight is calculated based on the number of foreign key fields, the nullable flag of the foreign key fields, and the number of records corresponding to each directed edge in the ring dependency node set. Directed edges whose ring break weight reaches the preset ring break threshold are marked as delayed association edges. Separate the foreign key fields corresponding to the delayed associated edges from the first write field set, generate a primary key-first batch containing the primary key fields and non-delayed fields, and generate a foreign key backfill batch containing the foreign key fields corresponding to the delayed associated edges. Perform a topological sort on the data table dependency graph after removing delayed associated edges to generate a synchronous write hierarchy, and arrange the foreign key backfill batch after the primary key-first batch.

[0038] Specifically, after receiving the list of changes to be synchronized generated in the previous stage, the system reads the data table identifier, primary key value, change type, and newly generated change fingerprint carried in each record of the list, and filters out the dependencies related to these data tables from the pre-configured primary and foreign key dependency mapping. The primary and foreign key dependency mapping is used to describe the reference relationships between different business tables. In one feasible implementation, each dependency record includes at least the child table name, child table foreign key field, parent table name, parent table primary key field, nullable flag for the foreign key field, and the number of foreign key fields. For example, if the dependency mapping records that the employee ID field in the flight attendant scheduling table references the employee ID field in the personnel information table, then the flight attendant scheduling table is the child table, the employee ID field is the child table foreign key field, the personnel information table is the parent table, and the employee ID field is the parent table primary key field.

[0039] The system uses the data tables involved in the list of changes to be synchronized as graph nodes and constructs directed edges according to the direction from parent table to child table, generating a data table dependency graph. That is, if a foreign key field in a child table references a primary key field in a parent table, a directed edge is created in the data table dependency graph from the parent table node to the child table node. With this direction, nodes with an in-degree of zero represent nodes that do not currently depend on other data tables to be synchronized. For example, when the personnel information table is referenced by the flight attendant scheduling table, it can be used as a first-write node. The system performs a topological sort on the data table dependency graph based on in-degree traversal. During initialization, all nodes with an in-degree of zero are added to the processing queue. Then, nodes are sequentially retrieved from the queue, grouped to the current synchronization write level, and the outgoing edges of those nodes are removed. Simultaneously, the in-degree of the pointed-to nodes is updated. When the in-degree of a pointed-to node is updated to zero, it is added to the subsequent processing queue. The system repeats the above in-degree traversal process until the queue is empty.

[0040] If, after the queue is empty, there are still graph nodes in the data table dependency graph that have not been aggregated to the synchronous write level, the system identifies these graph nodes as a set of circular dependency nodes and performs a cycle-breaking assessment on the directed edges in the set. The system calculates the cycle-breaking weight based on the number of foreign key fields corresponding to each directed edge, the nullability flag of the foreign key fields, and the number of related records in the list of changes to be synchronized. Here, the cycle-breaking weight indicates the degree to which the directed edge is suitable as a delayed association edge; the larger the cycle-breaking weight, the more suitable it is to postpone the backfilling of the foreign key relationship. The formula for calculating the cycle-breaking weight is as follows: In this formula, This represents a directed edge in a circular set of dependent nodes, i.e., a specific primary-foreign key dependency relationship; Represents a directed edge The weight of the broken loop; Represents a directed edge The corresponding number of foreign key fields; for a single-field foreign key, the value is 1, and for a composite foreign key, the value is the corresponding number of fields. This indicates the number of records in the list of changes to be synchronized that belong to the directed edge sub-table and contain the foreign key field; This indicates that the foreign key field can be nullable, representing the corresponding weight factor. If the foreign key field allows null values, then... The value is 1.0. If the foreign key field does not allow null values, then... The value is 0.2. In one feasible implementation, if a circular dependency is formed between the crew schedule table and the line safety inspection table, where the crew schedule table has 100 records to be synchronized containing relevant foreign key fields, and these foreign key fields have a quantity of 1 and can be null, then the weight of the broken cycle of the directed edge is... .

[0041] The system marks directed edges whose loop-breaking weight reaches a preset loop-breaking threshold as candidate delayed dependency edges. In one feasible implementation, the preset loop-breaking threshold is set to 50.0. If multiple candidate delayed dependency edges exist, the system selects them sequentially according to their loop-breaking weight from high to low, and temporarily removes the selected edge from the data table dependency graph after each selection, then re-executes the in-degree traversal to determine if the circular dependency is resolved. If, after the re-traversal, there are still graph nodes that have not been aggregated to the synchronous write level, the system continues to select the next directed edge that reaches the preset loop-breaking threshold as a delayed dependency edge, until there is no longer a set of circular dependency nodes in the data table dependency graph, or no new candidate delayed dependency edges exist. For foreign key fields that are not allowed to be null and cannot be processed by the target database delay constraints or temporary placeholders, the system does not treat them as delayed dependency edges, but retains their dependencies to avoid generating unwritable batches.

[0042] After identifying the delayed association edges, the system performs field splitting on the records involving delayed association edges in the list of changes to be synchronized. For each record involving a delayed association edge, the system separates the foreign key field corresponding to the delayed association edge from the initial write field set, generating a primary key-first batch and a foreign key-backfill batch. The primary key-first batch includes at least the target table identifier, record primary key field, record primary key value, change type, change fingerprint, and non-delayed field values ​​(excluding delayed foreign key fields). The foreign key-backfill batch includes at least the target table identifier, record primary key field, record primary key value, foreign key field name to be backfilled, and foreign key field value to be backfilled. During subsequent writes to the target database, the system can locate the primary key-first write record using the target table identifier and record primary key value, and update the foreign key field value to be backfilled to that record.

[0043] After removing delayed association edges and splitting fields, the system performs a topological sort on the data table dependency graph after removing delayed association edges to generate synchronous write layers. Each synchronous write layer contains a set of data tables that can be written to within the same dependency layer. The system then performs a secondary sort on the records within the same synchronous write layer based on the change types obtained in the previous stage, placing newly added type records before updated type records, ensuring that data records that need to be created first in the same layer enter the write queue first. If records from multiple data tables exist within the same layer, the system first arranges them according to the data table order obtained from the topological sort, and then arranges them within the same data table according to the rule of prioritizing newly added types and then updating types.

[0044] After sorting, the system divides the sorted list of changes to be synchronized into blocks according to a preset batch capacity threshold. In one feasible implementation, the batch capacity threshold is set to 1000 records. The block division process is implemented using a batch splitting algorithm, the formula of which is: In this formula, This represents the sequence of records to be synchronized after dependency sorting and change type sorting have been completed; Indicates the first position in the sorted sequence. One record to be synchronized; This indicates the total number of records to be synchronized after sorting; This represents the batch capacity threshold, which is set to 1000 in this embodiment. Indicates the batch number, and ; Indicates the generated first One batch; Indicates the first The end position of each batch in the record sequence. If the sorted record sequence to be synchronized contains 2500 records, then... When the value is 1000, the system generates three master data batches, with the first two batches each containing 1000 records and the third batch containing 500 records.

[0045] The system will add the master data batches generated according to the above block algorithm to the ordered batch processing queue in topological hierarchy order, and arrange the foreign key backfill batches after the corresponding primary key-first batches. If the foreign key backfill batch involves multiple delayed association edges, it will be added to the queue in the order of the corresponding primary key-first batches. In the final generated ordered batch processing queue, each batch retains the target data table identifier, record primary key value, change type, change fingerprint, and the set of fields to be written, so that subsequent data transformation steps can directly perform field transformation and format cleaning based on the batch content.

[0046] This step creates executable batches of records to be synchronized according to the table dependencies, making it easier to write them to the target database in batches later.

[0047] S5. According to the ordered batch processing queue, perform data transformation and cleaning on the records in the queue based on the field mapping rules to generate standardized data batches; Furthermore, in S5, generating standardized data batches specifically includes the following steps: Extract the original data type of records in the ordered batch processing queue based on the field mapping rules; Map the original data type to the standard data type corresponding to the target database, and perform default value filling and format cleaning for null values ​​and non-standard formats; Based on the cleaned records, generate standardized data batches that correspond to the target database table structure.

[0048] Specifically, upon receiving the ordered batch processing queue generated in the previous stage, the system initiates a standardized data format conversion process. The system iterates through the queue in batches, and for each record to be synchronized in the current batch, it retrieves and loads the corresponding field mapping rules from the initial configuration based on the target data table identifier it carries. These field mapping rules contain a one-to-one mapping dictionary from source data types to target data types, and define in detail the name correspondences of specific fields, cleaning strategies, and preset default value configurations. During processing, the system first determines the type of the current batch. If it is a foreign key backfill batch, the field mapping rules are applied only to the primary key and the foreign key fields to be backfilled. If it is a primary key-first batch or other ordinary data batch, data conversion and cleaning are performed on all fields to be written in the record.

[0049] The system performs data standardization on the determined fields to be written based on the aforementioned field mapping rules. This process is implemented using a field-level data standardization algorithm model, the transformation algorithm formula of which is defined as follows: In this algorithm formula, This indicates the record currently being processed. This represents a specific field in the record that needs to be written. Representation field The final output target value after standardization; Representation field In this embodiment, the original value in the source database can be the original shift time string in the subway crew shift schedule, such as 2024 / 05 / 10 08:00. Representation field The primitive data type in the source database, such as the nvarchar type in SQL Server; This indicates that the field is a field in the field mapping rule. The specified target data type and cleaning rules, in this embodiment, can be specified as the datetime type in the MySQL database, with accompanying date format unification rules; This represents a standardized conversion function, which internally encapsulates type mapping, format detection and cleaning, and default value filling logic.

[0050] The internal processing logic of the standardized conversion function first extracts the original data type of the record based on the mapping dictionary in the field mapping rules, and maps the original data type to the standard data type corresponding to the target database. For example, it maps the nvarchar type of the source database to the varchar type of the target database. Then, the function performs cleaning and filling on the field values. If the original value of a field is null in the database and a default value is configured for the field in the field mapping rules, the system uses this default value as the output value of the field. In one feasible implementation, a default value constant of 0 can be set for numeric fields. If the field does not have a default value configured, the system retains its null state for constraint verification by the target database. If the original value of a field is not null, the system uses a type probe to check whether the format of the original value conforms to the specification of the target data type. If it does not conform to the specification, the system performs format conversion according to the cleaning rules. For example, it converts the original scheduling time string 2024 / 05 / 10 08:00 to the format 2024-05-10 08:00:00, which can be directly recognized by the target database.

[0051] After performing the aforementioned standardization transformation algorithm on all fields to be written within the current batch, the system generates a standardized data batch corresponding to the target database table structure based on the cleaned records. During the generation process, the system replaces the source field names in the original records with the target field names specified in the field mapping rules, and replaces the original field values ​​with the output values ​​calculated by the standardization algorithm. The final generated standardized data batch is structurally adapted to the column definitions of the target data table, and each record retains metadata information such as primary key values, change types, and change fingerprints inherited from the previous stage for use in subsequent data writing steps. After processing all batches in the ordered batch processing queue, the system outputs a transformed queue containing the standardized data batches.

[0052] This step converts heterogeneous data from the source into a format that conforms to the target database's specifications, providing a data foundation for subsequent batch write operations to the target database.

[0053] S6. Write standardized data batches into the target database and update the checkpoint metadata table and synchronize the fingerprint index.

[0054] Furthermore, in S6, writing standardized data batches into the target database and updating the checkpoint metadata table and synchronized fingerprint index specifically includes the following steps: The standardized data batches are submitted to the target database using conflict update syntax. After submission, extract the upper bound of the batch update timestamp in the standardized data batch; The upper bound of the batch update timestamp is overwritten to the checkpoint metadata table, and the change fingerprints corresponding to each record in this commit are updated to the synchronization fingerprint index.

[0055] Specifically, the system receives the transformed queue containing standardized data batches from the previous stage's output, and sequentially extracts each standardized data batch from the queue to perform the final persistent write. It should be noted that each record in each standardized data batch, in addition to carrying the standardized field values, also retains an update timestamp that has been continuously transmitted with the record since the data extraction stage, as well as metadata such as the primary key value, change type, and change fingerprint generated since the change identification stage and transmitted through each stage. This update timestamp is the same as the update timestamp used when calculating the change fingerprint in the change identification stage. For the currently extracted standardized data batch, the system obtains the corresponding target database connection channel and starts a write transaction based on the target data table identifier carried in the batch. To prevent batch writes from causing prolonged occupation of database row-level locks, the system configures a transaction timeout constant for this write transaction; in one feasible implementation, this timeout constant is set to 60 seconds.

[0056] The system employs an appropriate write method based on the type of the current batch. If the current batch is a primary key-first batch or a regular data batch, the system reads the standardized field names and values ​​of each record within the batch and constructs a commit statement with conflict update syntax using the underlying object-relational mapping framework. In one feasible implementation, the target database uses the MySQL engine, and the system constructs the commit statement using the INSERT ON DUPLICATE KEY UPDATE syntax (updates when duplicate keys are encountered). When this statement is executed, if the primary key for the record does not exist in the target data table, the engine directly inserts it as a new row; if the primary key already exists, the engine uses the update clause to overwrite the corresponding non-primary key field of the row, thus achieving idempotent writes of updating when the key exists and inserting when it does not exist. If the current batch is a foreign key backfill batch, since this batch only contains the record primary key and the foreign key field to be backfilled, the system constructs an update statement using the record primary key as the positioning condition, updating the foreign key field value to be backfilled to the record already written to the target data table by the corresponding primary key-first batch, thereby completing the delayed detached foreign key associations while ensuring that the main table data is written to the database first.

[0057] The system sends the commit statements for the current batch to the target database for execution all at once. After confirming that the number of affected rows meets expectations, it continues to update the synchronization watermark and fingerprint status within the same write transaction. Finally, it commits the write transaction in a unified manner, making data writing, checkpoint updating, and fingerprint updating an atomic operation. This avoids duplicate synchronization or inconsistencies in the baseline caused by data already being written to the database but watermark or fingerprint updates failing. When updating the watermark, the system iterates through all records in the current batch, extracting the update timestamp carried in the metadata of each record to calculate the upper bound of the batch update timestamp in this standardized data batch. This extraction process is implemented through a timestamp extreme value calculation model, and its extraction algorithm formula is defined as follows: In this algorithm formula, This indicates the upper bound of the final extracted batch update timestamp; This indicates the total number of successfully written records contained in the current standardized data batch; This indicates the numerical index of the record being traversed. ; Indicates the first in the batch The update timestamp corresponding to each record. In this embodiment, if the current data being processed is a batch of subway safety training record tables, the update timestamp of a particular record... The specific value could be 15:30:00 on May 10, 2024; This represents a mathematical extremum function that finds the maximum value in a set of values. The system uses this algorithm to compare the timestamp values ​​of all records within a batch and selects the latest time point as the upper bound value.

[0058] After obtaining the upper bound of the batch update timestamp, the system overwrites this upper bound value into the checkpoint metadata table in the target database. Using the target data table identifier corresponding to the current batch as the index key, the system overwrites and updates the corresponding breakpoint timestamp field in the checkpoint metadata table, ensuring that the synchronization watermark of this business table is precisely advanced to the specified level. The system represents the point in time. Simultaneously, it extracts the primary key and the latest change fingerprint from each record in the current batch, updating the change fingerprints corresponding to each submitted record in the target database's synchronous fingerprint index. For newly added records, the system inserts a completely new primary key and fingerprint mapping record into the synchronous fingerprint index; for updated records, the system overwrites the existing historical change fingerprint records in the synchronous fingerprint index based on the primary key, ensuring consistency between the fingerprint database baseline and the data state of the target business table. It should be noted that for foreign key backfill batches, since the primary key and change fingerprint of the records were already registered during the corresponding primary key pre-batch write, the system only advances the watermark without repeatedly registering fingerprints when processing foreign key backfill batches, avoiding redundant writing of fingerprint states.

[0059] This step enables idempotent data writing and simultaneously advances the breakpoint level and fingerprint status, making it easier for the next task to continue execution from the breakpoint.

[0060] Example 2:

[0061] Cross-system data synchronization typically involves manual periodic export and import or outsourcing the development of customized data interfaces by the original system vendor. This approach suffers from data lag, is prone to data omissions or entry errors, has a long implementation cycle, and subsequent functional expansion and maintenance are easily dependent on external vendors. To address these issues, this invention provides an automatic cross-database data synchronization system, such as... Figure 2 As shown.

[0062] The system includes: The task generation module is used to parse the synchronization configuration file and query the checkpoint metadata table to generate synchronization task instructions that include the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. The data extraction module is used to establish a connection with the source database according to the synchronization task instructions, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. The change identification module is used to calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint. The change fingerprint is compared with the synchronization fingerprint index, the matching records are filtered and the change type of the remaining records is marked to obtain a list of changes to be synchronized. The queue orchestration module is used to obtain the primary and foreign key dependencies involved in the list of changes to be synchronized, sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and change types, and generate an ordered batch processing queue. The data transformation module is used to transform and clean the records in the ordered batch processing queue according to the field mapping rules, and generate standardized data batches. The data writing module is used to write standardized data batches into the target database and update the checkpoint metadata table and synchronize the fingerprint index.

[0063] Specifically, upon system startup, the task generation module parses the synchronization configuration file, extracts the database connection address, source table identifier set, field mapping rules, and primary key rules, and queries the target database's checkpoint metadata table to obtain the breakpoint timestamp values ​​of each source table. These parameters are then aggregated to generate a synchronization task instruction. Subsequently, the data extraction module establishes a connection with the source database based on this synchronization task instruction. Using the current breakpoint timestamp of each source table as a filter, it extracts records whose modification time is later than that timestamp and aggregates them to generate an incremental record set.

[0064] After receiving the incremental record set, the change identification module calculates the primary key, update timestamp, and field content of each record to generate a change fingerprint. This change fingerprint is then compared with the synchronization fingerprint index in the target database: if the fingerprints match, the record is filtered out; if the primary key exists but the fingerprint is different, it is marked as an update; if the primary key does not exist, it is marked as an addition. Finally, the marked records are aggregated to generate a list of changes to be synchronized.

[0065] The queue orchestration module obtains the primary and foreign key dependencies involved in the list of changes to be synchronized and constructs a data table dependency graph accordingly. This module marks directed edges in circular dependencies as delayed association edges by calculating the cycle break weight, and splits related records into primary key-first batches and foreign key backfill batches. Subsequently, it performs topological sorting on the dependency graph after removing delayed association edges to determine the synchronization write level, and within the same level, arranges newly added type records before updated type records, processes them in blocks according to a preset batch capacity threshold, and generates an ordered batch processing queue.

[0066] The data conversion module extracts the original data types of each record according to the field mapping rules in the order of the ordered batch processing queue, converts them into the standard data types of the target database, and performs default value filling and formatting cleaning on null values ​​and non-standard format data, finally generating a standardized data batch that completely corresponds to the table structure of the target database.

[0067] The data writing module uses a conflict update syntax to perform updates when the data exists and inserts when it doesn't exist on standardized data batches before committing them to the target database. After data submission, the module extracts the upper bound of the batch update timestamp and overwrites it to the checkpoint metadata table. At the same time, it updates the change fingerprints corresponding to each record to the synchronization fingerprint index, completing the entire cross-database synchronization process.

[0068] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for automatic cross-database data synchronization, characterized in that, Includes the following steps: S1. Parse the synchronization configuration file and query the checkpoint metadata table to generate a synchronization task instruction containing the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. S2. Establish a connection with the source database according to the synchronization task instruction, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. S3. Calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint. Compare the change fingerprint with the synchronization fingerprint index, filter matching records and mark the change type of the remaining records to obtain a list of changes to be synchronized. S4. Obtain the primary and foreign key dependencies involved in the list of changes to be synchronized, and sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and the change type to generate an ordered batch processing queue. S5. According to the ordered batch processing queue, perform data transformation and cleaning on the records in the queue based on the field mapping rules to generate standardized data batches; S6. Write the standardized data batch into the target database and update the checkpoint metadata table and the synchronization fingerprint index.

2. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S1, the generation of the synchronization task instruction specifically includes the following steps: Parse the synchronization configuration file to extract the source database connection address, source data table identifier set, field mapping rules, and primary key rules; The checkpoint metadata table is queried based on each source data table identifier in the extracted source data table identifier set to obtain the breakpoint timestamp value corresponding to each source data table; Each breakpoint timestamp value is used as the corresponding current breakpoint timestamp, and combined with the extracted source database connection address, the source data table identifier set, the field mapping rule, and the primary key rule to generate a synchronization task instruction.

3. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S2, the step of extracting the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set specifically includes the following steps: Initiate a session request to the source database based on the source database connection address; After establishing the session corresponding to the session request, for each of the source data tables, a query statement is generated using the current breakpoint timestamp corresponding to the source data table as the filtering condition. Execute the query statement and read the data results returned by the query statement, and summarize the data results as an incremental record set.

4. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S3, the process of filtering and matching records and marking the change types of the remaining records to obtain the list of changes to be synchronized specifically includes the following steps: Traverse the incremental record set, extract the currently traversed record, calculate the primary key, the update timestamp, and the field content of the currently traversed record, and generate a change fingerprint; The changed fingerprint is compared with the synchronized fingerprint index. If the changed fingerprint already exists in the synchronized fingerprint index, the record corresponding to the changed fingerprint is filtered out. If the primary key of the currently traversed record exists in the synchronization fingerprint index but the corresponding change fingerprint is different, then the change type of the currently traversed record is marked as an update type; if the primary key of the currently traversed record does not exist in the synchronization fingerprint index, then the change type of the currently traversed record is marked as an addition type, and the marked records are summarized to generate a list of changes to be synchronized.

5. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S4, sorting and dividing the list of changes to be synchronized into blocks to generate an ordered batch processing queue specifically includes the following steps: A data table dependency graph is constructed based on the primary and foreign key dependencies, and a topology sorting algorithm is used to determine the synchronous write level. Within the same synchronous write level, newly added type records in the list of changes to be synchronized are arranged before updated type records; According to the preset batch capacity threshold, the sorted list of changes to be synchronized is divided into blocks to generate an ordered batch processing queue.

6. The method for automatic cross-database data synchronization according to claim 5, characterized in that, The step of constructing a data table dependency graph based on the primary and foreign key dependencies and determining the synchronous write level using a topology sorting algorithm specifically includes the following steps: The data tables in the list of changes to be synchronized are used as graph nodes, and the primary and foreign key dependencies are used as directed edges to generate a data table dependency graph. Perform an in-degree traversal on the data table dependency graph, and identify the graph nodes that are not collected to the synchronous write level after the in-degree traversal as a set of circular dependency nodes. The ring break weight is calculated based on the number of foreign key fields, the nullable identifier of the foreign key fields, and the number of records corresponding to each directed edge in the ring dependency node set. The directed edges whose ring break weight reaches the preset ring break threshold are marked as delayed association edges. Separate the foreign key field corresponding to the delayed association edge from the first write field set, generate a primary key advance batch containing the primary key field and non-delayed fields, and generate a foreign key backfill batch containing the foreign key field corresponding to the delayed association edge. Perform a topological sort on the data table dependency graph after removing the delayed associated edges to generate a synchronous write hierarchy, and arrange the foreign key backfill batch after the primary key-first batch.

7. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S5, the generation of standardized data batches specifically includes the following steps: Extract the original data type of the records in the ordered batch processing queue according to the field mapping rules; The original data type is mapped to the standard data type corresponding to the target database, and default value filling and format cleaning are performed on null values ​​and non-standard formats; Based on the cleaned records, a standardized data batch corresponding to the target database table structure is generated.

8. The method for automatic cross-database data synchronization according to claim 1, characterized in that, In S6, the step of writing the standardized data batch into the target database and updating the checkpoint metadata table and the synchronization fingerprint index specifically includes the following steps: The standardized data batch is submitted to the target database using conflict update syntax; After the submission is completed, extract the upper bound of the batch update timestamp in the standardized data batch; The upper bound of the batch update timestamp is overwritten to the checkpoint metadata table, and the change fingerprint corresponding to each record submitted this time is updated to the synchronization fingerprint index.

9. A cross-database automatic data synchronization system, characterized in that, The system for an automatic cross-database data synchronization method according to any one of claims 1-8, the system comprising: The task generation module is used to parse the synchronization configuration file and query the checkpoint metadata table to generate synchronization task instructions that include the source database connection address, source data table identifier set, field mapping rules, primary key rules, and the current breakpoint timestamp of each source data table. The data extraction module is used to establish a connection with the source database according to the synchronization task instruction, and extract the incremental record set whose update timestamp is greater than the corresponding current breakpoint timestamp from each source data table corresponding to the source data table identifier set. The change identification module is used to calculate the primary key, update timestamp and field content of the records in the incremental record set based on the primary key rules to generate a change fingerprint, compare the change fingerprint with the synchronization fingerprint index, filter matching records and mark the change type of the remaining records to obtain a list of changes to be synchronized. The queue orchestration module is used to obtain the primary and foreign key dependencies involved in the list of changes to be synchronized, sort and divide the list of changes to be synchronized into blocks based on the primary and foreign key dependencies and the change type, and generate an ordered batch processing queue. The data conversion module is used to convert and clean the records in the ordered batch processing queue according to the field mapping rules, and generate standardized data batches. The data writing module is used to write the standardized data batches into the target database and update the checkpoint metadata table and the synchronization fingerprint index.

Images