Database synchronization method and system, source data and target data synchronization device

[0065] Example one

[0066] This embodiment provides a database synchronization method, such as figure 1 Shown, please refer to figure 1 :

[0067] S101. Monitor the source table in the source database;

[0068] S102. When it is monitored that a certain part of the source data in the source table undergoes an operation based on DML (Data Manipulation Language, data manipulation language), the source unique index corresponding to this part of the source data is added to the intermediate table corresponding to the source table;

[0069] S103. After the first synchronization condition is met, determine the source data to be synchronized in the source table according to the source unique index in the intermediate table, and send to the corresponding target table in the target database the source data that contains at least the source unique index corresponding to the source data to be synchronized. Synchronize messages.

[0070] Among them, when it is detected in step S102 that a certain part of the source data in the source table undergoes a DML operation, this part of the source data has changed on the basis of the original data, including delete (delete) this part of the source data, insert (insert) This part of the source data, or update (update) this part of the source data; after these three DML operations are monitored, the source unique index corresponding to this part of the source data will be added to the intermediate table corresponding to the source table. The intermediate table is created in advance. An intermediate table can uniquely correspond to a source table. At this time, an intermediate table only records the source unique index of the source data in the source table. The source unique index is preferably the primary key value of the source table. , Directly use the primary key value of the source table as the source unique index to reduce the definition process, reduce costs and save time; it can also be an intermediate table that can correspond to the source unique index of the source data of multiple source tables. In this case, it is necessary Further add the identification of each source table to the source unique index.

[0071] In step S103, according to the source unique index determined in step S102, the source unique index of the source table is searched, and a synchronization message is generated according to the search result; this search process includes the source unique index in the intermediate table and the source table one by one. The source unique index is compared, and the comparison results are as follows: there is no source unique index in the source table, it means that the source data has a delete operation, and the synchronization message at this time only contains the source unique index and does not correspond to it The source data; the source unique index exists in the source table, it means that the source data corresponding to the source unique index exists in the source table, indicating that the source data has an insert operation or an update operation. At this time, the source data is extracted as The data to be synchronized is added to the synchronization message, that is, the synchronization message contains the source unique index and the corresponding source data to be synchronized. Such a search process allows the intermediate table to only add the source unique index, which saves disk space.

[0072] The above-mentioned first synchronization condition represents the timing that needs to be met to send the synchronization message to the target table in the target library. This timing can be a timing mechanism with a preset interval time, that is, the synchronization message is sent to the target when the preset time is reached. Table; under the premise of synchronizing multiple tables, if an intermediate table corresponding to the source table is generated when the DML operation of the source table occurs, the cumulative number of intermediate tables reaches the preset threshold can also be used as the synchronization message The timing of sending to the target table is determined by the threshold value according to the total amount of the source table; it can also be a combination of the above two first synchronization conditions, that is, the synchronization message is sent to the target table when either of the two is met. This can avoid the burden of real-time synchronization on the database, and can also make the database synchronization timely enough.

[0073] Further, before monitoring the source table in the source database, it also includes determining whether the data magnitude of the source table is less than a preset magnitude threshold. If the data magnitude of the source table is less than the preset magnitude threshold at this time, after the second synchronization condition is met, all current source data of the source table and the corresponding source unique index can be sent to the target table for synchronization matching. On the premise that the magnitude of the source table data is less than the threshold, this method consumes less time and has no effect on the source table; the preset magnitude threshold is determined by the performance of the database. The synchronization matching process is preferably a synchronization matching process based on a merge statement, which specifically includes: sending the source unique index of all current data in the source table to the target table, matching the source unique index with the target unique index of the target table, and if the matching is successful, Use the source data to update the corresponding target data in the target table; if the match is unsuccessful, use the source table data to insert the target table; finally, find and delete the target unique index in the target table but the source table does not exist in the source table. The target data of the index. Further, the second synchronization condition is that the preset time is reached, that is, it is set to send all current source data of the source table and the corresponding source unique index to the target table every time interval; the interval time at this time can be based on the source table It depends on the frequency of DML operation and does not have to be set to a fixed value.

[0074] Example two

[0075] This embodiment provides a database synchronization method, such as figure 2 Shown, please refer to figure 2 :

[0076] S201: Receive a synchronization message from a source table in a source database, where the synchronization message includes at least a source unique index corresponding to the source data to be synchronized where an operation based on a data manipulation language occurs;

[0077] Wherein, the synchronization message is consistent with the synchronization message in Embodiment 1, and will not be repeated here.

[0078] S202: Extract the source unique index from the synchronization message;

[0079] S203: Search for a corresponding target unique index in the corresponding target table of the target database according to the source unique index, and perform a synchronization operation according to the search result and the synchronization message.

[0080] In step S202, extracting the source unique index from the synchronization message means that the received synchronization message contains at least the source unique index corresponding to the source data to be synchronized in which the DML operation has occurred, and the source unique index is extracted and The target unique index of the target table is compared, that is, the corresponding target unique index is searched in the target table according to the source unique index in step S203. There are two types of search results: the target unique index corresponding to the source unique index exists in the target table; the target unique index corresponding to the source unique index does not exist in the target table. There are two types of synchronization messages: they only contain the source unique index; they contain the source unique index and the corresponding source data to be synchronized. When the search result is that there is a target unique index corresponding to the source unique index in the target table, and the synchronization message contains only the unique index, it can be concluded that the source data has a delete operation. At this time, delete the target unique index in the target table and the corresponding Target data; when the search result is that there is a target unique index corresponding to the source unique index in the target table, and the synchronization message contains the source unique index and the corresponding source data to be synchronized, it can be concluded that the source data has been updated. The source data to be synchronized update the target data corresponding to the target unique index in the target table; when the search result is that there is no target unique index corresponding to the source unique index in the target table, and the synchronization message contains the source unique index and the corresponding source to be synchronized When data, it can be concluded that an insert operation has occurred in the source data. At this time, the source unique index and the corresponding source data to be synchronized are inserted into the target table. Further, the target unique index is preferably the primary key value of the target table.

[0081] Example three

[0082] This embodiment provides a database synchronization method, such as image 3 Shown, please refer to image 3 :

[0083] S301. Monitor the data of the source table in the source database;

[0084] S302: When it is monitored that a certain part of the source data in the source table is operated based on the data manipulation language, add the source unique index corresponding to this part of the source data to the intermediate table corresponding to the source table;

[0085] S303. After the first synchronization condition is met, determine the source data to be synchronized in the source table according to the source unique index in the intermediate table, and send to the corresponding target table in the target database the source data that contains at least the source unique index corresponding to the source data to be synchronized. Synchronization message

[0086] S304: Extract the source unique index from the synchronization message;

[0087] S305: Search for a corresponding target unique index in the target table according to the source unique index, and perform a synchronization operation according to the search result and the synchronization message.