WO2019237574A1 - Method and device for identifying database differences - Google Patents

Abstract

A method and device for identifying database differences, an electronic device and a computer-readable storage medium, the method comprising: configuring service database information stored in system libraries in different environments into independent files (S310); obtaining service database information recorded in the independent files in different environments (S320); using an information summarization algorithm to hash process each record in the service database information so as to generate an information summarization value corresponding to each record (S330); and according to the identification information of the each record, comparing the information summarization values of the same identification information correspondingly recorded in different environments, so as to obtain records having differences (S340). The present method may improve the efficiency of identifying database differences.

Description

Method and device for identifying database differences Technical field

This application claims priority from Chinese patent application 201810605199.9, filed on June 13, 2018, with the invention name "A Method and Device for Identifying Database Differences", the entire contents of which are hereby incorporated by reference.

The present application relates to the field of computer technology, and in particular, to a method and an apparatus for verifying database differences, an electronic device, and a computer-readable storage medium.

Background technique

In order to ensure the consistency of the database in different environments (such as production environment, test environment, pre-release environment, development environment), the database structure needs to be compared before release production. In the cloud environment, the database fields are usually compared one by one to find the differences between the databases in different environments. However, the inventors realized that this method is inefficient and needs to adopt the polling method. For each field of the database, it is necessary to find the existence of each field in another environment database. After all comparisons are completed, the database can be judged Whether the table structure is consistent and the time complexity is extremely high.

Moreover, when the database needs to check more tables, this method has a long working cycle and the process is not intelligent enough. In addition, in the comparison process, it is usually directly connected to the database to obtain database information in real time, which also takes a certain amount of time.

technical problem

In order to solve the problems of low efficiency and long time consuming of comparing database differences in related technologies, this application provides a method for identifying database differences.

Technical solutions

In one aspect, the present application provides a method for identifying differences in a database. The method includes:

Configure the business database information stored in the system library in different environments into independent files;

Obtaining business database information recorded in said independent files in different environments;

Hashing each record in the business database information with an information digest algorithm to generate an information digest value corresponding to each record;

According to the identification information of each record, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain a record having a difference.

On the other hand, the present application also provides a device for identifying differences in a database. The device includes:

A file configuration module configured to configure the business database information stored in the system library in different environments into independent files;

An information reading module configured to obtain business database information recorded in said independent files in different environments;

The digest generating module is configured to perform a hash processing on each record in the service database information by using an information digest algorithm to generate an information digest value corresponding to each record;

The difference comparison module is configured to compare the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record to obtain a record having a difference.

On the other hand, the present application also provides an electronic device. The electronic device includes:

processor;

Memory configured to store processor-executable instructions;

Wherein, the processor is configured to execute any one of the above-mentioned methods for identifying database differences.

On the other hand, the present application also provides a computer-readable storage medium. The computer-readable storage medium stores a computer program, and the computer program can be executed by a processor by any one of the foregoing methods for identifying differences in databases.

Beneficial effect

The technical solutions provided by the embodiments of the present application may include the following beneficial effects:

The technical solution provided in this application configures the business database information recorded in the system library under different environments into files, so that each record can be directly read from the file for comparison, so there is no need to record one by one in the record comparison process. Visiting the system library saves data acquisition time and improves the efficiency of discerning differences. Further, by calculating the new information summary value of each record and comparing the information summary value of each record in different environments, it is possible to quickly find inconsistent records and further improve the efficiency of identifying database differences.

It should be understood that the above general description and the following detailed description are merely exemplary, and should not limit the present application.

BRIEF DESCRIPTION OF THE DRAWINGS

The drawings herein are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application, and are configured together with the description to explain the principles of the application.

FIG. 1 is a schematic diagram of an implementation environment involved in this application;

Fig. 2 is a block diagram of a server according to an exemplary embodiment;

Fig. 3 is a flow chart showing a method for identifying database differences according to an exemplary embodiment;

4 is a detailed flowchart of step 330 in the embodiment corresponding to FIG. 3;

5 is a detailed flowchart of step 340 in the embodiment corresponding to FIG. 3;

Fig. 6 is a block diagram of a device for identifying a database difference according to an exemplary embodiment;

FIG. 7 is a detailed block diagram of the digest generating module in the embodiment corresponding to FIG. 6.

Embodiments of the invention

Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, the same numerals in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with this application. Rather, they are merely examples of devices and methods consistent with certain aspects of the application as detailed in the appended claims.

FIG. 1 is a schematic diagram of an implementation environment according to the present application. The implementation environment includes: multiple servers 110, each of which performs database development, testing, production, and pre-release. The server 110 for database development can be considered as a development environment and the server 110 for database testing can be considered as a test environment. The server 110 that officially provides database services to the outside can be considered as a production environment. A server that does not provide external services but is configured in the same way as the production environment 110 can be considered a pre-release environment.

Because the configuration of the server 110 (eg, operating system, storage system) is different in different environments, the databases designed in the development environment may be different in other environments. In order to verify whether the designed databases are different in different environments. The server 110 can use the database difference comparison method provided in this application to determine whether the developed database is in different environments based on the business database information stored in the system library in different environments (development environment, test environment, production environment, and pre-release environment) There will be discrepancies and find the information that is different.

Referring to FIG. 2, FIG. 2 is a schematic structural diagram of a server 110 according to an embodiment of the present application. The server 110 may have a large difference due to different configurations or performance, and may include one or more central processing units (central processing units (CPU) 222 (for example, one or more processors) and memory 232, one or more storage media 230 (for example, one or more storage devices) storing application programs 242 or data 244. The memory 232 and the storage medium 230 may be temporary storage or persistent storage. The program stored in the storage medium 230 may include one or more modules (not shown), and each module may include a series of instruction operations on the server 110. Furthermore, the central processing unit 222 may be configured to communicate with the storage medium 230 and execute a series of instruction operations in the storage medium 230 on the server 110. The server 110 may also include one or more power sources 226, one or more wired or wireless network interfaces 250, one or more input / output interfaces 258, and / or, one or more operating systems 241, such as Windows ServerTM, Mac OS XTM, UnixTM, LinuxTM, FreeBSDTM and more. The steps performed by the server 110 in the embodiments shown in FIG. 3, FIG. 4, and FIG. 5 described below may be based on the server structure shown in FIG. 2.

Those of ordinary skill in the art may understand that all or part of the steps of implementing the following embodiments may be completed by hardware, or related hardware may be instructed by a program. The program may be stored in a computer-readable storage medium. The aforementioned storage medium may be a read-only memory, a magnetic disk, or an optical disk.

Fig. 3 is a flow chart showing a method for identifying database differences according to an exemplary embodiment. The application scope and execution subject of the database difference identification method (for example, the method is configured as the server 110 of the implementation environment shown in FIG. 1). As shown in FIG. 3, the method may be executed by the server 110 and may include the following steps.

In step 310, the business database information stored in the system library in different environments is configured as an independent file.

Among them, different environments refer to production environment, test environment, pre-release environment, and development environment. The servers running in different environments may have different databases running due to different configurations. Business database information in different environments can be read in advance from the information_schema (information summary) library in the current environment. information_schema is a system library that stores business database information in the current environment. Getting the business database information directly from the system library can save the time of reading information from each business database. In MySQL (Relational Database Management System), information_schema is considered as a database, or information database. It holds information about all other databases (that is, business databases) maintained by MySQL. Such as database name, database table, table column data type and access permissions.

This application reads the business database information from the system library in the environment where the business database is located, and stores it separately as a file, that is, an independent file. Therefore, in the subsequent comparison process, there is no need to access the system library to read the business database information. Read each record from the file for comparison, which saves data acquisition time and improves the efficiency of discerning differences.

In step 320, business database information recorded in the independent files in different environments is obtained.

During the difference comparison process, a certain server can obtain independent files configured by servers in other environments through network transmission, and then read the business data database information recorded in independent files in different environments.

In step 330, an information digest algorithm is used to hash each record in the business database information to generate an information digest value corresponding to each record.

It should be noted that there are multiple read-only tables in the system library, which record business database information, including TABLES table (providing table information), COLUMNS table (providing column information), VIEWS table (providing view information), TRIGGERS table (providing Trigger information) and so on. By comparing whether the records of these tables are different in different environments, it can be determined whether the business databases in different environments are consistent.

Specifically, the server reads the business database information recorded in independent files in different environments, that is, it obtains the records of the above tables in the system library in different environments. The server uses the message digest algorithm to calculate the content of each record and converts each record into a string. The message digest algorithm can be MD5 algorithm, SHA-1 algorithm, etc. In one embodiment, the above step 330 specifically includes: using an MD5 encryption algorithm to calculate an MD5 value of each record in the service database information under different environments, and obtaining an information summary value of each record. Therefore, when performing a database difference comparison, the MD5 value of each record in different environments is compared, so that inconsistent records can be quickly found without the need to compare all the fields one by one.

In step 340, according to the identification information of each record, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain a record having a difference.

The identification information can be an index, a field, or a combination of multiple fields, mainly to uniquely identify this record. The identification information may be the serial number, name, etc. of the record. After each record is calculated using the information digest algorithm, the information digest value and the record's identification information are combined into a key-value pair.

The identification information corresponding to the records in different environments is the same. For example, the first record is called record AA in different environments, but the record AA may be different in different environments, so the record AA in different environments may calculate different information summary values. By comparing whether the information summary values of the records corresponding to the same identification information in different environments are consistent, it can be determined whether the records corresponding to the identification information are different in different environments. If the information summary values are inconsistent, the records corresponding to the identification information may be considered to be different in different environments.

For example, the record AA in the production environment calculates the MD5 value to be 512345, and the test environment calculates the MD5 value to be 123456. From this, it can be obtained that the record AA is different in different environments.

In the technical solution provided by the foregoing exemplary embodiment of the present application, the business database information recorded in the system library under different environments is configured as a file, so that each record can be directly read from the file for comparison, and thus during the record comparison process There is no need to access the system library one by one, which saves data acquisition time and improves the efficiency of discerning differences. Further, by calculating the MD5 value of each record and comparing the MD5 value of each record in different environments, it is possible to quickly find inconsistent records and further improve the efficiency of identifying database differences.

In an exemplary embodiment, after the above step 340, the technical solution provided in this application further includes the following steps:

Field-by-field comparison is performed on the records with differences, and the fields with differences are located.

After the discrepant records are found in the above step 340, the fields in the discrepant records can be further compared one by one, so as to accurately locate the fields in which the business database information differs in different environments. This application first filters out the records that have differences, and then further compares the fields in the difference records one by one, so as to quickly locate the fields that have differences, and overcomes the existing time-consuming comparison of the fields in all records one by one. defect. After finding out the fields that are different in different environments, the developer adjusts the developed business database based on the found difference fields to improve the compatibility of the business database in different environments.

In an exemplary embodiment, as shown in FIG. 4, the above step 330 specifically includes:

In step 331, for each record in the service database information in a different environment, a dynamic update field in the record is removed;

It should be noted that each record contains a lot of fields, and the fields that need to be consistent are extracted. There is no need to maintain consistency, such as creation time and update time, and it can be eliminated. Dynamic update fields are fields that do not need to be consistent in the record, such as creation time, update time, and so on.

In step 332, an information digest algorithm is used to hash the remaining fields of each record to generate an information digest value corresponding to each record.

Among them, the remaining fields refer to the remaining fields in the record after excluding dynamic update fields. After removing the fields that do not need to be consistent for each record, the remaining fields are hashed using an information digest algorithm to generate the information digest value for each record. Then compare whether the information summary values of the records corresponding to the same identification information in different environments are consistent, so as to determine the records that have differences.

In an exemplary embodiment, as shown in FIG. 5, the above step 340 specifically includes:

In step 341, the identification information of each record is correspondingly stored with the information summary value corresponding to the record, to obtain the information summary value of the record corresponding to the identification information in different environments;

Specifically, after the information summary value of each record is calculated, the identification information and the information summary value of the record are correspondingly stored in the form of key-value pairs. For example, the corresponding information summary of the identification information AA in the production environment The value is 123678, the corresponding information digest value of the identification information AA in the test environment is 234545, and the information digest value of the identification information AA in the development environment is 127898, so that the information summary value corresponding to the same identification information in different environments can be obtained. .

In step 342, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain records with differences.

For example, the corresponding information summary value of the identification information AA in the production environment is 123678, the corresponding information summary value of the identification information AA in the test environment is 234545, and the information summary value of the identification information AA in the development environment is 127898. Information AA: Compare the information summary values (123678, 234545, 127898) of the identification information AA in different environments (development environment, test environment, production environment). After comparison, it is found that the information digest value of the identification information AA in different environments is inconsistent, and thus the records corresponding to the identification information AA are different in different environments. On the contrary, if the information digest values of the identification information AA in different environments are consistent, it can be considered that there is no difference in the records corresponding to the identification information AA in different environments.

The following is a device embodiment of the present application, which can be configured to execute a method embodiment for identifying a database difference performed by the above-mentioned server 110 of the present application. For details not disclosed in the device embodiments of this application, please refer to the embodiment of the method for identifying differences in the database of this application.

Fig. 6 is a block diagram of a device for identifying differences in a database according to an exemplary embodiment. The device for identifying differences in a database may be configured as a server 110 in the implementation environment shown in Fig. 1 to execute any one of Figs. 3 to 5 All or part of the steps of the method for identifying database differences shown below. As shown in FIG. 6, the device includes, but is not limited to, a file configuration module 610, an information reading module 620, a digest generating module 630, and a difference comparison module 640.

File configuration module 610, configured to configure business database information stored in system libraries in different environments into independent files

The information reading module 620 is configured to obtain service database information recorded in the independent files in different environments;

The digest generating module 630 is configured to use an information digest algorithm to hash each record in the service database information to generate an information digest value corresponding to each record;

The difference comparison module 640 is configured to compare the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record to obtain a record having a difference.

The implementation process of the functions and functions of each module in the above device is described in detail in the implementation process of the corresponding steps in the above method for identifying differences in databases, and is not repeated here.

The file configuration module 610, the information reading module 620, the summary generating module 630, and the difference comparison module 640 may be functional modules configured to execute corresponding steps in the above-mentioned method for identifying database differences. It can be understood that these modules can be implemented by hardware, software, or a combination of both. When implemented in hardware, these modules may be implemented as one or more hardware modules, such as one or more application specific integrated circuits. When implemented in software, these modules may be implemented as one or more computer programs executing on one or more processors, such as programs stored in the memory 232 executed by the central processor 222 of FIG. 2.

In an exemplary embodiment, the apparatus for identifying database differences further includes, but is not limited to:

The field locating module is configured to perform field-by-field comparison on the records having differences, and locate the fields having differences.

In an exemplary embodiment, the digest generating module 630 includes, but is not limited to:

The encryption calculation unit is configured to use the MD5 encryption algorithm to calculate the MD5 value of each record in the business database information under different environments, and obtain the information summary value of each record.

In an exemplary embodiment, as shown in FIG. 7, the digest generating module 630 includes, but is not limited to:

A field removing unit 631 is configured to remove dynamically updated fields in the records for each record in the service database information under different environments;

The encryption processing unit 632 is configured to perform hash processing on the remaining fields of each record using an information digest algorithm to generate an information digest value corresponding to each record.

In an exemplary embodiment, the difference comparison module 640 includes, but is not limited to:

The corresponding storage unit is configured to store the identification information of each record and the information summary value corresponding to the record, to obtain the information summary value of the record corresponding to the identification information in different environments;

The digest comparison unit is configured to compare the information digest values of the records corresponding to the same identification information in different environments, and obtain a record with a difference.

Optionally, the present application further provides an electronic device configured to execute all or part of the database difference identification method shown in any of FIGS. 3-5 in the server 110 configured as the implementation environment shown in FIG. 1. step. The electronic device includes:

processor;

Memory configured to store processor-executable instructions;

Wherein, the processor is configured to execute the method for identifying a database difference according to the above exemplary embodiment.

The specific manner in which the processor of the electronic device performs operations in this embodiment has been described in detail in the embodiment of the method for identifying differences in the database, and will not be described in detail here.

In an exemplary embodiment, a storage medium is also provided, and the storage medium is a computer-readable storage medium, and may be, for example, temporary and non-transitory computer-readable storage media including instructions. A computer program is stored in the storage medium, and the computer program can be executed by the central processing unit 222 of the server 110 to complete the foregoing method for identifying database differences.

It should be understood that the present application is not limited to the precise structure that has been described above and shown in the drawings, and various modifications and changes may be performed without departing from the scope thereof. The scope of the application is limited only by the accompanying claims.

Claims (20)

A method for identifying database differences, wherein the method includes: Configure the business database information stored in the system library in different environments into independent files; Obtaining business database information recorded in said independent files in different environments; Hashing each record in the business database information with an information digest algorithm to generate an information digest value corresponding to each record; According to the identification information of each record, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain a record having a difference. The method according to claim 1, wherein after comparing the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record to obtain a record having a difference, the method further comprises: Field-by-field comparison is performed on the records with differences, and the fields with differences are located. The method according to any one of claims 1-2, wherein the using an information digest algorithm to hash each record in the business database information to generate an information digest value corresponding to each record includes: The MD5 encryption algorithm is used to calculate the MD5 value of each record in the business database information under different environments to obtain the information summary value of each record. The method according to any one of claims 1-3, wherein the adopting an information digest algorithm to hash each record in the business database information to generate an information digest value corresponding to each record includes: For each record in the business database information under different environments, the dynamic update field in the record is eliminated; An information digest algorithm is used to hash the remaining fields of each record to generate an information digest value corresponding to each record. The method according to any one of claims 1-4, wherein, according to the identification information of each record, comparing the information summary values of records corresponding to the same identification information in different environments to obtain a record having a difference, including : Storing the identification information of each record and the information digest value corresponding to the record to obtain the information digest value of the record corresponding to the identification information in different environments; The information summary values of the records corresponding to the same identification information under different environments are compared to obtain records with differences. A device for identifying database differences, wherein the device includes: A file configuration module configured to configure the business database information stored in the system library in different environments into independent files; An information reading module configured to obtain business database information recorded in said independent files in different environments; The digest generating module is configured to perform a hash processing on each record in the service database information by using an information digest algorithm to generate an information digest value corresponding to each record; The difference comparison module is configured to compare the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record to obtain a record having a difference. The apparatus according to claim 6, wherein the apparatus further comprises: The field locating module is configured to perform field-by-field comparison on the records having differences, and locate the fields having differences. The apparatus according to any one of claims 6 to 7, wherein the digest generating module comprises: The encryption calculation unit is configured to use the MD5 encryption algorithm to calculate the MD5 value of each record in the business database information under different environments, and obtain the information summary value of each record. The apparatus according to any one of claims 6 to 8, wherein the digest generating module comprises: A field culling unit configured to delete dynamically updated fields in the records for each record in the service database information under different environments; The encryption processing unit is configured to use an information digest algorithm to hash the remaining fields of each record to generate an information digest value corresponding to each record. The apparatus according to any one of claims 6-9, wherein the difference comparison module comprises: The corresponding storage unit is configured to store the identification information of each record and the information summary value corresponding to the record, to obtain the information summary value of the record corresponding to the identification information in different environments; The digest comparison unit is configured to compare the information digest values of the records corresponding to the same identification information in different environments, and obtain a record with a difference. An electronic device, wherein the electronic device includes: processor; Memory configured to store processor-executable instructions; The processor is configured to perform the following steps: Configure the business database information stored in the system library in different environments into independent files; Obtaining business database information recorded in said independent files in different environments; Hashing each record in the business database information with an information digest algorithm to generate an information digest value corresponding to each record; According to the identification information of each record, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain a record having a difference. The electronic device according to claim 11, wherein after comparing the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record to obtain a record having a difference, the method further comprises : Field-by-field comparison is performed on the records with differences, and the fields with differences are located. The electronic device according to any one of claims 11-12, wherein the information digest algorithm is used to hash each record in the service database information to generate an information digest value corresponding to each record, including: : The MD5 encryption algorithm is used to calculate the MD5 value of each record in the business database information under different environments to obtain the information summary value of each record. The electronic device according to any one of claims 11-13, wherein the information digest algorithm is used to hash each record in the service database information to generate an information digest value corresponding to each record, including: : For each record in the business database information under different environments, the dynamic update field in the record is eliminated; An information digest algorithm is used to hash the remaining fields of each record to generate an information digest value corresponding to each record. The electronic device according to any one of claims 11 to 14, wherein, according to the identification information of each record, comparing the information summary values of the records corresponding to the same identification information in different environments to obtain a record having a difference, include: Storing the identification information of each record and the information digest value corresponding to the record to obtain the information digest value of the record corresponding to the identification information in different environments; The information summary values of the records corresponding to the same identification information under different environments are compared to obtain records with differences. A computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and the computer program can execute the following steps by a processor: Configure the business database information stored in the system library in different environments into independent files; Obtaining business database information recorded in said independent files in different environments; Hashing each record in the business database information with an information digest algorithm to generate an information digest value corresponding to each record; According to the identification information of each record, the information summary values of the records corresponding to the same identification information in different environments are compared to obtain a record having a difference. The computer-readable storage medium according to claim 16, wherein after comparing the information summary values of the records corresponding to the same identification information in different environments according to the identification information of each record, obtaining a record having a difference, the The method also includes: Field-by-field comparison is performed on the records with differences, and the fields with differences are located. The computer-readable storage medium according to any one of claims 16-17, wherein the information digest algorithm is used to hash each record in the business database information to generate an information summary corresponding to each record Values, including: The MD5 encryption algorithm is used to calculate the MD5 value of each record in the business database information under different environments to obtain the information summary value of each record. The computer-readable storage medium according to any one of claims 16 to 18, wherein the information digest algorithm is used to hash each record in the business database information to generate an information summary corresponding to each record Values, including: For each record in the business database information under different environments, the dynamic update field in the record is eliminated; An information digest algorithm is used to hash the remaining fields of each record to generate an information digest value corresponding to each record. The computer-readable storage medium according to any one of claims 16 to 19, wherein, according to the identification information of each record, the information summary values of records corresponding to the same identification information in different environments are obtained to obtain a difference Records, including: Storing the identification information of each record and the information digest value corresponding to the record to obtain the information digest value of the record corresponding to the identification information in different environments; The information summary values of the records corresponding to the same identification information under different environments are compared to obtain records with differences.