A database data comparison method and device

Abstract

The embodiment of the present invention provides a database data comparison method and device, which relates to the field of data processing technology. The specific implementation scheme is: obtain a target data table size determined according to the size of a first data table in a baseline database and / or the size of a second data table in a comparison database; determine the data blocks corresponding to the first data table and the second data table respectively according to the target data table size; load the data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table; after each data block pair is loaded into the memory, calculate the first check value of the first data block and the second check value of the second data block in the data block pair, and if the first check value and the second check value are inconsistent, compare each piece of data in the first data block and the second data block. Applying the scheme provided by the present invention to perform data comparison can improve the efficiency of data comparison.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a database data comparison method and device. Background Art

[0002] In order to improve the quality of software, developers will continuously iterate and upgrade the software version. Since the data related to the software operation is often stored in the database, the iterative upgrade of the software may cause inconsistency in the data stored in the database before and after the software upgrade, which may easily lead to incorrect operation results when the upgraded software runs based on the data in the database. Therefore, it is necessary to compare the software data stored in the database before and after the software upgrade, and then determine the differences in the software data stored in the database for correction.

[0003] In the prior art, when comparing the software data in the database before and after the software upgrade, the software data is often compared one by one. Although this method can complete the data comparison, the comparison efficiency is low, especially when the amount of software data in the database is large, the comparison of software data consumes more computing resources and is less efficient. Summary of the invention

[0004] The purpose of the embodiment of the present invention is to provide a database data comparison method and device to improve the efficiency of data comparison. The specific technical solution is as follows:

[0005] An embodiment of the present invention provides a database data comparison method, the method comprising:

[0006] Obtaining a target data table size determined according to a size of a first data table in a baseline database and / or a size of a second data table in a comparison database, wherein the first data table and the second data table have the same table name;

[0007] Determining data blocks corresponding to the first data table and the second data table respectively according to the target data table size;

[0008] Loading data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table;

[0009] After each data block pair is loaded into the memory, a first check value of the first data block and a second check value of the second data block in the data block pair are calculated. If the first check value and the second check value are inconsistent, each data in the first data block and the second data block is compared based on whether the first data block and the second data block in the data block pair have a primary key.

[0010] The embodiment of the present invention further provides a database data comparison device, the device comprising:

[0011] A data table size obtaining module, used to obtain a target data table size determined according to the size of a first data table in a baseline database and / or the size of a second data table in a comparison database, wherein the first data table and the second data table have the same table name;

[0012] A data block determination module, configured to determine the data blocks corresponding to the first data table and the second data table respectively according to the target data table size;

[0013] A data block loading module, used for loading data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table;

[0014] The data block comparison module is used to calculate the first check value of the first data block and the second check value of the second data block in each data block pair after the data block pair is loaded into the memory. If the first check value and the second check value are inconsistent, the data in the first data block and the second data block are compared according to whether the first data block and the second data block in the data block pair have a primary key.

[0015] An embodiment of the present invention further provides an electronic device, comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus;

[0016] Memory, used to store computer programs;

[0017] The processor is used to implement the above-mentioned database data comparison method steps when executing the program stored in the memory.

[0018] An embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned database data comparison method steps are implemented.

[0019] The embodiment of the present invention further provides a computer program product including instructions, which, when executed on a computer, enables the computer to execute the above database data comparison method.

[0020] Beneficial effects of the embodiments of the present invention:

[0021] As can be seen from the above, in the solution provided by the embodiment of the present invention, each piece of data in the database table is not directly compared, but a data block pair formed by data blocks containing data in the first data table and the second data table is first obtained, and the first check value of the first data block and the second check value of the second data block in each data block pair are calculated. If the first check value and the second check value are consistent, indicating that the data in the first data block and the second data block are consistent, then the pieces of data in the first data block and the second data block are no longer compared; only when the first check value and the second check value are different, the pieces of data in the first data block and the second data block are compared, which reduces the number of pieces of data that need to be compared and improves the efficiency of data comparison.

[0022] Of course, it is not necessary to achieve all of the advantages described above at the same time to implement any product or method of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0024] Figure 1 A schematic diagram of a flow chart of a first database data comparison method provided in an embodiment of the present invention;

[0025] Figure 2 A schematic diagram of a field screening interface provided by an embodiment of the present invention;

[0026] Figure 3 A schematic diagram of a flow chart of a second database data comparison method provided in an embodiment of the present invention;

[0027] Figure 4 A schematic diagram of the structure of a database data comparison device provided in an embodiment of the present invention;

[0028] Figure 5 A schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0029] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field based on this application belong to the scope of protection of the present invention.

[0030] The following first describes the execution subject of the embodiment of the present invention.

[0031] The execution subject of the embodiment of the present invention may be an electronic device connected to a database and capable of reading and processing data. Specifically, the electronic device may be a server, a desktop computer, and the like.

[0032] In order to reduce the computing resources consumed when comparing data in a database and improve the comparison efficiency, an embodiment of the present invention provides a database data comparison method and device, which are introduced below.

[0033] In one embodiment of the present invention, see Figure 1 , a flow chart of a first database data comparison method is provided, the method comprising the following steps S101-S104.

[0034] Step S101: obtaining a target data table size determined according to the size of a first data table in a baseline database and / or the size of a second data table in a comparison database.

[0035] The first data table and the second data table have the same table name.

[0036] The table name can be manually specified. For example, an interface is provided to the maintenance personnel responsible for software upgrades, on which the table names of both the baseline database and the comparison database are displayed, and the maintenance personnel select the table names of the first data table and the second data table to be compared.

[0037] The above-mentioned baseline database can be understood as data before the software upgrade, and the above-mentioned comparison database can be understood as the database after the software upgrade.

[0038] The baseline database and comparison database to be compared can be preset, for example, the names and addresses of the baseline database and the comparison database are stored in a configuration file, so that the baseline database and the comparison database can be directly connected through a database driver, which is easy to operate.

[0039] The target data table size may be represented by the size of the first data table and / or the size of the second data table. For example, the size of any one of the data tables may be randomly selected as the target data table size, or the larger data table of the first data table and the second data table may be determined and its size may be used as the target data table size; for another example, the average of the first data table size and the second data table size may be calculated and the obtained average may be used as the target data table size.

[0040] Step S102: Determine the data blocks corresponding to the first data table and the second data table respectively according to the target data table size.

[0041] In one embodiment of the present invention, when the target data table is relatively large and the corresponding first data table and the second data table have a relatively large amount of data, the data in the first data table and the second data table may be divided into data blocks, that is, the data in the first data table and the second data table may be divided into a plurality of data blocks. When the target data table is relatively small, it is equivalent to a relatively small amount of data to be compared when comparing the first data table and the second data table, and the computing resources consumed during the comparison are correspondingly relatively small. In this case, the first data table and the second data table may not be divided as described above.

[0042] Specifically, a size threshold may be preset as a reference for determining the size of the target data table. If the size of the target data table is smaller than the preset size threshold, the entire first data table is determined to be one data block, and the entire second data table is determined to be one data block; if the size of the target data table is not smaller than the size threshold, the first data table and the second data table are divided into data blocks according to the preset data block size to obtain data blocks corresponding to the first data table and the second data table, respectively.

[0043] Data blocking can be performed according to the data rows of the data tables, that is, starting from the first row in the first data table and the second data table, a determined number of data rows are sequentially placed into the same data block until no new data rows can be placed, thereby obtaining data blocks in which data blocking is completed. In this process, the size of the data block is always larger than the total amount of data contained in all the data rows placed in the data block; and the first data row in the first data table and the second data table that has not been placed in the data block is taken as the first row, and the above process is repeated until the data in the data tables are all divided.

[0044] In this case, each data block may include multiple rows of data in the first data table and the second data table, and the data included in each data block is the same as the fields of the first data table and the second data table that are not subjected to data segmentation.

[0045] As can be seen from the above, in the solution provided by the embodiment of the present invention, by setting a size threshold, data segmentation is performed on the first data table and the second data table with a larger target data table size. While realizing data segmentation, it also avoids further data segmentation on the first data table and the second data table with a smaller target data table size, thereby saving computing resources for data segmentation.

[0046] The size threshold may be set to the size of a data block. In this implementation, data in the data table that exceeds the data block size will be divided into data blocks.

[0047] The size of the data block can be set according to the memory of the execution subject of the scheme provided by the embodiment of the present invention. That is, the preset data block size can be determined according to the available memory size. When comparing, it is necessary to read in two data blocks at the same time and compare them in the memory. Therefore, the preset data block size can be set to half of the available memory size, or it can be set to a size lower than half of the available memory size. The embodiment of the present invention does not limit this. In this way, the size of the data block is set according to the available memory size, so that when comparing the data blocks, the available memory can completely accommodate the data in the data block, and the situation of insufficient available memory will not be caused, that is, the risk of memory overflow is effectively reduced.

[0048] Step S103: Load data block pairs into the memory one by one.

[0049] Among them, a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table. In this case, in a data block pair, the row number of the data row in the first data block in the first data table is the same as the row number of the data row in the second data block in the second data table. For example, the first data block contains the data rows from the 1st row to the 1000th row in the first data table, and the second data block also contains the data rows from the 1st row to the 1000th row in the second data table; in this case, according to the order of the data rows contained in the data block in the original first data table and the second data table, the identification of the data block can be determined, and the identification can be represented by a positive integer. For example, in the first data table and the second data table, the 1-1000th row of data is in the No. 1 data block, the 1001-2000th row of data is in the No. 2 data block, and so on. The data block pair can contain data blocks with the same identification, for example, the No. 1 data block corresponding to the first data table and the second data table constitutes a data block pair, the No. 2 data block constitutes a data block pair, and so on. The table data in each data block pair is the data located at the corresponding position in the data table with the same table name before and after the software upgrade.

[0050] In one embodiment of the present invention, data blocks may be loaded in a polling manner, that is, each obtained data block pair is loaded into the memory in turn; for each data block pair loaded into the memory, the data processing of the following step S104 is performed, and after the processing is completed, the space in the memory storing the data block pair can be released for loading the next data block pair.

[0051] Step S104: After each data block pair is loaded into the memory, a first check value of the first data block and a second check value of the second data block in the data block pair are calculated; if the first check value and the second check value are inconsistent, each piece of data in the first data block and the second data block is compared based on whether the first data block and the second data block in the data block pair have a primary key.

[0052] In one embodiment of the present invention, the first check value and the second check value are information digests calculated using the same check algorithm. Specifically, the check algorithm used may be a preset hash algorithm, such as an MD5 (Message-Digest 5) algorithm, a SHA (Secure Hash Algorithm) algorithm, etc. A hash operation is performed on the data contained in the first data block according to the calculation method of the hash algorithm to obtain a first check value, and a hash operation is performed on the data contained in the second data block to obtain a second check value.

[0053] According to the principle of information summary, the first check value and the second check value are consistent, which can indicate that the data in the first data block is consistent with the data in the second data block. In this case, the data in the first data block and the second data block are no longer compared, that is, the rows of data in the data table contained in the first data block and the second data block are compared.

[0054] On the contrary, if the first check value and the second check value are inconsistent, it means that the data in the first data block is inconsistent with the data in the second data block. In this case, each data in the first data block and the second data block is compared to obtain one or more data with specific differences.

[0055] Whether the first data block and the second data block have a primary key corresponds to whether the first data table and the second data table have a primary key, that is, if the first data table and the second data table have a primary key, the primary key is the primary key of the first data block and the second data block, otherwise, the first data block and the second data block do not have a primary key. Different comparison methods can be used to compare each piece of data when there is a primary key and when there is no primary key. The specific comparison method can be referred to in the subsequent embodiments, which will not be described in detail here.

[0056] As can be seen from the above, in the solution provided by the embodiment of the present invention, each piece of data in the database table is not directly compared, but a data block pair formed by data blocks containing data in the first data table and the second data table is first obtained, and the first check value of the first data block and the second check value of the second data block in each data block pair are calculated. If the first check value and the second check value are consistent, indicating that the data in the first data block and the second data block are consistent, then the pieces of data in the first data block and the second data block are no longer compared; only when the first check value and the second check value are different, the pieces of data in the first data block and the second data block are compared, which reduces the number of pieces of data that need to be compared and improves the efficiency of data comparison.

[0057] The specific comparison method of each piece of data in the aforementioned step S104 is described below.

[0058] In one embodiment of the present invention, the comparison of each piece of data in the first data block and the second data block in the aforementioned step S104 can be implemented in the following two ways:

[0059] In the first implementation, if both the first data block and the second data block in the data block pair have primary keys, the primary keys are used as indexes, and based on the indexes, the data rows to be compared in the first data block and the second data block are determined, and the determined data rows to be compared are compared.

[0060] The primary key is a combination of one or more columns in the table. The value of the primary key can uniquely identify each row in the table. If there is only one field in the primary key, the value of the primary key is the field value under this field; if there are multiple fields in the primary key, the value of the primary key is the combined field value of the field values ​​under the above multiple fields.

[0061] The primary key is used as the index, that is, the field value or the combined field value under the primary key field is used as the index value, so that the data rows in the first data block and the second data block can be uniquely corresponding. When setting the index, the data in the data block can be converted into the Dataframe format, and the index is obtained according to the preset setting method under the format.

[0062] Specifically, using the primary key as the index, determine the data rows to be compared according to the above steps, which can be done in the following way:

[0063] A data row having the same first index value and second index value is determined as a data row to be compared, wherein the first index value is: an index value corresponding to the index in the first data block, and the second index value is: an index value corresponding to the index in the second data block.

[0064] The first index value and the second index value are field values ​​under the fields included in the primary key. Data rows with the same field values ​​under all fields included in the primary key are queried in the first data block and the second data block respectively, and the obtained data rows are the data rows to be compared.

[0065] As can be seen from the above, by determining the data rows with the same first index value and the second index value, the corresponding data rows with the same index value before and after the software upgrade can be obtained in the data block. By comparing the data rows, it can be determined whether the data rows have differences when the software is upgraded.

[0066] When comparing data in the to-be-compared data row, you can compare field values ​​under pre-selected fields in the to-be-compared data row to determine whether the field values ​​under the selected fields are the same.

[0067] In one embodiment of the present invention, the pre-selected fields may be all the fields of the data rows to be compared, or may be fields selected by a maintenance person who maintains the database.

[0068] Specifically, the electronic device as the execution subject of the embodiment of the present invention can provide the user with an interface for selecting a field, for example Figure 2 The field screening interface showing the screening conditions is shown. The excluded fields indicate the fields that the maintenance personnel specify not to be compared, such as "password", "id (identification)", and "name", which are three fields existing in the first data block and the second data block. In this case, after the maintenance personnel selects the excluded fields, the pre-selected fields are the other fields in the first data block and the second data block except the excluded fields.

[0069] The filter conditions in the figure can also be used to positively select the fields to be compared, that is, the pre-selected fields mentioned above. For example, if you select the "name" field in the "Select Field" selection box below the filter conditions, the pre-selected fields will include this field. Alternatively, the maintenance personnel can also enter the fields to be compared in the "Input Content" input box as the pre-selected fields mentioned above.

[0070] In this case, when comparing the data rows to be compared, only the pre-selected fields can be compared, which not only meets the needs of maintenance personnel, but also allows flexible adjustment of field selection according to settings, avoiding interference of some fields that are meaningless to maintenance personnel in the comparison process, and can also reduce the fields to be compared, thereby reducing the computing resources consumed by the comparison.

[0071] In the second implementation method, if there is no primary key in the first data block and the second data block in the data block pair, the same fields in the first data block and the second data block are determined, the determined fields are used as indexes, and the data rows to be compared in the first data block and the second data block are determined based on the index, and the determined data rows to be compared are compared.

[0072] In one embodiment of the present invention, the difference set of all fields in the first data block and all fields in the second data block can be calculated to obtain the fields in the first data block and the second data block, which are referred to as group A fields. In this case, the fields in all fields in the first data block except the group A fields and the fields in all fields in the second data block except the group A fields are the same fields. The determined same fields are used as indexes, and the data rows to be compared in the first data block and the second data block are determined based on the indexes, and the determined data rows to be compared are compared, which is the same as the method of using the primary key as the index, and determining the data rows to be compared in the first data block and the second data block based on the indexes, and comparing the determined data rows to be compared in the aforementioned embodiment. The only difference is that the indexes used are different, which will not be described in detail here.

[0073] As can be seen from the above, in the solution provided by the embodiment of the present invention, when the primary key exists or does not exist in the first data block and the second data block, the field used as the index can be found, and the data row to be compared can be determined according to the index to achieve data comparison. This makes the solution independent of the primary key for comparing each piece of data, and the solution has a wider scope of application.

[0074] In one embodiment of the present invention, before the aforementioned step S101 obtains the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database, the method further includes:

[0075] Determine whether only one of the first data table and the second data table has a primary key; if so, determine that all data rows in the first data table and the second data table are different; if not, execute the step of obtaining a target data table size determined based on the size of the first data table in the baseline database and / or the size of the second data table in the comparison database.

[0076] In one embodiment of the present invention, a metadata table may be provided in the database, that is, a data table that stores a data table structure. The data table structure represents the attributes and relationships of the data tables in the database, and the attributes may include the field names of the database tables. In this case, the metadata tables before and after the software upgrade may be queried to record the primary keys of the first data table and the second data table respectively. If the primary key of the first data table or the second data table is not recorded in the above metadata table, it is determined that only one of the first data table and the second data table has a primary key.

[0077] When only one data table has a primary key, in the data table with the primary key, the data in the columns corresponding to the fields included in the primary key do not exist in any data row of the data table without the primary key. Therefore, it can be determined that there are differences in all data rows in the first data table and the second data table.

[0078] If not only one of the first data table and the second data table has a primary key, that is, both the first data table and the second data table have a primary key or neither of them has a primary key, the aforementioned step S101 is executed and will not be described in detail here.

[0079] In another embodiment of the present invention, similar to the above-mentioned method of determining the primary key, non-primary key fields may also be stored in the metadata table. In this case, the differences in the non-primary key fields of the metadata table before and after the upgrade are compared. If it is found that one of the first data table and the second data table has a unique non-primary key field, then similar to the above-mentioned situation, it can also be determined that all data rows in the first data table and the second data table are different.

[0080] As can be seen from the above, before determining the size of the target data table, it is possible to first determine whether only one of the first data table and the second data table has a primary key, and determine whether there are field differences between the first data table and the second data table. If so, it is determined that all data rows in the first data table and the second data table are different. In this case, there is no need to compare each data item in the first data table and the second data table, and it is also possible to directly determine that there are differences between the first data table and the second data table without performing data segmentation, thereby reducing the comparison and data segmentation process, further saving computing resources, and improving the efficiency of comparison.

[0081] In one embodiment of the present invention, according to the steps shown in the aforementioned embodiments, the determined data rows to be compared are compared, and after the difference data rows in the first data block and the second data block in each data block pair are determined, a new first data table containing the difference data rows belonging to the first data block is obtained, and a new second data table containing the difference data rows belonging to the second data block is obtained; and the step of obtaining the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database is returned until the new first data table and the new second data table remain unchanged.

[0082] The difference data rows are data rows with different field values ​​in the first data block and the second data block. This includes the first index value and the second index value being different, that is, the field value used as the first index value and the field value used as the second index value are different, or the first index value and the second index value are the same, but the field values ​​of the non-indexed fields are different. After obtaining the difference data rows, all the difference data rows in the first data block can be spliced ​​into a new first data table, and all the data rows in the second data block can be spliced ​​into a second data table, and the above step S101 is returned to be executed, which will not be described in detail here. When executing step S101 again, the difference data rows can still be obtained, forming a loop.

[0083] The above steps are used to determine the actual difference between the first data table and the second data table based on the difference data rows. On the one hand, the difference data rows may be caused by the difference that actually exists in the first data table and the second data table; on the other hand, the difference data rows may also be caused by the misalignment of the data blocks when the first data block and the second data block are obtained, for example:

[0084] In the benchmark database, there are data rows with primary key ids 1, 2, 3, 4, and 5. In the comparison database, due to software upgrade, there are data rows with primary key ids 1, 2, 10000, 3, 4, and 5. The extra data row with id 10000 is misaligned, that is, when data is partitioned, the rows that originally corresponded to each other between indexes may be partitioned into different data blocks, forming different data rows.

[0085] In the above case, for the convenience of the embodiment, it is assumed that when the blocks are divided according to the preset data block size, each data block can just accommodate two data rows, then the data blocks formed by the reference database can be data blocks corresponding to the primary key combination (1, 2), (3, 4), (5), and the data blocks formed by the comparison database can be data blocks corresponding to the primary key combination (1, 2), (10000, 3), (4, 5). According to the above embodiment, the data block pair includes:

[0086] The first data block corresponding to the primary key combination (1, 2) and the second data block corresponding to the primary key combination (1, 2) have no different data rows;

[0087] The first data block corresponding to the primary key combination (3, 4) and the second data block corresponding to the primary key combination (10000, 3), the difference data rows are the data row with id=4 and the data row with id=10000;

[0088] The first data block corresponding to the primary key (5) and the second data block corresponding to the primary key combination (4, 5), the difference data row is the data row with id=4.

[0089] Among them, only the data row with id=10000 is a real difference, and the two difference data rows with id=4 are generated by misalignment.

[0090] In another embodiment of the present invention, the misalignment may also be caused by missing data rows in the comparison database, which is similar to the above-mentioned case of adding data rows and will not be described in detail here.

[0091] Therefore, it is necessary to determine the difference data rows generated by the misalignment according to the new first data table and the new second data table. In the new first data table and the second data table, through the above cycle, the difference data rows generated by the misalignment will eventually be allocated to the same data block pair in each cycle, and will no longer be used as difference data rows when the new first data table and the new second data table are obtained next time. That is, the data rows of the new first data table and the new second data table will continue to decrease. When the new first data table and the second data table remain unchanged, the remaining difference data rows are formed by the differences that actually exist in the first data table and the second data table.

[0092] Continuing with the above embodiment, the two difference data rows with id=4 are used to form a new first data table and a new second data table respectively. The new first data table includes data rows corresponding to the primary key (4), and the new second data table includes data rows corresponding to the primary key (10000, 4). When data is partitioned, the first data block corresponding to (4) and the second data block corresponding to (10000, 4) exist in the same data block pair, and only the difference data rows are left, which are data rows corresponding to the primary key (10000). At this time, when the new first data table and the new second data table are obtained according to the above loop, the new first data table and the new second data table will not be reduced any more, and the loop ends. The data row corresponding to the primary key 10000 is the real difference between the first data table and the second data table.

[0093] As can be seen from the above, in the solution provided by the embodiment of the present invention, after obtaining the difference data rows, a new first data table and a new second data table are formed based on the difference data rows, and the data blocking and comparison method in the aforementioned embodiment is re-performed, thereby solving the problem that the difference data rows caused by data blocking have more differences than those in the actual first data table and the second data table, and improving the accuracy of the comparison.

[0094] The overall process of the database data comparison method provided by the embodiment of the present invention is described below.

[0095] See also Figure 3 , when starting to implement the database data comparison method, the configuration can be parsed first, that is, the database configuration can be parsed, such as the address of the database, etc. In the step of selecting a database driver, the selected database driver is used to connect to a specified database for data processing, and the database includes a reference database and a comparison database, that is, the step of connecting the database in the figure is performed;

[0096] In the branch of reading the metadata table, after obtaining the metadata table, the table structure can be obtained according to the information recorded in the metadata table, and the primary key of the metadata table record can be obtained; in addition, the obtained table structure can also be used for array comparison, that is, the table structure information of the reference database and the comparison database are compared. If they are not completely consistent, it can also be determined that there are differences; in addition, the metadata table can be used to obtain the primary key. If the primary key of the data table to be compared in the reference database and the comparison database is not obtained, the comparison field difference set is calculated, that is, the implementation process mentioned in the second implementation method of the specific comparison method for comparing each data in the aforementioned step S104 in the aforementioned embodiment.

[0097] In the branch of reading data tables, a table is selected, i.e., the first data table and the second data table are selected; the screening conditions are parsed, i.e., the user specifies the table name and the fields to be compared in the aforementioned embodiment; data segmentation, i.e., the implementation method of using a preset size threshold in the aforementioned step S102 to determine whether to perform data segmentation, if the target data table size is smaller than the preset size threshold, the file is read as a whole table, i.e., the entire first data table is taken as one data block, and the entire second data table is taken as one data block, which are read into the memory for comparison; otherwise, the file is read in blocks to obtain the data block pair in the aforementioned embodiment; in the file md5 encryption step, md5 is a verification algorithm for calculating a first verification value of a first data block and a second verification value of a second data block in a data block pair; the encryption comparison is to compare the first verification value and the second verification value to determine whether the two verification values ​​are consistent; if not, an array comparison is performed, i.e., each piece of data in the first data block and the second data block is compared.

[0098] If misaligned data is obtained during array comparison, the misaligned data is combined, and the new first data table and the new second data table are obtained by referring to the method of obtaining the new first data table and the new second data table in the above embodiment. The new first data table and the new second data table are processed again according to step S101, that is, the step of returning to the selection table in the figure.

[0099] The encryption comparison conclusion is the judgment result of whether the two check values ​​are consistent; the block conclusion is the difference obtained by comparing each data in the data block pair. The obtained differences are integrated into the block conclusion to form a test report. The report can be found in the embodiment of Table 1 below, which is not described in detail here.

[0100] As mentioned above, when comparing database data, the data to be compared is divided into granularities from tables to data blocks, and then to the data in the data tables. By calculating the checksum value, while ensuring the accuracy of the calculation, the amount of data that may need to be compared is reduced, greatly improving the comparison efficiency.

[0101] Table 1

[0102]

[0103]

[0104] In one embodiment of the present invention, referring to Table 1, after the above comparison is completed, the electronic device as the execution subject of the embodiment of the present invention can display a report containing the compared differences on the interface, and the report can be included as a list in the figure, and each row of the list represents the comparison result of the first data table and the second data table corresponding to a table name. In addition, the report can also include the time spent on the comparison, the version number information of the baseline database and the comparison database. The report can be displayed in formats such as HTML (HyperText Mark-up Language) and Excel.

[0105] As shown in the figure, the list shows the report results formed based on the differences obtained by comparison, which may include: newly added fields, that is, fields are added to the second data table relative to the first data table, such as the comparison between the first data table and the second data table in the second row of the list in the figure with the table name A2, where a new "tel" field is added; missing fields, that is, fields are reduced in the second data table relative to the first data table; difference fields, which exist in both the first data table and the second data table, but according to the comparison results, the corresponding field values ​​of the first data table and the second data table under the difference fields are not completely consistent.

[0106] In addition, the interface can also specifically list: the number of difference rows, that is, the number of data rows with differences; the number of difference columns, that is, the number of fields with differences; the pass rate, indicating whether there are differences. 100% means no difference, and 0 means there are differences. The above interface can also list specific difference details, including table field name, field description, field value data type, difference data, etc. Similar to Table 1, the report content with differences can be marked in dark color, and a hyperlink to the above difference details can be set in the dark area for easy reference by maintenance personnel.

[0107] Corresponding to the database data comparison method in the above embodiment, an embodiment of the present invention provides a database data comparison device.

[0108] In one embodiment of the present invention, see Figure 4 , a schematic diagram of the structure of a database data comparison device is provided, the device comprising:

[0109] A data table size obtaining module 401 is used to obtain a target data table size determined according to the size of a first data table in a baseline database and / or the size of a second data table in a comparison database, wherein the first data table and the second data table have the same table name;

[0110] A data block determination module 402, configured to determine data blocks corresponding to the first data table and the second data table respectively according to the target data table size;

[0111] A data block loading module 403 is used to load data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table;

[0112] The data block comparison module 404 is used to calculate the first check value of the first data block and the second check value of the second data block in each data block pair after the data block pair is loaded into the memory. If the first check value and the second check value are inconsistent, the data in the first data block and the second data block are compared according to whether the first data block and the second data block in the data block pair have a primary key.

[0113] As can be seen from the above, in the solution provided by the embodiment of the present invention, each piece of data in the database table is not directly compared, but a data block pair formed by data blocks containing data in the first data table and the second data table is first obtained, and the first check value of the first data block and the second check value of the second data block in each data block pair are calculated. If the first check value and the second check value are consistent, indicating that the data in the first data block and the second data block are consistent, then the pieces of data in the first data block and the second data block are no longer compared; only when the first check value and the second check value are different, the pieces of data in the first data block and the second data block are compared, which reduces the number of pieces of data that need to be compared and improves the efficiency of data comparison.

[0114] In one embodiment of the present invention, the data block comparison module 404 includes:

[0115] a check value calculation unit, configured to calculate a first check value of a first data block and a second check value of a second data block in each data block pair after the data block pair is loaded into the memory;

[0116] A data comparison unit is used to process in the following manner if the first check value and the second check value are inconsistent: if the first data block and the second data block in the data block pair both have primary keys, the primary key is used as an index, and based on the index, the data rows to be compared in the first data block and the second data block are determined, and the determined data rows to be compared are compared; if the first data block and the second data block in the data block pair do not have primary keys, the same fields in the first data block and the second data block are determined, the determined fields are used as indexes, and based on the indexes, the data rows to be compared in the first data block and the second data block are determined, and the determined data rows to be compared are compared.

[0117] As can be seen from the above, in the solution provided by the embodiment of the present invention, when the primary key exists or does not exist in the first data block and the second data block, the field used as the index can be found, and the data row to be compared can be determined according to the index to achieve data comparison. This makes the solution independent of the primary key for comparing each piece of data, and the solution has a wider scope of application.

[0118] In one embodiment of the present invention, the data comparison unit is specifically used to process in the following manner if the first check value and the second check value are inconsistent: if both the first data block and the second data block in the data block pair have primary keys, the primary key is used as an index, and data rows with the same first index value and second index value are determined as data rows to be compared, wherein the first index value is: the index value corresponding to the index in the first data block, and the second index value is: the index value corresponding to the index in the second data block; the determined data rows to be compared are compared; if both the first data block and the second data block in the data block pair do not have primary keys, the same fields in the first data block and the second data block are determined, the determined fields are used as indexes, and the data rows to be compared in the first data block and the second data block are determined based on the indexes, and the determined data rows to be compared are compared.

[0119] As can be seen from the above, by determining the data rows with the same first index value and the second index value, the corresponding data rows with the same index value before and after the software upgrade can be obtained in the data block. By comparing the data rows, it can be determined whether the data rows have differences when the software is upgraded.

[0120] In one embodiment of the present invention, the data block determination module 402 is specifically used to determine that the entire first data table is one data block, and determine that the entire second data table is one data block if the target data table size is less than a preset size threshold; if the target data table size is not less than the size threshold, the first data table and the second data table are divided into data blocks according to the preset data block size to obtain data blocks corresponding to the first data table and the second data table respectively.

[0121] As can be seen from the above, in the solution provided by the embodiment of the present invention, by setting a size threshold, data segmentation is performed on the first data table and the second data table with a larger target data table size. While realizing data segmentation, it also avoids further data segmentation on the first data table and the second data table with a smaller target data table size, thereby saving computing resources for data segmentation.

[0122] In one embodiment of the present invention, the preset data block size is determined according to the available memory size.

[0123] In this way, the size of the data block is set according to the available memory size, so that when comparing data blocks, the available memory can completely accommodate the data in the data block, and insufficient available memory will not be caused, which effectively reduces the risk of memory overflow.

[0124] In one embodiment of the present invention, the device further comprises:

[0125] A primary key judgment module is used to judge whether only one of the first data table and the second data table has a primary key; if so, determine whether all data rows in the first data table and the second data table are different; if not, execute the step of obtaining the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database.

[0126] As can be seen from the above, before determining the size of the target data table, it is possible to first determine whether only one of the first data table and the second data table has a primary key, and determine whether there are field differences between the first data table and the second data table. If so, it is determined that all data rows in the first data table and the second data table are different. In this case, there is no need to compare each data item in the first data table and the second data table, and it is also possible to directly determine that there are differences between the first data table and the second data table without performing data segmentation, thereby reducing the comparison and data segmentation process, further saving computing resources, and improving the efficiency of comparison.

[0127] In one embodiment of the present invention, the device further comprises:

[0128] A new data table acquisition module is used to obtain a new first data table containing each difference data row belonging to the first data block and a new second data table containing each difference data row belonging to the second data block after determining the difference data rows that are different between the first data block and the second data block in each data block pair; and return to trigger the data table size acquisition module 401 to obtain a target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database, until the new first data table and the new second data table remain unchanged.

[0129] As can be seen from the above, in the solution provided by the embodiment of the present invention, after obtaining the difference data rows, a new first data table and a new second data table are formed based on the difference data rows, and the data blocking and comparison method in the aforementioned embodiment is re-performed, thereby solving the problem that the difference data rows caused by data blocking have more differences than those in the actual first data table and the second data table, and improving the accuracy of the comparison.

[0130] The embodiment of the present invention further provides an electronic device, such as Figure 5 As shown, it includes a processor 501, a communication interface 502, a memory 503 and a communication bus 504, wherein the processor 501, the communication interface 502, and the memory 503 communicate with each other through the communication bus 504.

[0131] Memory 503, used for storing computer programs;

[0132] The processor 501 is used to implement the steps of the database data comparison method in any of the above-mentioned embodiments when executing the program stored in the memory 503.

[0133] The communication bus mentioned in the above electronic device can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0134] The communication interface is used for communication between the above electronic device and other devices.

[0135] The memory may include a random access memory (RAM) or a non-volatile memory (NVM), such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0136] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0137] In another embodiment of the present invention, a computer-readable storage medium is provided, in which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned database data comparison methods are implemented.

[0138] In another embodiment provided by the present invention, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute any database data comparison method in the above embodiments.

[0139] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present invention is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state hard disk Solid State Disk (SSD)), etc.

[0140] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0141] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0142] The above description is only a preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.

Claims

1. A database data comparison method, characterized in that: The method comprises: Obtaining a target data table size determined according to a size of a first data table in a baseline database and / or a size of a second data table in a comparison database, wherein the first data table and the second data table have the same table name; the target data table size is represented by the size of the first data table and / or the size of the second data table; Determining data blocks corresponding to the first data table and the second data table respectively according to the target data table size; Loading data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table; After each data block pair is loaded into the memory, a first check value of the first data block and a second check value of the second data block in the data block pair are calculated, and if the first check value and the second check value are inconsistent, data in the first data block and the second data block are compared according to whether the first data block and the second data block in the data block pair have a primary key; Before obtaining the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database, the method further includes: Determine whether only one of the first data table and the second data table has a primary key; If yes, determining that all data rows in the first data table and the second data table are different; If not, then executing the step of obtaining the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database; The determining, according to the target data table size, data blocks corresponding to the first data table and the second data table respectively includes: If the target data table size is smaller than a preset size threshold, determining the entire first data table as one data block, and determining the entire second data table as one data block; If the target data table size is not less than the size threshold, dividing the first data table and the second data table into data blocks according to a preset data block size to obtain data blocks corresponding to the first data table and the second data table respectively; The method further comprises: After determining the difference data rows in which the first data block and the second data block in each data block pair have differences, obtaining a new first data table including the difference data rows belonging to the first data block, and obtaining a new second data table including the difference data rows belonging to the second data block; Returning to the step of obtaining the target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database, until the new first data table and the new second data table remain unchanged; The method further comprises: After the comparison between the first data table and the second data table is completed, a report containing the compared differences is displayed on the interface; wherein the report includes a list; each row in the list represents a comparison result between the first data table and the second data table corresponding to a table name; the fields in the list include: newly added fields added to the second data table relative to the first data table, missing fields reduced from the second data table relative to the first data table, and difference fields that exist in both the first data table and the second data table and whose field values ​​in the first data table and the second data table are inconsistent.

2. The method according to claim 1, characterized in that The comparing each piece of data in the first data block and the second data block according to whether the first data block and the second data block in the data block pair have a primary key includes: If both the first data block and the second data block in the data block pair have primary keys, the primary keys are used as indexes, and based on the indexes, data rows to be compared in the first data block and the second data block are determined, and the determined data rows to be compared are compared; If neither the first data block nor the second data block in the data block pair has a primary key, the same fields in the first data block and the second data block are determined, the determined fields are used as indexes, and the data rows to be compared in the first data block and the second data block are determined based on the index, and the determined data rows to be compared are compared.

3. The method according to claim 2, characterized in that The step of determining the data rows to be compared in the first data block and the second data block based on the index comprises: A data row having the same first index value and second index value is determined as a data row to be compared, wherein the first index value is: an index value corresponding to the index in the first data block, and the second index value is: an index value corresponding to the index in the second data block.

4. The method according to claim 1, characterized in that The preset data block size is determined according to the available memory size.

5. A database data comparison device, characterized in that: The device comprises: A data table size obtaining module, used to obtain a target data table size determined according to the size of a first data table in a baseline database and / or the size of a second data table in a comparison database, wherein the first data table and the second data table have the same table name; the target data table size is represented by the size of the first data table and / or the size of the second data table; A data block determination module, configured to determine the data blocks corresponding to the first data table and the second data table respectively according to the target data table size; A data block loading module, used for loading data block pairs into the memory one by one, wherein a data block pair includes: a first data block corresponding to the first data table and a second data block corresponding to the second data table; A data block comparison module is used to calculate a first check value of a first data block and a second check value of a second data block in each data block pair after each data block pair is loaded into the memory, and if the first check value and the second check value are inconsistent, compare each piece of data in the first data block and the second data block according to whether the first data block and the second data block in the data block pair have a primary key; The device also includes: A primary key determination module is used to determine whether only one of the first data table and the second data table has a primary key; if so, determine whether all data rows in the first data table and the second data table are different; if not, execute the step of obtaining a target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database; The data block determination module is specifically configured to determine that the entire first data table is one data block, and the entire second data table is one data block, if the size of the target data table is smaller than a preset size threshold; if the size of the target data table is not smaller than the size threshold, divide the first data table and the second data table into data blocks according to the preset data block size, to obtain data blocks corresponding to the first data table and the second data table respectively; The device also includes: A new data table acquisition module is used to obtain a new first data table containing each difference data row belonging to the first data block, and obtain a new second data table containing each difference data row belonging to the second data block after determining the difference data row in the first data block and the second data block in each data block pair; Return to the step of triggering the data table size obtaining module to obtain a target data table size determined according to the size of the first data table in the baseline database and / or the size of the second data table in the comparison database, until the new first data table and the new second data table remain unchanged; The device also includes: A display module is used to display a report containing the compared differences on an interface after the comparison between the first data table and the second data table is completed; wherein the report includes a list; each row in the list represents a comparison result between the first data table and the second data table corresponding to a table name; the fields in the list include: newly added fields added to the second data table relative to the first data table, missing fields reduced from the second data table relative to the first data table, and difference fields that exist in both the first data table and the second data table and whose field values ​​in the first data table and the second data table are inconsistent.

6. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing the method steps described in any one of claims 1 to 4 when executing a program stored in a memory.

7. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method steps described in any one of claims 1 to 4 are implemented.

Images