A data change recording method, device and medium
By recording the data addition, deletion, and modification process in the database, the problem of difficulty in tracing the intermediate state of an entity object after multiple changes is solved, and historical data tracing is realized in case of data misprocessing.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- INSPUR ZHUOSHU BIG DATA IND DEV CO LTD
- Filing Date
- 2022-11-28
- Publication Date
- 2026-05-19
AI Technical Summary
In existing technologies, it is difficult to record and trace the intermediate states of an entity object that have changed multiple times, especially in cases of data misprocessing, where it is difficult to trace historical data.
By obtaining information such as the primary key, change category, and time of the changed data, record information is constructed. A few fields are added to record the process of adding, deleting, and modifying data, ensuring that data is added to the existing database without being deleted, and recording intermediate states.
It enables the tracing of historical data in the event of data misprocessing, ensuring that no intermediate states between the initial and final states are lost.
Smart Images
Figure CN115757454B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database technology, and in particular to a method, device and medium for recording data changes. Background Technology
[0002] Conventional database table field design prioritizes simplifying design logic, improving database performance, and reducing data coupling; records within the same table are typically independent. In data visualization scenarios, the focus is often on displaying the current state, neglecting the historical evolution of the data.
[0003] In existing technologies, logical deletion is used to record multiple changes of an entity object. However, this method is difficult to represent the intermediate states of an entity object's multiple changes, making it difficult to trace historical data in the event of data misprocessing. Summary of the Invention
[0004] This application provides a data change recording method, device, and medium to solve the following technical problem: when an entity object undergoes multiple changes, and in the event of data misprocessing, it is difficult to trace back to historical data.
[0005] The embodiments of this application adopt the following technical solutions:
[0006] This application provides a data change recording method. It includes: obtaining primary key information of the changed data; obtaining the change category of the changed data; wherein the change category includes at least one of data addition category and data deletion / modification category; determining the data change status identifier corresponding to the changed data based on a preset data change status identifier table and the change category; wherein the preset data change status identifier table includes multiple data change states and identifiers corresponding to each of the multiple data change states; obtaining the data status change time corresponding to the changed data; and recording the change process of the changed data based on the primary key information, change category, data change status identifier, and data status change time.
[0007] This application embodiment constructs record information corresponding to the changed data by acquiring information such as the data change category, change time, and primary key of the changed data. It can record the addition, deletion, and modification process of a certain data by adding a few fields, thereby archiving the data change process by recording all additions, deletions, and modifications to the same data object. This application embodiment does not have the concept of "data deletion"; any operation adds data to the existing database, thus ensuring that no intermediate states between the initial and final states are lost. Furthermore, in the event of data misprocessing, historical data can be traced.
[0008] In one implementation of this application, obtaining the data status change time corresponding to the changed data specifically includes: obtaining the first time corresponding to the changed data in the case of adding a new data category; and / or obtaining the second time corresponding to the changed data in the case of deleting or modifying a data category.
[0009] In one implementation of this application, the change process of the changed data is recorded based on primary key information, change category, data change status identifier, and data status change time. Specifically, this includes: grouping multiple change categories corresponding to the same primary key information into the same group; sorting multiple change categories based on the data status change time corresponding to each of the multiple change categories in the same group; and writing the data change status identifiers corresponding to each of the multiple change categories in the same group into a preset database table according to the sorting order, so as to record the change process of the changed data.
[0010] In one implementation of this application, based on the sorting order, the data change status identifiers corresponding to multiple change categories in the same group are sequentially written into a preset database table. Specifically, this includes: constructing record information corresponding to the changed data based on the primary key information corresponding to the changed data, the content of the changed field, the data change status identifier corresponding to the data addition category, the first time, the data change status identifier corresponding to the data deletion / modification category, and the second time; and writing the record information corresponding to multiple change categories in the same group into the preset database table sequentially based on the sorting order.
[0011] In one implementation of this application, constructing record information corresponding to the changed data specifically includes: generating a unique primary key based on the information to be recorded corresponding to the current changed data; and generating record information corresponding to the current changed data based on the unique primary key and the information to be recorded.
[0012] In one implementation of this application, the data change status identifier corresponding to the changed data is determined based on a preset data change status identifier table and change type. Specifically, this includes: obtaining the field content before and after the change corresponding to the changed data; determining the change category based on the field content before and after the change; determining the identifier group corresponding to the change category in the preset data change status identifier table; and determining the corresponding data change status identifier in the identifier group based on the field content before and after the change.
[0013] In one implementation of this application, the data addition category and the data deletion / modification category each correspond to multiple different data change states.
[0014] In one implementation of this application, the data addition category and the data deletion / modification category each correspond to multiple different data change states, specifically including: the data addition category includes at least one of the following states: data insertion state, data modified by other data state, and data inherited from other data state; the data deletion / modification category includes at least one of the following states: data deleted state, data modified to other data state, and data inherited from other data state.
[0015] This application provides a data change recording device, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: obtain primary key information of changed data and change category of the changed data; wherein the change category includes at least one of data addition category and data deletion / modification category; determine the data change status identifier corresponding to the changed data based on a preset data change status identifier table and change category; wherein the preset data change status identifier table includes multiple data change states and identifiers corresponding to each of the multiple data change states; obtain the data status change time corresponding to the changed data; and record the change process of the changed data based on the primary key information, change category, data change status identifier, and data status change time.
[0016] This application provides a non-volatile computer storage medium storing computer-executable instructions. The computer-executable instructions are configured to: obtain primary key information of changed data and the change category of the changed data; wherein the change category includes at least one of data addition category and data deletion / modification category; determine the data change status identifier corresponding to the changed data based on a preset data change status identifier table and the change category; wherein the preset data change status identifier table includes multiple data change states and identifiers corresponding to each of the multiple data change states; obtain the data status change time corresponding to the changed data; and record the change process of the changed data based on the primary key information, change category, data change status identifier, and data status change time.
[0017] The at least one technical solution adopted in this application embodiment can achieve the following beneficial effects: This application embodiment constructs record information corresponding to the changed data by obtaining information such as the data change category, change time, and primary key of the changed data. It can record the addition, deletion, and modification process of a certain data by adding a few fields, thereby archiving and retaining the data change process by recording all additions, deletions, and modifications of the same data object. This application embodiment does not have the concept of "data deletion"; any operation adds data to the existing database, thereby ensuring that no intermediate state between the initial and final states is lost. Furthermore, in the event of data misprocessing, historical data can be traced. Attached Figure Description
[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:
[0019] Figure 1 A flowchart of a data change recording method provided in an embodiment of this application;
[0020] Figure 2 This is a schematic diagram of the structure of a data change recording device provided in an embodiment of this application. Detailed Implementation
[0021] This application provides a data change recording method, device, and medium.
[0022] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.
[0023] Conventional database table field design prioritizes simplifying design logic, improving database performance, and reducing data coupling; records within the same table are typically independent. In data visualization scenarios, the focus is often on displaying the current state, neglecting the historical evolution of the data.
[0024] In existing technologies, logical deletion is used to record multiple changes of an entity object. However, this method is difficult to represent the intermediate states of an entity object's multiple changes, making it difficult to trace historical data in the event of data misprocessing.
[0025] To address the aforementioned issues, this application provides a data change recording method, device, and medium. By acquiring information such as the data change category, change time, and primary key of the changed data, record information corresponding to the changed data is constructed. By adding a few fields, the process of adding, deleting, and modifying a piece of data can be recorded, thereby archiving the data change process by recording all additions, deletions, and modifications to the same data object. This application does not involve the concept of "data deletion"; any operation adds data to the existing database, ensuring that no intermediate states between the initial and final states are lost. Furthermore, in the event of data misprocessing, historical data can be traced.
[0026] The technical solutions proposed in the embodiments of this application will be described in detail below with reference to the accompanying drawings.
[0027] Figure 1 This is a flowchart illustrating a data change recording method provided in an embodiment of this application. Figure 1 As shown, the data change recording method includes the following steps:
[0028] S101. Obtain the primary key information of the changed data, as well as the change category of the changed data. The change category shall include at least one of the data addition category and the data deletion / modification category.
[0029] In one embodiment of this application, the data addition category and the data deletion / modification category each correspond to multiple different data change states.
[0030] In one embodiment of this application, the data addition category includes at least one of the following: data insertion state, data modified by other data state, and data inherited from other data state. The data deletion / modification category includes at least one of the following: data deleted state, data modified to other data state, and data inherited from other data state.
[0031] Specifically, each data object corresponds to a primary key, which remains unchanged throughout the data's lifecycle. Each data object (corresponding to a real-world entity) should be viewed as a living entity, exhibiting different states (changes in attribute values) at different life stages (time periods). Data deletion and modification should be recorded, even one-to-many and many-to-one inheritance relationships should be recorded. When ultimately displaying the changes of a data object, the state changes should be shown over time.
[0032] Furthermore, the recording method provided in this application embodiment includes multiple recording contents such as UID, ID, field 1, field 2, field 3, ..., IN-TYPE, IN-DATE, OUT-TYPE, and OUT-DATE.
[0033] in,
[0034] UID: The primary key of this record, which is unique within this table;
[0035] ID: The primary key of an object, which remains unchanged throughout the lifecycle of the data;
[0036] IN-TYPE: Data addition category. For example: A indicates new / inserted data, B indicates data modification from other data, and C indicates data inherited from other data (one-to-many or many-to-one inheritance relationship).
[0037] OUT-TYPE: Data deletion / modification category (when data changes), records the type of data change. For example: D indicates that the data was deleted, E indicates that the data was modified to other data, and F indicates that the data was inherited by other data (one-to-many or many-to-one inheritance relationship);
[0038] IN-DATE: Indicates the time when the data was added;
[0039] OUT-DATE: The time when the data was deleted or modified.
[0040] S102. Based on the preset data change status identifier table and change category, determine the data change status identifier corresponding to the changed data. The preset data change status identifier table includes multiple data change statuses and identifiers corresponding to each of these statuses.
[0041] In one embodiment of this application, the pre-change and post-change field contents corresponding to the changed data are obtained. Based on the pre-change and post-change field contents, the change category is determined. In a preset data change status identifier table, the identifier group corresponding to the change category is determined. Based on the pre-change and post-change field contents, the corresponding data change status identifier is determined in the identifier group.
[0042] Specifically, it retrieves the field content before and after the change for the corresponding data. Based on the change between the two, it can be determined whether the current data change is a data addition or a data deletion / modification.
[0043] Furthermore, this application embodiment provides a preset data change status identifier table, which includes multiple data change status identifiers, and these multiple data change status identifiers are grouped according to the data change category. For example, multiple data change status identifiers belonging to the data addition category are grouped into the same group, and multiple data change status identifiers belonging to the data deletion / modification category are grouped into the same group.
[0044] Furthermore, after determining the change category based on the content of the fields before and after the change, the corresponding identifier group for the change category is determined in the preset data change status identifier table, and the type of change of the current data is determined based on the content of the fields before and after the change, so as to determine the corresponding data change status identifier in the identifier group.
[0045] S103. Obtain the time of data status change corresponding to the changed data.
[0046] In one embodiment of this application, the first time corresponding to the change data in the case of adding a new data category, and / or the second time corresponding to the change data in the case of deleting or modifying a data category.
[0047] Specifically, when recording changed data, it is also necessary to record the time when the data changed. In this embodiment, IN-DATE represents the time when the data was added; OUT-DATE represents the time when the data was deleted or modified.
[0048] It should be noted that the time of data addition and the time of data deletion / modification in this application embodiment can be changed as needed, and this application embodiment does not impose any restrictions on this.
[0049] S104. Record the change process of the changed data based on primary key information, change category, data change status identifier, and data status change time.
[0050] In one embodiment of this application, multiple change categories corresponding to the same primary key information are grouped into the same group. Based on the data status change time corresponding to each change category within the same group, the multiple change categories are sorted. Based on the sorting order, the data change status identifiers corresponding to each change category within the same group are sequentially written into a preset database table to record the change process of the changed data.
[0051] Specifically, based on the primary key information corresponding to the changed data, the content of the changed fields, the data change status identifier corresponding to the data addition category, the first time, the data change status identifier corresponding to the data deletion / modification category, and the second time, record information corresponding to the changed data is constructed. Based on the sorting order, the record information corresponding to multiple change categories in the same group is sequentially written into a preset database table. Further, a unique primary key is generated based on the information to be recorded corresponding to the current changed data. Based on the unique primary key and the information to be recorded, the record information corresponding to the current changed data is generated. Table 1 is a specific example of a data change record provided in this application embodiment. The process of recording the change of changed data is explained in detail through Table 1:
[0052]
[0053] Table 1
[0054] As shown in Table 1, the data with ID 101 is a normal newly added data. It was added on the date 20220921 and has remained unchanged until the current time.
[0055] The data with ID 102 was added on date 20220821 and deleted on date 20220822;
[0056] Data with UID 10003 and ID 103 was added on date 20220901 and modified on date 20220903. The UID changed, but the ID remained 103, indicating that the two records are actually different time periods of the same data.
[0057] The data with ID 104 was created on date 20220911 and was inherited by the data with ID 105 on date 20220922.
[0058] This application embodiment, by adding a limited number of fields, can record the process of adding, deleting, and modifying a certain piece of data. By recording all additions, deletions, and modifications to the same data object, the data change process is archived. In this application embodiment, there is no concept of "data deletion"; any operation is to add data to the existing database, thus ensuring that no intermediate states between the initial and final states are lost.
[0059] Figure 2 This is a schematic diagram of the structure of a data change recording device provided in an embodiment of this application. Figure 2 As shown, a data change recording device includes:
[0060] At least one processor; and,
[0061] A memory communicatively connected to the at least one processor; wherein,
[0062] The memory stores instructions that can be executed by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to:
[0063] Obtain the primary key information of the changed data, and obtain the change category of the changed data; wherein the change category includes at least one of the data addition category and the data deletion / modification category;
[0064] Based on a preset data change status identifier table and the change category, the data change status identifier corresponding to the changed data is determined; wherein, the preset data change status identifier table includes multiple data change statuses, and also includes the identifiers corresponding to the multiple data change statuses respectively;
[0065] Obtain the data state change time corresponding to the changed data;
[0066] Based on the primary key information, the change category, the data change status identifier, and the data status change time, the change process of the changed data is recorded.
[0067] This application embodiment also provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:
[0068] Obtain the primary key information of the changed data, and obtain the change category of the changed data; wherein the change category includes at least one of the data addition category and the data deletion / modification category;
[0069] Based on a preset data change status identifier table and the change category, the data change status identifier corresponding to the changed data is determined; wherein, the preset data change status identifier table includes multiple data change statuses, and also includes the identifiers corresponding to the multiple data change statuses respectively;
[0070] Obtain the data state change time corresponding to the changed data;
[0071] Based on the primary key information, the change category, the data change status identifier, and the data status change time, the change process of the changed data is recorded.
[0072] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.
[0073] The foregoing has described specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.
[0074] The above description is merely an embodiment of this application and is not intended to limit this application. For those skilled in the art, various modifications and variations can be made to the embodiments of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the embodiments of this application should be included within the scope of the claims of this application.
Claims
1. A method for recording data changes, characterized in that, The method includes: Obtain the primary key information of the changed data, and obtain the change category of the changed data; wherein the change category includes at least one of the data addition category and the data deletion / modification category; Based on a preset data change status identifier table and the change category, the data change status identifier corresponding to the changed data is determined; wherein, the preset data change status identifier table includes multiple data change statuses, and also includes the identifiers corresponding to the multiple data change statuses respectively; Obtain the data state change time corresponding to the changed data; Based on the primary key information, the change category, the data change status identifier, and the data status change time, the change process of the changed data is recorded; The step of obtaining the data state change time corresponding to the changed data specifically includes: Obtain the first time corresponding to the change data in the case of the newly added data category; and / or Obtain the second time corresponding to the data change category; The process of recording the change of the changed data based on the primary key information, the change category, the data change status identifier, and the data status change time specifically includes: Group the multiple change categories corresponding to the same primary key information into the same group; Based on the data status change time corresponding to the multiple change categories in the same group, the multiple change categories are sorted. Based on the sorting order, the data change status identifiers corresponding to the multiple change categories in the same group are sequentially written into a preset database table to record the change process of the change data. The step of sequentially writing the data change status identifiers corresponding to the multiple change categories in the same group into a preset database table based on the sorting order specifically includes: Based on the primary key information, changed field content, data change status identifier corresponding to the data addition category, the first time, the data change status identifier corresponding to the data deletion / modification category, and the second time, the record information corresponding to the changed data is constructed. Based on the sorting order, the record information corresponding to the multiple change categories in the same group is sequentially written into the preset database table; Constructing the record information corresponding to the changed data specifically includes: Generate a unique primary key based on the information to be recorded corresponding to the currently changed data; Based on the unique primary key and the information to be recorded, generate the record information corresponding to the currently changed data; The step of determining the data change status identifier corresponding to the changed data based on the preset data change status identifier table and the change category specifically includes: Obtain the field content before and after the change corresponding to the changed data; The change category is determined based on the field content before the change and the field content after the change; In the preset data change status identifier table, the identifier group corresponding to the change category is determined; Based on the field content before the change and the field content after the change, the corresponding data change status identifier is determined in the identifier group.
2. The data change recording method according to claim 1, characterized in that, The data addition category and the data deletion / modification category each correspond to multiple different data change states.
3. The data change recording method according to claim 1, characterized in that, The data addition category and the data deletion / modification category each correspond to multiple different data change states, specifically including: The newly added data category includes at least one of the following: data insertion status, data modified by other data status, and data inherited from other data status. The data deletion / modification categories include at least one of the following: data deleted, data modified to other data, and data inherited by other data.
4. A data change recording device, comprising: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Obtain the primary key information of the changed data, and obtain the change category of the changed data; wherein the change category includes at least one of the data addition category and the data deletion / modification category; Based on a preset data change status identifier table and the change category, the data change status identifier corresponding to the changed data is determined; wherein, the preset data change status identifier table includes multiple data change statuses, and also includes the identifiers corresponding to the multiple data change statuses respectively; Obtain the data state change time corresponding to the changed data; Based on the primary key information, the change category, the data change status identifier, and the data status change time, the change process of the changed data is recorded; The step of obtaining the data state change time corresponding to the changed data specifically includes: Obtain the first time corresponding to the change data in the case of the newly added data category; and / or Obtain the second time corresponding to the data change category; The process of recording the change of the changed data based on the primary key information, the change category, the data change status identifier, and the data status change time specifically includes: Group the multiple change categories corresponding to the same primary key information into the same group; Based on the data status change time corresponding to the multiple change categories in the same group, the multiple change categories are sorted. Based on the sorting order, the data change status identifiers corresponding to the multiple change categories in the same group are sequentially written into a preset database table to record the change process of the change data. The step of sequentially writing the data change status identifiers corresponding to the multiple change categories in the same group into a preset database table based on the sorting order specifically includes: Based on the primary key information, changed field content, data change status identifier corresponding to the data addition category, the first time, the data change status identifier corresponding to the data deletion / modification category, and the second time, the record information corresponding to the changed data is constructed. Based on the sorting order, the record information corresponding to the multiple change categories in the same group is sequentially written into the preset database table; Constructing the record information corresponding to the changed data specifically includes: Generate a unique primary key based on the information to be recorded corresponding to the currently changed data; Based on the unique primary key and the information to be recorded, generate the record information corresponding to the currently changed data; The step of determining the data change status identifier corresponding to the changed data based on the preset data change status identifier table and the change category specifically includes: Obtain the field content before and after the change corresponding to the changed data; The change category is determined based on the field content before the change and the field content after the change; In the preset data change status identifier table, the identifier group corresponding to the change category is determined; Based on the field content before the change and the field content after the change, the corresponding data change status identifier is determined in the identifier group.
5. A non-volatile computer storage medium storing computer-executable instructions, wherein the computer... Executable instructions are set as follows: Obtain the primary key information of the changed data, and obtain the change category of the changed data; wherein, The change category includes at least one of the data addition category and the data deletion / modification category; Based on a preset data change status identifier table and the change category, the data change status identifier corresponding to the changed data is determined; wherein, the preset data change status identifier table includes multiple data change statuses, and also includes the identifiers corresponding to the multiple data change statuses respectively; Obtain the data state change time corresponding to the changed data; Based on the primary key information, the change category, the data change status identifier, and the data status change time, the change process of the changed data is recorded; The step of obtaining the data state change time corresponding to the changed data specifically includes: Obtain the first time corresponding to the change data in the case of the newly added data category; and / or Obtain the second time corresponding to the data change category; The process of recording the change of the changed data based on the primary key information, the change category, the data change status identifier, and the data status change time specifically includes: Group the multiple change categories corresponding to the same primary key information into the same group; Based on the data status change time corresponding to the multiple change categories in the same group, the multiple change categories are sorted. Based on the sorting order, the data change status identifiers corresponding to the multiple change categories in the same group are sequentially written into a preset database table to record the change process of the change data. The step of sequentially writing the data change status identifiers corresponding to the multiple change categories in the same group into a preset database table based on the sorting order specifically includes: Based on the primary key information, changed field content, data change status identifier corresponding to the data addition category, the first time, the data change status identifier corresponding to the data deletion / modification category, and the second time, the record information corresponding to the changed data is constructed. Based on the sorting order, the record information corresponding to the multiple change categories in the same group is sequentially written into the preset database table; Constructing the record information corresponding to the changed data specifically includes: Generate a unique primary key based on the information to be recorded corresponding to the currently changed data; Based on the unique primary key and the information to be recorded, generate the record information corresponding to the currently changed data; The step of determining the data change status identifier corresponding to the changed data based on the preset data change status identifier table and the change category specifically includes: Obtain the field content before and after the change corresponding to the changed data; The change category is determined based on the field content before the change and the field content after the change; In the preset data change status identifier table, the identifier group corresponding to the change category is determined; Based on the field content before the change and the field content after the change, the corresponding data change status identifier is determined in the identifier group.