A data collection method based on primary key mapping

Abstract

The present invention particularly relates to a data collection method based on primary key mapping. This data collection method based on primary key mapping records the primary key mappings of different systems in the same data table, and relies on the data update time to complete data fusion and collection while ensuring the independence between systems; configures the information of the synchronization table in the synchronization service through the synchronization table information table and the synchronization table field table, associates the data of the tables between systems through the primary key mapping table, and determines the addition and deletion through the synchronization primary key table. This data collection method based on primary key mapping neither affects the independence between systems nor requires excessive development at the business level. Through the synchronization service, it directly realizes the synchronous collection of database tables by configuring the information of the synchronization table.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a data collection method based on primary key mapping. Background Art

[0002] In the early stage of grass-roots data governance projects, in order to meet the personalized and security requirements of local areas, the same system is often deployed in multiple places. When a unified parent system needs to be established later, the parent system often needs to synchronously collect the data of the subsystems.

[0003] In order to complete the data association and synchronization between two systems and reduce duplicate coding, the present invention proposes a data collection method based on primary key mapping. Summary of the Invention

[0004] In order to make up for the defects of the prior art, the present invention provides a simple and efficient data collection method based on primary key mapping.

[0005] The present invention is realized by the following technical solutions:

[0006] A data collection method based on primary key mapping, by recording the primary key mappings of different systems in the same data table, relying on the data update time, while ensuring the independence between systems, completing data fusion and collection;

[0007] It includes the following steps:

[0008] Step S1, the parent system creates a synchronization system table, a synchronization table information table, a synchronization table field table and a primary key mapping table; the subsystem creates a synchronization primary key table;

[0009] Step S2, after the synchronization service starts, connect to the database of the system to be synchronized according to the synchronization system table, read the data of the synchronization table information table, obtain the synchronization table information, and synchronize the data in sequence according to the synchronization order of the synchronization table;

[0010] Step S3, compare the subsystem data table and the synchronization primary key table, obtain the primary keys of the deleted data, query the parent system primary keys in the primary key mapping table according to the subsystem primary keys, delete the corresponding parent system data, and at the same time delete the corresponding primary key mapping table data and synchronization primary key table data;

[0011] Step S4, compare the subsystem data table and the synchronization primary key table, obtain the newly added data, and process the data according to the field information in the synchronization table field table;

[0012] After the processing is completed, insert the data into the parent system, and at the same time obtain the parent system primary keys, record the table name, subsystem primary keys and parent system primary keys into the primary key mapping table, and finally insert the newly added primary key data into the synchronization primary key table;

[0013] Step S5: Obtain the last update time according to the synchronization table information table, obtain the updated data of the synchronization table based on the update time increment, and process the data according to the field information in the synchronization table field table;

[0014] After the modification is completed, query the parent system primary key in the primary key mapping table according to the data primary key, and modify the data;

[0015] Step S6: After each synchronization, record the latest data update time of the current synchronization table in the synchronization table information table.

[0016] The synchronization system table is used to record the system information to be synchronized, including the system name to be synchronized and the database connection information.

[0017] The synchronization table information table is used to record the information of the tables to be synchronized, including the system name to be synchronized, the table name, the table primary key field name, the table data update time field name, the last update time of the table, and the synchronization order of the table;

[0018] The synchronization order of the tables is configured according to the association relationship between the tables, and the associated tables are synchronized first; when synchronizing, the updated data is read according to the table update time field name and the last update time value increment.

[0019] After the synchronization is completed, obtain the latest update time of the synchronization table, and update the last update time field of the table.

[0020] The synchronization table field table is used to record all field information of the synchronization table, including the system name, the table name, the field name, whether the field is associated with the primary key of other tables, and the table name of the associated table;

[0021] When synchronizing, obtain the field information of the table to be synchronized according to the table name. When the field is the primary key field of the record associated table, the primary key mapping is modified during the synchronization process;

[0022] The primary key mapping table is used to record the mapping information of the primary keys of the tables in the subsystem and the parent system during the synchronization process, including the system name, the table name, the parent system data primary key value, and the subsystem data primary key value;

[0023] During synchronization, for the field value associated with the primary key of other tables, query the new primary key value from the primary key mapping table according to the primary key value and the associated table name, and modify the field value;

[0024] When adding new data, obtain the parent system primary key, save it to the primary key mapping table, and perform corresponding operations according to the query of the parent system primary key value when updating and deleting data.

[0025] The synchronization primary key table is used to record the primary key information of the currently synchronized tables, including the table name and the primary key value;

[0026] During data synchronization, compare the primary key data of the synchronization table with the corresponding table primary key data in the synchronization primary key table, obtain the newly added and deleted data, and synchronize these two types of data separately.

[0027] A data collection device based on primary key mapping, characterized in that it includes a memory and a processor; the memory is used to store computer programs, and the processor is used to implement the above method steps when executing the computer programs.

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

[0029] The beneficial effect of the present invention is that: the data collection method based on primary key mapping neither affects the independence between systems nor requires excessive development at the business level. Through the synchronization service, by configuring the synchronization table information, the synchronous collection of database tables is directly realized. Brief Description of the Drawings

[0030] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0031] Appendix Figure 1 It is a schematic diagram of the data collection method based on primary key mapping of the present invention. Detailed Embodiments

[0032] In order to enable those skilled in the art to better understand the technical solutions in the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in combination with the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0033] In a database, an auto-increment primary key can be used to ensure the uniqueness of data table records, improve query efficiency, facilitate data maintenance, and primary keys are also commonly used for association design between multiple tables. During the process of data fusion and collection between systems, for the case of multi-site deployment of the same system, the data table structures are the same, and data collection mainly solves the association and conflict of auto-increment primary keys of data tables.

[0034] In a database, an auto-increment primary key can be used to ensure the uniqueness of data table records. At the same time, it can improve query efficiency, facilitate data maintenance, and is also commonly used as an association design between multiple tables. During the process of data integration and collection between systems, for the situation of multi-site deployment of the same system, the data table structures are the same, and data collection mainly solves the association and conflict of auto-increment primary keys in data tables.

[0035] In this scenario, the data table structures are the same, and synchronization mainly solves the conflict of primary keys between systems. Therefore, by recording the primary key mapping of data between systems, during the synchronization process, the primary keys between systems can be associated when adding new data, and data can be associated through the mapping information of primary keys during updates and deletions, realizing the synchronous collection of data.

[0036] This data collection method based on primary key mapping, by recording the primary key mapping of the same data table in different systems and relying on the data update time, completes data integration and collection while ensuring the independence between systems;

[0037] Configure the information of the synchronization table in the synchronization service through the synchronization table information table B and the synchronization table field table C, associate the data of the tables between systems through the primary key mapping table D, and judge the addition and deletion through the synchronization primary key table E.

[0038] It includes the following steps:

[0039] Step S1: The parent system creates a synchronization system table A, a synchronization table information table B, a synchronization table field table C, and a primary key mapping table D; the child system creates a synchronization primary key table E;

[0040] Step S2: After the synchronization service starts, connect to the databases of the systems to be synchronized according to the synchronization system table A, read the data in the synchronization table information table B, obtain the synchronization table information, and synchronize the data in sequence according to the synchronization order of the synchronization table;

[0041] Step S3: Compare the data table of the child system and the synchronization primary key table E, obtain the primary keys of the deleted data, query the primary keys of the parent system in the primary key mapping table D according to the primary keys of the child system, delete the corresponding data in the parent system, and at the same time delete the data in the corresponding primary key mapping table D and the synchronization primary key table E;

[0042] Step S4: Compare the data table of the child system and the synchronization primary key table E, obtain the newly added data, and process the data according to the field information in the synchronization table field table C;

[0043] After the processing is completed, insert the data into the parent system, obtain the primary key of the parent system at the same time, record the table name, the primary key of the child system, and the primary key of the parent system in the primary key mapping table D, and finally insert the newly added primary key data into the synchronization primary key table E;

[0044] Step S5: Obtain the last update time according to the synchronization table information table B, obtain the updated data of the synchronization table according to the update time increment, and process the data based on the field information in the synchronization table field table C;

[0045] After the modification is completed, query the parent system primary key in the primary key mapping table D according to the data primary key, and modify the data;

[0046] Step S6: After each synchronization, record the latest data update time of the current synchronization table in the synchronization table information table B.

[0047] The synchronization system table A is used to record the system information to be synchronized, including the system name to be synchronized and the database connection information.

[0048] The synchronization table information table B is used to record the information of the tables to be synchronized, including the system name, table name, table primary key field name, table data update time field name, the last update time of the table, and the synchronization order of the table;

[0049] The synchronization order of the tables is configured according to the association relationship between the tables, and the associated tables are synchronized first; when synchronizing, the updated data is read according to the table update time field name and the last update time value increment.

[0050] After the synchronization is completed, obtain the latest update time of the synchronization table, and update the last update time field of the table.

[0051] The synchronization table field table C is used to record all field information of the synchronization table, including the system name, table name, field name, whether the field is associated with the primary key of other tables, and the table name of the associated table;

[0052] When synchronizing, obtain the field information of the table to be synchronized according to the table name. When the field is the primary key field of the record associated table, the primary key mapping is modified during the synchronization process;

[0053] The primary key mapping table D is used to record the mapping information of the primary keys of the tables in the subsystem and the parent system during the synchronization process, including the system name, table name, parent system data primary key value, and subsystem data primary key value;

[0054] When synchronizing, for the field values associated with the primary keys of other tables, query the new primary key value from the primary key mapping table D according to the primary key value and the associated table name, and modify the field values;

[0055] When adding new data, obtain the parent system primary key, save it to the primary key mapping table D, and perform corresponding operations according to the query of the parent system primary key value when updating and deleting data.

[0056] The synchronization primary key table E is used to record the primary key information of the tables that have been synchronized currently, including the table name and the primary key value;

[0057] During data synchronization, compare the primary key data of the synchronization table with the corresponding table primary key data in the synchronization primary key table E to obtain the newly added and deleted data, and synchronize these two types of data separately.

[0058] The data collection device based on primary key mapping includes a memory and a processor; the memory is used to store computer programs, and the processor is used to implement the above method steps when executing the computer programs.

[0059] A computer program is stored on the readable storage medium, and the computer program implements the above method steps when executed by the processor.

[0060] Compared with the prior art, the data collection method based on primary key mapping has the following characteristics:

[0061] First, synchronize data periodically through the intermediate service, directly synchronize data at the database level, and the business layer is unaware.

[0062] Second, the synchronization content can be directly configured, avoiding repetitive coding work.

[0063] The above-described embodiments are only one of the specific implementation manners of the present invention, and the ordinary 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 data collection method based on primary key mapping, characterized in that: By recording the primary key mappings of different systems for the same data table and relying on the data update time, while ensuring the independence between systems, data fusion and collection are completed; It includes the following steps: Step S1: The parent system creates a synchronization system table, a synchronization table information table, a synchronization table field table, and a primary key mapping table; the child system creates a synchronization primary key table; Step S2: After the synchronization service starts, connect to the database of the system to be synchronized according to the synchronization system table, read the data in the synchronization table information table, obtain the synchronization table information, and synchronize the data in sequence according to the synchronization order of the synchronization table; Step S3: Compare the data table of the child system with the synchronization primary key table to obtain the primary keys of the deleted data, query the primary keys of the parent system in the primary key mapping table according to the primary keys of the child system, delete the corresponding parent system data, and at the same time delete the corresponding data in the primary key mapping table and the synchronization primary key table; Step S4: Compare the data table of the child system with the synchronization primary key table to obtain the newly added data, and process the data according to the field information in the synchronization table field table; After the processing is completed, insert the data into the parent system, obtain the primary key of the parent system at the same time, record the table name, the primary key of the child system, and the primary key of the parent system into the primary key mapping table, and finally insert the newly added primary key data into the synchronization primary key table; Step S5: Obtain the last update time according to the synchronization table information table, obtain the updated data of the synchronization table according to the update time increment, and process the data according to the field information in the synchronization table field table; After the modification is completed, query the primary key of the parent system in the primary key mapping table according to the data primary key and modify the data; Step S6: After each synchronization, record the latest data update time of the current synchronization table in the synchronization table information table.

2. The data collection method based on primary key mapping according to claim 1, wherein: The synchronization system table is used to record the information of the system to be synchronized, including the name of the system to be synchronized and the database connection information.

3. The data collection method based on primary key mapping according to claim 1, wherein: The synchronization table information table is used to record the information of the table to be synchronized, including the name of the system to be synchronized, the table name, the table primary key field name, the table data update time field name, the last update time of the table, and the synchronization order of the table; The synchronization order of the table is configured according to the association relationship between the tables, and the associated tables are synchronized preferentially; When synchronizing, read the updated data incrementally according to the table update time field name and the last update time value; After the synchronization is completed, obtain the latest update time of the synchronization table and update the last update time field of the table.

4. The data collection method based on primary key mapping according to claim 1, wherein: The synchronization table field table is used to record all the field information of the synchronization table, including the system name, the table name, the field name, whether the field is associated with the primary key of other tables, and the table name of the associated table; When synchronizing, obtain the field information of the table to be synchronized according to the table name. When the field is the primary key field of the record associated table, the primary key mapping is modified during the synchronization process.

5. The data collection method based on primary key mapping according to claim 1, characterized in that: The primary key mapping table is used to record the mapping information of the primary keys of the tables in the child system and the parent system during the synchronization process, including the system name, the table name, the primary key value of the parent system data, and the primary key value of the child system data; When synchronizing, for the field value associated with the primary key of other tables, query the new primary key value from the primary key mapping table according to the primary key value and the associated table name, and modify the field value; When adding new data, obtain the primary key of the parent system and save it to the primary key mapping table. When updating and deleting data, perform corresponding operations according to the query of the primary key value of the parent system.

6. The data collection method based on primary key mapping according to claim 1, wherein: The synchronization primary key table is used to record the primary key information of the currently synchronized tables, including the table name and the primary key value; During data synchronization, compare the primary key data of the synchronization table with the corresponding table primary key data in the synchronization primary key table to obtain the newly added and deleted data, and synchronize these two types of data respectively.

7. A data collection device based on primary key mapping, characterized in that: It includes a memory and a processor; the memory is used to store computer programs, and the processor is used to implement the method described in any one of claims 1 to 6 when executing the computer programs.

8. A readable storage medium, characterized in that: A computer program is stored on the readable storage medium, and when the computer program is executed by a processor, the method described in any one of claims 1 to 6 is implemented.

Images