Database table-level data synchronization methods and devices

Abstract

This invention provides a method and apparatus for data synchronization at the table level in a database, comprising: S1, acquiring the record set of a source data table at a first time point and generating a target data table; S2, continuously acquiring operations of the source data table from a second time point less than or equal to the first time point; S3, dividing the operations of the source data table into multiple sub-operation sets according to the primary key, with each sub-operation set storing related operations of the primary key arranged in chronological order; S4, counting the number of related operations stored in each sub-operation set, and when the number of stored operations reaches a preset threshold, acquiring the target operation involved in the current count in the sub-operation set; S5, updating the data of the corresponding primary key in the target data table according to the target operation; S6, resetting the storage count to zero and repeatedly executing step S4. This invention continuously acquires operations of the source data table in the source database from the second time point and continuously updates them in a loop, improving the synchronization speed while ensuring no data is missed.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method and apparatus for synchronizing data at the table level in a database. Background Technology

[0002] Data is an indispensable information foundation we encounter every day, and database technology is a core technology of information systems. With the development of information technology, the amount of data in databases and their storage locations are constantly changing to meet usage requirements. For example, data migration and synchronization are required in various situations, such as database expansion, data redistribution in database tables, replacement of the database with a new server, replacement of the information system, and database backup.

[0003] The existing data synchronization technology for data tables involves: obtaining the first operation on the migrated data, parsing that operation and all subsequent operations, and then repeating the above operations on the data in the target database sequentially according to the order recorded in the database log. Simultaneously, to ensure consistency between the target and source databases after synchronization, the correctness of each operation and the sequential nature of the operations must be guaranteed, ensuring sequential operation.

[0004] However, when the database contains a large amount of data and each row of data is operated on a large number of times, the existing sequential operation and repetitive execution synchronization method has the drawback of slow synchronization speed. For example, if the source data table has 1,000 rows of data and each row has 10,000 operations, then these 1,000 rows need to be processed 10,000 times sequentially, so the total number of operations in the synchronization process is quite large. Summary of the Invention

[0005] This invention provides a method and apparatus for synchronizing data at the table level in a database, aiming to solve the problem of slow synchronization speed in existing data synchronization methods.

[0006] This invention is implemented as follows: a method for synchronizing data at the table level in a database is provided, the method comprising the following steps:

[0007] S1. At the first time point, obtain the record set of the source data table from the source database and insert the record set into the target database to form the target data table;

[0008] S2. Starting from the second time point, continuously acquire the source data table in the source database, wherein the second time point is less than or equal to the first time point;

[0009] S3. Divide the operations of the source data table into multiple sub-operation sets according to the primary key, and each sub-operation set stores the relevant operations of the primary key arranged in chronological order;

[0010] S4. Count the number of related operations stored in each sub-operation set. When the number of stored operations reaches a preset threshold, obtain the target operation involved in this count in the sub-operation set.

[0011] S5. Update the data of the corresponding primary key in the target data table according to the target operation;

[0012] S6. When the number of sub-operations stored in the central storage reaches a preset threshold, the count is reset to zero, and step S4 is executed repeatedly.

[0013] Furthermore, step S5 includes:

[0014] The target operation in the target data table is executed in ascending order of timestamp according to the primary key corresponding to the target operation.

[0015] Furthermore, step S5 also includes:

[0016] The target operation is compressed to obtain a substitute operation for the target operation;

[0017] The primary key corresponding to the target operation in the target data table is used to perform the substitution operation.

[0018] Furthermore, the step of compressing the target operation to obtain a substitute operation for the target operation includes:

[0019] Obtain the operation corresponding to the maximum timestamp in the target operation;

[0020] The alternative operation to the target operation is determined based on the operation type and field value of the operation corresponding to the maximum timestamp.

[0021] Furthermore, when the operation type corresponding to the maximum timestamp is insertion, the operation type of the replacement operation includes deletion and insertion;

[0022] When the operation type corresponding to the maximum timestamp is modification, the operation type of the alternative operation is deletion and insertion;

[0023] When the operation type corresponding to the maximum timestamp is deletion, the operation type of the alternative operation is deletion.

[0024] Furthermore, this invention also proposes a data synchronization device at the table level in a database, characterized in that the device comprises:

[0025] The forming unit is used to obtain the record set of the source data table from the source database at a first time point, and insert the record set into the target database to form the target data table;

[0026] A continuous acquisition unit is used to continuously acquire the source data table in the source database starting from a second time point, wherein the second time point is less than or equal to the first time point;

[0027] A partitioning unit is used to divide the operations of the source data table into multiple sub-operation sets according to the primary key, and each sub-operation set stores the related operations of the primary key arranged in chronological order.

[0028] The target operation acquisition unit is used to count the number of related operations stored in each sub-operation set, and when the number of stored operations reaches a preset threshold, acquire the target operation involved in the current count in the sub-operation set.

[0029] The update unit is used to update the data of the corresponding primary key in the target data table according to the target operation;

[0030] The loop unit is used to clear the count when the number of stored operations in the sub-operation set reaches a preset threshold, and to repeatedly execute the step of counting the number of stored operations related to each sub-operation set. When the number of stored operations reaches the preset threshold, the step of obtaining the target operation involved in the current count in the sub-operation set is performed.

[0031] Furthermore, the update unit includes:

[0032] The first execution module is used to execute the target operation in the target data table according to the primary key corresponding to the target operation in ascending order of timestamp.

[0033] Furthermore, the updating unit also includes:

[0034] A compression operation module is used to compress the target operation to obtain a replacement operation for the target operation;

[0035] The second execution module is used to execute the substitution operation on the primary key corresponding to the target operation in the target data table.

[0036] Furthermore, the compression operation module includes:

[0037] The acquisition submodule is used to acquire the operation corresponding to the maximum timestamp in the target operation;

[0038] The determination submodule is used to determine the alternative operation for the target operation based on the operation type and field value of the operation corresponding to the maximum timestamp.

[0039] Furthermore, when the operation type corresponding to the maximum timestamp is insertion, the operation type of the replacement operation includes deletion and insertion;

[0040] When the operation type corresponding to the maximum timestamp is modification, the operation type of the alternative operation is deletion and insertion;

[0041] When the operation type corresponding to the maximum timestamp is deletion, the operation type of the alternative operation is deletion.

[0042] The beneficial effect of this invention lies in that, compared with the prior art, this invention obtains the record set of the source data table from the source database at a first time point and inserts this record set into the target database to form the migrated target data table. The operation of obtaining the source data table from the source database is continuously performed from a second time point less than or equal to the first time point. The operations for each primary key in the obtained operation set are divided into multiple sub-operation sets according to the primary key, and each sub-operation set stores the related operations of the primary key arranged in chronological order. In a non-stop migration operation, the data in the source database changes every second, making it difficult to have a synchronization endpoint. In this application, the operations of the source data table are continuously obtained from the source database from the second time point, and each sub-operation set is gradually divided. The data of the operations stored in the sub-operation sets are statistically analyzed. Therefore, when the number of operations accumulates to a preset threshold, the operations involved in this statistical count are taken as the target operations. The data of the corresponding primary key in the target data table is updated according to the specific content of the target operations. When the number of stored operations in the sub-operation set reaches a preset threshold, a primary key data update is performed. At this time, the data in the source table is still changing. Therefore, the count of newly stored operations in the sub-operation set is restarted, and the acquisition of target operations and updates to the target data table are performed cyclically. When the number of stored operations reaches the preset threshold again, the target operations involved in this count in the sub-operation set are acquired, and the corresponding primary key data is updated based on the newly determined target operations. This cyclical process ensures data synchronization between the target data table and the source data. Each primary key has its own corresponding sub-operation set, and the number of stored operations in each sub-operation set is counted independently, operating in parallel. Therefore, the number of stored operations in multiple sub-operation sets can reach the preset threshold simultaneously, resulting in simultaneous synchronous updates of data for multiple primary keys, significantly shortening the data table synchronization time. Compared to the traditional synchronization method of sequentially updating primary keys one by one, this application increases the synchronization speed by a factor of several for the same number of sub-operation set partitions. Attached Figure Description

[0043] Figure 1 This is a flowchart illustrating the data synchronization method at the table level in a database provided in an embodiment of the present invention;

[0044] Figure 2 This is a detailed flowchart illustrating step S5 in the table-level data synchronization process provided in this embodiment of the invention.

[0045] Figure 3This is a schematic diagram of a database table-level data synchronization device provided in an embodiment of the present invention;

[0046] Figure 4 This is a schematic diagram of another module of the data synchronization device at the table level in the database provided in this embodiment of the invention. Detailed Implementation

[0047] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0048] Please see Figure 1 This is a flowchart illustrating a table-level data synchronization method in a database provided in Embodiment 1 of the present invention. The method includes the following steps:

[0049] Step S1: At the first time point, obtain the record set of the source data table from the source database and insert the record set into the target database to form the target data table.

[0050] Step S2: Continuously acquire the source data table in the source database starting from the second time point, wherein the second time point is less than or equal to the first time point.

[0051] Before the migration, the database containing the data table is called the source database, and the data table is called the source data table. After the data table is migrated, the database containing the source data table is called the target database.

[0052] Upon receiving a data migration instruction, the system locates the source data table to be migrated from the source database and retrieves its record set. This record set can be obtained either by using the MigOut component to extract table data from a specified database according to configuration, and then using other components to retrieve the data.

[0053] Let's denote the moment when the source data table migration begins as the first time point T1. During the migration period, the data in the source database continues to change without system downtime. To ensure consistency between the data in the target database and the source database, it's necessary to synchronize the data changes in the source database to the target database. Therefore, starting from the second time point T2, we continuously acquire all operations on the source data tables in the source database. Since several operations may occur every second during database system operation, to ensure that all operations from T1 to the current time point are acquired and to avoid missing operations that could lead to synchronization errors, the second time point is defined as less than or equal to the first time point (T2 ≤ T1), and the set of all operations is called the operation set.

[0054] Step S3: Divide the operations of the source data table into multiple sub-operation sets according to the primary key, and each sub-operation set stores the relevant operations of the primary key arranged in chronological order.

[0055] The relevant operations include operation type and data, and each operation has a corresponding timestamp.

[0056] The operations for each primary key in the obtained operation set are divided into multiple sub-operation sets according to the primary key. Each sub-operation set stores all related operations corresponding to one or more primary keys. These sub-operation sets are mutually exclusive, and the union of all sub-operation sets constitutes the operation set.

[0057] For example, Table 1 below is a set of operations obtained from the source database:

[0058] Table 1

[0059]

[0060] Store the values ​​of primary key 1 in Table 1 into sub-operation set 1, which is shown in Table 2:

[0061] Table 2

[0062]

[0063] Store the values ​​of primary key 2 in Table 1 into sub-operation set 2, which is shown in Table 3:

[0064] Table 3

[0065]

[0066] Store the values ​​of primary key 3 from Table 1 into sub-operation set 3, which is shown in Table 4:

[0067] Table 4

[0068]

[0069] In the example above, when the operation set is divided into multiple sub-operation sets according to the primary key, all operations for one primary key are considered as a sub-operation set. If there are many primary keys and the operation frequency is high, the number of operations in the operation set will be enormous. In this case, related operations for multiple primary keys can be stored in the same sub-operation set to reduce the number of sub-operation sets. For example, if the source data table has 2000 primary keys, and the operation set stores related operations for primary keys 1-2000, with each primary key potentially having one or multiple related operations, then all operations corresponding to primary keys 1-50 can be stored in sub-operation set 1; all operations corresponding to primary keys 51-100 can be stored in sub-operation set 2; all operations corresponding to primary keys 101-150 can be stored in sub-operation set 3; and so on, storing the operations for a total of 2000 primary keys in 40 sub-operation sets.

[0070] In addition, to facilitate subsequent processing of primary key operations, when there are multiple primary keys in the sub-operation set, the operations of the same primary key can be arranged in timestamp order before the operations of another primary key are continued, and the relevant operations of each primary key can be listed in turn.

[0071] Step S4: Count the number of related operations stored in each sub-operation set. When the number of stored operations reaches a preset threshold, obtain the target operation involved in this count in the sub-operation set.

[0072] Step S5: Update the data of the corresponding primary key in the target data table according to the target operation.

[0073] In a non-stop migration operation, the data in the source database changes every second, making it difficult to establish a synchronization endpoint. In this application, starting from the second time point T2, operations on the source data target in the source database are continuously acquired. Each operation is progressively divided into sub-operation sets, and the data of the operations stored in these sub-operation sets is statistically analyzed. When the cumulative number of operations reaches a preset threshold, the operations involved in this statistical count are taken as the target operations. The corresponding primary key data in the target data table is then updated based on the specific content of the target operations.

[0074] Specifically, methods for updating the corresponding primary key data in the target data table based on the target operation may include:

[0075] The primary key corresponding to the target operation in the target data table is used to execute the target operation in ascending order of timestamp. That is, the primary key corresponding to the target operation is retrieved from the target data table, and the data corresponding to that primary key is processed sequentially according to the timestamp size, from smallest to largest. Based on the operation type and field values, a corresponding static statement (SQL statement; the statement type corresponds to the operation type) is generated. Updates stop after the operation with the largest timestamp has been executed, and the resulting operation is used as the final data for that primary key.

[0076] See Figure 2 In another embodiment, step S5 further includes the following steps:

[0077] Step S51: Compress the target operation to obtain a substitute operation for the target operation.

[0078] Step S52: The substitution operation is performed on the primary key corresponding to the target operation in the target data table.

[0079] When the storage quantity reaches a preset threshold, obtain the operation corresponding to the largest timestamp in the target operation of the sub-operation set, and determine the alternative operation for the primary key target operation based on the operation type and field of the operation corresponding to the largest timestamp.

[0080] Specifically, when the operation type corresponding to the maximum timestamp is insert, delete and insert statements are generated based on the field value of the operation corresponding to the maximum timestamp. The delete statement is executed first, followed by the insert statement, to update the data corresponding to the primary key in the target data label with the field value of the operation corresponding to the maximum timestamp.

[0081] When the operation type corresponding to the maximum timestamp is modification, delete and insert statements are generated based on the field value of the operation corresponding to the maximum timestamp. The delete statement is executed first, followed by the insert statement, updating the data corresponding to the primary key in the target data label to the field value of the operation corresponding to the maximum timestamp.

[0082] When the operation type corresponding to the maximum timestamp is deletion, a deletion statement is generated based on the field value of the operation corresponding to the maximum timestamp, and the data corresponding to the primary key in the target data label is deleted.

[0083] In this embodiment, a corresponding alternative operation is generated based on the operation corresponding to the largest timestamp in the target operation. The target data table is then updated by executing the alternative operation. Through the above operation compression process, it is unnecessary to execute all target operations sequentially on the target data table, thus accelerating the update speed.

[0084] For example, when synchronizing the field with primary key 2 in Table 3 above to the target database, the operation row for primary key 2 can be compressed to obtain the alternative operation for primary key 2: 2aaa. "2aaa" will then be used as the result for primary key 2 in the target database.

[0085] For example, when the preset threshold is 10,000, the target operation for a primary key is 10,000 operations. Existing synchronization methods require repeatedly executing these 10,000 target operations in the target data table to achieve data synchronization. However, the solution in this invention only needs to compress these 10,000 target operations into a substitute operation, and then update the result in the target data table based on the substitute operation to achieve eventual consistency in data synchronization. Compared to existing synchronization schemes, this invention saves thousands of operations on a single primary key, increasing synchronization speed by thousands of times!

[0086] It should be noted that each primary key has its own corresponding set of sub-operations, and the storage quantity in each sub-operation set is counted independently, operating in parallel. Therefore, when the storage quantity in multiple sub-operation sets simultaneously reaches a preset threshold, the data of multiple primary keys is updated synchronously, greatly shortening the data table synchronization time. Compared to the traditional synchronization method of sequentially updating primary keys one by one, this application increases the synchronization speed by a factor of several times for the same number of sub-operation sets.

[0087] For example, if the source data table has 2000 primary keys, the existing synchronization method operates sequentially on each of the 2000 primary keys one by one. However, using the method in this invention, the operation set is divided into 400 sub-operation sets, and operations are performed on all 400 sub-operation sets concurrently, increasing the synchronization speed by 400 times. If each primary key is treated as a sub-operation set, the speed improvement is 2000 times. The more sub-operation sets there are, the greater the speed improvement becomes.

[0088] Step S6: When the number of sub-operations stored in the central storage reaches a preset threshold, the count is reset to zero, and step S4 is executed repeatedly.

[0089] When the number of operations stored in the sub-operation set reaches a preset threshold, a primary key data update is performed. Since the data in the source table is still constantly changing, the count of newly stored operations in the sub-operation set is restarted, and step S4 is executed repeatedly. When the number of stored operations reaches the preset threshold again, the target operations involved in this count in the sub-operation set are obtained, and the corresponding primary key data is updated based on the newly determined target operations. This periodic loop ensures that the data in the target table is synchronized with the data in the source table.

[0090] In this embodiment, at a first time point, the record set of the source data table is obtained from the source database, and this record set is inserted into the target database to form the migrated target data table. Starting from a second time point less than or equal to the first time point, operations on the source data table are continuously retrieved from the source database. Operations for each primary key in the obtained operation set are divided into multiple sub-operation sets according to the primary key. Each sub-operation set stores the relevant operations for the primary key arranged chronologically. During a non-stop migration operation, the data in the source database changes every second, making it difficult to establish a synchronization endpoint. In this application, operations on the source data table are continuously retrieved from the source database starting from the second time point T2. Sub-operation sets are gradually divided, and the data of the operations stored in the sub-operation sets is statistically analyzed. When the number of operations accumulates to a preset threshold, the operations involved in this statistical count are taken as the target operations. The data of the corresponding primary key in the target data table is updated according to the specific content of the target operations. When the number of stored operations in the sub-operation set reaches a preset threshold, a primary key data update is performed. At this time, the data in the source data table is still changing. Therefore, the count of newly stored operations in the sub-operation set is restarted, and step S4 is executed repeatedly. When the number of stored operations reaches the preset threshold again, the target operation involved in this count in the sub-operation set is obtained, and the corresponding primary key data is updated according to the newly determined target operation. This cycle ensures data synchronization between the target data table and the source data. Each primary key has its own corresponding sub-operation set, and the number of stored operations in each sub-operation set is counted independently, operating in parallel. Therefore, the number of stored operations in multiple sub-operation sets can reach the preset threshold simultaneously, resulting in simultaneous synchronous updates of data for multiple primary keys, greatly shortening the data table synchronization time. Compared to the traditional synchronization method of sequentially updating primary keys one by one, this application increases the synchronization speed by a factor of several times for the same number of sub-operation set divisions.

[0091] Please see Figure 3 This is a module schematic of a table-level data synchronization device in a database provided in Embodiment 4 of the present invention. The device includes:

[0092] The forming unit 10 is used to obtain the record set of the source data table from the source database at a first time point, and insert the record set into the target database to form the target data table;

[0093] The continuous acquisition unit 20 is used to continuously acquire the source data table in the source database starting from a second time point, wherein the second time point is less than or equal to the first time point;

[0094] Before the migration, the database containing the data table is called the source database, and the data table is called the source data table. After the data table is migrated, the database containing the source data table is called the target database.

[0095] Upon receiving a data migration instruction, the system locates the source data table to be migrated from the source database and retrieves its record set. This record set can be obtained either by using the MigOut component to extract table data from a specified database according to configuration, and then using other components to retrieve the data.

[0096] Let's denote the moment when the source data table migration begins as the first time point T1. During the migration period, the data in the source database continues to change without system downtime. To ensure consistency between the data in the target database and the source database, it's necessary to synchronize the data changes in the source database to the target database. Therefore, starting from the second time point T2, we continuously acquire all operations on the source data tables in the source database. Since several operations may occur every second during database system operation, to ensure that all operations from T1 to the current time point are acquired and to avoid missing operations that could lead to synchronization errors, the second time point is defined as less than or equal to the first time point (T2 ≤ T1), and the set of all operations is called the operation set.

[0097] The partitioning unit 30 is used to divide the operations of the source data table into multiple sub-operation sets according to the primary key, and each sub-operation set stores the related operations of the primary key arranged in chronological order.

[0098] The relevant operations include operation type and data, and each operation has a corresponding timestamp.

[0099] The operations for each primary key in the obtained operation set are divided into multiple sub-operation sets according to the primary key. Each sub-operation set stores all related operations corresponding to one or more primary keys. These sub-operation sets are mutually exclusive, and the union of all sub-operation sets constitutes the operation set.

[0100] For example, Table 1 below is a set of operations obtained from the source database:

[0101] Table 1

[0102]

[0103] Store the values ​​of primary key 1 in Table 1 into sub-operation set 1, which is shown in Table 2:

[0104] Table 2

[0105]

[0106] Store the values ​​of primary key 2 in Table 1 into sub-operation set 2, which is shown in Table 3:

[0107] Table 3

[0108]

[0109] Store the values ​​of primary key 3 from Table 1 into sub-operation set 3, which is shown in Table 4:

[0110] Table 4

[0111]

[0112] In the example above, when the operation set is divided into multiple sub-operation sets according to the primary key, all operations for one primary key are considered as a sub-operation set. If there are many primary keys and the operation frequency is high, the number of operations in the operation set will be enormous. In this case, related operations for multiple primary keys can be stored in the same sub-operation set to reduce the number of sub-operation sets. For example, if the source data table has 2000 primary keys, and the operation set stores related operations for primary keys 1-2000, with each primary key potentially having one or multiple related operations, then all operations corresponding to primary keys 1-50 can be stored in sub-operation set 1; all operations corresponding to primary keys 51-100 can be stored in sub-operation set 2; all operations corresponding to primary keys 101-150 can be stored in sub-operation set 3; and so on, storing the operations for a total of 2000 primary keys in 40 sub-operation sets.

[0113] In addition, to facilitate subsequent processing of primary key operations, when there are multiple primary keys in the sub-operation set, the operations of the same primary key can be arranged in timestamp order before the operations of another primary key are continued, and the relevant operations of each primary key can be listed in turn.

[0114] The target operation acquisition unit 40 is used to count the number of related operations stored in each sub-operation set, and when the number of stored operations reaches a preset threshold, acquire the target operation involved in the current count in the sub-operation set.

[0115] Update unit 50 is used to update the data of the corresponding primary key in the target data table according to the target operation;

[0116] In a non-stop migration operation, the data in the source database changes every second, making it difficult to establish a synchronization endpoint. In this application, starting from the second time point T2, operations on the source data target in the source database are continuously acquired. Each operation is progressively divided into sub-operation sets, and the data of the operations stored in these sub-operation sets is statistically analyzed. When the cumulative number of operations reaches a preset threshold, the operations involved in this statistical count are taken as the target operations. The corresponding primary key data in the target data table is then updated based on the specific content of the target operations.

[0117] Specifically, methods for updating the corresponding primary key data in the target data table based on the target operation may include:

[0118] The primary key corresponding to the target operation in the target data table is used to execute the target operation in ascending order of timestamp. That is, the primary key corresponding to the target operation is retrieved from the target data table, and the data corresponding to that primary key is processed sequentially according to the timestamp size, from smallest to largest. Based on the operation type and field values, a corresponding static statement (SQL statement; the statement type corresponds to the operation type) is generated. Updates stop after the operation with the largest timestamp has been executed, and the resulting operation is used as the final data for that primary key.

[0119] See Figure 4 In another embodiment, the updating unit 50 further includes:

[0120] Compression operation module 51 is used to compress the target operation to obtain a substitute operation for the target operation;

[0121] Compression operation module 51 also includes:

[0122] The acquisition submodule is used to acquire the operation corresponding to the maximum timestamp in the target operation;

[0123] The determination submodule is used to determine the alternative operation for the target operation based on the operation type and field value of the operation corresponding to the maximum timestamp.

[0124] The second execution module 52 is used to execute the substitution operation on the primary key corresponding to the target operation in the target data table.

[0125] When the storage quantity reaches a preset threshold, obtain the operation corresponding to the largest timestamp in the target operation of the sub-operation set, and determine the alternative operation for the primary key target operation based on the operation type and field of the operation corresponding to the largest timestamp.

[0126] Specifically, when the operation type corresponding to the maximum timestamp is insert, delete and insert statements are generated based on the field value of the operation corresponding to the maximum timestamp. The delete statement is executed first, followed by the insert statement, to update the data corresponding to the primary key in the target data label with the field value of the operation corresponding to the maximum timestamp.

[0127] When the operation type corresponding to the maximum timestamp is modification, delete and insert statements are generated based on the field value of the operation corresponding to the maximum timestamp. The delete statement is executed first, followed by the insert statement, updating the data corresponding to the primary key in the target data label to the field value of the operation corresponding to the maximum timestamp.

[0128] When the operation type corresponding to the maximum timestamp is deletion, a deletion statement is generated based on the field value of the operation corresponding to the maximum timestamp, and the data corresponding to the primary key in the target data label is deleted.

[0129] In this embodiment, a corresponding alternative operation is generated based on the operation corresponding to the largest timestamp in the target operation. The target data table is then updated by executing the alternative operation. Through the above operation compression process, it is unnecessary to execute all target operations sequentially on the target data table, thus accelerating the update speed.

[0130] For example, when synchronizing the field with primary key 2 in Table 3 above to the target database, the operation row for primary key 2 can be compressed to obtain the alternative operation for primary key 2: 2aaa. "2aaa" will then be used as the result for primary key 2 in the target database.

[0131] For example, when the preset threshold is 10,000, the target operation for a primary key is 10,000 operations. Existing synchronization methods require repeatedly executing these 10,000 target operations in the target data table to achieve data synchronization. However, the solution in this invention only needs to compress these 10,000 target operations into a substitute operation, and then update the result in the target data table based on the substitute operation to achieve eventual consistency in data synchronization. Compared to existing synchronization schemes, this invention saves thousands of operations on a single primary key, increasing synchronization speed by thousands of times!

[0132] It should be noted that each primary key has its own corresponding set of sub-operations, and the storage quantity in each sub-operation set is counted independently, operating in parallel. Therefore, when the storage quantity in multiple sub-operation sets simultaneously reaches a preset threshold, the data of multiple primary keys is updated synchronously, greatly shortening the data table synchronization time. Compared to the traditional synchronization method of sequentially updating primary keys one by one, this application increases the synchronization speed by a factor of several times for the same number of sub-operation sets.

[0133] For example, if the source data table has 2000 primary keys, the existing synchronization method operates sequentially on each of the 2000 primary keys one by one. However, using the method in this invention, the operation set is divided into 400 sub-operation sets, and operations are performed on all 400 sub-operation sets concurrently, increasing the synchronization speed by 400 times. If each primary key is treated as a sub-operation set, the speed improvement is 2000 times. The more sub-operation sets there are, the greater the speed improvement becomes.

[0134] The loop unit 60 is used to clear the count when the number of stored items in the sub-operation set reaches a preset threshold, and to repeatedly execute the step of counting the number of stored items of related operations in each sub-operation set. When the number of stored items reaches the preset threshold, the step of obtaining the target operation involved in the current count in the sub-operation set is performed.

[0135] When the number of operations stored in the sub-operation set reaches a preset threshold, a primary key data update is performed. At this time, the data in the source data table is still constantly changing. Therefore, the count of newly stored operations in the sub-operation set is restarted, and the process returns to the target operation acquisition unit 40 and update unit 50. When the number of stored operations reaches the preset threshold again, the target operations involved in this count in the sub-operation set are acquired, and the corresponding primary key data is updated based on the newly determined target operations. This cycle repeats to ensure data synchronization between the target data table and the source data table.

[0136] In this embodiment, at a first time point, the record set of the source data table is obtained from the source database, and this record set is inserted into the target database to form the migrated target data table. Starting from a second time point less than or equal to the first time point, operations on the source data table are continuously retrieved from the source database. Operations for each primary key in the obtained operation set are divided into multiple sub-operation sets according to the primary key. Each sub-operation set stores the relevant operations for the primary key arranged chronologically. During a non-stop migration operation, the data in the source database changes every second, making it difficult to establish a synchronization endpoint. In this application, operations on the source data table are continuously retrieved from the source database starting from the second time point. Sub-operation sets are gradually divided, and the data of the operations stored in the sub-operation sets is statistically analyzed. When the number of operations accumulates to a preset threshold, the operations involved in this statistical count are taken as the target operations. The data of the corresponding primary key in the target data table is updated according to the specific content of the target operations. When the number of stored operations in the sub-operation set reaches a preset threshold, a primary key data update is performed. At this time, the data in the source table is still changing. Therefore, the count of newly stored operations in the sub-operation set is restarted, and the acquisition of target operations and updates to the target data table are performed cyclically. When the number of stored operations reaches the preset threshold again, the target operations involved in this count in the sub-operation set are acquired, and the corresponding primary key data is updated based on the newly determined target operations. This cyclical process ensures data synchronization between the target data table and the source data. Each primary key has its own corresponding sub-operation set, and the number of stored operations in each sub-operation set is counted independently, operating in parallel. Therefore, the number of stored operations in multiple sub-operation sets can reach the preset threshold simultaneously, resulting in simultaneous synchronous updates of data for multiple primary keys, significantly shortening the data table synchronization time. Compared to the traditional synchronization method of sequentially updating primary keys one by one, this application increases the synchronization speed by a factor of several for the same number of sub-operation set partitions.

[0137] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements 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 synchronizing data at the table level in a database, characterized in that, The method includes the following steps: S1. At the first time point, obtain the record set of the source data table from the source database and insert the record set into the target database to form the target data table; S2. Starting from the second time point, continuously acquire the source data table in the source database, wherein the second time point is less than or equal to the first time point; S3. Divide the operations of the source data table into multiple sub-operation sets according to the primary key. Each sub-operation set stores the related operations of the primary key arranged in chronological order. The related operations include operation type and data, and each operation has a corresponding timestamp. S4. Count the number of related operations stored in each sub-operation set. When the number of stored operations reaches a preset threshold, obtain the target operation involved in this count in the sub-operation set. S5. Update the data of the corresponding primary key in the target data table according to the target operation; S6. When the number of sub-operations stored in the central storage reaches a preset threshold, the count is cleared to zero, and step S4 is executed repeatedly. The steps in step S5 also include: The target operation is compressed to obtain a substitute operation for the target operation; The substitution operation is performed on the primary key corresponding to the target operation in the target data table. The specific steps for compressing the target operation to obtain a replacement operation are as follows: When the storage quantity reaches a preset threshold, obtain the operation corresponding to the maximum timestamp in the target operation of the sub-operation set, and determine the alternative operation of the target operation corresponding to the corresponding primary key based on the operation type and field of the operation corresponding to the maximum timestamp. In step S3: when there are many primary keys, high operation frequency, and a large number of operations in the operation set, the related operations of multiple primary keys are stored in the same sub-operation set; when there are multiple primary keys in the sub-operation set, the operations of the same primary key are arranged in the order of timestamps and then followed by the operations of another primary key, and the related operations of each primary key are listed in turn.

2. The data synchronization method at the table level in a database as described in claim 1, characterized in that, The steps in step S5 include: The target operation in the target data table is executed in ascending order of timestamp according to the primary key corresponding to the target operation.

3. The data synchronization method at the table level in a database as described in claim 1, characterized in that, The step of compressing the target operation to obtain a substitute operation for the target operation includes: Obtain the operation corresponding to the maximum timestamp in the target operation; The alternative operation to the target operation is determined based on the operation type and field value of the operation corresponding to the maximum timestamp.

4. The data synchronization method at the table level in a database as described in claim 3, characterized in that, When the operation type corresponding to the maximum timestamp is insertion, the operation type of the alternative operation includes deletion and insertion; When the operation type corresponding to the maximum timestamp is modification, the operation type of the alternative operation is deletion and insertion; When the operation type corresponding to the maximum timestamp is deletion, the operation type of the alternative operation is deletion.

5. A data synchronization device at the table level in a database, characterized in that, The device includes: The forming unit is used to obtain the record set of the source data table from the source database at a first time point and insert the record set into the target database to form the target data table; A continuous acquisition unit is used to continuously acquire the source data table in the source database starting from a second time point, wherein the second time point is less than or equal to the first time point; A partitioning unit is used to divide the operations of the source data table into multiple sub-operation sets according to the primary key. Each sub-operation set stores the related operations of the primary key arranged in chronological order. The related operations include operation type and data, and each operation has a corresponding timestamp. The target operation acquisition unit is used to count the number of related operations stored in each sub-operation set, and when the number of stored operations reaches a preset threshold, acquire the target operation involved in the current count in the sub-operation set. The update unit is used to update the data of the corresponding primary key in the target data table according to the target operation; The loop unit is used to clear the count when the number of stored items in the sub-operation set reaches a preset threshold, and to repeatedly execute the counting of the number of stored items of related operations in each sub-operation set. When the number of stored items reaches the preset threshold, the unit obtains the steps of the target operation involved in the current count in the sub-operation set. The update unit further includes: A compression operation module is used to compress the target operation to obtain a replacement operation for the target operation; The second execution module is used to execute the substitution operation on the primary key corresponding to the target operation in the target data table. The compression operation module is specifically used to, when the storage quantity reaches a preset threshold, obtain the operation corresponding to the maximum timestamp in the target operation of the sub-operation set, and determine the replacement operation of the target operation corresponding to the corresponding primary key based on the operation type and field of the operation corresponding to the maximum timestamp. The partitioning unit is further configured to: store the related operations of multiple primary keys in the same sub-operation set when there are many primary keys, high operation frequency, and a large number of operations in the operation set; and when there are multiple primary keys in the sub-operation set, arrange the operations of the same primary key in the order of timestamps and then continue the operations of another primary key, and list the related operations of each primary key in sequence.

6. The data synchronization device at the table level in a database as described in claim 5, characterized in that, The update unit includes: The first execution module is used to execute the target operation in the target data table according to the primary key corresponding to the target operation in ascending order of timestamp.

7. The data synchronization device at the table level in a database as described in claim 5, characterized in that, The compression operation module includes: The acquisition submodule is used to acquire the operation corresponding to the maximum timestamp in the target operation; The determination submodule is used to determine the alternative operation for the target operation based on the operation type and field value of the operation corresponding to the maximum timestamp.

8. The data synchronization device at the table level in a database as described in claim 7, characterized in that, When the operation type corresponding to the maximum timestamp is insertion, the operation type of the alternative operation includes deletion and insertion; When the operation type corresponding to the maximum timestamp is modification, the operation type of the alternative operation is deletion and insertion; When the operation type corresponding to the maximum timestamp is deletion, the operation type of the alternative operation is deletion.

Images