Cross-database data synchronization method

Abstract

The invention discloses a cross-database data synchronization method, which is used for data synchronization from a source library to a target library, and comprises the following steps: carrying out primary screening on data tables based on the source library according to a non-migration table name to obtain a migration table, and carrying out secondary screening according to a record number of the migration table to obtain a to-be-synchronized table; according to the table name of the to-be-synchronized table, comparing data tables of a target library, and according to a comparison result, obtaining a data table corresponding to the to-be-synchronized table in the target library; and according to the database type of the target library, obtaining a matched SQL language, processing table data in the to-be-synchronized table, and executing table data synchronization work of the table data in the to-be-synchronized table to the corresponding data table in the target library. According to the method, the to-be-synchronized table is accurately positioned through two times of screening, and invalid processing is reduced; the adaptive SQL grammar is automatically generated according to the type of the target library, naming rules of the source library and the target library are unified, table data mapping is performed, structure consistency is ensured, and the problem of synchronization failure caused by grammar difference of heterogeneous databases is effectively solved.

Description

Technical Field

[0001] This invention belongs to the field of data synchronization technology, specifically relating to a cross-database data synchronization method. Background Technology

[0002] With the rapid development of enterprise IT infrastructure, the importance of data as a core asset is becoming increasingly prominent. In real-world business scenarios, enterprises often need to migrate or synchronize data from one database type to another to meet the integration requirements between different systems. For example, an enterprise may need to migrate historical data from a MySQL database in an old system to an Oracle database in a new system, or achieve data consistency across cloud platform databases in a multi-cloud environment. This process typically involves large-scale full data migration or incremental updates for routine maintenance, placing stringent requirements on synchronization efficiency, data consistency, compatibility, and security.

[0003] However, existing technologies have significant limitations when handling data synchronization across different database types. They often rely on manually written scripts or the use of general-purpose tools, but these solutions struggle to adapt to the syntactic differences and complex business logic between heterogeneous databases, leading to high development costs and maintenance difficulties.

[0004] Existing technologies mainly employ three methods: file transfer or middleware forwarding. This involves exporting source library data to files (such as CSV or XML) and then importing it into the target library, or forwarding data using middleware. File conversion and network transmission consume significant time, especially in large-scale data migration scenarios, where the synchronization cycle is significantly prolonged, resulting in low efficiency. File format conversion can easily lead to data loss or format errors, and it cannot verify data consistency in real time, posing a risk to data integrity.

[0005] Furthermore, real-time synchronization of triggers / log parsing is achieved through incremental synchronization via database triggers or parsed logs. Deploying triggers requires modifying the source database table structure, which may impact the stability of the business system. Additionally, different databases have significantly different log formats, necessitating the development of separate parsing logic for each database, resulting in poor generality.

[0006] In summary, the limitations and challenges of existing cross-database data synchronization technologies mainly lie in the performance bottlenecks of large-scale data synchronization and how to ensure data consistency and integrity during the synchronization process between databases. Summary of the Invention

[0007] This invention provides a cross-database data synchronization method that avoids redundant synchronization operations through primary and secondary filtering, solves performance bottlenecks such as bandwidth, and improves synchronization efficiency. By comparing the table names of the tables to be synchronized and synchronizing data between tables, it solves the problems of data synchronization consistency and integrity.

[0008] The technical solution adopted in this invention is as follows:

[0009] A cross-database data synchronization method for synchronizing data from a source database to a target database, comprising:

[0010] Based on the non-migrating table name, the data tables in the source database are filtered once to obtain the migration table. Based on the number of records in the migration table, a second filtering is performed to obtain the table to be synchronized.

[0011] Based on the table name of the table to be synchronized, compare it with the data tables in the target database, and based on the comparison result, obtain the data table in the target database that corresponds to the table to be synchronized.

[0012] Based on the database type of the target database, a matching SQL language is obtained, the table data in the table to be synchronized is processed, and the table data in the table to be synchronized is synchronized to the table data of the corresponding data table in the target database.

[0013] The cross-database data synchronization method disclosed in this invention also has the following additional technical features:

[0014] The non-migrated table names are specifically:

[0015] In response to the non-migration statistics table, multiple non-migration table names are obtained, wherein the non-migration statistics table contains multiple non-migration table names; or,

[0016] Extract the data table names from the source database and compare them with the data table names in the target database.

[0017] If the data table name in the source database exists in the target database, then the timestamps of the data table in the source database and the data table in the target database are compared. If the timestamp of the data table in the source database is later than the timestamp of the data table in the target database, then the data table name is included in the non-migrated table name.

[0018] Based on the non-migrated table names, a first-stage data table filtering is performed on the source database to obtain the migrated tables, specifically:

[0019] Read the data table names from the source database, delete the table names based on the non-migrated table names, and filter the data table names from the source database to obtain the migrated tables.

[0020] Based on the number of records in the migration table, a second filtering process is performed to obtain the table to be synchronized, specifically:

[0021] Based on the migration table, read the number of records in the migration table.

[0022] When the number of records in the migration table is greater than 0, the migration table is included in the table to be synchronized.

[0023] When the number of records in the migration table is 0, the table name of the migration table is deleted for secondary filtering.

[0024] The number of records read from the migration table is as follows:

[0025] The table data of the migration table is read to obtain the number of records in the migration table, so as to determine the validity of the migration table.

[0026] Based on the comparison results, the data table corresponding to the table to be synchronized in the target database is obtained, specifically:

[0027] When the table name of the table to be synchronized exists in the target database, it shall be used as the data table corresponding to the table to be synchronized.

[0028] If the table name to be synchronized does not exist in the target database, a data table with the same name is created in the target database as the data table corresponding to the table to be synchronized.

[0029] The process of processing the table data in the table to be synchronized is as follows:

[0030] Based on the database type of the target database, convert the format of the table data in the table to be synchronized, including at least one of uppercase and lowercase letters and any one of the concatenation operators.

[0031] The process of synchronizing table data in the table to be synchronized with the corresponding table data in the target database is as follows:

[0032] In response to user input, determine the synchronization type of the data, which includes at least full synchronization and incremental synchronization.

[0033] When the synchronization type is full synchronization, all table data of the corresponding data table in the target database is deleted, the table data of the table to be synchronized is synchronized, and the synchronization process is recorded through the synchronization log table.

[0034] When the synchronization type is incremental synchronization, the timestamps of the table data in the table to be synchronized are compared with the timestamps of the corresponding table data in the target database. If the timestamp of the table data in the table to be synchronized is later than the timestamp of the corresponding table data in the target database, the table data synchronization is performed, and the synchronization process is recorded in the synchronization log table.

[0035] The present invention also discloses a storage medium,

[0036] The storage medium stores a computer program, which, when executed, implements the steps of the cross-database data synchronization method.

[0037] The present invention further discloses a processing apparatus, comprising:

[0038] Memory, used to store computer programs;

[0039] A processor is used to implement the steps of the cross-database data synchronization method when executing the computer program.

[0040] Due to the adoption of the above technical solution, the beneficial effects achieved by this invention are as follows:

[0041] 1. In this invention, through a first-stage filtering (filtering source database tables based on non-migrated table names) and a second-stage filtering (excluding empty tables based on record count), the tables requiring synchronization are accurately located, avoiding invalid processing of redundant or empty tables, and significantly reducing the data volume and execution time of the synchronization task. Adaptive SQL syntax (such as table data case rules and pagination statements) is automatically generated based on the target database type, avoiding manual adjustment of SQL scripts, improving automation, shortening the development cycle, unifying table data naming rules between the source and target databases, reducing synchronization errors caused by naming inconsistencies, and resolving synchronization failures due to database syntax differences. Furthermore, no manual intervention is required to filter tables requiring synchronization or adjust SQL syntax, reducing manual configuration and debugging time.

[0042] For example, Oracle databases automatically convert table data names to uppercase ("FIELD_NAME"), while MySQL preserves the original case (field_name).

[0043] Furthermore, by comparing table names and synchronizing table data between the source and target databases, the consistency of table structures between the two databases is ensured. If a table with the same name does not exist in the target database, it is automatically created; if the table data does not match, synchronization is achieved through table data mapping to ensure data consistency.

[0044] Moreover, by narrowing the synchronization scope through two filtering steps (without migrating table names + number of records), combined with the batch processing capabilities of table name comparison and table data synchronization, it is suitable for large-scale data migration scenarios (such as historical data migration and system reconstruction). Attached Figure Description

[0045] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this invention, illustrate exemplary embodiments of the invention and are used to explain the invention, but do not constitute an undue limitation of the invention. In the drawings:

[0046] Figure 1 This is a flowchart illustrating the cross-database data synchronization method according to one embodiment of the present invention. Detailed Implementation

[0047] To more clearly illustrate the overall concept of the present invention, a detailed description will be provided below with reference to the accompanying drawings and examples.

[0048] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and therefore the scope of protection of the invention is not limited to the specific embodiments disclosed below.

[0049] like Figure 1 As shown, a cross-database data synchronization method is used for data synchronization from a source database to a target database, including:

[0050] S100: Based on the non-migrating table name, perform a first-stage filtering of data tables in the source database to obtain the migration table. Based on the number of records in the migration table, perform a second-stage filtering to obtain the table to be synchronized.

[0051] The core purpose of this step is to accurately locate the data tables that need to be synchronized through a two-stage filtering mechanism (filtering without migrating table names + filtering by record count), thereby reducing redundant operations and improving synchronization efficiency.

[0052] One filtering step involves filtering based on non-migrated table names. All table names in the source database are filtered, excluding non-migrated table names, to obtain the migrated tables.

[0053] Specifically, the program parses the file, extracts the set of table names to be excluded, and reads the non-migrated table names. It removes the non-migrated table names from the source database table name list to obtain the migration table. The source database table names are read using the "Table Input" component of the ETL tool, and the filtering operation is performed in conjunction with the "SQL Script" component. This supports dynamic updates to the "Non-Migration Table Names" file without requiring modifications to the code logic, adapting to changes in business requirements.

[0054] The second filtering step involves a validity check based on the number of records. For each migration table, a query is executed to obtain its record count. If the record count is 0, the table is removed from the migration table to exclude empty tables, ultimately resulting in the tables to be synchronized.

[0055] Specifically, the "SQL script" component of the ETL tool is used to dynamically construct query statements based on the database type of the source database, adapting to the syntax differences of different databases.

[0056] Understandably, failing to filter and migrate tables may cause ETL tools to process large amounts of invalid data, increasing computational resource consumption and synchronization time. Failure to exclude empty tables may trigger synchronization errors in the target database (such as inserting null data), impacting the stability of the business system.

[0057] This step narrows down the synchronization scope through two rounds of filtering, reducing the amount of data processed by the ETL tool, shortening the synchronization cycle, and improving synchronization efficiency. After excluding invalid tables, the target database only receives valid data, avoiding logical errors caused by redundant data. Furthermore, by configuring "do not migrate table names" in the file, users can flexibly adjust the tables to be synchronized without modifying the code logic.

[0058] In summary, this step, through a two-stage filtering mechanism (filtering without migrating table names + checking the validity of record counts), resolves redundant operations in cross-database synchronization, significantly improving synchronization efficiency and stability.

[0059] S200: Based on the table name of the table to be synchronized, compare it with the data tables in the target database, and based on the comparison result, obtain the data table in the target database that corresponds to the table to be synchronized.

[0060] The core purpose of this step is to ensure the consistency of table names and table data during the data synchronization process by comparing the table structures of the source database to be synchronized with those of the target database, thereby avoiding synchronization failures or data inconsistencies caused by differences in table structures.

[0061] Based on the table name to be synchronized and all table names in the target database, a table name comparison is performed. All table names in the target database are retrieved, and the table name to be synchronized is compared one by one with the table names in the target database to determine if a table with the same name already exists.

[0062] Specifically, the SQL syntax (such as table data case rules) is adjusted according to the target database type. For example, if the target database is Oracle, the table name is automatically converted to uppercase ("USER_INFO"); if the target database is MySQL, the original case is preserved (user_info).

[0063] Based on the comparison results, if the target table exists, the existing table with the same name in the target database is used as the synchronization target. If the target table does not exist, a new table with the same name as the table to be synchronized is created in the target database and used as the synchronization target.

[0064] Understandably, if the target database does not contain the table to be synchronized or the table name does not match, directly performing synchronization will result in an error. Through comparison and dynamic adaptation, the table structure can be automatically created or adjusted to ensure the continuity of the synchronization process and avoid synchronization interruption.

[0065] This step ensures that the source and target databases have consistent table names and data structures, preventing data loss or logical errors caused by differences in table structures and improving data consistency. Dynamic adaptation avoids synchronization interruptions due to table structure mismatches, reducing retries and resource consumption, and improving synchronization efficiency.

[0066] This step, through table name comparison and dynamic adaptation, resolves the synchronization failure and data inconsistency issues caused by inconsistent table structures during cross-database synchronization.

[0067] S300: Based on the database type of the target database, obtain the matching SQL language, process the table data in the table to be synchronized, and execute the synchronization of the table data in the table to be synchronized to the table data of the corresponding data table in the target database.

[0068] The core purpose of this step is to dynamically generate suitable SQL syntax based on the database type of the target database and process the table data format in the tables to be synchronized, ensuring table data compatibility and data consistency during the data synchronization process across database types.

[0069] The system dynamically generates SQL syntax based on the target database type (e.g., MySQL, Oracle). It also adjusts the format of SQL statements (e.g., table data case rules, pagination statements) according to the target database type. For example, if the target database is Oracle (forced uppercase), it automatically generates "FIELD_NAME"; if the target database is MySQL (default lowercase), it generates field_name.

[0070] Enclose table data names in database-specific syntax (such as double quotes "") to avoid syntax errors caused by conflicts between table data names and keywords.

[0071] Specifically, the "SQL script" component of the ETL tool is used to dynamically construct SQL statements adapted to different databases. Syntax rules are automatically selected through a predefined database type mapping table (e.g., MySQL → lowercase, Oracle → uppercase).

[0072] Based on the database type of the target database, determine the case rules (such as mandatory uppercase, preserving the original format), adjust the table data names, adapt the connectors, and replace special characters in the table data names (such as compatibility handling of underscores _ and hyphens -).

[0073] Specifically, the table data format rules are dynamically configured through the "Set Variables" component of the ETL tool.

[0074] Based on the processed table data names, table data mapping is performed, matching the table data of the table to be synchronized with the corresponding table data in the target database one by one to ensure that the table data order and data type are consistent. Specifically, the "Insert & Update" component of the ETL tool is used to perform table data synchronization.

[0075] Understandably, different databases have significant differences in their support for table data name case and syntax (e.g., Oracle forces uppercase, MySQL defaults to lowercase). Without dynamic adaptation, this can lead to table data recognition failures or syntax errors. Furthermore, inconsistent table data name case may cause the target database to fail to recognize the source table data, resulting in data insertion failures. Unprocessed table data formats (such as special characters) may cause SQL syntax errors, interrupting the synchronization process.

[0076] This step ensures that the table names and formats in the source and target databases are completely matched through case conversion and syntax adaptation, avoiding data loss due to format differences. Pre-processing table data mapping and formatting prevents retries or interruptions during synchronization due to format errors, shortening the synchronization cycle. When adding a new database type, only the predefined syntax mapping table needs to be extended; the entire synchronization logic does not need to be refactored.

[0077] This step resolves the synchronization failure issue caused by differences in syntax and inconsistent table data formats between heterogeneous databases through dynamic SQL syntax adaptation and table data format processing.

[0078] In a preferred embodiment of the present invention, the non-migrating table name specifically refers to:

[0079] In response to the non-migration statistics table, multiple non-migration table names are obtained, wherein the non-migration statistics table contains multiple non-migration table names; or,

[0080] Extract the data table names from the source database and compare them with the data table names in the target database.

[0081] If the data table name in the source database exists in the target database, then the timestamps of the data table in the source database and the data table in the target database are compared. If the timestamp of the data table in the source database is later than the timestamp of the data table in the target database, then the data table name is included in the non-migrated table name.

[0082] The core objective of this implementation method is to accurately identify and filter tables in the source database that do not need to be migrated (tables that do not need to be migrated), reduce redundant synchronization operations, improve data synchronization efficiency, and at the same time ensure that tables in the target database that already have the latest data do not need to be migrated repeatedly.

[0083] For obtaining table names without migration, any of the following embodiments can be used:

[0084] Example 1: Dynamic configuration based on non-migration statistics tables.

[0085] A pre-defined "non-migration statistics table" (such as an Excel / CSV file) contains multiple table names to be excluded. The program parses the file, reads the non-migration statistics table, and extracts all non-migration table names. Users can modify the statistics table content at any time (such as adding / deleting table names) without modifying the code logic.

[0086] Specifically, the statistical table is read through the "Table Input" component of the ETL tool, and dynamic queries are executed in conjunction with the "SQL Script" component.

[0087] Example 2: Intelligent judgment based on timestamp.

[0088] Extract the table names from the source database and compare them one by one with the table names in the target database. If a table name from the source database exists in the target database, then query the timestamps of the tables in both the source and target databases.

[0089] If the source database timestamp is less than or equal to the target database timestamp, then the data in the target database is already up-to-date and does not need to be migrated. This table name will be counted as a table name that does not need to be migrated.

[0090] If the source database timestamp is greater than the target database timestamp, then the source database data has been updated and needs to be migrated.

[0091] Timestamp comparison ensures that tables in the target database that already have the latest data do not need to be migrated repeatedly, avoiding the risk of data overwriting and enhancing data consistency.

[0092] This implementation solves the problems of redundant operations, data consistency, and high maintenance costs in cross-database synchronization by dynamically configuring the non-migrating statistics table and intelligently judging by timestamp comparison.

[0093] In a preferred embodiment of the present invention, based on the non-migrating table name, a data table is filtered once from the source database to obtain the migration table, specifically as follows:

[0094] Read the data table names from the source database, delete the table names based on the non-migrated table names, and filter the data table names from the source database to obtain the migrated tables.

[0095] The core objective of this implementation method is to accurately locate the tables to be migrated (migration tables) through a one-time screening mechanism, reduce redundant synchronization operations, improve data synchronization efficiency, and ensure that the target database only receives valid data.

[0096] Generate appropriate SQL syntax based on the source database type (e.g., MySQL, Oracle) to query all table names in the source database. Transfer the query results to an intermediate table (source_tables) to provide a data foundation for subsequent filtering.

[0097] The program parses the "Non-Migrate Table Names" file, reads the non-migrate table names, and extracts the set of table names to be excluded (no_migrate_tables). It then removes the table names from the source database table name list (source_tables) that were previously in the no_migrate_tables list, resulting in the migration table set (migration_tables).

[0098] Specifically, the ETL tool is integrated, the table name filtering logic is executed through the "SQL script" component, and the source database table names are read using the "table input" component.

[0099] This implementation narrows the synchronization scope through a one-time filtering mechanism, reducing the amount of data processed by ETL tools, shortening the synchronization cycle, and improving synchronization efficiency. After filtering invalid tables, the target database only receives valid data, avoiding logical errors caused by redundant data and enhancing data consistency.

[0100] This implementation method solves the problems of redundant operations, data consistency, and high maintenance costs in cross-database synchronization by dynamically reading the source database table names and filtering tables that are not migrated.

[0101] In a preferred embodiment of the present invention, a secondary screening is performed based on the number of records in the migration table to obtain the table to be synchronized, specifically as follows:

[0102] Based on the migration table, read the number of records in the migration table.

[0103] When the number of records in the migration table is greater than 0, the migration table is included in the table to be synchronized.

[0104] When the number of records in the migration table is 0, the table name of the migration table is deleted for secondary filtering.

[0105] The core objective of this implementation method is to eliminate invalid tables (empty tables) with zero records in the source database through a secondary filtering mechanism, thereby further narrowing down the range of data to be synchronized, reducing redundant synchronization operations, improving data synchronization efficiency, and ensuring that the target database only receives valid data.

[0106] For each migration table, execute an SQL query to obtain its record count.

[0107] Based on the migration table, perform empty table filtering. If the number of records is greater than 0, the table is added to the set of tables to be synchronized (sync_tables). If the number of records is 0, the table is removed from the migration table set to avoid synchronizing empty tables.

[0108] The names of the tables to be synchronized are maintained in an intermediate table (such as sync_table_list) to provide a data source for subsequent synchronization processes. No manual intervention is required; the program automatically determines the validity of the tables, reducing maintenance costs.

[0109] Understandably, failing to filter empty tables can lead to ETL tools processing large amounts of invalid data (such as empty tables), increasing computational resource consumption and synchronization time. This application addresses this by using a secondary filtering mechanism to narrow the synchronization scope, reduce the amount of data processed by the ETL tool, shorten the synchronization cycle, and improve synchronization efficiency. After filtering empty tables, the target database only receives valid data, avoiding logical errors caused by empty table synchronization, such as primary key conflicts, and enhancing data consistency.

[0110] This implementation solves the problems of redundant operations, data consistency, and high maintenance costs in cross-database synchronization by dynamically reading the number of records in the migration table and filtering out empty tables.

[0111] In a preferred embodiment of this implementation, the number of records read from the migration table is specifically as follows:

[0112] The table data of the migration table is read to obtain the number of records in the migration table, so as to determine the validity of the migration table.

[0113] The core objective of this embodiment is to determine whether the migration table is valid (i.e., whether it contains synchronizable data) by reading the table data of the migration table and counting the number of records.

[0114] For each migration table, an SQL query is executed to retrieve its table data information. The presence or absence of data in the table is used to indirectly determine if the table is empty. It's important to note that adapted SQL syntax is generated based on the source database type, ensuring compatibility with the database syntax. Specifically, an ETL tool is integrated, using the "SQL Script" component to execute the table data reading logic and the "Table Input" component to retrieve the migration table name.

[0115] By counting the number of rows in the table query results (e.g., COUNT(*)), we can determine if the migration table is empty. If the number of records is greater than 0, the table is valid and migration work needs to be performed; if the number of records is 0, the table is invalid and empty.

[0116] This embodiment narrows the synchronization scope through table data reading and record count mechanisms, reducing the amount of data processed by ETL tools, shortening the synchronization cycle, and improving synchronization efficiency. The automated filtering mechanism reduces manual configuration and debugging time, adapts to changing business needs, and lowers maintenance costs.

[0117] This embodiment solves the problems of redundant operations, data consistency, and high maintenance costs in cross-database synchronization by dynamically reading table data from the migration table and counting the number of records.

[0118] In a preferred embodiment of the present invention, based on the comparison results, the data table in the target database corresponding to the table to be synchronized is obtained, specifically as follows:

[0119] When the table name of the table to be synchronized exists in the target database, it shall be used as the data table corresponding to the table to be synchronized.

[0120] If the table name to be synchronized does not exist in the target database, a data table with the same name is created in the target database as the data table corresponding to the table to be synchronized.

[0121] The core objective of this implementation method is to dynamically determine whether a corresponding table exists in the target database by comparing the table names to be synchronized in the source database with those in the target database, and to automatically create a table with the same name if it does not exist, thereby ensuring the consistency of the table structure during data synchronization and avoiding synchronization failure or data loss due to missing tables.

[0122] Based on the table name to be synchronized and all table names in the target database, the table name to be synchronized is compared with the table names in the target database one by one to determine whether there is a table with the same name.

[0123] If the table name to be synchronized is not matched in the target database, it will be created automatically if the table name does not exist.

[0124] If the target database lacks the tables to be synchronized, performing synchronization directly will result in errors. Automatic table creation ensures the continuity of the synchronization process. By ensuring that the table names in the source and target databases match exactly, data loss or logical errors due to missing tables or inconsistent structures are avoided.

[0125] This implementation method solves the problem of synchronization failure and data inconsistency caused by inconsistent table structures in cross-database synchronization by dynamically comparing table names and automatically creating missing tables.

[0126] In a preferred embodiment of the present invention, processing the table data in the table to be synchronized specifically involves:

[0127] Based on the database type of the target database, convert the format of the table data in the table to be synchronized, including at least one of uppercase and lowercase letters and any one of the concatenation operators.

[0128] The core objective of this implementation method is to dynamically convert the format (such as case sensitivity and concatenation characters) of the table data in the table to be synchronized according to the database type of the target database, so as to ensure the compatibility of table data names between the source and target databases and avoid synchronization failures or data errors caused by format differences.

[0129] Based on the target database type (e.g., MySQL, Oracle), determine the format requirements. This includes adjusting table names according to the target database's case sensitivity rules. For example, for Oracle (which mandates uppercase), the table name `user_name` is converted to `USER_NAME`; for MySQL (which defaults to lowercase), the original table name `user_name` is retained.

[0130] Adapt connectors by replacing special characters (such as hyphens - and spaces) in table data names with formats supported by the target database (such as underscores _). For example, convert the table data name user-name to user_name to adapt for Oracle or MySQL.

[0131] Specifically, it calls a predefined rule base and automatically selects the conversion rule based on the target database type (e.g., MySQL → lowercase, Oracle → uppercase).

[0132] It is understandable that different databases have significantly different rules for handling table data names (e.g., Oracle forces uppercase, while MySQL defaults to lowercase). If not dynamically adapted, it may lead to table data recognition failure or syntax errors.

[0133] This implementation method ensures that the table data names in the source and target databases match perfectly by converting table data to case and adapting connectors, thus avoiding data loss due to format differences and improving data consistency. Pre-processing table data format prevents synchronization interruptions caused by format errors, shortens the synchronization cycle, and improves synchronization efficiency.

[0134] This implementation solves the compatibility issues caused by differences in table data name case and connectors during cross-database synchronization by dynamically converting the table data format and adapting it to the target database syntax.

[0135] In a preferred embodiment of the present invention, the table data in the table to be synchronized is synchronized to the table data in the corresponding data table in the target database, specifically as follows:

[0136] In response to user input, determine the synchronization type of the data, which includes at least full synchronization and incremental synchronization.

[0137] When the synchronization type is full synchronization, all table data of the corresponding data table in the target database is deleted, the table data of the table to be synchronized is synchronized, and the synchronization process is recorded through the synchronization log table.

[0138] When the synchronization type is incremental synchronization, the timestamps of the table data in the table to be synchronized are compared with the timestamps of the corresponding table data in the target database. If the timestamp of the table data in the table to be synchronized is later than the timestamp of the corresponding table data in the target database, the table data synchronization is performed, and the synchronization process is recorded in the synchronization log table.

[0139] The core objective of this implementation method is to dynamically adjust the target database table data structure according to the synchronization type specified by the user (full synchronization or incremental synchronization), ensuring the consistency of data between the source database and the target database table, and avoiding synchronization failure or data inconsistency caused by differences in table data structure.

[0140] When a user selects "Full Synchronization", the target database table data needs to be cleared. This means deleting all table data in the corresponding data table in the target database and directly synchronizing all table data (including newly added table data) of the table to be synchronized to the target database to achieve full synchronization of table data.

[0141] When the user selects "Incremental Synchronization," synchronization is performed based on the timestamp differences in the table data. Specifically, if the timestamp of the table data to be synchronized is later than the timestamp of the corresponding table data in the target database, it indicates that new data has been modified or added in the source database. In this case, an update operation is required, overwriting the table data in the target database with the data from the source database. It's important to note that only the table data in the table to be synchronized is iterated and compared with the corresponding table data in the target database. Data missing from the target database is not processed and is retained. Based on the table data comparison results, only newly added or updated data records are synchronized.

[0142] Full synchronization requires completely replacing the target database table data to avoid data conflicts caused by residual table data; incremental synchronization requires dynamically adapting to changes in table data to avoid data loss due to missing data in the table to be synchronized, ensuring that the data structure of the target database table adjusts in real time as the source database changes. Users can freely choose the synchronization type (full / incremental) to suit different scenario requirements.

[0143] This implementation method solves the problems of table data consistency, resource utilization, and high maintenance costs in cross-database synchronization by dynamically adapting the table data structure and supporting full / incremental synchronization modes.

[0144] The present invention also provides a storage medium,

[0145] The storage medium stores a computer program, which, when executed, implements the steps of the cross-database data synchronization method.

[0146] Therefore, any effect that can be achieved by cross-database data synchronization methods will not be elaborated here.

[0147] The present invention further provides a processing apparatus, comprising:

[0148] Memory, used to store computer programs;

[0149] A processor is used to implement the steps of the cross-database data synchronization method when executing the computer program.

[0150] Therefore, any effect that can be achieved by cross-database data synchronization methods will not be elaborated here.

[0151] For any parts not mentioned in this invention, existing technologies can be used or referenced.

[0152] The various embodiments in this specification are described in a progressive manner. The same or similar parts between the various embodiments can be referred to each other. Each embodiment focuses on describing the differences from other embodiments.

[0153] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A cross-database data synchronization method, characterized in that, Data synchronization from the source database to the target database includes: Based on the non-migrating table name, the data tables in the source database are filtered once to obtain the migration table. Based on the number of records in the migration table, a second filtering is performed to obtain the table to be synchronized. Based on the table name of the table to be synchronized, compare it with the data tables in the target database, and based on the comparison result, obtain the data table in the target database that corresponds to the table to be synchronized. Based on the database type of the target database, a matching SQL language is obtained, the table data in the table to be synchronized is processed, and the table data in the table to be synchronized is synchronized to the table data of the corresponding data table in the target database.

2. The cross-database data synchronization method according to claim 1, characterized in that, The non-migrated table names are specifically: In response to the non-migration statistics table, multiple non-migration table names are obtained, wherein the non-migration statistics table contains multiple non-migration table names; or, Extract the data table names from the source database and compare them with the data table names in the target database. If the data table name in the source database exists in the target database, then the timestamps of the data table in the source database and the data table in the target database are compared. If the timestamp of the data table in the source database is later than the timestamp of the data table in the target database, then the data table name is included in the non-migrated table name.

3. The cross-database data synchronization method according to claim 1, characterized in that, Based on the non-migrated table names, a first-stage data table filtering is performed on the source database to obtain the migrated tables, specifically: Read the data table names from the source database, delete the table names based on the non-migrated table names, and filter the data table names from the source database to obtain the migrated tables.

4. The cross-database data synchronization method according to claim 1, characterized in that, Based on the number of records in the migration table, a second filtering process is performed to obtain the table to be synchronized, specifically: Based on the migration table, read the number of records in the migration table. When the number of records in the migration table is greater than 0, the migration table is included in the table to be synchronized. When the number of records in the migration table is 0, the table name of the migration table is deleted for secondary filtering.

5. The cross-database data synchronization method according to claim 4, characterized in that, The number of records read from the migration table is as follows: The table data of the migration table is read to obtain the number of records in the migration table, so as to determine the validity of the migration table.

6. The cross-database data synchronization method according to claim 1, characterized in that, Based on the comparison results, the data table corresponding to the table to be synchronized in the target database is obtained, specifically: When the table name of the table to be synchronized exists in the target database, it shall be used as the data table corresponding to the table to be synchronized. If the table name to be synchronized does not exist in the target database, a data table with the same name is created in the target database as the data table corresponding to the table to be synchronized.

7. The cross-database data synchronization method according to claim 1, characterized in that, The process of processing the table data in the table to be synchronized is as follows: Based on the database type of the target database, convert the format of the table data in the table to be synchronized, including at least one of uppercase and lowercase letters and any one of the concatenation operators.

8. The cross-database data synchronization method according to claim 1, characterized in that, The process of synchronizing table data in the table to be synchronized with the corresponding table data in the target database is as follows: In response to user input, determine the synchronization type of the data, which includes at least full synchronization and incremental synchronization. When the synchronization type is full synchronization, all table data of the corresponding data table in the target database is deleted, the table data of the table to be synchronized is synchronized, and the synchronization process is recorded through the synchronization log table. When the synchronization type is incremental synchronization, the timestamps of the table data in the table to be synchronized are compared with the timestamps of the corresponding table data in the target database. If the timestamp of the table data in the table to be synchronized is later than the timestamp of the corresponding table data in the target database, the table data synchronization is performed, and the synchronization process is recorded in the synchronization log table.

9. A storage medium, characterized in that, The storage medium stores a computer program that, when executed, implements the steps of the cross-database data synchronization method as described in any one of claims 1 to 8.

10. A processing apparatus, characterized in that, include: Memory, used to store computer programs; A processor, configured to implement the steps of the cross-database data synchronization method as described in any one of claims 1 to 8 when executing the computer program.

Images