Database comparison method and related device

Abstract

The invention discloses a database comparison method and related device.The database comparison method comprises the steps that firstly, the sequence of fields in a reference database during query and the field mapping relation corresponding to a target database to be compared are obtained, and then the target database to be compared is obtained according to the sequence of the fields in the reference database during query on the basis of the field mapping relation; the method comprises the following steps: determining the sequence of fields corresponding to a target library during query, then correspondingly generating a query statement according to the sequence of the fields during query of a reference library and the sequence of the query fields in the target library, respectively querying data in a reference database and a target database, and comparing the queried data. Therefore, when the data is queried, the standard database and the target database have the consistent field sequence, so that the data consistency of corresponding fields in different databases is ensured, the accuracy of data comparison is effectively improved, the manpower and time cost is saved through the mode, and the efficiency of data comparison is effectively improved.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a database comparison method and related devices. Background Art

[0002] A database is a structured collection used to store and manage data. In scenarios such as data migration, system upgrades, data synchronization, and data audits, it is usually necessary to use database comparison methods to compare the structures or data differences of different databases to ensure data consistency, completeness, and accuracy.

[0003] In the related art, a table file can be used to import and export data in a database to verify the consistency of the data.

[0004] However, since databases have different field processing mechanisms, there may be import failures or data errors when using this method to import data into the target database, resulting in poor efficiency and accuracy of the comparison. Summary of the invention

[0005] In view of this, the present application provides a database comparison method and related devices. By mapping the order of querying fields in a reference database to a target database, the order of fields when performing data queries between the reference database and the target database can be made consistent, thereby ensuring the consistency of data in different databases and effectively improving the accuracy of data comparison.

[0006] To solve the above problems, the technical solutions provided by this application are as follows:

[0007] On the one hand, the present application proposes a database comparison method, comprising:

[0008] Acquire a first field query logic of a reference database, and a mapping relationship between the reference database and a target database, wherein the target database is a database to be compared with the reference database, the first field query logic is used to identify the order of query fields in a data table of the reference database, and the mapping relationship is used to identify a field mapping relationship between a data table in the reference database and a data table in the target database to be compared;

[0009] Based on the mapping relationship between the reference database and the target database, the first field query logic is mapped to the target database to form a second field query logic of the target database, wherein the second field query logic is used to identify the order of query fields in a data table of the target database;

[0010] Generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively;

[0011] Obtaining a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively;

[0012] A comparison result is obtained according to the first data set and the second data set.

[0013] In a possible implementation, before acquiring the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes:

[0014] According to the comparison requirements, the table structure information corresponding to the benchmark database and the target database is extracted into a table file, wherein the table file includes a benchmark table and a target table. The benchmark table is used to store the table structure information of each data table in the benchmark database, and the target table is used to store the table structure information of each data table in the target database. The table structure information of the benchmark table and the target table have a corresponding relationship.

[0015] In a possible implementation, the table structure includes field information, and before acquiring the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes:

[0016] Determining special fields in the reference table and the target table that have different types of corresponding fields;

[0017] If the type of the special field is a binary object, a long text, a character string, or a multimedia text, the information corresponding to the special field is deleted from the reference table and the target table to obtain an updated reference table and target table;

[0018] The generating, according to the first field query logic and the second field query logic, a first query statement corresponding to the reference library and a second query statement corresponding to the target library respectively, comprises:

[0019] According to the first field query logic and the second field query logic, respectively, based on the updated table structure information of the reference table and the target table, a first query statement corresponding to the reference database and a second query statement corresponding to the target database are generated.

[0020] In a possible implementation, the first field query logic is obtained in the following manner:

[0021] If a primary key exists in the data table in the reference database, determining the order of query fields in the data table based on the order of fields in the data table;

[0022] If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the search fields in the data table;

[0023] The first field query logic is obtained based on the order of query fields corresponding to all data tables in the reference database.

[0024] In a possible implementation, obtaining a comparison result according to the first data set and the second data set includes:

[0025] Performing a difference operation on the first data set and the second data set to obtain a first difference set and a second difference set, wherein the first difference set and the second difference set respectively represent sets of data that exist in one of the first data set or the second data set but do not exist in the other data set;

[0026] For the target data in the first difference set, a similarity comparison is performed on each data in the second difference set:

[0027] Determine the similarity between each data in the second difference set and the target data;

[0028] According to the similarity, determining maximum similarity data, wherein the maximum similarity data is data having the greatest similarity to the target data;

[0029] If the maximum similarity is greater than a preset threshold, storing the target data and the maximum similarity data as similar data in the data table;

[0030] If the maximum similarity is not greater than a preset threshold, an empty record is added to the data table.

[0031] In another aspect, the present application provides a database comparison device, the device comprising an acquisition unit, a generation unit, a query unit, and a comparison unit:

[0032] The acquisition unit is used to acquire a first field query logic of a reference database and a mapping relationship between the reference database and a target database, the target database being a database to be compared with the reference database, the first field query logic being used to identify an order of query fields in a data table of the reference database, and the mapping relationship being used to identify a field mapping relationship between a data table in the reference database and a data table in the target database to be compared;

[0033] The generating unit is used to map the first field query logic to the target database based on the mapping relationship between the reference database and the target database to form a second field query logic of the target database, wherein the second field query logic is used to identify the order of query fields in the data table of the target database;

[0034] The generating unit is further configured to generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively;

[0035] The query unit is used to obtain a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively;

[0036] The comparison unit is used to obtain a comparison result according to the first data set and the second data set.

[0037] In a possible implementation, the device further includes an extraction unit:

[0038] The extraction unit is used to extract the table structure information corresponding to the benchmark database and the target database into a table file according to the comparison requirements. The table file includes a benchmark table and a target table. The benchmark table is used to store the table structure information of each data table in the benchmark database. The target table is used to store the table structure information of each data table in the target database. The table structure information of the benchmark table and the target table have a corresponding relationship.

[0039] In a possible implementation manner, the device further includes a determining unit and a deleting unit:

[0040] The determining unit is used to determine a special field in the reference table and the target table where the type of the corresponding field is different;

[0041] The deleting unit is used for deleting the information corresponding to the special field in the reference table and the target table to obtain an updated reference table and target table if the type of the special field is a binary object, a long text, a character string, and a multimedia text;

[0042] The generating unit is further configured to:

[0043] According to the first field query logic and the second field query logic, respectively, based on the updated table structure information of the reference table and the target table, a first query statement corresponding to the reference database and a second query statement corresponding to the target database are generated.

[0044] In a possible implementation manner, the acquiring unit is further configured to:

[0045] If a primary key exists in the data table in the reference database, determining the order of query fields in the data table based on the order of fields in the data table;

[0046] If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the search fields in the data table;

[0047] The first field query logic is obtained based on the order of query fields corresponding to all data tables in the reference database.

[0048] In a possible implementation, the comparison unit further includes:

[0049] Performing a difference operation on the first data set and the second data set to obtain a first difference set and a second difference set, wherein the first difference set and the second difference set respectively represent sets of data that exist in one of the first data set or the second data set but do not exist in the other data set;

[0050] For the target data in the first difference set, a similarity comparison is performed on each data in the second difference set:

[0051] Determine the similarity between each data in the second difference set and the target data;

[0052] According to the similarity, determining maximum similarity data, wherein the maximum similarity data is data having the greatest similarity to the target data;

[0053] If the maximum similarity is greater than a preset threshold, storing the target data and the maximum similarity data as similar data in the data table;

[0054] If the maximum similarity is not greater than a preset threshold, an empty record is added to the data table.

[0055] In another aspect, the present application provides a computer device, the computer device comprising a processor and a memory:

[0056] The memory is used to store computer programs;

[0057] The processor is configured to execute the method according to the computer program.

[0058] In yet another aspect, the present application provides a computer-readable medium for storing a computer program, wherein the computer program implements the method when executed by a computer device.

[0059] In yet another aspect, the present application provides a computer program product comprising a computer program, which, when executed on a computer device, enables the computer device to perform the method.

[0060] It can be seen that this application has the following beneficial effects:

[0061] The present application provides a database comparison method, which determines the field order that the target database should correspond to when querying by obtaining the field order when querying in the reference database and the field mapping relationship corresponding to the target database to be compared, and then generates a query statement according to the order of the reference database query fields and the order of the target database query fields, and queries and compares the data in the reference database and the target database respectively. In this way, the order of the fields when querying data between the reference database and the target database can be made consistent, so as to ensure the consistency of data in different databases, avoid the misalignment of the comparison data caused by the different field query orders of the database, effectively improve the accuracy of data comparison, and in this way, the query statement can be automatically generated to compare the database data, saving manpower and time costs, and effectively improving the efficiency of data comparison. BRIEF DESCRIPTION OF THE DRAWINGS

[0062] Figure 1 A flowchart of a database comparison method provided in an embodiment of the present application;

[0063] Figure 2 A schematic diagram of a source input file provided in an embodiment of the present application;

[0064] Figure 3 An overall flow chart of a similarity comparison provided in an embodiment of the present application;

[0065] Figure 4 A flowchart of another database comparison method provided in an embodiment of the present application;

[0066] Figure 5 A schematic diagram of a process flow of a database comparison device provided in an embodiment of the present application. DETAILED DESCRIPTION

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

[0068] As described in the background technology, in the related technology, the consistency of data can be verified by importing and exporting data in the database through a table file. However, since databases have different field processing mechanisms, when using this method to import data into the target database, there are cases where the import fails or there are data errors, resulting in poor efficiency and accuracy of data comparison.

[0069] Therefore, the present application provides a database comparison method, which determines the field order that the target database should correspond to when querying by obtaining the field order when querying in the benchmark database and the field mapping relationship corresponding to the target database to be compared, thereby ensuring that the order of fields when performing data queries between the benchmark database and the target database is consistent, thereby ensuring the consistency of data in different databases, effectively improving the accuracy of data comparison, saving manpower and time costs, and effectively improving the efficiency of data comparison.

[0070] See also Figure 1 As shown, it is a flowchart of a database comparison method provided in an embodiment of the present application. In this embodiment, it can be executed by a computer device as an example.

[0071] S101: Acquire the first field query logic of the reference database and the mapping relationship between the reference database and the target database.

[0072] There may be one or more data tables used to store data in the benchmark database. When performing data comparison, data queries are performed in data tables. Data tables consist of rows and columns, where rows represent records and columns represent fields. For example, in data table 1, ID, Name, and Age represent fields, 21 and 20 represent the field values ​​corresponding to the field Age, and a row of data (1, XX, 21) is a record, which contains the specific values ​​of all fields.

[0073] Table 1

[0074] ID Name Age 1 XX 21 2 XXX 20

[0075] Field order refers to the order in which fields are arranged when defining a data table. When using a query statement to query the data in the entire table, if the order of the fields is not defined, the data will be queried according to the field order.

[0076] The order of query fields refers to the order in which the specified fields are displayed when querying the data table, which determines the order of columns in the query results. For example, for data table 1, the order of query fields is defined as ID, Age, Name when querying, then the data obtained when querying the entire table is in the form of Table 2:

[0077] Table 2

[0078] ID Age Name 1 21 XX 2 20 XXX

[0079] The first field query logic is used to identify the order of querying fields in the data table of the reference database. When the database has multiple data tables, the computer device needs to query each data table in the reference database in turn when performing database comparison. Since different data tables have different fields, the order of querying fields in each data table is different. Therefore, it is necessary to obtain the order of querying fields in the reference database according to the order of querying fields in the data table.

[0080] As an example, there are data tables A and B in the benchmark database. The field order of data table A is a, b and c, and the fields of data table B are d, e and f. The computer device first queries data table A and then queries data table B in this order to query data. The order of the query fields in the benchmark database can be obtained according to the field order of the data tables, which is a, b, c, d, e, f. The order of the query fields in data table A or data table B can also be customized as needed to obtain the order of the query fields in the benchmark database, such as a, b, c, e, f, e, etc.

[0081] The field mapping relationship refers to the correspondence between the fields of the two databases that need to be compared. The computer device can obtain the mapping relationship between the reference database and the target database based on the comparison requirements or the inherent correspondence in the actual scenario.

[0082] As an example, in a database migration scenario, fields in different databases before and after the database migration have a corresponding relationship, and the computer device obtains the mapping relationship between the fields in the databases before and after the migration through the corresponding relationship.

[0083] S102: Based on the mapping relationship between the reference database and the target database, the first field query logic is mapped to the target database to form a second field query logic of the target database.

[0084] The second field query logic is used to identify the order of query fields in the data table of the target database. In the actual application scenario of database migration or database upgrade, the order of fields in the target database may change.

[0085] Assume that there are three fields a, b, and c in data table A in the base database. In the absence of a primary key, data table B in the target database has fields d, e, and f corresponding to the mapping relationship between a, b, and c. The fields in base table A are arranged in the order of a, b, and c. In the database migration scenario, data table B may have a new primary key (such as e is the primary key of the target table). If data table A is directly compared with data table B, the query data order will not match the field mapping relationship. As shown in Table 3, the alphabetical order represents the field priority.

[0086] Table 3

[0087]

[0088] The computer device maps the order of query fields in the benchmark database to the target database through the mapping relationship between the benchmark database and the target database, and forms the order of query fields corresponding to the benchmark database in the target database, avoiding data query result mismatch caused by primary key changes or new construction during database migration (transformation).

[0089] As an example, the benchmark database has fields a, b, c, and the fields in the target database mapped to the benchmark database are e, d, f, where the order of query fields in the benchmark database is a, b, c, then the order is mapped to the target database so that the order of query fields in the target database is e, d, f.

[0090] S103: Generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively.

[0091] The query statement is an instruction for querying data in a database, the first query statement is an instruction for querying data in a reference database, and the second query statement is an instruction for querying data in a target database. The computer device generates a corresponding first query statement to query data in the reference database according to the order of query fields in the reference database, and generates a corresponding second query statement to query data in the target database according to the order of query fields in the target database.

[0092] As an example, the benchmark database is a MySQL database, in which the order of query fields is a, b, c, and the order of query fields in the target database is e, d, f. If a, b, c are in a data table A, and e, d, f are in a data table B, the first query statement can be (Select a, b, c from A;); the corresponding second query statement can be (Select e, d, ffrom B;).

[0093] As another example, the benchmark database is a MySQL database, in which the order of query fields is a, b, c, and the order of query fields in the target database is e, d, f. If a and b are in data table A, c is in data table B, e and d are in data table A', and c is in data table B', since the database query needs to be performed in units of one data table, the first query statement can be (Select a, b from A;) and (Select c from B;); the corresponding second query statement can be (Select e, dfrom A';)(Select f from B';).

[0094] S104: Obtain a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively.

[0095] The first data set is a data set obtained by querying the reference database according to the first query statement. When the reference database and the target database need to compare multiple data tables, the data set includes data in multiple data tables in the reference database to be queried.

[0096] The computer device queries the relevant data in the reference database according to the first query statement to obtain a first data set, and queries the relevant data in the target database according to the second data query statement to obtain a second data set.

[0097] As an example, the first query statement of the computer device is (Select a,b from A;) and (Selectc from B;). When querying in the benchmark database, the data corresponding to the a and b fields in table A are first queried and stored in table file 1. Then the data corresponding to the c field in table B is queried and appended to table file 1 for storage. When the second query statement can be (Select e,dfrom A';)(Select f from B';), the corresponding data queried in table A' and table B' are stored in table file 2 according to the above steps.

[0098] S105: Obtain a comparison result according to the first data set and the second data set.

[0099] The computer device compares data differences based on a first data set obtained by querying a benchmark database and a second data set obtained by querying a target database. The comparison result may be difference data between a benchmark table and a target table, difference data between a target table and a benchmark table, or data pairs with differences between the benchmark table and the target table.

[0100] Therefore, by obtaining the field order when querying in the benchmark library, mapping it to the target library, and generating the corresponding query field order in the target library, the order of corresponding fields can be made consistent when performing data queries between the benchmark library and the target library, ensuring the consistency of data compared in different databases, avoiding the misalignment of comparison data due to different field query orders in the database, and effectively improving the accuracy of data comparison. In this way, query statements can be automatically generated and the data in the database can be compared, which not only saves manpower and time costs, effectively improves the efficiency of data comparison, but also avoids the influence of subjective factors in the comparison process.

[0101] When performing database comparison, in order to establish a comparison relationship, the table structure information of the reference database and the target database may be preprocessed and exported into a table file for maintaining the field mapping rule. In a possible implementation, before obtaining the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes:

[0102] According to the comparison requirements, extract the table structure information corresponding to the benchmark database and the target database into a table file.

[0103] The table file includes a reference table and a target table. The reference table is used to store the table structure information of each data table in the reference database, and the target table is used to store the table structure information of each data table in the target database. The table structure information of the reference table and the target table have a corresponding relationship.

[0104] Table structure information refers to the information that defines and describes the database table, including the table name and information about the fields in the table, such as field name, data type, constraints, and field length. The table file includes two tables: the benchmark table and the target table. The benchmark table is used to store the table structure information in the benchmark database, and the target table is used to store the table structure information in the target database.

[0105] The computer equipment determines the table structure information in the benchmark database and the target database, such as the table name, field name, field type, field length, etc., based on the data to be compared, through a preprocessing method combining a third-party dependency library and SQL, and extracts it into a table file according to the corresponding relationship of the field information, which is used as a source input file for subsequent data comparison. In addition, since the database performs data query in units of data tables, the information of all fields of the same data table is placed in adjacent rows in the benchmark table and the target table to facilitate data query.

[0106] refer to Figure 2 , is a schematic diagram of a source input file provided by the present application, in which the left side of the source input file is a reference table obtained by extracting the table structure information of the reference database, and the right side is a target table obtained by extracting the table structure information of the target database. In this file, the A and B fields of Table1 in the reference table are respectively corresponding to the A' and B' fields of Table1 in the target table.

[0107] Therefore, the table structure information in the database is backed up in this way. Using this table file as the source input file, the field information in the benchmark database can be further processed, thereby avoiding tampering with the database during the query based on the field and ensuring the security of the data in the original database. In addition, the table file contains information about the fields in the benchmark database and the target database, and can establish a mapping relationship between corresponding fields in different databases. Data query based on this table file helps to improve the efficiency of data comparison.

[0108] Based on the source input file, data comparison is performed between the reference table and the target table. In a possible implementation, the table structure includes field information. Before obtaining the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes:

[0109] Identify special fields in the base table and target table that have different types for corresponding fields;

[0110] If the type of the special field is a binary object, a long text, a character string, or a multimedia text, the information corresponding to the special field is deleted from the base table and the target table to obtain an updated base table and an updated target table;

[0111] Generating a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively, including:

[0112] According to the first field query logic and the second field query logic, respectively, based on the updated table structure information of the reference table and the target table, a first query statement corresponding to the reference database and a second query statement corresponding to the target database are generated.

[0113] The information of the fields in the base table and the target table has a corresponding relationship, that is, the field mapping rules are maintained in the source input file, and the comparison relationship between the base table and the target table is established.

[0114] Special fields are fields with different parsing field threshold results due to different database types. They are usually expressed as different types in different databases. Fields of binary objects, strings, texts, etc. generally contain a large amount of data. In the scenario of comparing data details, these fields are less important in the data table. Therefore, the computer device determines the fields of different types according to the relationship between the corresponding fields. When the type of the field is binary object, long text, string, and multimedia text, the information corresponding to the field is deleted from the base table and the target table at the same time to obtain an updated base table and target table.

[0115] As an example, in Figure 2If the reference table includes a field E of the large text type, the information corresponding to the field E is deleted, that is, the row of records with sequence number 5 is deleted in both the reference table and the data table to obtain the updated reference table and target table.

[0116] After the benchmark table and the target table are updated, some special fields are deleted from the table structure information of the benchmark table and the target table compared with the table structure information between the benchmark database and the target database. The computer device needs to generate a corresponding first query statement based on the updated table structure information of the benchmark table and the target table, according to the order of the query fields in the benchmark database, and generate a corresponding second query statement based on the order of the query fields in the target database.

[0117] As an example, both the benchmark database and the target database are MySQL databases. The order of query fields in the benchmark database is a, b, c, and the order of query fields in the target database is e, d, f, where a, b, c are in a data table A, and e, d, f are in a data table B. However, after the special fields are deleted in the source input file, the remaining fields in the benchmark table are a and c, and the remaining fields in the target table are e and f. The first query statement generated is (Select a, c from A;), and the corresponding second query statement is (Select e, f from B;).

[0118] Therefore, by deleting these fields, we can effectively avoid the situation where query data errors caused by different database types. In the scenario of detailed data comparison, the text data or strings corresponding to special fields are unimportant fields. Deleting these fields can effectively improve the efficiency of data comparison.

[0119] In a possible implementation, the first field query logic is obtained in the following manner:

[0120] If the data table in the reference database has a primary key, the order of the query fields in the data table is determined based on the field order of the data table;

[0121] If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the query fields in the data table;

[0122] Based on the order of query fields corresponding to all data tables in the reference database, a first field query logic is obtained.

[0123] The primary key is used to uniquely identify a row of data in a data table. It is often non-repeatable and non-empty. Fields in the database table are sorted based on this attribute.

[0124] When a database table is created, a unique primary key is usually set. However, in some cases, a database table can be created without a primary key. Common scenarios without a primary key are shown in Table 4.

[0125] Table 4

[0126]

[0127] In the absence of a primary key, compared to the traditional methods of processing database tables such as self-increasing primary keys, random primary keys, joint primary keys, etc., setting a primary key facilitates field sorting. However, this method introduces a new primary key, which affects the initial characteristics of the database table and will have a certain impact on the comparison results.

[0128] When data table A in the benchmark database does not have a primary key, the computer device obtains the index order of the fields in data table A determined according to actual needs as the order of the actual query fields in data table A. When data table A has a primary key, the field order, that is, the order of the fields when defining the data table, is used as the order of the query fields in the data table. Then, the first field query logic is obtained according to the order of the query fields corresponding to all data tables in the benchmark database, and then the field query logic is mapped to data table B to keep the data consistent.

[0129] Therefore, this method can determine the order of query fields in the benchmark database according to predefined rules. When the data table in the benchmark database does not have a primary key, the index order of the fields is used as the order of the query fields to retain the original characteristics of the database and reduce unnecessary modifications.

[0130] In a possible implementation, obtaining a comparison result according to the first data set and the second data set includes:

[0131] S1051: Perform a difference operation on the first data set and the second data set to obtain a first difference set and a second difference set.

[0132] The first difference set and the second difference set respectively represent sets of data that exist in one of the first data set or the second data set but do not exist in the other data set.

[0133] The computer device obtains a first difference set between the reference database and the target database and a second difference set between the target database and the reference database through a difference operation based on the data set obtained in the reference database and the set obtained in the target database.

[0134] As an example, the first data set is (1, 2, 3, 4, 5), and the second data set is (2, 4, 5, 7, 8). The computer device obtains a first difference set (1, 3, 4) and a second difference set (7, 8) by performing a difference operation.

[0135] S1052: For the target data in the first difference set, perform a similarity comparison on each data in the second difference set:

[0136] Determine the similarity between each data in the second difference set and the target data;

[0137] According to the similarity, the maximum similarity data is determined, and the maximum similarity data is the data with the greatest similarity to the target data;

[0138] If the maximum similarity is greater than a preset threshold, the target data and the maximum similarity data are stored in the data table as similar data;

[0139] If the maximum similarity is not greater than the preset threshold, add an empty record to the data table.

[0140] The target data is a data in the first difference set. When comparing data, since the data in the first difference set and the data in the corresponding position in the second difference set do not have a corresponding relationship, it is necessary to compare all the data in the first difference set with all the data in the second difference set separately to analyze the reasons that may lead to the failure of data comparison.

[0141] Similarity refers to the degree of similarity between data, which can be calculated by methods such as cosine similarity or Euclidean distance. The preset threshold is a threshold pre-set based on the method for calculating similarity. When the similarity between two data is greater than the threshold, it indicates that the two data are similar.

[0142] The computer device obtains a first difference set between the reference database and the target database and a second difference set between the target database and the reference database through a difference operation based on the data set obtained in the reference database and the set obtained in the target database, and then compares the data in the first difference set with the data in the second difference set one by one. Taking a target data in the first difference set as an example, the specific data comparison method is as follows:

[0143] The computer device calculates the similarity between all the data in the second difference set and the target data through a data similarity comparison method, and records the similarity result. After comparing all the data in the second difference set, the data with the highest similarity to the data in the first difference set is determined. When the similarity is greater than a preset threshold, the compared data is considered similar, and it is considered that there is a possibility of tampering with the target data to the data with the highest similarity in the second difference set. The data with the highest similarity to the target data in the set and the target data are stored in the data table as comparison data. If the similarity between the target data and the data with the highest similarity in the set is not greater than the preset threshold, it is considered that the target data does not exist in the target database, and an empty record is added to the data table.

[0144] As an example, in the scenario of database migration, assume that there is a first difference set t1 and a second difference set t2, wherein the first difference set contains a data element set [c1, c2, c3] and includes header information to indicate that the data in the difference set exists in the reference database, and the second difference set contains a data element set [c4, c5, c6] and includes header information to indicate that the data in the difference set exists in the target database. When performing data comparison, the two difference sets are merged as a data set as an input set. At this time, the input set cannot well reflect the separability, that is, it cannot indicate whether the data exists in the reference database or the target database. It needs to be rearranged according to the initial field order of the reference table to avoid the aggregation of data in the same type of database.

[0145] Then, the computer device extracts c1 from the benchmark table set t1. In the summarized set, the database table is distinguished based on the header element whether it is located in the benchmark database or the target database. The comparison scenarios involve the following three scenarios: (1) If the data to be compared is located in the benchmark database, skip this comparison. (2) If the data to be compared is located in the target data and the similarity is greater than the preset range, the corresponding data results are grouped into a comparison group and stored in the comparison group dictionary. (3) If the data to be compared is located in the target database, but the similarity is less than the preset range, the data to be compared will be automatically eliminated until all the data to be compared are compared. If the comparison data with a similarity greater than the preset range with c1 is still not found, it is determined that the data in the benchmark database is lost during the migration process, and an empty record is automatically added to the comparison group.

[0146] refer to Figure 3, which is a general flow chart of a similarity comparison provided by the present application. First, the first difference set and the second difference set are merged, and the merged set is used as a data set and input into the similarity calculation program. Then, the data is redirected according to the field order of the benchmark database, and the rearranged data is traversed. First, the first data in the benchmark database is selected for comparison with other data in the set. When it is determined by the header tag that the data to be compared is in the target database, the similarity between the data is calculated to obtain similarity result data, that is, the similarity factor. Otherwise, the comparison is skipped. When the similarity factor is equal to 0, a blank line is added to the data table. When the similarity factor is greater than 0, it is compared with the historical similarity factor. If it is less than the historical similarity factor, the current data and the corresponding similarity factor are stored in the register. After comparison with other data, the data with the largest similarity factor is obtained and stored in the data table as comparison data. Then, the second data in the benchmark database is selected for comparison with the unmatched data in the set one by one. In this way, after all the data in the set are compared, the final data table is obtained and the data is displayed.

[0147] Therefore, by calculating the similarity between the data in a difference set and the data in another difference set, data differences can be identified and data that may have been tampered with can be located. In the scenario of database migration, the data differences before and after migration can be intuitively obtained, thereby helping to improve the accuracy of data migration.

[0148] In order to further sort out the database comparison method proposed in the embodiment of this application, see Figure 4 , which is a flow chart of another database comparison method provided in an embodiment of the present application, firstly, the reference database and the target database are preprocessed according to the comparison requirements, and the table structure information is extracted into a table file, wherein the table structure information of the reference database is stored in the reference table of the table file, and the table structure information of the target database is stored in the target table of the table file, and then the field type is preprocessed in the table file. If the field type is a large character table, a media text or a long text type, the corresponding field in the table file is converted to the same type or the corresponding field information is directly deleted, and then based on the order of the reference database query field and the field mapping relationship between the target database, a mapping relationship of the order of the query field is generated, and then a corresponding query statement is constructed according to the mapping relationship to query and obtain a corresponding data set, and then a difference set operation is performed on the data set to obtain a difference set, and the similarity of each data in the difference set is calculated to obtain the result of the secondary comparison and perform data visualization.

[0149] Based on the database comparison method provided in the above embodiment, the embodiment of the present application also provides a database comparison device, which is described in detail below with reference to the accompanying drawings.

[0150] See also Figure 5, is a flow chart of a database comparison device provided in the present application, the device 500 includes an acquisition unit 501, a generation unit 502, a query unit 503, and a comparison unit 504:

[0151] The acquisition unit 501 is used to acquire a first field query logic of a reference database and a mapping relationship between the reference database and a target database, wherein the target database is a database to be compared with the reference database, the first field query logic is used to identify the order of query fields in a data table of the reference database, and the mapping relationship is used to identify a field mapping relationship between a data table in the reference database and a data table in the target database to be compared;

[0152] The generating unit 502 is used to map the first field query logic to the target database based on the mapping relationship between the reference database and the target database to form a second field query logic of the target database, wherein the second field query logic is used to identify the order of query fields in the data table of the target database;

[0153] The generating unit 502 is further configured to generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively;

[0154] The query unit 503 is used to obtain a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively;

[0155] The comparison unit 504 is used to obtain a comparison result according to the first data set and the second data set.

[0156] Therefore, by obtaining the field order when querying in the benchmark library, mapping it to the target library, and generating the corresponding query field order in the target library, the field order when querying data between the benchmark library and the target library can be made consistent, ensuring the consistency of data compared in different databases, avoiding the misalignment of comparison data due to different field query orders in the database, and effectively improving the accuracy of data comparison. In this way, query statements can be automatically generated to compare databases, saving manpower and time costs and effectively improving the efficiency of data comparison.

[0157] In a possible implementation, the device further includes an extraction unit:

[0158] The extraction unit is used to extract the table structure information corresponding to the benchmark database and the target database into a table file according to the comparison requirements. The table file includes a benchmark table and a target table. The benchmark table is used to store the table structure information of each data table in the benchmark database. The target table is used to store the table structure information of each data table in the target database. The table structure information of the benchmark table and the target table have a corresponding relationship.

[0159] Therefore, the table structure information in the database is backed up in this way. Using this table file as the source input file, the field information in the benchmark database can be further processed, thereby avoiding tampering with the database during the query based on the field and ensuring the security of the data in the original database. In addition, the table file contains information about the fields in the benchmark database and the target database, and can establish a mapping relationship between corresponding fields in different databases. Data query based on this table file helps to improve the efficiency of data comparison.

[0160] In a possible implementation manner, the device further includes a determining unit and a deleting unit:

[0161] The determining unit is used to determine a special field in the reference table and the target table where the type of the corresponding field is different;

[0162] The deleting unit is used for deleting the information corresponding to the special field in the reference table and the target table to obtain an updated reference table and target table if the type of the special field is a binary object, a long text, a character string, and a multimedia text;

[0163] The generating unit is further configured to:

[0164] According to the first field query logic and the second field query logic, respectively, based on the updated table structure information of the reference table and the target table, a first query statement corresponding to the reference database and a second query statement corresponding to the target database are generated.

[0165] Therefore, by deleting these fields, we can effectively avoid the situation where query data errors caused by different database types. In the scenario of detailed data comparison, the text data or strings corresponding to special fields are unimportant fields. Deleting these fields can effectively improve the efficiency of data comparison.

[0166] In a possible implementation manner, the acquiring unit is further configured to:

[0167] If a primary key exists in the data table in the reference database, determining the order of query fields in the data table based on the order of fields in the data table;

[0168] If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the search fields in the data table;

[0169] The first field query logic is obtained based on the order of query fields corresponding to all data tables in the reference database.

[0170] Therefore, this method can determine the order of query fields in the benchmark database according to predefined rules. When the data table in the benchmark database does not have a primary key, the index order of the fields is used as the order of the query fields to retain the original characteristics of the database and reduce unnecessary modifications.

[0171] In a possible implementation, the comparison unit further includes:

[0172] Performing a difference operation on the first data set and the second data set to obtain a first difference set and a second difference set, wherein the first difference set and the second difference set respectively represent sets of data that exist in one of the first data set or the second data set but do not exist in the other data set;

[0173] For the target data in the first difference set, a similarity comparison is performed on each data in the second difference set:

[0174] Determine the similarity between each data in the second difference set and the target data;

[0175] According to the similarity, determining maximum similarity data, wherein the maximum similarity data is data having the greatest similarity to the target data;

[0176] If the maximum similarity is greater than a preset threshold, storing the target data and the maximum similarity data as similar data in the data table;

[0177] If the maximum similarity is not greater than a preset threshold, an empty record is added to the data table.

[0178] Therefore, by calculating the similarity between the data in a difference set and the data in another difference set, data differences can be identified and data that may have been tampered with can be located. In the scenario of database migration, the data differences before and after migration can be intuitively obtained, thereby helping to improve the accuracy of data migration.

[0179] Based on the above embodiments, an embodiment of the present application provides a computer device, including:

[0180] Memory for storing computer programs;

[0181] A processor is used to implement the steps of the above-mentioned database comparison method when executing the computer program.

[0182] On the basis of the above-mentioned embodiments, the embodiments of the present application further provide a computer-readable medium, on which a computer program is stored, and when the computer program is processed and executed, the steps of the above-mentioned database comparison method are implemented.

[0183] On the basis of the above-mentioned embodiments, the embodiments of the present application further provide a computer program product including a computer program, which, when executed on a computer device, enables the computer device to execute the steps of the database comparison method.

[0184] It should be noted that the various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts between the various embodiments can be referred to each other. For the system or device disclosed in the embodiment, since it corresponds to the method disclosed in the embodiment, the description is relatively simple, and the relevant parts can be referred to the method part description.

[0185] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A database comparison method, characterized in that: The method comprises: Acquire a first field query logic of a reference database, and a mapping relationship between the reference database and a target database, wherein the target database is a database to be compared with the reference database, the first field query logic is used to identify the order of query fields in a data table of the reference database, and the mapping relationship is used to identify a field mapping relationship between a data table in the reference database and a data table in the target database to be compared; Based on the mapping relationship between the reference database and the target database, the first field query logic is mapped to the target database to form a second field query logic of the target database, wherein the second field query logic is used to identify the order of query fields in a data table of the target database; Generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively; Obtaining a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively; A comparison result is obtained according to the first data set and the second data set.

2. The method according to claim 1, characterized in that Before acquiring the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes: According to the comparison requirements, the table structure information corresponding to the benchmark database and the target database is extracted into a table file, wherein the table file includes a benchmark table and a target table. The benchmark table is used to store the table structure information of each data table in the benchmark database, and the target table is used to store the table structure information of each data table in the target database. The table structure information of the benchmark table and the target table have a corresponding relationship.

3. The method according to claim 2, characterized in that The table structure includes field information, and before acquiring the first field query logic of the reference database and the mapping relationship between the reference database and the target database, the method further includes: Determining special fields in the reference table and the target table that have different types of corresponding fields; If the type of the special field is a binary object, a long text, a character string, or a multimedia text, the information corresponding to the special field is deleted from the reference table and the target table to obtain an updated reference table and target table; The generating, according to the first field query logic and the second field query logic, a first query statement corresponding to the reference library and a second query statement corresponding to the target library respectively, comprises: According to the first field query logic and the second field query logic, respectively, based on the updated table structure information of the reference table and the target table, a first query statement corresponding to the reference database and a second query statement corresponding to the target database are generated.

4. The method according to claim 1, characterized in that: The first field query logic is obtained in the following way: If a primary key exists in the data table in the reference database, determining the order of query fields in the data table based on the order of fields in the data table; If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the search fields in the data table; The first field query logic is obtained based on the order of query fields corresponding to all data tables in the reference database.

5. The method according to claim 1, characterized in that The obtaining a comparison result according to the first data set and the second data set includes: Performing a difference operation on the first data set and the second data set to obtain a first difference set and a second difference set, wherein the first difference set and the second difference set respectively represent sets of data that exist in one of the first data set or the second data set but do not exist in the other data set; For the target data in the first difference set, a similarity comparison is performed on each data in the second difference set: Determine the similarity between each data in the second difference set and the target data; According to the similarity, determining maximum similarity data, wherein the maximum similarity data is data having the greatest similarity to the target data; If the maximum similarity is greater than a preset threshold, storing the target data and the maximum similarity data as similar data in the data table; If the maximum similarity is not greater than a preset threshold, an empty record is added to the data table.

6. A database comparison device, characterized in that: The device comprises an acquisition unit, a generation unit, a query unit, and a comparison unit: The acquisition unit is used to acquire a first field query logic of a reference database and a mapping relationship between the reference database and a target database, the target database being a database to be compared with the reference database, the first field query logic being used to identify an order of query fields in a data table of the reference database, and the mapping relationship being used to identify a field mapping relationship between a data table in the reference database and a data table in the target database to be compared; The generating unit is used to map the first field query logic to the target database based on the mapping relationship between the reference database and the target database to form a second field query logic of the target database, wherein the second field query logic is used to identify the order of query fields in the data table of the target database; The generating unit is further configured to generate a first query statement corresponding to the reference database and a second query statement corresponding to the target database according to the first field query logic and the second field query logic respectively; The query unit is used to obtain a first data set corresponding to the reference database and a second data set corresponding to the target database according to the first query statement and the second query statement respectively; The comparison unit is used to obtain a comparison result according to the first data set and the second data set.

7. The device according to claim 6, characterized in that The acquisition unit is also used for: If a primary key exists in the data table in the reference database, determining the order of query fields in the data table based on the order of fields in the data table; If the data table in the reference database does not have a primary key, the index order of the fields in the data table is used as the order of the search fields in the data table; The first field query logic is obtained based on the order of query fields corresponding to all data tables in the reference database.

8. A computer device, characterized in that: The computer device comprises a processor and a memory: The memory is used to store computer programs; The processor is configured to execute the method according to any one of claims 1 to 5 according to the computer program.

9. A computer-readable medium, characterized in that The computer-readable medium is used to store a computer program, and when the computer program is executed by a computer device, the method according to any one of claims 1 to 5 is implemented.

10. A computer program product comprising a computer program, characterized in that When the method is executed on a computer device, the computer device is enabled to execute the method according to any one of claims 1 to 5.

Images