Method and device for obtaining change data in database and storage medium

Abstract

The invention relates to the technical field of databases, in particular to a method and equipment for obtaining changed data in a database and a storage medium. According to the method for obtaining the change data in the database, a section is introduced between an application program and a database driver, and change data information in the database is obtained and comprises data change occurrence time, a database name, a table name, an operation type and data before and after change; wherein the operation type comprises newly adding, deleting and modifying; when the operation type is newly added, only obtaining the changed data; when the operation type is deletion, only acquiring data before change; and when the operation type is modification, simultaneously obtaining the data before the modification and the data after the modification. The method and the device for acquiring the changed data in the database and the storage medium do not depend on logs of the database, do not need support of database characteristics, and are suitable for all databases; and the method has the characteristics of high real-time performance and small influence on the performance of the database.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device and storage medium for obtaining changed data in a database. Background Art

[0002] Change Data Capture (CDC) is an important technology in the field of database technology. It is used to capture database change data, achieve real-time data synchronization, and ensure data consistency.

[0003] Change data capture focuses on identifying and recording the addition, deletion, and modification operations of data in the database. By capturing every change in the data, these changes are then provided in a structured manner to downstream systems such as data warehouses, message queues, and stream processing engines, ultimately achieving real-time synchronization and analysis of data.

[0004] Currently, there are two main methods for capturing database change data: one is based on database logs, such as the binary log of MySQL databases and the redo log of Oracle databases; the other is based on database features, such as logical replication of PostgreSQL databases and change tracking of SQL Server databases.

[0005] However, both of these methods require support from database features and have a certain impact on database performance. In scenarios with a large number of write requests, the database administrator (DBA) may even disable these features to improve database performance.

[0006] In order to capture the changed data in the database, realize the real-time synchronization of the data and ensure the consistency of the data, the present invention proposes a method, a device and a storage medium for obtaining the changed data in the database. Summary of the Invention

[0007] In order to overcome the deficiencies of the prior art, the present invention provides a simple and efficient method, device and storage medium for obtaining changed data in a database.

[0008] The present invention is achieved through the following technical solutions:

[0009] A method for obtaining changed data in a database, characterized by: introducing a section between an application and a database driver to obtain changed data information in the database, including the time when the data change occurred, the database name, the table name, the operation type, and the data before and after the change;

[0010] Among them, the operation types include adding, deleting and modifying;

[0011] When the operation type is add, only the data after the change is obtained; when the operation type is delete, only the data before the change is obtained; when the operation type is modify, both the data before and after the change are obtained.

[0012] The new operation does not involve old data, only new data, so you only need to get the new data.

[0013] When the operation type is add, a post-processing aspect is introduced between the application and the database driver. Before the database driver returns the data insertion result to the application, the newly inserted data identity ID in the new addition response is obtained through the database addition protocol. Then, the query method provided by the database driver is used to query the newly added data according to the identity ID to obtain the newly added data.

[0014] When the operation type is Add, the process of obtaining the new data information is as follows:

[0015] Step S1.1: The application operates the database driver and initiates a database insert request;

[0016] Step S1.2: The database driver accesses the database and completes the data insertion operation;

[0017] Step S1.3: The change data capture aspect captures the data insertion response returned by the database driver, obtains the data identity ID of the newly added data, and uses the obtained data identity ID to query the database to complete the capture of the newly added data;

[0018] Step S1.4: The database driver returns the data insertion result to the application.

[0019] The delete operation does not involve new data, only old data, so you only need to get the deleted data.

[0020] When the operation type is delete, a front-end aspect is introduced between the application and the database driver. Before the database driver operates the database to delete data, the same conditions as the current delete operation are used to query the database to obtain the data set affected by the current delete operation, thereby completing the capture of the deleted data.

[0021] When the operation type is delete, the process of obtaining deleted data information is as follows:

[0022] Step S2.1: The application operates the database driver to initiate a database deletion request;

[0023] Step S2.2: The change data capture aspect queries the database using the same conditions as the delete request to obtain the data set affected by the delete operation and complete the capture of the deleted data.

[0024] Step S2.3: The database driver accesses the database, completes the deletion operation, and returns the deletion result to the application.

[0025] The modification operation involves both old and new data, so it is necessary to obtain the data before and after the modification operation.

[0026] When the operation type is modification, a wraparound aspect is introduced between the application and the database driver. Before the database driver operates the database to modify the data, the database is queried using the same conditions as the current modification operation to obtain the dataset affected by the modification operation and the dataset before the modification, including the dataset identification number ID.

[0027] After the modification occurs, the obtained dataset identity number ID is used to query the database, obtain the modified data, and complete the capture of the modified data.

[0028] When the operation type is modification, the process of obtaining the modified data information is as follows:

[0029] Step S3.1: The application operates the database driver and initiates a database modification request;

[0030] Step S3.2: The change data capture aspect queries the database with the same conditions as the modification request to obtain the data set affected by the modification operation and complete the data capture before the modification;

[0031] At the same time, based on the dataset before modification, obtain the ID of the data affected by this modification operation;

[0032] Step S3.3: The database driver accesses the database and completes the modification operation;

[0033] Step S3.4: The change data capture aspect queries the database based on the ID of the acquired dataset, obtains the modified data, and completes the capture of the modified data.

[0034] Step S3.5: Return the modification result to the application.

[0035] Use JSON structure to represent captured data change information.

[0036] A device for obtaining change data in a database, characterized by comprising:

[0037] One or more processors, one or more memories, and one or more programs, wherein the one or more programs are stored in the one or more memories and configured to be executed by the one or more processors, and the one or more programs include instructions for performing any of the above methods.

[0038] A readable storage medium is characterized in that: a computer program is stored on the readable storage medium, and the computer program implements the above method when executed by a processor.

[0039] The beneficial effects of the present invention are as follows: the method, device and storage medium for obtaining changed data in the database complete the data changes before and after the addition, deletion and modification operations of the database by operating the database at the application layer. It does not rely on the database log, does not require the support of database characteristics, and is applicable to all databases; it also does not require polling of the database, and has the characteristics of high real-time performance and little impact on database performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0041] Attachment Figure 1 This is a schematic diagram of the operational flow of the application program of the present invention accessing a database.

[0042] Attachment Figure 2 This is a schematic diagram of the process of obtaining new data information in the present invention.

[0043] Attachment Figure 3 This is a flow chart of obtaining deleted data information according to the present invention.

[0044] Attachment Figure 4 This is a flow chart of obtaining modified data information according to the present invention. DETAILED DESCRIPTION

[0045] In order to enable those skilled in the art to better understand the technical solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the embodiments described are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work should fall within the scope of protection of the present invention.

[0046] The application program uses the driver (API) provided by the database manufacturer to perform add, delete, modify, and query operations on the database. Query operations do not cause changes to the data in the database and are not within the scope of this invention. However, add, delete, and modify operations will cause changes to the data in the database. Figure 1 shown.

[0047] The method for obtaining changed data in the database introduces aspects between the application and the database driver to obtain the changed data information in the database, including the time when the data change occurred, the database name, the table name, the operation type, and the data before and after the change;

[0048] Among them, the operation types include adding, deleting and modifying;

[0049] When the operation type is add, only the data after the change is obtained; when the operation type is delete, only the data before the change is obtained; when the operation type is modify, both the data before and after the change are obtained.

[0050] The new operation does not involve old data, only new data, so you only need to get the new data.

[0051] When the operation type is add, a post-processing aspect is introduced between the application and the database driver. Before the database driver returns the data insertion result to the application, the newly inserted data identity ID in the new addition response is obtained through the database addition protocol. Then, the query method provided by the database driver is used to query the newly added data according to the identity ID to obtain the newly added data.

[0052] When the operation type is Add, the process of obtaining the new data information is as follows:

[0053] Step S1.1: The application operates the database driver and initiates a database insert request;

[0054] Step S1.2: The database driver accesses the database and completes the data insertion operation;

[0055] Step S1.3: The change data capture aspect captures the data insertion response returned by the database driver, obtains the data identity ID of the newly added data, and uses the obtained data identity ID to query the database to complete the capture of the newly added data;

[0056] Step S1.4: The database driver returns the data insertion result to the application.

[0057] The delete operation does not involve new data, only old data, so you only need to get the deleted data.

[0058] When the operation type is delete, a front-end aspect is introduced between the application and the database driver. Before the database driver operates the database to delete data, the same conditions as the current delete operation are used to query the database to obtain the data set affected by the current delete operation, thereby completing the capture of the deleted data.

[0059] When the operation type is delete, the process of obtaining deleted data information is as follows:

[0060] Step S2.1: The application operates the database driver to initiate a database deletion request;

[0061] Step S2.2: The change data capture aspect queries the database using the same conditions as the delete request to obtain the data set affected by the delete operation and complete the capture of the deleted data.

[0062] Step S2.3: The database driver accesses the database, completes the deletion operation, and returns the deletion result to the application.

[0063] The modification operation involves both old and new data, so it is necessary to obtain the data before and after the modification operation.

[0064] When the operation type is modification, a wraparound aspect is introduced between the application and the database driver. Before the database driver operates the database to modify the data, the database is queried using the same conditions as the current modification operation to obtain the dataset affected by the modification operation and the dataset before the modification, including the dataset identification number ID.

[0065] After the modification occurs, the obtained dataset identity number ID is used to query the database, obtain the modified data, and complete the capture of the modified data.

[0066] When the operation type is modification, the process of obtaining the modified data information is as follows:

[0067] Step S3.1: The application operates the database driver and initiates a database modification request;

[0068] Step S3.2: The change data capture aspect queries the database with the same conditions as the modification request to obtain the data set affected by the modification operation and complete the data capture before the modification;

[0069] At the same time, based on the dataset before modification, obtain the ID of the data affected by this modification operation;

[0070] Step S3.3: The database driver accesses the database and completes the modification operation;

[0071] Step S3.4: The change data capture aspect queries the database based on the ID of the acquired dataset, obtains the modified data, and completes the capture of the modified data.

[0072] Step S3.5: Return the modification result to the application.

[0073] Considering compatibility with multiple programming languages, the method, device, and storage medium for obtaining change data in this database use a JSON structure to represent the captured data change information; an example is as follows:

[0074] {

[0075] "timestamp":"2021-09-01 12:00:00",

[0076] "database":"test",

[0077] "table":"user",

[0078] "operation":"insert",

[0079] "before":[

[0080] {}

[0081] ],

[0082] "after":[

[0083] {} ]

[0085] }

[0086] In the above JSON format:

[0087] timestamp indicates the time when the data change occurs;

[0088] database indicates the database name;

[0089] table indicates the table name;

[0090] Operation indicates the operation type, including insert, delete, and update, which correspond to the operations of adding, deleting, and modifying respectively;

[0091] Before indicates the data before the change, which is only valid in delete and modify operations;

[0092] after indicates the data after the change, which is only valid in the add and modify operations.

[0093] The devices that obtain change data in this database include:

[0094] One or more processors, one or more memories, and one or more programs, wherein the one or more programs are stored in the one or more memories and configured to be executed by the one or more processors, and the one or more programs include instructions for performing any of the above methods.

[0095] The computer-readable storage medium stores one or more programs, wherein the one or more programs include instructions, and when the instructions are executed by a device for obtaining change data in a database, the device for obtaining change data in the database executes any one of the above methods.

[0096] In summary, the method, device, and storage medium for obtaining changed data in the database perform cross-sectioning by operating the database at the application layer to complete data changes before and after database addition, deletion, and modification operations. This method does not rely on database logs or require support from database features, and is applicable to all databases. It also does not require polling of the database, and is characterized by high real-time performance and little impact on database performance.

[0097] The embodiment described above is only one specific implementation of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for obtaining change data in a database, characterized by: Introduce aspects between the application and the database driver to obtain the changed data information in the database, including the time when the data change occurred, the database name, the table name, the operation type, and the data before and after the change; Among them, the operation types include adding, deleting and modifying; When the operation type is add, only the data after the change is obtained; when the operation type is delete, only the data before the change is obtained; when the operation type is modify, both the data before and after the change are obtained.

2. The method for obtaining change data in a database according to claim 1, characterized in that: When the operation type is add, a post-aspect is introduced between the application and the database driver. Before the database driver returns the data insertion result to the application, the identity identification number ID of the newly inserted data in the new response is obtained through the database addition protocol, and then the query method provided by the database driver is used to query the newly added data according to the identity identification number ID to obtain the newly added data.

3. The method for obtaining change data in a database according to claim 2, characterized in that: When the operation type is Add, the process of obtaining the new data information is as follows: Step S1.1, the application operates the database driver and initiates a database insert request; Step S1.2, the database driver accesses the database to complete the data insertion operation; Step S1.3, change the data capture aspect to capture the data insertion response returned by the database driver, obtain the data identity identification number ID of the newly added data, and use the obtained data identity identification number ID to query the database to complete the capture of the newly added data; Step S1.4: The database driver returns the data insertion result to the application.

4. The method for obtaining change data in a database according to claim 1, characterized in that: When the operation type is deletion, a front-end aspect is introduced between the application and the database driver. Before the database driver operates the database to delete data, the same conditions as the current deletion operation are used to query the database to obtain the data set affected by the deletion operation, thereby completing the capture of the deleted data.

5. The method for obtaining change data in a database according to claim 1, characterized in that: When the operation type is delete, the process of obtaining the deleted data information is as follows: Step S2.1, the application operates the database driver to initiate a database deletion request; Step S2.2: Change the data capture aspect to query the database with the same conditions as the deletion request, obtain the data set affected by the deletion operation, and complete the capture of the deleted data; Step S2.3: The database driver accesses the database, completes the deletion operation, and returns the deletion result to the application.

6. The method for obtaining change data in a database according to claim 1, characterized in that: When the operation type is modification, a wraparound aspect is introduced between the application and the database driver. Before the database driver operates the database to modify the data, the same conditions as the current modification operation are used to query the database to obtain the data set affected by the modification operation and the data set before the modification, including the data set identity ID. After the modification occurs, the obtained data set identity number ID is used to query the database, obtain the modified data, and complete the capture of the modified data.

7. The method for obtaining change data in a database according to claim 1, characterized in that: When the operation type is modification, the process of obtaining modification data information is as follows: Step S3.1, the application operates the database driver and initiates a database modification request; Step S3.2: Change the data capture aspect to query the database with the same conditions as the modification request, obtain the data set affected by the modification operation, and complete the data capture before the modification; At the same time, according to the data set before modification, the identity ID of the data affected by this modification operation is obtained; Step S3.3, the database driver accesses the database to complete the modification operation; Step S3.4: The change data capture aspect queries the database according to the identity ID of the acquired data set, obtains the modified data, and completes the capture of the modified data; Step S3.5: Return the modification result to the application.

8. The method for obtaining change data in a database according to claim 1, characterized in that: The captured data change information is represented using the JSON structure.

9. A device for obtaining changed data in a database, characterized in that: include: One or more processors, one or more memories, and one or more programs, wherein the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing any of the methods according to claims 1 to 8.

10. A computer-readable storage medium storing one or more programs, characterized in that: The one or more programs include instructions, which, when executed by a device for obtaining change data from a database, enable the device for obtaining change data from the database to perform any one of the methods according to claims 1 to 8.

Images