CN110222028B - Data management method, device, equipment and storage medium - Google Patents
Data management method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN110222028B CN110222028B CN201910366894.9A CN201910366894A CN110222028B CN 110222028 B CN110222028 B CN 110222028B CN 201910366894 A CN201910366894 A CN 201910366894A CN 110222028 B CN110222028 B CN 110222028B
- Authority
- CN
- China
- Prior art keywords
- work order
- information
- modification
- data
- database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/219—Managing data history or versioning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a data management method, a device, equipment and a storage medium, wherein the method is applied to a MySQL database and comprises the following steps: receiving a data modification request work order submitted by a user through a work order system, wherein the data modification request work order comprises work order information; modifying the MySQL database according to the data modification request worksheet; recording modification information of the data modification request work order, wherein the modification information is used for indicating modified data in the database; and storing the modification information into a search engine, and associating the modification information with the work order information. Therefore, the modification information is stored in the search engine, and the modification information is associated with the work order information of the data modification request work order, so that the modification records of the database can be stored in the search engine and the work order system for a long time, and a user can conveniently backtrack the modification condition of the database at any time.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a data management method, apparatus, device, and storage medium.
Background
The MySQL database can store data, data relation and other information, modification operations such as addition and deletion can be performed on the information in the database, the information obtained by recording modification is called modification records, and the modification records can reflect historical modification conditions, so that management personnel can manage the database better, and the storage of the modification records is of great significance.
The MySQL database obtains the modification records by means of incremental backups. Compared with the backup of all the data of the database after modification, the incremental backup only needs to record the modified data in the database to obtain a binlog log containing modification records, the data modification records comprise modification time and data before and after modification, and a user can backtrack the modification condition of the database by searching the binlog log.
When the amount of data stored in the database is large, the database may delete the binlog log stored for a long time periodically, and therefore, for the data with a long time, the relevant modification record may not be searched.
Disclosure of Invention
The embodiment of the application provides a data management method, which can perform efficient data management on modification records generated in the process of modifying an information base.
In one aspect, an embodiment of the present application provides a data management method, where the method includes that a data management device first receives a data modification request work order submitted by a user through a work order system, where the data modification request work order includes work order information, then modifies a MySQL database according to the data modification request work order, records modification information of the data modification request work order, where the modification information is used to indicate modified data in the database, and finally stores the modification information in a search engine, and associates the modification information with the work order information. Through the scheme provided by the embodiment, the modification information and the work order information which contain rich information can be obtained, so that the modification condition of the database can be comprehensively and specifically recorded, the modification information is stored in the search engine, and the modification information is associated with the work order information of the data modification request work order, so that the modification record of the database can be stored in the search engine and the work order system for a long time, and a user can conveniently backtrack the modification condition of the database at any time.
In one possible design, the work order information includes SQL statements, and after the work order system receives a data modification request work order submitted by a user, syntax analysis is performed on the SQL statements, and in the case that there is no problem in the analysis result, the SQL statements are called to execute the operation of modifying the MySQL database according to the work order information. By the scheme provided by the embodiment, the possibility of successful database modification can be improved, so that the efficiency of data management is further improved.
In a possible design, under the condition that no problem exists in the analysis result, the SQL statement is hashed by the work order system to obtain a hash value, whether the hash value exists is judged, and if the hash value does not exist, the SQL statement is called to execute the operation of modifying the MySQL database according to the work order information. By the scheme provided by the embodiment, the database can be prevented from repeatedly executing the same modification operation, the computing resources are saved, and the generation of redundant modification information is reduced, so that the efficiency of data management is further improved.
In one possible design, in the absence of the hash value, the hash value is saved, a work order number corresponding to the data modification request work order is generated by the work order system, and the work order number is associated with the hash value and the work order information.
In one possible design, the worksheet number is associated with a keyword in the search engine. By the scheme provided by the embodiment, the user can conveniently obtain the work order number associated with the keyword by searching the keyword in the search engine subsequently, so that the hash value and the work order information associated with the work order number are obtained, and the modification information associated with the work order information can be further obtained, so that the data backtracking capability of the embodiment is further improved.
In a possible design, after the MySQL database is modified according to the data modification request worksheet, a corresponding binlog log is generated, and accordingly, the recording of the modification information of the data modification request worksheet refers to extracting the modified library name, the form name, the primary key ID, and data before and after modification in the MySQL database from the binlog log as the modification information of the data modification request worksheet.
In a possible design, after the above steps are performed for multiple times, a search engine stores multiple pieces of modification information of a database, so in this embodiment, query information input by a user through the search engine may also be obtained, a target keyword matched with the query information is searched in the search engine, a target work order number associated with the target keyword is searched, target work order information associated with the target work order number is then queried in the work order system, and finally, the target work order information and the target modification information associated with the target work order information are output through the work order system. Through the scheme of the embodiment, the quick backtracking of the modification information and the work order information generated in the process can be realized.
In another aspect, an embodiment of the present application provides a data management apparatus, which includes a receiving unit, a modifying unit, a recording unit, a saving unit, and an associating unit. The receiving unit is used for receiving a data modification request work order submitted by a user through a work order system, wherein the data modification request work order comprises work order information; the modification unit is used for modifying the MySQL database according to the data modification request worksheet; the recording unit is used for recording modification information of the data modification request work order, and the modification information is used for indicating modified data in the database; the storage unit is used for storing the modification information into a search engine; and the association unit is used for associating the modification information with the work order information.
In one possible design, the work order information includes an SQL statement, and the data management apparatus further includes a verification unit configured to parse the SQL statement; the modification unit is specifically configured to, in a case that there is no problem in an analysis result, call the SQL statement to execute the operation of modifying the MySQL database according to the work order information.
In a possible design, the data management apparatus further includes a duplicate checking unit, configured to perform hash processing on the SQL statement by the work order system to obtain a hash value when an analysis result is that there is no problem; judging whether the hash value exists; and if the work order information does not exist, calling the SQL sentence to execute the operation of modifying the MySQL database according to the work order information.
In a possible design, the saving unit is further configured to save the hash value in the absence of the hash value; the data management device also comprises a numbering unit which is used for generating a work order number corresponding to the data modification request work order through the work order system; the association unit is further configured to associate the work order number with the hash value and the work order information.
In a possible design, the association unit is further configured to establish an association relationship between the worksheet number and a keyword in the search engine.
In a possible design, the data management apparatus further includes a generating unit, configured to generate a binlog log after modifying the MySQL database according to the data modification request worksheet; the recording unit is specifically configured to extract, from the binlog log, the modified library name, the form name, the primary key ID, and data before and after modification in the MySQL database, as modification information of the data modification request work order.
In a possible design, the receiving unit is configured to obtain query information input by a user through the search engine; the data management device also comprises a searching unit, a searching unit and a searching unit, wherein the searching unit is used for searching a target keyword matched with the query information in the search engine, searching a target work order number associated with the target keyword, and then querying target work order information associated with the target work order number in the work order system; the data management device further comprises an output unit, which is used for outputting the target work order information and the target modification information associated with the target work order information through the work order system.
In yet another aspect, an embodiment of the present application provides a data management device, which includes a processor, a memory, and a communication interface, where the processor, the memory, and the communication interface are connected to each other, where the data management device performs data interaction with other devices through the communication interface, the memory is used for storing a computer program, the computer program includes program instructions, and the processor is configured to call the program instructions to perform the method according to the first aspect.
In yet another aspect, an embodiment of the present application provides a computer-readable storage medium, where a computer program is stored in the computer-readable storage medium, and the computer program includes program instructions that are executed by a processor to perform the method according to the first aspect.
In the method, a work order system receives a data modification request work order submitted by a user, then a MySQL database is modified according to the data modification request work order, modification information of the data modification request work order is recorded and obtained, and finally the modification information is stored in a search engine and is associated with the work order information in the data modification request work order. Therefore, according to the method and the device, the modification information is stored in the search engine, and is associated with the work order information of the data modification request work order, so that the modification records of the database can be stored in the search engine and the work order system for a long time, a user can conveniently backtrack the modification condition of the database at any time, and the problem that the data can be lost is avoided. In summary, then, the present application provides an efficient data management method for database modification scenarios.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings used in the description of the embodiments will be briefly introduced below.
Fig. 1 is a schematic view of an application scenario of a data management method provided in an embodiment of the present application;
fig. 2 is a schematic flow chart of a data management method provided in an embodiment of the present application;
FIG. 3 is a schematic flow chart diagram of a data management method according to another embodiment of the present application;
FIG. 4 is a schematic block diagram of a data management apparatus provided in an embodiment of the present application;
fig. 5 is a structural block diagram of a data management device according to an embodiment of the present application.
Detailed Description
The technical solutions in the embodiments of the present application will be described clearly and completely with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only some embodiments of the present application, and not all embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
The present application is mainly applied to a data management device, which may be a conventional data management device, a database server, a web server, or the data management device described in the fourth embodiment of the present application, and the present application does not limit this. When the data management equipment performs data interaction with other terminal equipment, the data management equipment and the other terminal equipment record and transmit the characteristics of the data according to a preset format, wherein the characteristics of the data comprise time, place, type and the like. The terminal device may be any device having a data transceiving function and a processing function, and the present application does not limit this.
The database can store information such as data and data relations, and the information in the database can be subjected to modification operations such as addition and deletion. In order to better manage the modification process, the database records the modification condition through a log. But the modification condition recorded in the log is not only single in content, but the log is more and more difficult to be managed orderly along with the increase of the content. In order to solve the problem of data management aiming at the modification condition of a database, the application provides an efficient data management method.
In order to better understand the embodiment of the present invention, a method applying the embodiment of the present invention will be described below with reference to fig. 1, and the embodiment of the present invention may be applied to a scenario where a database is modified.
The scheme can be completed by cooperation of the client device and the data management device, or can be completed by the data management device with the input and output function independently, which is not limited in the application. When the scheme is completed by cooperation of the client device and the data management device, as shown in fig. 1, the client device and the data management device may perform data interaction in a wired or wireless manner. The client device can log in the work order system through an application program or a web page installed on the client device and perform data interaction with the data management device through the work order system, and similarly, the data management device can also log in the work order system through an application program or a web page installed on the data management device and perform data interaction with the client device through the work order system. The work order system is a system capable of recording, processing and tracking the completion condition of work.
Specifically, the client device uploads a data modification request work order in the work order system, the data modification request work order is used for requesting the data management device to modify the database, and therefore after the data management device receives the data modification request instruction through the work order system, the data modification request work order information in the data modification request work order is extracted at first, and the work order information comprises information such as SQL statements, modification reasons, uploading time and uploading personnel. The SQL statement, i.e. Structured Query Language (SQL), is a modification instruction applied to the database, and when the database management device calls the database to execute the SQL statement, the modification operation on the database can be completed.
After the work order information is extracted, the data management device performs syntax analysis on the SQL statement to determine whether the SQL statement has problems, such as syntax problems, problems that object information is incorrect or not in compliance with the specification, and problems that the SQL statement does not conform to the customized normative constraints for the SQL statement. If the analysis result is that no problem exists, whether the SQL statement is executed or not is checked, namely the hash value of the SQL statement is calculated, whether the same hash value is stored in the work order system or not is judged, if yes, the SQL statement is executed, repeated operation warning information is prompted to prompt a user that the SQL statement is executed repeatedly, if not, the SQL statement is not executed, the hash value is stored in the work order system, then a work order number corresponding to the data modification request work order is generated through the work order system, the work order number is associated with keywords in a search engine, and the hash value of the SQL statement, the work order information of the corresponding data modification request work order and the work order number of the data modification request work order are also associated. The work order number has uniqueness, different data modification request work orders correspond to different work order numbers, and the work order numbers can be any combination of characters such as digital and English special characters.
And under the condition that the SQL sentence is determined to be correct and is not executed, the data management equipment calls the database to execute the SQL sentence in the work order information so as to perform modification operations such as addition and deletion on the database and obtain a binlog log recorded with the modification condition of the database, and then extracts the modified library name, the form name, the main key ID, the data before and after modification, the modification time and the like in the MySQL database from the binlog log to serve as the modification information of the data modification request work order.
And after the modification information of the data modification request work order and the work order information are obtained, storing the modification information into a search engine, and associating the modification information with the work order information in the work order system. The search engine can be a distributed storage based search engine, and the distributed storage based search engine has stronger data search capability and data storage capability compared with a non-distributed storage search engine.
After the steps are carried out for a plurality of times, some modification information is stored in the search engine, some work order information is also stored in the work order system, in this case, query information input by the search engine is obtained, then a target keyword matched with the query information is searched in the search engine, a target work order number associated with the target keyword is searched, then target work order information associated with the target work order number is queried in the work order system, target modification information associated with the target work order information is obtained in the search engine, and finally the target work order information and the target modification information are output through the work order system. Therefore, the data management equipment can manage the modification information and the work order information orderly and efficiently, so that the modification information and the work order information can be traced quickly.
It should be noted that, when the present scheme is implemented solely by the data management device having the input/output function, the data management device may log in the work order system through an application program or a web page installed on the data management device, and the data management device has the input/output function, so that the user may directly log in the work order system on the data management device and upload information such as the data modification request work order. As seen from the above-described process of completing the solution by the cooperation of the client device and the data management device, it can be inferred that the process of completing the solution by the data management device alone is undoubtedly performed, and details of this application are not repeated herein.
It should be noted that the content shown in fig. 1 is an example, and does not constitute a limitation to the embodiment of the present invention, because the present application does not limit 1 and so on. For example, the present application may utilize a client device and a data management device to cooperate to complete the scheme, or may only utilize a data management device with an input/output function to complete the scheme alone.
Referring to fig. 2, which is a schematic flowchart of a data management method provided in an embodiment of the present application, the data management method shown in fig. 2 may include:
201: and receiving a data modification request work order submitted by a user through the work order system, wherein the data modification request work order comprises work order information.
In the embodiment of the present application, the data management device receives, through the work order system, a data modification request work order for instructing to modify the database, where the data modification request work order may be input into the work order system through an input device (e.g., a keyboard or the like) of the data management device, or may be input into the work order system through an input device of the client device by a user, and the data management device may log into the work order system through an application program or a web page installed on the data management device and perform data interaction with another device (e.g., a client device) through the work order system.
It should be noted that the data modification request work order includes work order information, and the work order information is used to describe the database modification condition, including information such as SQL statements, modification reasons, uploading time, and uploading personnel. The SQL statement is a modification instruction applied to the database, and when the database management device calls the database to execute the SQL statement, the modification operation on the database can be completed.
It should be noted that the work order system may be a computer software package or a Web-based software package for recording, processing and tracking the completion of the work. Specifically, the work order system can be used for creating, suspending, solving the transaction request submitted by the user, and providing a normalized, unified and clear transaction management mode. The work order system in the embodiment of the application is a work order system which is specially used for managing modification transactions of a database, so that a user can create transactions on the work order system, namely a data modification request work order, the work order system stores the data modification request work order after receiving the data modification request work order created by the user, the user can inquire the transactions after logging in the work order system at any time and any place, in addition, the work order system can also provide the data modification request work order for data management equipment and feed back modification results of the data management equipment for modifying the database according to the data modification request work order and the like to the user in time, therefore, the work order system in the embodiment of the application can be regarded as a management system of the database, not only can assist in completing modification of the database, but also can obtain work order information for describing modification conditions of the database, and is different from a common work order system.
202: and modifying the MySQL database according to the data modification request worksheet.
In the embodiment of the application, the database is modified according to the data modification request work order, that is, the database is called to execute the SQL statement in the data modification request work order, so as to complete the modification of the database.
203: and recording the modification information of the data modification request work order.
In the embodiment of the application, while the database is modified, modification information of the data modification request work order is recorded, wherein the modification information is used for indicating modified contents in the database and comprises information such as data before and after modification, modification time, modified library names, modified table names and primary key IDs.
In one implementation, during the process of calling the database to execute the SQL statement in the data modification request worksheet, a binlog log is generated, in which information describing the modification of the database is recorded, and then the modified database name, the form name, the primary key ID, the modification time, the data before and after modification, and the like in the database are extracted from the binlog as the modification information of the data modification request worksheet. Wherein, the binlog log is a binary log of the database and is used for recording the modified information of the database.
In one implementation, before the database is modified according to the data modification request worksheet, syntax analysis is performed on the SQL statements, that is, whether the SQL statements are correct is detected, and if the analysis result is that no problem exists, the SQL statements are called to execute the operation of modifying the MySQL database according to the worksheet information, and then if the SQL statements are determined to be correct, the database is called to execute the SQL statements to modify the database. In another case, namely, in the case of determining that the SQL statement is wrong, statement wrong warning information is prompted by the work order system.
In the embodiment of the application, before the SQL statement is executed by calling the database, whether the SQL statement is correct is checked according to a preset syntax rule of the SQL statement, and the SQL statement is executed on the calling database only when the SQL statement is verified to be correct. Namely, whether the SQL statement has at least one of the problems of grammar problem, incorrect object information or non-conformity with the standard, non-conformity with the standardized constraint aiming at the SQL statement customized, and the like is sequentially judged.
In one implementation, the SQL statement is parsed with a parser. Examples of the parser include a MySQL-based SQL parser.
In one implementation, after the SQL statement is checked to be correct, not only the correctness of the SQL statement may be determined, but also, in the case that the SQL statement is determined to be incorrect, statement error information indicating that the SQL statement is incorrect may be obtained, where the information includes a table, a library, a column, and the like in the SQL statement is incorrect or does not conform to the specification, or a non-existent object is included in the SQL statement, or the SQL statement does not conform to the preset SQL normative constraint. For example, the delete statement does not indicate data that needs to be deleted, and the create statement does not have information such as a primary key.
In one implementation, after determining that the SQL statement is not problematic (i.e., determining that the SQL statement is correct), it is detected whether the SQL statement is repeatedly executed, and then, if it is determined that the SQL statement is not repeatedly executed, the database is called to execute the SQL statement to modify the database. In another case, namely, in the case that the SQL statement is determined to be executed repeatedly, the repeated operation warning information is prompted by the work order system. Specifically, the detecting whether the SQL statement is repeatedly executed may be that a hash algorithm is used to calculate a hash value of the SQL statement, then whether the work order system stores the hash value is detected to determine whether the hash value already exists, and when the work order system detects that the hash value is stored in the work order system, the SQL statement is repeatedly executed. And under the other condition, namely under the condition that the hash value is not stored in the work order system, determining that the SQL statement is not repeatedly executed, saving the hash value into the work order system, and calling the SQL statement to execute the operation of modifying the MySQL database according to the work order information.
In this embodiment of the present application, before the SQL statement is executed by the call database, whether the SQL statement in the data modification request work order is executed is checked by comparing the SQL statement in the data modification request work order with the SQL statement in the work order system, and the SQL statement is executed by the call database only when it is verified that the SQL statement is not executed. Specifically, if the SQL statement that is consistent with the SQL statement in the data modification request work order is found in the work order system, it is determined that the SQL statement is executed, otherwise, the SQL statement is not executed. In the specific implementation, the hash value of the SQL statement is calculated by using hash calculation, and since the probability that the hash values obtained by hash calculation are the same is extremely low for any two different pieces of information, the hash value can be used as identification information of the SQL statement, and whether the SQL statements are the same or not can be determined by comparing the identification information between the SQL statements. Therefore, the embodiment of the application can quickly determine whether the work order system has the SQL sentences consistent with the SQL sentences of the data modification request work orders or not by calculating the hash values of the SQL sentences and then comparing the hash values of the hash sentences of all the work order systems stored in the work order system. The principle of the hash algorithm is the same as fingerprint extraction, namely, a mark for ensuring the uniqueness of a file by using shorter information, the mark is related to each byte of the file, and a reverse rule is difficult to find, so that the hash value of the SQL statement can uniquely identify the SQL statement.
In one implementation, when the hash value is determined to be absent, the hash value is saved in a work order system, a work order number corresponding to the data modification request work order is generated through the work order system, and the work order number is associated with the hash value and the work order information. The work order number can uniquely confirm corresponding work order information and can be any combination of characters such as numbers, english special characters and the like.
In one implementation, the work order number of the data modification request work order is associated with a keyword in a search engine.
204: and storing the modification information into a search engine, and associating the modification information with the work order information.
In the embodiment of the application, after the modification information is recorded and the work order information in the data modification request work order is extracted, the modification information is stored in the search engine and is associated with the work order information in the work order system. Specifically, a relational mapping table is established between the modification information and the associated work order information, so that the corresponding modification information can be found in a search engine subsequently according to the work order information.
In one implementation, the search engine is a distributed storage based search engine. The distributed storage based search engine refers to a search engine based on a distributed storage system, that is, data is stored on a plurality of independent devices in a distributed mode. Compared with a search engine with non-distributed storage, the search engine based on distributed storage has stronger data search capability and data storage capability, can meet the requirement of large-scale storage application, and shares storage load by using an expandable system structure and a plurality of storage servers, so that the reliability, the availability and the access efficiency of a system are improved, and the expansion is easy.
In one implementation, if the modification of the database according to the data modification request work order fails, that is, if the database is invoked and executed fails, a modification failure warning message is prompted to prompt the user that the database is failed to be modified.
In one implementation, query information input by a user through a search engine is acquired, a target keyword matched with the query information is searched in the search engine, a target work order number associated with the target keyword is searched, target work order information associated with the target work order number is queried in a work order system, and finally, the target work order information and target modification information associated with the target work order information are output through the work order system.
After the steps are carried out for a plurality of times, some modification information is stored in the search engine, some work order information is also stored in the work order system, in this case, query information input through the search engine is obtained, then a target keyword matched with the query information is searched in the search engine, then a target work order number associated with the target keyword is obtained, target work order information associated with the target work order number is obtained in the work order system, target modification information associated with the target work order is queried in the search engine, and finally the target modification information and the target work order information are output through the work order system. Therefore, the data management equipment can manage the modification information and the work order information in order and efficiently, so that the modification information and the work order information can be searched quickly. Wherein the query information may be any form of characters.
In an implementation mode, the modification information is stored in a search engine, and the work order information is also stored in the search engine, so that when data is subsequently retrieved, the target work order information and the target modification information are acquired in the search engine at one time, and therefore the retrieval time can be saved.
In the embodiment of the application, a work order system receives a data modification request work order submitted by a user, then the MySQL database is modified according to the data modification request work order, modification information of the data modification request work order is recorded and obtained, and finally the modification information is stored in a search engine and is associated with the work order information in the data modification request work order. Therefore, according to the embodiment of the application, the modification information is stored in the search engine and is associated with the work order information of the data modification request work order, so that the modification records of the database can be stored in the search engine and the work order system for a long time, a user can conveniently backtrack the modification condition of the database at any time without worrying about the problem of data loss, and the user can precisely backtrack the data due to the fact that the work order information contains information such as SQL (structured query language) statements, modification reasons, uploading time, uploading personnel and the like, and can be positioned to specific uploading personnel, modification reasons and the like. In summary, then, the present application provides an efficient data management method for database modification scenarios.
Referring to fig. 3, which is a schematic flowchart illustrating another data management method according to an embodiment of the present application, where the data management method shown in fig. 3 may include:
301: and receiving a data modification request work order submitted by a user through a work order system, wherein the data modification request work order comprises work order information, and the work order information comprises SQL statements.
302: and carrying out syntactic analysis on the SQL statement.
In the embodiment of the application, whether the SQL statement is correct is analyzed according to a preset syntax rule of the SQL statement, or the syntax analyzer is used to perform syntax analysis on the SQL statement to determine whether the SQL statement is correct. Examples of the syntax analyzer include an SQL parser based on MySQL.
In one implementation, after the SQL statement is checked to be correct, not only the correctness of the SQL statement may be determined, but also, in the case that the SQL statement is determined to be incorrect, statement error information indicating that the SQL statement is incorrect may be obtained, where the information includes a table, a library, a column, and the like in the SQL statement is incorrect or does not conform to the specification, or a non-existent object is included in the SQL statement, or the SQL statement does not conform to the preset SQL normative constraint. For example, the delete statement does not indicate data that needs to be deleted, and the create statement does not have information such as a primary key.
303: and under the condition that the analysis result is that no problem exists, carrying out hash processing on the SQL statement through the work order system to obtain a hash value.
In the embodiment of the application, when the analysis result is that no problem exists, that is, when the SQL statement is determined to be correct, the work order system performs hash processing on the SQL statement to obtain a hash value. The principle of the hash processing is the same as that of fingerprint extraction, namely, a mark for ensuring the uniqueness of a file by using shorter information is used as a mark for mapping a large file into a small string of characters, the mark is related to each byte of the file, and a reverse rule is difficult to find, so that the hash value of an SQL statement can uniquely identify the SQL statement.
304: and judging whether the hash value exists, if not, storing the hash value, generating a work order number corresponding to the data modification request work order through a work order system, associating the work order number with the hash value and the work order information, and establishing an association relation between the work order number and keywords in a search engine.
In the embodiment of the application, if the hash value is not found in the work order system, it is determined that the SQL statement has not been executed, the hash value is stored, a work order number corresponding to the data modification request work order is generated by the work order system, the work order number is associated with the hash value and the work order information, and an association relationship is established between the work order number and a keyword in a search engine. In another case, that is, when it is detected that the hash value is not stored in the work order system, it is determined that the SQL statement is not repeatedly executed, the hash value is stored in the work order information of the data modification request work order, and then the work order system prompts statement error warning information.
305: and calling SQL sentences to modify the MySQL database to generate corresponding binlog logs.
In the embodiment of the present application, when it is determined that the SQL statement is correct and is not repeatedly executed, the database is called to execute the SQL statement in the data modification request work order to complete modification of the database, and in the process of calling the database to execute the SQL statement in the data modification request work order, a binlog log is generated, in which information describing a modification condition of the database is recorded. Wherein, the binlog log is a binary log of the database and is used for recording the modified information of the database.
306: and extracting the modified library name, the list name, the primary key ID and the data before and after modification in the MySQL database from the binlog log to serve as modification information of the data modification request worksheet.
In the embodiment of the present application, after the binlog log is obtained, the modified library name, the form name, the primary key ID, and the data before and after modification in the database are extracted from the binlog log as modification information of the data modification request work order
In one implementation, after determining that the SQL statement is correct, before checking whether the SQL statement is repeatedly executed, the work order system detects whether a commit confirmation command is received, where the commit confirmation command is used to instruct execution of the step of checking whether the SQL statement is repeatedly executed, executes the step of checking whether the SQL statement is repeatedly executed if the commit confirmation command is received, and does not execute the step of checking whether the SQL statement is repeatedly executed if the commit confirmation command is not received.
307: and storing the modification information into a search engine, and associating the modification information with the work order information.
308: and acquiring query information input by a user through the search engine.
In this embodiment of the present application, the data management device receives, by using the search engine, query information, where the query information may be consistent with a part of words in the modification information, or may be similar in meaning to the part of words in the modification information, and this is not limited in this embodiment of the present application.
309: searching a target keyword matched with the query information in a search engine, and searching a target worksheet number associated with the target keyword.
In the embodiment of the application, the data management device searches a target keyword matched with the query information in a search engine, and searches a target worksheet number associated with the target keyword, wherein the matching mode comprises fuzzy matching, precise matching and the like.
310: and inquiring target work order information related to the target work order number in the work order system.
311: and outputting the target work order information and target modification information related to the target work order information through a work order system.
The embodiment of the application provides a specific implementation method of a data management method. Specifically, after receiving the data modification request work order through the work order system, the data management device firstly checks whether an SQL statement in the data modification request work order is correct, and checks whether the SQL statement is repeatedly executed under the condition that the SQL statement is correct, if the SQL statement is determined to be correct and not repeatedly executed, the data management device calls the database to execute the SQL statement to complete modification of the database, records modification information containing modification content of the database, and finally uploads the modification information to a search engine, and associates the modification information with the work order information. Therefore, before the SQL statement is executed by calling the database to modify the database, the SQL statement is determined to be correct and not to be executed repeatedly, so that the efficiency of data management can be further improved.
It should be noted that the above description of the various embodiments is intended to emphasize the differences between the various embodiments, and the same or similar parts may be referred to each other, and therefore, for brevity, the description is not repeated herein.
The embodiment of the application also provides a data management device, which is used for executing the unit of the data management method in any one of the preceding items. Specifically, referring to fig. 4, a schematic block diagram of a data management apparatus according to an embodiment of the present application is provided. The data management apparatus of the present embodiment includes: a receiving unit 401, a modifying unit 402, a recording unit 403, a saving unit 404 and an associating unit 405. Specifically, the method comprises the following steps:
a receiving unit 401, configured to receive, through a work order system, a data modification request work order submitted by a user, where the data modification request work order includes work order information; a modifying unit 402, configured to modify the MySQL database according to the data modification request form; a recording unit 403, configured to record modification information of the data modification request work order, where the modification information is used to indicate modified data in the database; a saving unit 404, configured to save the modification information to a search engine; an associating unit 405, configured to associate the modification information with the work order information.
In one implementation, the work order information includes an SQL statement, and the data management apparatus further includes a verification unit 406 configured to perform syntax analysis on the SQL statement; the modifying unit 402 is specifically configured to, if the analysis result indicates that there is no problem, call the SQL statement to perform the operation of modifying the MySQL database according to the work order information.
In one implementation, the data management apparatus further includes a duplicate checking unit 407, configured to perform hash processing on the SQL statement by the work order system to obtain a hash value when the analysis result indicates that there is no problem; judging whether the hash value exists; and if the MySQL database does not exist, calling the SQL statement to execute the operation of modifying the MySQL database according to the work order information.
In one implementation, the storing unit 404 is further configured to store the hash value when the hash value does not exist; the data management device further includes a numbering unit 408, configured to generate, by the work order system, a work order number corresponding to the data modification request work order; the associating unit 405 is further configured to associate the work order number with the hash value and the work order information.
In one implementation, the association unit 405 is further configured to establish an association relationship between the worksheet number and a keyword in the search engine.
In one implementation, the data management apparatus further includes a generating unit 409, configured to generate a binlog log after modifying the MySQL database according to the data modification request form; the recording unit 403 is specifically configured to extract, from the binlog log, the modified library name, the form name, the primary key ID, and data before and after modification in the MySQL database, so as to serve as modification information of the data modification request work order.
In one implementation, the receiving unit 401 is configured to obtain query information input by a user through the search engine; the data management apparatus further includes a searching unit 410, configured to search the search engine for a target keyword matching the query information, search for a target work order number associated with the target keyword, and then query the work order system for target work order information associated with the target work order number; the data management apparatus further includes an output unit 411 configured to output the target work order information and the target modification information associated with the target work order information through the work order system.
Referring to fig. 5, a schematic block diagram of a data management device according to another embodiment of the present application is provided. The data management device in the present embodiment as shown in the figure may include: a processor 510, a memory 520, and a communication interface 530. The processor 510, memory 520, and communication interface 530 are connected by a bus 540. Specifically, the method comprises the following steps:
a communication interface 530, configured to perform the function of the receiving unit 401, and configured to receive a data modification request work order submitted by a user through a work order system, where the data modification request work order includes work order information; a processor 510, configured to execute the function of the modification unit 402, and configured to modify the MySQL database according to the data modification request form; the processor 510 is further configured to execute a function of the recording unit 403, configured to record modification information of the data modification request work order, where the modification information is used to indicate modified data in the database; the processor 510 is further configured to execute the function of the saving unit 404, and is configured to save the modified information into the search engine; the processor 510 is further configured to execute the function of the association unit 405, and is configured to associate the modification information with the work order information.
In one implementation, the work order information includes SQL statements; the processor 510 is further configured to execute a verification unit 406, configured to perform syntax analysis on the SQL statement; the processor 510 is specifically configured to, if there is no problem in the analysis result, call the SQL statement to perform the operation of modifying the MySQL database according to the work order information.
In one implementation, the processor 510 is further configured to execute a function of a duplicate checking unit 407, configured to perform a hash process on the SQL statement by using the work order system to obtain a hash value if there is no problem in the analysis result; judging whether the hash value exists; and if the MySQL database does not exist, calling the SQL statement to execute the operation of modifying the MySQL database according to the work order information.
In one implementation, the processor 510 is further configured to save the hash value if the hash value does not exist; the processor 510 is further configured to execute the function of the numbering unit 408, and is configured to generate a work order number corresponding to the data modification request work order through the work order system; the processor 510 is further configured to associate the work order number with the hash value and the work order information.
In one implementation, the processor 510 is further configured to associate the worksheet number with a keyword in the search engine.
In one implementation, the processor 510 is further configured to execute a function of the generating unit 409, configured to generate a binlog log after the MySQL database is modified according to the data modification request form; the processor 510 is specifically configured to extract, from the binlog log, a modified library name, a form name, a primary key ID, and data before and after modification in the MySQL database, so as to serve as modification information of the data modification request work order.
In one implementation, the communication interface 530 is further configured to obtain query information input by a user through the search engine; the processor 510 is further configured to execute a function of the searching unit 410, configured to search a target keyword matched with the query information in the search engine, search a target work order number associated with the target keyword, and then query target work order information associated with the target work order number in the work order system; the communication interface 530 is further configured to execute the function of the output unit 411, and is configured to output the target work order information and the target modification information associated with the target work order information through the work order system.
It should be understood that in the embodiment of the present Application, the Processor 510 may be a Central Processing Unit (CPU), and the Processor 510 may also be other general-purpose processors, digital Signal Processors (DSPs), application Specific Integrated Circuits (ASICs), field Programmable Gate Arrays (FPGAs) or other Programmable logic devices, discrete Gate or transistor logic devices, discrete hardware components, etc. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
The memory 520 may include both read-only memory and random access memory, and provides instructions and data to the processor 510. A portion of memory 520 may also include non-volatile random access memory. For example, the memory 520 may also store device type information.
The computer readable storage medium may be an internal storage unit of the data management device of any of the foregoing embodiments, for example, a hard disk or a memory of the data management device. The computer readable storage medium may also be an external storage device of the data management device, such as a plug-in hard disk provided on the data management device, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), and the like. Further, the computer-readable storage medium may also include both an internal storage unit of the data management apparatus and an external storage apparatus. The computer-readable storage medium is used to store a computer program and other programs and data required by the data management apparatus. The computer-readable storage medium may also be used to temporarily store data that has been output or is to be output.
In a specific implementation, the processor 510 described in this embodiment may execute the implementation manners described in the first embodiment and the second embodiment of the data management method provided in this embodiment, and may also execute the implementation manner of the data management apparatus described in this embodiment, which is not described herein again.
Those of ordinary skill in the art will appreciate that the elements and algorithm steps of the examples described in connection with the embodiments disclosed herein may be embodied in electronic hardware, computer software, or combinations of both, and that the components and steps of the examples have been described in a functional general in the foregoing description for the purpose of illustrating clearly the interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the implementation. Skilled artisans may implement the described functionality using different data management methods for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.
It can be clearly understood by those skilled in the art that, for convenience and simplicity of description, the specific working processes of the data management device, the data management apparatus, and the unit described above may refer to the corresponding processes in the foregoing data management method embodiments, and are not described herein again.
In the several embodiments provided in the present application, it should be understood that the disclosed data management apparatus, data management device, and data management method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, a division of a unit is only a logical division, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may also be an electrical, mechanical or other form of connection.
The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one position, or may be distributed on a plurality of network units. Some or all of the elements may be selected according to actual needs to achieve the purpose of the solution of the embodiments of the present application.
In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit may be implemented in the form of hardware, or may also be implemented in the form of a software functional unit.
The integrated unit, if implemented in the form of a software functional unit and sold or used as a separate product, may be stored in a computer readable storage medium. Based on such understanding, the technical solutions of the present application may substantially or partially contribute to the prior art, or all or part of the technical solutions may be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a data management device, or a network device) to execute all or part of the steps of the methods according to the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
Claims (8)
1. A data management method is applied to a MySQL database and is characterized by comprising the following steps:
receiving a data modification request work order submitted by a user through a work order system, wherein the data modification request work order comprises work order information, and the work order information comprises SQL statements;
performing syntactic analysis on the SQL statement;
under the condition that the analysis result shows that no problem exists, carrying out hash processing on the SQL statement through the work order system to obtain a hash value;
judging whether the hash value exists;
if not, calling the SQL statement to execute: modifying the MySQL database according to the data modification request worksheet;
recording modification information of the data modification request work order, wherein the modification information is used for indicating modified data in the database;
and storing the modification information into a search engine, and associating the modification information with the work order information.
2. The method of claim 1, further comprising:
saving the hash value in the absence of the hash value;
and generating a work order number corresponding to the data modification request work order through the work order system, and associating the work order number with the hash value and the work order information.
3. The method of claim 2, further comprising:
and establishing an incidence relation between the worksheet number and the keywords in the search engine.
4. The method of claim 1, wherein after the modification of the MySQL database according to the data modification request worksheet, the method further comprises:
generating a corresponding binlog log;
the recording of the modification information of the data modification request work order includes:
and extracting the modified library name, the list name, the primary key ID and the data before and after modification in the MySQL database from the binlog log to serve as modification information of the data modification request work order.
5. The method of claim 3, further comprising:
acquiring query information input by a user through the search engine;
searching a target keyword matched with the query information in the search engine, and searching a target work order number associated with the target keyword;
inquiring target work order information related to the target work order number in the work order system;
and outputting the target work order information and the target modification information associated with the target work order information through the work order system.
6. A data management apparatus, comprising:
the system comprises a receiving unit, a data processing unit and a processing unit, wherein the receiving unit is used for receiving a data modification request work order submitted by a user through a work order system, the data modification request work order comprises work order information, and the work order information comprises SQL statements; the work order system is a work order system for managing modification affairs of a database;
the verification unit is used for carrying out syntactic analysis on the SQL statement;
the duplicate checking unit is used for carrying out hash processing on the SQL statement through the work order system under the condition that the analysis result is that no problem exists, so as to obtain a hash value; judging whether the hash value exists;
and the modification unit is used for calling the SQL statement to execute if the SQL statement does not exist: modifying the MySQL database according to the data modification request worksheet;
the recording unit is used for recording modification information of the data modification request work order, and the modification information is used for indicating modified data in the database;
the storage unit is used for storing the modification information into a search engine;
and the association unit is used for associating the modification information with the work order information.
7. A data management device comprising a processor, a memory and a communication interface, the processor, the memory and the communication interface being interconnected, wherein the data management device is configured to interact with other devices via the communication interface, the memory is configured to store a computer program comprising program instructions, and the processor is configured to invoke the program instructions to perform the method of any one of claims 1 to 5.
8. A computer-readable storage medium, characterized in that the computer storage medium stores a computer program comprising program instructions for execution by a processor for performing the method of any one of claims 1-5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910366894.9A CN110222028B (en) | 2019-04-30 | 2019-04-30 | Data management method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910366894.9A CN110222028B (en) | 2019-04-30 | 2019-04-30 | Data management method, device, equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110222028A CN110222028A (en) | 2019-09-10 |
| CN110222028B true CN110222028B (en) | 2022-11-15 |
Family
ID=67820284
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910366894.9A Active CN110222028B (en) | 2019-04-30 | 2019-04-30 | Data management method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110222028B (en) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111241125B (en) * | 2020-01-08 | 2023-06-02 | 北京数培通网络科技有限公司 | Method and device for recording operation log, electronic equipment and storage medium |
| CN111625552B (en) * | 2020-05-20 | 2024-01-02 | 北京百度网讯科技有限公司 | Data collection method, device, equipment and readable storage medium |
| CN111651431B (en) * | 2020-06-09 | 2023-06-27 | 中国雄安集团数字城市科技有限公司 | Database service-oriented management flow standardization method |
| CN112862449A (en) * | 2021-03-02 | 2021-05-28 | 岭东核电有限公司 | Structural chemical industry bill generation method and device, computer equipment and storage medium |
| CN117153361B (en) * | 2023-10-30 | 2024-01-26 | 苏州阿基米德网络科技有限公司 | Data processing method and device of medical equipment asset management system |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102572134A (en) * | 2010-12-30 | 2012-07-11 | 上海博泰悦臻电子设备制造有限公司 | Work order processing method, work order building system and work order processing system |
| WO2014000825A1 (en) * | 2012-06-29 | 2014-01-03 | Data2Knowledge Gmbh | A method for maintaining one or more table values for a number of tables in a database system |
| CN105373541A (en) * | 2014-08-22 | 2016-03-02 | 博雅网络游戏开发（深圳）有限公司 | Processing method and system for data operation request of database |
Family Cites Families (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7761900B2 (en) * | 2006-08-02 | 2010-07-20 | Clarendon Foundation, Inc. | Distribution of content and advertisement |
| CN103514166B (en) * | 2012-06-15 | 2016-09-21 | 阿里巴巴集团控股有限公司 | SQL inspection method and device |
| CN103745283A (en) * | 2012-10-17 | 2014-04-23 | 镇江雅迅软件有限责任公司 | Method of automatically judging workflow transfer node |
| CN103729442B (en) * | 2013-12-30 | 2017-11-24 | 华为技术有限公司 | Record the method and database engine of transaction journal |
| SG11201701066XA (en) * | 2014-11-19 | 2017-03-30 | Informex Inc | Data retrieval apparatus, program and recording medium |
| CN104376365B (en) * | 2014-11-28 | 2018-01-09 | 国家电网公司 | A kind of building method in the information system operation rule storehouse based on association rule mining |
| CN105654311A (en) * | 2015-12-24 | 2016-06-08 | 北京京东尚科信息技术有限公司 | User information providing method and device |
| US20170264428A1 (en) * | 2016-03-08 | 2017-09-14 | Manifold Technology, Inc. | Data storage system with blockchain technology |
| CN106874440B (en) * | 2017-02-07 | 2019-11-12 | 杭州秘猿科技有限公司 | A kind of block chain state storage method based on SQL database |
| CN108629564B (en) * | 2018-04-28 | 2019-03-08 | 中国船舶重工集团公司第七一九研究所 | A kind of Management in Large Scale Project system and method based on block chain technology |
| CN108876121B (en) * | 2018-05-31 | 2023-04-18 | 康键信息技术（深圳）有限公司 | Work order processing method and device, computer equipment and storage medium |
| CN108876122B (en) * | 2018-05-31 | 2024-02-06 | 康键信息技术（深圳）有限公司 | Batch work order processing method and device, computer equipment and storage medium |
-
2019
- 2019-04-30 CN CN201910366894.9A patent/CN110222028B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102572134A (en) * | 2010-12-30 | 2012-07-11 | 上海博泰悦臻电子设备制造有限公司 | Work order processing method, work order building system and work order processing system |
| WO2014000825A1 (en) * | 2012-06-29 | 2014-01-03 | Data2Knowledge Gmbh | A method for maintaining one or more table values for a number of tables in a database system |
| CN105373541A (en) * | 2014-08-22 | 2016-03-02 | 博雅网络游戏开发（深圳）有限公司 | Processing method and system for data operation request of database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110222028A (en) | 2019-09-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110222028B (en) | Data management method, device, equipment and storage medium | |
| US8782609B2 (en) | Test failure bucketing | |
| US10621211B2 (en) | Language tag management on international data storage | |
| CN109445837B (en) | Application program publishing method and device | |
| CN112306879A (en) | Interface parameter checking method, device, equipment and storage medium | |
| CN116226170A (en) | Database statement conversion method and device, electronic equipment and storage medium | |
| CN111338817B (en) | Interface preprocessing method and device | |
| CN112328631A (en) | Production fault analysis method and device, electronic equipment and storage medium | |
| CN112231407A (en) | DDL synchronization method, device, equipment and medium of PostgreSQL database | |
| CN114116691A (en) | Data import checking method, system, device and computer readable medium | |
| CN111444208B (en) | Data updating method and related equipment | |
| US11182375B2 (en) | Metadata validation tool | |
| US10782942B1 (en) | Rapid onboarding of data from diverse data sources into standardized objects with parser and unit test generation | |
| US10289531B2 (en) | Software integration testing with unstructured database | |
| CN107092671B (en) | Method and equipment for managing meta information | |
| CN116010461A (en) | Data blood relationship analysis method and device, storage medium and electronic equipment | |
| CN114416847A (en) | Data conversion method, device, server and storage medium | |
| CN112817931B (en) | Incremental version file generation method and device | |
| CN114625751A (en) | Data tracing query method and device based on block chain | |
| CN113037521A (en) | Method for identifying state of communication equipment, communication system and storage medium | |
| US11347722B2 (en) | Big data regression verification method and big data regression verification apparatus | |
| CN114546731B (en) | Workflow data recovery method and data recovery system | |
| CN115659413A (en) | Data checking method and device, electronic equipment and storage medium | |
| CN113778758A (en) | Data recovery method, device and equipment and readable storage medium | |
| CN115186019A (en) | Service data processing method, device, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |