CN110795451A - SQL fingerprint restoration method and device, computer equipment and storage medium - Google Patents

SQL fingerprint restoration method and device, computer equipment and storage medium Download PDF

Info

Publication number
CN110795451A
CN110795451A CN201910881257.5A CN201910881257A CN110795451A CN 110795451 A CN110795451 A CN 110795451A CN 201910881257 A CN201910881257 A CN 201910881257A CN 110795451 A CN110795451 A CN 110795451A
Authority
CN
China
Prior art keywords
sql
restored
variable
fingerprint
list
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.)
Granted
Application number
CN201910881257.5A
Other languages
Chinese (zh)
Other versions
CN110795451B (en
Inventor
高中正
金海锋
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Ping An Technology Shenzhen Co Ltd
Original Assignee
Ping An Technology Shenzhen Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Ping An Technology Shenzhen Co Ltd filed Critical Ping An Technology Shenzhen Co Ltd
Priority to CN201910881257.5A priority Critical patent/CN110795451B/en
Priority to PCT/CN2019/116950 priority patent/WO2021051522A1/en
Publication of CN110795451A publication Critical patent/CN110795451A/en
Application granted granted Critical
Publication of CN110795451B publication Critical patent/CN110795451B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

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/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • 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/245—Query processing
    • G06F16/2455—Query execution

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
  • Collating Specific Patterns (AREA)

Abstract

The application relates to a SQL fingerprint restoration method and device, computer equipment and a storage medium. The method comprises the following steps: acquiring an SQL fingerprint to be restored; the SQL fingerprint to be restored is obtained by analyzing the SQL fingerprint to be restored, so that the variables, the list names corresponding to the variables and the syntax trees of the variable list names can be obtained, and the variables can be found accurately; acquiring list names and variable list names corresponding to the variables by traversing variable nodes of the syntax tree; according to the list names and the variable column names corresponding to the variables in the database where the SQL fingerprints to be restored are located, the types of the corresponding columns in the list where the variables are located are obtained, and the SQL fingerprints to be restored can be accurately restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement. The SQL statement is restored through the SQL fingerprint, the performance of the SQL statement is analyzed through the SQL fingerprint, and the work efficiency of analyzing the performance of the SQL statement is improved.

Description

SQL fingerprint restoration method and device, computer equipment and storage medium
Technical Field
The present application relates to the field of computer technologies, and in particular, to a method and an apparatus for SQL fingerprint recovery, a computer device, and a storage medium.
Background
The use of bound variables in SQL (structured query language) has become a common consensus for developers. Generally, in an OLTP system (online transaction processing system), it often happens that the same SQL statement is executed repeatedly, except for different predicates, and each execution is considered as a different SQL statement due to the difference of predicates, so that an execution plan is regenerated every time, and thus contention and consumption of resources caused by each hard parsing are very large and blocking may also be caused.
The SQL statement that uses the binding variables is the SQL fingerprint. Such SQL statements can acquire execution plans by the explain method, but MySQL (relational database management system) currently does not support acquisition of SQL fingerprint execution plans. In some cases, only the SQL fingerprint is obtained, and in order to analyze the performance of the SQL statement, the execution plan of the SQL statement needs to be obtained through the SQL fingerprint so as to analyze the performance of the SQL statement. However, at present, no good method is available for restoring the SQL statement by the SQL fingerprint to analyze the performance of the SQL statement by the SQL fingerprint, so the work efficiency for analyzing the performance of the SQL statement is low.
Disclosure of Invention
Therefore, in order to solve the above technical problems, it is necessary to provide a method, an apparatus, a computer device, and a storage medium for reducing an SQL fingerprint, which improve performance of analyzing an SQL statement and have low work efficiency.
A SQL fingerprint recovery method, the method comprising:
acquiring an SQL fingerprint to be restored;
analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein the syntax tree includes variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the list;
traversing the variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to the variables of the variable nodes from the Jason structure;
obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored;
and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
In one embodiment, the step of obtaining the SQL fingerprint to be restored includes:
acquiring an SQL fingerprint;
calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result;
and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
In one embodiment, the step of analyzing the SQL fingerprint to be restored to obtain the syntax tree of the SQL fingerprint to be restored includes:
and calling an analysis interface in the SQL analysis package to analyze the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored.
In one embodiment, the step of obtaining, according to the list name and the variable column name corresponding to each variable, a type of a corresponding column in the list where each variable is located in the database where the SQL fingerprint to be restored is located includes:
acquiring an identifier of a database where the SQL fingerprint to be restored is located;
connecting the database where the SQL fingerprint to be restored is located according to the identification;
and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the corresponding column in the list in which each variable is located.
In one embodiment, the step of searching in the database where the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable and determining the type of the corresponding column in the list where each variable is located includes:
determining a list where each variable is located according to the list name corresponding to each variable;
determining the column of each variable according to the column name of each variable in the list of each variable;
and determining the type of the column corresponding to each variable according to the column where each variable is located.
In one embodiment, the step of restoring the SQL fingerprint to be restored according to the type of the column corresponding to each variable to obtain a restored SQL statement includes:
and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
In one embodiment, the method further comprises the following steps:
calling an explain method to obtain the execution plan of the restored SQL statement and obtain the execution plan of the restored SQL statement;
and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
An SQL fingerprint recovery apparatus, the apparatus comprising:
the SQL fingerprint acquisition module is used for acquiring an SQL fingerprint to be restored;
the syntax tree acquisition module is used for analyzing the SQL fingerprint to be restored to acquire the syntax tree of the SQL fingerprint to be restored; wherein the syntax tree includes variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the list;
the traversal module is used for traversing the variable nodes of the syntax tree and acquiring the list names and the variable list names corresponding to the variables of the variable nodes from the Jason structure;
the column type obtaining module is used for obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored;
and the restoring module is used for restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
A computer device comprising a memory storing a computer program and a processor implementing the steps of the method when executing the computer program.
A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the steps of the method.
The SQL fingerprint restoration method, the device, the computer equipment and the storage medium obtain the SQL fingerprint to be restored; the SQL fingerprint to be restored is obtained by analyzing the SQL fingerprint to be restored, so that the variables, the list names corresponding to the variables and the syntax trees of the variable list names can be obtained, and the variables can be found accurately; acquiring list names and variable list names corresponding to the variables by traversing variable nodes of the syntax tree; according to the list names and the variable column names corresponding to the variables in the database where the SQL fingerprints to be restored are located, the types of the corresponding columns in the list where the variables are located are obtained, and the SQL fingerprints to be restored can be accurately restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement. The SQL statement is restored through the SQL fingerprint, the performance of the SQL statement is analyzed through the SQL fingerprint, and the work efficiency of analyzing the performance of the SQL statement is improved.
Drawings
FIG. 1 is a diagram illustrating an exemplary scenario for an SQL fingerprint recovery method;
FIG. 2 is a flow diagram illustrating a SQL fingerprint recovery method according to an embodiment;
FIG. 3 is a block diagram of an embodiment of an SQL fingerprint recovery device;
FIG. 4 is a block diagram of an SQL fingerprint recovery device in another embodiment;
FIG. 5 is a diagram illustrating an internal structure of a computer device according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
The SQL fingerprint restoration method provided by the application can be applied to the application environment shown in FIG. 1. Wherein the terminal 102 communicates with the server 104 via a network. The SQL fingerprint to be restored can be acquired through the terminal 102; analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the lists; traversing variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to variables of the variable nodes from the Jason structure; obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement. The terminal 102 may also interact with the server 104 through a network, and the server 104 obtains the SQL fingerprint to be restored uploaded by the terminal 102; the server 104 analyzes the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the lists; traversing variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to variables of the variable nodes from the Jason structure; obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement. The terminal 102 may be, but not limited to, various personal computers, notebook computers, smart phones, tablet computers, and portable wearable devices, and the server 104 may be implemented by an independent server or a server cluster formed by a plurality of servers.
In an embodiment, as shown in fig. 2, an SQL fingerprint recovery method is provided, which is described by taking the example that the method is applied to the terminal in fig. 1, and includes the following steps:
step S220, obtaining the SQL fingerprint to be restored.
The SQL fingerprint to be restored refers to the SQL fingerprint which needs to be restored into an SQL statement. SQL fingerprints are SQL statements that use binding variables, such as: select from test where id? Let select from t where id be 1; select from t where id 2; select from t where id 3. The SQL fingerprints corresponding to these three SQL statements are select from t where id? . The user can directly upload the SQL fingerprint to be restored through the terminal; or the user stores the SQL fingerprint to be restored in the database in advance and acquires the SQL fingerprint to be restored from the database.
Step S240, analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are provided with Jason structures for describing the corresponding relation between the variable nodes and the lists.
The syntax tree is a graphic representation of a sentence structure, represents a derivation result of a sentence, and is beneficial to understanding the hierarchy of the sentence syntax structure. And the syntax tree of the SQL fingerprint to be restored is a tree formed by deduction according to the SQL fingerprint to be restored. The SQL fingerprint to be restored is analyzed through the SQL analysis package, and the syntax tree of the SQL fingerprint to be restored is obtained. And other tools capable of analyzing the SQL fingerprint to be restored can be used for analyzing the SQL fingerprint to be restored to obtain the syntax tree of the SQL fingerprint to be restored.
Step S260, traversing the variable nodes of the syntax tree, and obtaining the list names and variable column names corresponding to the variables of each variable node from the Jason structure.
The variable nodes are configured with a Jason structure for describing the corresponding relationship between the variable nodes and the lists, namely list names and variable column names corresponding to the variables are recorded in the json structure. When a variable node is traversed in a syntax tree, a corresponding list name and a variable column name can be found in a json structure configured by the variable node. Traversing the syntax tree of the SQL fingerprint to be restored according to the variable identification, determining each variable node in the syntax tree, and acquiring the list name and the variable column name corresponding to each variable node according to each variable node, if: in the syntax tree of the SQL fingerprint to be restored, the variable node is the question mark "? "identified, traversed to"? The judgment is that the variable node is a variable node, and the list name and the variable column name corresponding to the variable of the variable node can be obtained according to the variable node. The corresponding column in the list where the variable is located can be further found based on the list name and the variable column name corresponding to the variable.
Step S280, acquiring the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored.
The database where the SQL fingerprint to be restored is located may be connected, for example: the information _ schema is a database, specifically an information database, in which information about all other databases maintained by the MySQL server is stored, such as database names, tables of the databases, data types and access rights of table columns, and the like. Obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored, such as: the method comprises the steps of inquiring a table with table _ name, data _ type and other information in the columns under information _ schema (column information in the table is provided, and information of all columns and each column of a certain table is expressed in detail), acquiring data types of corresponding columns of variables in the columns by inquiring the table _ name from the columns.
And step S300, restoring the SQL fingerprint to be restored according to the type of the column corresponding to each variable to obtain the restored SQL sentence.
Wherein, the variables in the SQL fingerprint can be replaced according to the type of the obtained corresponding column of the variables (the type refers to the data type). For the example SQL statement, assuming that the data type of id is int, the SQL statement after the SQL fingerprint recovery is: select from test where id is 1.
In the SQL fingerprint restoration method, the SQL fingerprint to be restored is acquired; the SQL fingerprint to be restored is obtained by analyzing the SQL fingerprint to be restored, so that the variables, the list names corresponding to the variables and the syntax trees of the variable list names can be obtained, and the variables can be found accurately; acquiring list names and variable list names corresponding to the variables by traversing variable nodes of the syntax tree; according to the list names and the variable column names corresponding to the variables in the database where the SQL fingerprints to be restored are located, the types of the corresponding columns in the list where the variables are located are obtained, and the SQL fingerprints to be restored can be accurately restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement. The SQL statement is restored through the SQL fingerprint, the performance of the SQL statement is analyzed through the SQL fingerprint, and the work efficiency of analyzing the performance of the SQL statement is improved.
In one embodiment, the step of obtaining the SQL fingerprint to be restored includes: acquiring an SQL fingerprint; calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result; and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
The SQL analysis package can be an open-source Vitess/sqlparse analysis package; or an sql parser packet of the start source database TiDB; other parsing packages can be removed, and different open source parsing packages can be designed based on languages. The grammar checking interface in the SQL analysis package can be an interface provided for grammar checking in the Vitess/sqlparse analysis package; it may also be an interface provided for syntax checking in the sql parser package of the start source database TiDB. Carrying out syntax check on the SQL fingerprint based on a syntax check interface to obtain a check result, wherein when the syntax is correct, the check result is passed, and the SQL fingerprint which is passed by the check result is taken as the SQL fingerprint to be restored; and when the grammar is wrong, the check result is failed, the user is reminded of uploading the SQL fingerprint again, and the grammar mistake is reminded. And the user is prevented from uploading wrong SQL fingerprints, and the running speed is improved.
In one embodiment, the step of parsing the SQL fingerprint to be restored to obtain the syntax tree of the SQL fingerprint to be restored includes: and calling an analysis interface in the SQL analysis package, analyzing the SQL fingerprint to be restored, and obtaining a syntax tree of the SQL fingerprint to be restored.
The SQL analysis package can be an open-source Vitess/sqlparse analysis package; or an sql parser packet of the start source database TiDB; other parsing packages can be removed, and different open source parsing packages can be designed based on languages. The analysis interface in the SQL analysis package can be an interface provided in the Vitess/sqlparse analysis package for analyzing the SQL fingerprint; it may also be that the SQL fingerprint parsing interface is provided in the SQL parser package of the start source database TiDB. Specifically, an analysis interface in an open-source Vitess/sqlparse analysis package is called to analyze the SQL fingerprint to be restored, so as to obtain a syntax tree of the SQL fingerprint to be restored, and the analysis efficiency can be improved.
In one embodiment, the step of obtaining the type of the corresponding column in the list where each variable is located in the database where the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable includes: acquiring an identifier of a database where an SQL fingerprint to be restored is located; connecting a database where the SQL fingerprints to be restored are located according to the identification; and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the column corresponding to the list in which each variable is located.
The identification of the database can be transmitted in a parameter form through a terminal when a user starts the SQL fingerprint restoration method; or presetting the identifier of the database where the SQL fingerprint to be restored is located, and directly acquiring the identifier of the corresponding database. The database where the SQL fingerprint is located is connected through the identifier of the database, the database where the SQL fingerprint is located is connected through the transmitted parameter, the corresponding list can be found according to the list name, and the corresponding data type can be obtained in the corresponding column of the list, for example: the columns table has information such as table _ name, column _ name, and data _ type, and the table _ name is searched to obtain the relevant information. The method can automatically connect the database where the SQL fingerprint to be restored is located through parameters transmitted by the terminal when the user starts the SQL fingerprint restoration method, further determine the type of the corresponding column in the list where each variable is located, and improve the efficiency of restoring the SQL fingerprint into the SQL statement.
In one embodiment, the step of searching in the database where the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the column corresponding to the list where each variable is located includes: determining a list where each variable is located according to the list name corresponding to each variable; determining the column of each variable according to the column name of each variable in the list of each variable; and determining the type of the column corresponding to each variable according to the column where each variable is located.
Wherein, such as: the list name corresponding to the variable A is 'b', the variable column name is 'c', the 'c' column in the 'b' list is found in a column table of an information _ schema database according to the list name 'b' and the variable column name 'c', and the data type is the type of the column corresponding to the variable A according to the data type corresponding to the 'c' column where each variable is located. The types of the corresponding columns in the list where the variables are located are further determined, and the efficiency of restoring the SQL fingerprints into the SQL statements is improved.
In one embodiment, the step of restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement includes: and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding columns of the variables to obtain the restored SQL sentences.
Wherein, the variables in the SQL fingerprint can be replaced according to the type of the obtained corresponding column of the variables (the type refers to the data type). For the example SQL statement, assuming that the data type of id is int, the SQL statement after the SQL fingerprint recovery is: select from test where id is 1. The SQL statement is restored through the SQL fingerprint, the performance of the SQL statement is analyzed through the SQL fingerprint, and the work efficiency of analyzing the performance of the SQL statement is improved.
In one embodiment, further comprising: calling an explain method to obtain an execution plan of the restored SQL statement and obtaining the execution plan of the restored SQL statement; and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
After the restored SQL statement is obtained, connecting a MySQL database, calling an explain method to obtain an execution plan of the restored SQL statement, and obtaining the execution plan of the restored SQL statement; and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement. MySQL database is an open source relational database management system (RDBMS) that uses the most common database management language, Structured Query Language (SQL), for database management. The method of explain is the main method to see how the optimizer decides to execute the query. Therefore, the problem that the SQL fingerprint cannot acquire the execution plan in the MySQL is solved, and the work efficiency of analyzing the performance of the SQL statement is improved. When only the SQL fingerprint is acquired, in order to analyze the performance of the SQL statement, the corresponding execution plan may be acquired according to the SQL fingerprint from the past SQL statement, so as to optimize the performance, and improve the work efficiency.
It should be understood that, although the steps in the flowchart of fig. 2 are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in fig. 2 may include multiple sub-steps or multiple stages that are not necessarily performed at the same time, but may be performed at different times, and the order of performance of the sub-steps or stages is not necessarily sequential, but may be performed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
In one embodiment, as shown in fig. 3, there is provided an SQL fingerprint restoration apparatus, including: SQL fingerprint acquisition module 310, syntax tree acquisition module 320, traversal module 330, column type acquisition module 340, and restore module 350, wherein:
the SQL fingerprint acquisition module 310 is configured to acquire an SQL fingerprint to be restored;
the syntax tree obtaining module 320 is configured to parse the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the lists;
the traversal module 330 is configured to traverse the variable nodes of the syntax tree, and obtain a list name and a variable column name corresponding to a variable of each variable node from the Jason structure;
the column type obtaining module 340 is configured to obtain, according to the list name and the variable column name corresponding to each variable, a type of a corresponding column in the list where each variable is located in the database where the SQL fingerprint to be restored is located;
and the restoring module 350 is configured to restore the SQL fingerprint to be restored according to the type of the column corresponding to each variable, and obtain a restored SQL statement.
In one embodiment, the SQL fingerprint acquisition module 310 is further configured to: acquiring an SQL fingerprint; calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result; and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
In one embodiment, the syntax tree acquisition module 320 is further configured to: and calling an analysis interface in the SQL analysis package, analyzing the SQL fingerprint to be restored, and obtaining a syntax tree of the SQL fingerprint to be restored.
In one embodiment, the column type obtaining module 340 is further configured to: acquiring an identifier of a database where an SQL fingerprint to be restored is located; connecting a database where the SQL fingerprints to be restored are located according to the identification; and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the column corresponding to the list in which each variable is located.
In one embodiment, the column type obtaining module 340 is further configured to: determining a list where each variable is located according to the list name corresponding to each variable; determining the column of each variable according to the column name of each variable in the list of each variable; and determining the type of the column corresponding to each variable according to the column where each variable is located.
In one embodiment, the reduction module 350 is further configured to: and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding columns of the variables to obtain the restored SQL sentences.
As shown in fig. 4, in an embodiment, the SQL fingerprint recovery apparatus further includes a performance analysis module 360, configured to invoke an explain method to obtain an execution plan of the recovered SQL statement, and obtain an execution plan of the recovered SQL statement; and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
For the specific definition of the SQL fingerprint recovery apparatus, reference may be made to the above definition of the SQL fingerprint recovery method, which is not described herein again. The modules in the SQL fingerprint restoration apparatus may be wholly or partially implemented by software, hardware, or a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as shown in fig. 5. The computer device includes a processor, a memory, a network interface, a display screen, and an input device connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement an SQL fingerprint recovery method. The display screen of the computer equipment can be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer equipment can be a touch layer covered on the display screen, a key, a track ball or a touch pad arranged on the shell of the computer equipment, an external keyboard, a touch pad or a mouse and the like.
Those skilled in the art will appreciate that the architecture shown in fig. 5 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, there is provided a computer device comprising a memory storing a computer program and a processor implementing the following steps when the processor executes the computer program:
acquiring an SQL fingerprint to be restored; analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the lists; traversing variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to variables of the variable nodes from the Jason structure; obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
In one embodiment, the processor, when executing the computer program, further performs the steps of: acquiring an SQL fingerprint; calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result; and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
In one embodiment, the processor, when executing the computer program, further performs the steps of: and calling an analysis interface in the SQL analysis package, analyzing the SQL fingerprint to be restored, and obtaining a syntax tree of the SQL fingerprint to be restored.
In one embodiment, the processor, when executing the computer program, further performs the steps of: acquiring an identifier of a database where an SQL fingerprint to be restored is located; connecting a database where the SQL fingerprints to be restored are located according to the identification; and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the column corresponding to the list in which each variable is located.
In one embodiment, the processor, when executing the computer program, further performs the steps of: determining a list where each variable is located according to the list name corresponding to each variable; determining the column of each variable according to the column name of each variable in the list of each variable; and determining the type of the column corresponding to each variable according to the column where each variable is located.
In one embodiment, the processor, when executing the computer program, further performs the steps of: and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding columns of the variables to obtain the restored SQL sentences.
In one embodiment, the processor, when executing the computer program, further performs the steps of: calling an explain method to obtain an execution plan of the restored SQL statement and obtaining the execution plan of the restored SQL statement; and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
In one embodiment, a computer-readable storage medium is provided, having a computer program stored thereon, which when executed by a processor, performs the steps of:
acquiring an SQL fingerprint to be restored; analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein, the syntax tree comprises variable nodes; the variable nodes are configured with variable nodes of a Jason structure traversal syntax tree for describing the corresponding relation between the variable nodes and the lists, and list names and variable column names corresponding to the variables of the variable nodes are obtained from the Jason structure; traversing variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to the variable nodes from the Jason structure; obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored; and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
In one embodiment, the computer program when executed by the processor further performs the steps of: acquiring an SQL fingerprint; calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result; and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
In one embodiment, the computer program when executed by the processor further performs the steps of: and calling an analysis interface in the SQL analysis package, analyzing the SQL fingerprint to be restored, and obtaining a syntax tree of the SQL fingerprint to be restored.
In one embodiment, the computer program when executed by the processor further performs the steps of: acquiring an identifier of a database where an SQL fingerprint to be restored is located; connecting a database where the SQL fingerprints to be restored are located according to the identification; and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the column corresponding to the list in which each variable is located.
In one embodiment, the computer program when executed by the processor further performs the steps of: determining a list where each variable is located according to the list name corresponding to each variable; determining the column of each variable according to the column name of each variable in the list of each variable; and determining the type of the column corresponding to each variable according to the column where each variable is located.
In one embodiment, the computer program when executed by the processor further performs the steps of: and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding columns of the variables to obtain the restored SQL sentences.
In one embodiment, the computer program when executed by the processor further performs the steps of: calling an explain method to obtain an execution plan of the restored SQL statement and obtaining the execution plan of the restored SQL statement; and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory, among others. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.

Claims (10)

1. A SQL fingerprint recovery method, the method comprising:
acquiring an SQL fingerprint to be restored;
analyzing the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored; wherein the syntax tree includes variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the list;
traversing the variable nodes of the syntax tree, and acquiring list names and variable column names corresponding to the variables of the variable nodes from the Jason structure;
obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored;
and restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
2. The method according to claim 1, wherein the step of obtaining the SQL fingerprint to be restored comprises:
acquiring an SQL fingerprint;
calling a syntax checking interface in the SQL analysis packet to carry out syntax checking on the SQL fingerprint to obtain a checking result;
and taking the SQL fingerprint which passes the verification result as the SQL fingerprint to be restored.
3. The method according to claim 1, wherein the step of parsing the SQL fingerprint to be restored to obtain the syntax tree of the SQL fingerprint to be restored comprises:
and calling an analysis interface in the SQL analysis package to analyze the SQL fingerprint to be restored to obtain a syntax tree of the SQL fingerprint to be restored.
4. The method according to claim 1, wherein the step of obtaining, according to the list name and the variable column name corresponding to each variable, the type of the corresponding column in the list where each variable is located in the database where the SQL fingerprint to be restored is located includes:
acquiring an identifier of a database where the SQL fingerprint to be restored is located;
connecting the database where the SQL fingerprint to be restored is located according to the identification;
and searching in a database in which the SQL fingerprint to be restored is located according to the list name and the variable column name corresponding to each variable, and determining the type of the corresponding column in the list in which each variable is located.
5. The method according to claim 4, wherein the step of searching in the database of the to-be-restored SQL fingerprint according to the list name and the variable column name corresponding to each variable and determining the type of the corresponding column in the list of each variable includes:
determining a list where each variable is located according to the list name corresponding to each variable;
determining the column of each variable according to the column name of each variable in the list of each variable;
and determining the type of the column corresponding to each variable according to the column where each variable is located.
6. The method according to claim 1, wherein the step of restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain a restored SQL statement comprises:
and replacing the variables in the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
7. The method of any one of claims 1-6, further comprising:
calling an explain method to obtain the execution plan of the restored SQL statement and obtain the execution plan of the restored SQL statement;
and performing performance analysis on the restored SQL statement according to the execution plan of the restored SQL statement.
8. An apparatus for SQL fingerprint recovery, the apparatus comprising:
the SQL fingerprint acquisition module is used for acquiring an SQL fingerprint to be restored;
the syntax tree acquisition module is used for analyzing the SQL fingerprint to be restored to acquire the syntax tree of the SQL fingerprint to be restored; wherein the syntax tree includes variable nodes; the variable nodes are provided with Jason structure bodies used for describing the corresponding relation between the variable nodes and the list;
the traversal module is used for traversing the variable nodes of the syntax tree and acquiring the list names and the variable list names corresponding to the variables of the variable nodes from the Jason structure;
the column type obtaining module is used for obtaining the type of the corresponding column in the list of each variable according to the list name and the variable column name corresponding to each variable in the database of the SQL fingerprint to be restored;
and the restoring module is used for restoring the SQL fingerprint to be restored according to the type of the corresponding column of each variable to obtain the restored SQL statement.
9. A computer device comprising a memory and a processor, the memory storing a computer program, wherein the processor implements the steps of the method of any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 7.
CN201910881257.5A 2019-09-18 2019-09-18 SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium Active CN110795451B (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
CN201910881257.5A CN110795451B (en) 2019-09-18 2019-09-18 SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium
PCT/CN2019/116950 WO2021051522A1 (en) 2019-09-18 2019-11-11 Sql fingerprint restoration method and apparatus, computer device and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201910881257.5A CN110795451B (en) 2019-09-18 2019-09-18 SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium

Publications (2)

Publication Number Publication Date
CN110795451A true CN110795451A (en) 2020-02-14
CN110795451B CN110795451B (en) 2023-05-16

Family

ID=69427324

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201910881257.5A Active CN110795451B (en) 2019-09-18 2019-09-18 SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium

Country Status (2)

Country Link
CN (1) CN110795451B (en)
WO (1) WO2021051522A1 (en)

Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7092931B1 (en) * 2002-05-10 2006-08-15 Oracle Corporation Methods and systems for database statement execution plan optimization
CN101013442A (en) * 2007-02-07 2007-08-08 浙江大学 Non-proxy unified method for monitoring performance of data base
US20090063435A1 (en) * 2007-08-31 2009-03-05 Ebersole Steven Parameter type prediction in object relational mapping
CN106844380A (en) * 2015-12-04 2017-06-13 阿里巴巴集团控股有限公司 A kind of database operation method, information processing method and related device
CN110096513A (en) * 2019-04-10 2019-08-06 阿里巴巴集团控股有限公司 A kind of data query, fund checking method and device
CN110134707A (en) * 2019-04-12 2019-08-16 平安普惠企业管理有限公司 A kind of data query method, apparatus, electronic equipment and storage medium

Family Cites Families (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US10216826B2 (en) * 2014-09-02 2019-02-26 Salesforce.Com, Inc. Database query system

Patent Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7092931B1 (en) * 2002-05-10 2006-08-15 Oracle Corporation Methods and systems for database statement execution plan optimization
CN101013442A (en) * 2007-02-07 2007-08-08 浙江大学 Non-proxy unified method for monitoring performance of data base
US20090063435A1 (en) * 2007-08-31 2009-03-05 Ebersole Steven Parameter type prediction in object relational mapping
CN106844380A (en) * 2015-12-04 2017-06-13 阿里巴巴集团控股有限公司 A kind of database operation method, information processing method and related device
CN110096513A (en) * 2019-04-10 2019-08-06 阿里巴巴集团控股有限公司 A kind of data query, fund checking method and device
CN110134707A (en) * 2019-04-12 2019-08-16 平安普惠企业管理有限公司 A kind of data query method, apparatus, electronic equipment and storage medium

Also Published As

Publication number Publication date
WO2021051522A1 (en) 2021-03-25
CN110795451B (en) 2023-05-16

Similar Documents

Publication Publication Date Title
CN110209650B (en) Data normalization and migration method and device, computer equipment and storage medium
CN110795455B (en) Dependency analysis method, electronic device, computer apparatus, and readable storage medium
JP5298117B2 (en) Data merging in distributed computing
US9569207B2 (en) Source code flow analysis using information retrieval
CN111176996A (en) Test case generation method and device, computer equipment and storage medium
CN110737594B (en) Database standard conformance testing method and device for automatically generating test cases
CN107798030B (en) Splitting method and device of data table
CN108563694B (en) SQL execution method and device for logic deletion, computer equipment and storage medium
CN109710220B (en) Relational database query method, relational database query device, relational database query equipment and storage medium
CN111581271A (en) Interface parameter determination method and device, computer equipment and storage medium
CN110765152B (en) SQL extraction method, SQL extraction device, computer equipment and storage medium
CN111177113A (en) Data migration method and device, computer equipment and storage medium
CN112181423B (en) Project construction method and device based on configuration file, computer equipment and medium
CN112328219A (en) Service access processing method, device and system and computer equipment
US10782942B1 (en) Rapid onboarding of data from diverse data sources into standardized objects with parser and unit test generation
CN116226159A (en) Metadata blood-edge relationship analysis method, system, equipment and storage medium
CN109656947B (en) Data query method and device, computer equipment and storage medium
WO2021051583A1 (en) Method and apparatus for transmitting and collecting variable, and computer-readable storage medium
CN110795451B (en) SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium
US11580251B1 (en) Query-based database redaction
CN114116773A (en) Structured Query Language (SQL) text auditing method and device
CN112416966A (en) Ad hoc query method, apparatus, computer device and storage medium
CN112579705A (en) Metadata acquisition method and device, computer equipment and storage medium
CN115795425A (en) Permission verification method and device for data processing task
CN111309572B (en) Test analysis method and device, computer 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