SQL statement detection method and device, equipment and medium

Abstract

The invention relates to the technical field of database management, and discloses an SQL (Structured Query Language) statement detection method, device and equipment and a medium, the method comprises the following steps: obtaining a target SQL statement, and analyzing the target SQL statement to obtain statement fields corresponding to keywords in the target SQL statement; sorting and splicing statement contents in the statement fields according to a preset sorting dictionary to obtain field SQL (Structured Query Language) statements; updating sub-library and sub-table names in the field SQL statements into a target format to obtain updated SQL statements corresponding to the field SQL statements, and performing statement de-duplication on the updated SQL statements to obtain a de-duplication statement set; and performing similarity calculation on the de-duplication SQL statements in the traversed de-duplication statement set to obtain a statement similarity value, and determining a statement detection result according to the statement similarity value. According to the method and the device, the target SQL statement is detected, so that the recognition accuracy of the SQL statement is improved, compatible processing of sub-libraries and sub-tables is realized, and the detection efficiency of the SQL statement is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a SQL statement detection method, device, equipment and medium. Background Art

[0002] Structured Query Language (SQL) is a special-purpose programming language used to access data and to query, update, and manage relational database systems.

[0003] At present, in scenarios such as system monitoring and slow queries, in order to improve the efficiency of troubleshooting, string comparison, grouping, and scoring are often used to detect the similarity between SQL statements. However, when performing similarity detection on SQL statements, the similarity between every two SQL statements in all SQL statements is usually calculated first, and then similar SQL statements are filtered out based on the obtained similarities. However, these methods are inefficient when processing SQL statements with different values ​​but the same structure, and it is difficult to accurately identify SQL statements in sharded libraries and tables. Summary of the invention

[0004] The embodiments of the present invention provide a SQL statement detection method, device, equipment and medium to solve the problem in the prior art that similarity detection operation is complicated and SQL statements in sub-libraries and sub-tables cannot be accurately identified and processed.

[0005] A SQL statement detection method, comprising: Obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword; Sorting and concatenating all the statement fields using a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements; Update the sub-library and sub-table names in the field SQL statements to the target format, obtain update SQL statements corresponding to each field SQL statement, and perform statement deduplication on all the update SQL statements to obtain a deduplication statement set; The deduplication statement set is traversed to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, to obtain statement similarity values ​​corresponding to each of the deduplication SQL statements, and to determine statement detection results according to the statement similarity values.

[0006] A SQL statement detection device, comprising: A statement parsing module, used to obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword; A sorting and splicing module, used to sort and splice all the statement fields through a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements; A statement deduplication module is used to update the sub-library and sub-table names in the field SQL statements to the target format, obtain the update SQL statements corresponding to each field SQL statement, and perform statement deduplication on all the update SQL statements to obtain a deduplication statement set; The similarity calculation module is used to traverse the deduplication statement set to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, obtain statement similarity values ​​corresponding to each deduplication SQL statement, and determine statement detection results based on the statement similarity values.

[0007] A computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor is used to execute the above-mentioned SQL statement detection method.

[0008] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned SQL statement detection method is implemented.

[0009] The above-mentioned SQL statement detection method, device, equipment and medium, the SQL statement detection method of the present invention includes: obtaining a target SQL statement, parsing the target SQL statement, obtaining at least one keyword and a statement field corresponding to each keyword; sorting and splicing all the statement fields through a preset sorting dictionary to obtain a field SQL statement corresponding to each of the target SQL statements; updating the sub-library and sub-table names in the field SQL statement to a target format, obtaining an update SQL statement corresponding to each of the field SQL statements, and deduplicating all the update SQL statements to obtain a deduplicated statement set; traversing the deduplicated statement set to perform similarity calculation on the deduplicated SQL statements in the traversed deduplicated statement set to obtain a statement similarity value corresponding to each of the deduplicated SQL statements, and determining a statement detection result according to the statement similarity value.

[0010] The present invention achieves the division of target SQL statements and the acquisition of statement fields corresponding to each keyword by parsing the acquired target SQL statements. All statement fields are sorted and spliced ​​by a preset sorting dictionary, thereby achieving the reordering of target SQL statements. By updating the sub-library and sub-table names to the target format, the acquisition of updated SQL statements and the compatible processing of sub-libraries and sub-tables are achieved, thereby achieving the deduplication statement set. By performing similarity calculation on the traversed deduplication SQL statements, the calculation of statement similarity values ​​is achieved, thereby achieving the determination of statement detection results, thereby improving the accuracy of SQL statement similarity recognition and the efficiency of SQL statement similarity detection. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0012] Figure 1 is a flow chart of a SQL statement detection method in one embodiment of the present invention; Figure 2 is a flow chart of step S10 of the SQL statement detection method in one embodiment of the present invention; Figure 3 is a flow chart of step S20 of the SQL statement detection method in one embodiment of the present invention; Figure 4 is a flow chart of step S30 of the SQL statement detection method in one embodiment of the present invention; Figure 5 is a flowchart of step S30 of the SQL statement detection method in another embodiment of the present invention; Figure 6 is a flow chart of step S40 of the SQL statement detection method in one embodiment of the present invention; Figure 7 is a flowchart of step S40 of the SQL statement detection method in another embodiment of the present invention; Figure 8 It is a principle block diagram of a SQL statement detection device in one embodiment of the present invention; Fig. 9 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

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

[0014] In one embodiment, if Figure 1 and Figure 3 As shown, a SQL statement detection method is provided, comprising the following steps: S10: Obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword.

[0015] Understandably, the target SQL statement refers to the SQL statement after removing comments and modifying the format. SQL (Structured Query Language) statement refers to the structured query language used to access data and query, update and manage relational database systems. Keywords refer to the content corresponding to various clauses, such as SELECT, FROM, WHERE and IN. Statement fields refer to the specific content in the clause, for example, a, b, c in FROM a, b, c are statement fields.

[0016] Specifically, at least one target SQL statement is obtained, and then, all target SQL statements are parsed, that is, sqlparse is used to decompose the target SQL statement into multiple parts, that is, it is achieved by parsing the grammatical structure of the target SQL statement. sqlparse can parse the target SQL statement into a tree structure composed of multiple components (tokens), each component represents an element in the target SQL statement, such as keywords, identifiers, operators, etc., so as to obtain keywords in the target SQL statement, such as SELECT, FROM, WHERE, etc. Then, sqlparse is used to extract the content corresponding to the keywords, that is, traverse the parsed keywords, determine the location of the keywords, and extract the content after the keywords to obtain the statement fields corresponding to each keyword.

[0017] S20: sorting and concatenating all the statement fields using a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements.

[0018] Understandably, field SQL statements refer to statement fields in the same target SQL statement that are sorted in lexicographical order and concatenated. A preset sorting dictionary refers to a dictionary (or similar data structure) that has been defined before data processing, which is used to specify specific sorting rules or orders, so that the subsequent sorting of statement fields or other elements can be carried out according to these preset rules.

[0019] Specifically, a preset sorting dictionary is obtained, and all statement fields are sorted and spliced ​​by the preset sorting dictionary, that is, all statement fields corresponding to the same keyword are obtained, and then, the characters in all statement fields are identified and compared by the preset sorting dictionary, so as to be sorted in character order, wherein, when the first character is the same, the first character is compared until the sorting is completed. It is understandable that the query content is unique and the same statement field will not appear. After sorting the statement fields corresponding to all keywords, all keywords corresponding to the same target SQL statement are spliced ​​to obtain the field SQL statements corresponding to each target SQL statement.

[0020] S30: updating the sub-library and sub-table names in the field SQL statements to the target format, obtaining update SQL statements corresponding to the field SQL statements, and performing statement deduplication on all the update SQL statements to obtain a deduplication statement set.

[0021] Understandably, the sub-library and sub-table names refer to the sub-library and sub-table names queried in the field SQL statement. The target format refers to the conversion format of the date and number in the sub-library and sub-table names. The update SQL statement refers to the SQL statement obtained after the sub-library and sub-table names are updated. The deduplication statement set refers to the set set, which is used to store the update SQL statement.

[0022] Specifically, obtain the sub-library and sub-table names corresponding to the field SQL statements, and identify the sub-library and sub-table names queried in the field SQL statements through parsing. Obtain the regular expressions corresponding to the sub-library and sub-table names according to the types of the sub-library and sub-table names, convert the sub-library and sub-table names corresponding to their types into the target format through the regular expressions, and determine the converted field SQL statements as update SQL statements, so that the update SQL statements corresponding to the field SQL statements can be obtained. Obtain the first set collection, and store all update SQL statements in the first set collection, wherein, when storing, the first set collection will match the update SQL statements with the objects in the collection to determine that there are no identical statements in the collection, so that all update SQL statements are stored in the first set collection in sequence, and the first set collection storing the update SQL statements is determined as the deduplication statement collection.

[0023] S40: traversing the deduplication statement set to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, obtaining statement similarity values ​​corresponding to each of the deduplication SQL statements, and determining statement detection results according to the statement similarity values.

[0024] It can be understood that the deduplicated SQL statement refers to the SQL statement in the deduplicated statement set, which is unique in the deduplicated statement set. The statement similarity value is used to characterize the similarity between two statements. The statement detection result refers to the division result of similar and dissimilar SQL statements.

[0025] Specifically, the deduplication statement set is traversed, and two deduplication SQL statements are obtained from the deduplication statement set. Then, a preset similarity algorithm is obtained, and the similarity between the deduplication SQL statements in the traversed deduplication statement set is calculated by the similarity algorithm, so as to obtain the statement similarity value. In this way, the deduplication statement set is traversed again to obtain new deduplication SQL statements, and the statement similarity value is calculated until the statement similarity value corresponding to each deduplication SQL statement is obtained. Then, the deduplication SQL statements are divided into a similar statement set and a non-similar statement set by a set threshold, and the two sets are determined as statement detection results.

[0026] In the SQL statement detection method of the present invention, by parsing the obtained target SQL statement, the target SQL statement is divided, and the statement field corresponding to each keyword is obtained. All statement fields are sorted and spliced ​​by a preset sorting dictionary, and the target SQL statement is reordered. By updating the sub-library and sub-table names to the target format, the updated SQL statement is obtained, and the compatible processing of the sub-library and sub-table is achieved, thereby achieving a deduplication statement set. By calculating the similarity of the traversed deduplication SQL statements, the statement similarity value is calculated, thereby achieving the determination of the statement detection result, thereby improving the accuracy of SQL statement similarity recognition and the efficiency of SQL statement similarity detection.

[0027] In one embodiment, the step S10, i.e. obtaining the target SQL statement, includes: S101, obtaining a SQL statement to be detected and a statement type corresponding to the SQL statement to be detected, and detecting whether the statement type is a target statement type.

[0028] S102: When the statement type of the SQL statement to be detected is a target statement type, obtain a transformed regular expression corresponding to the target statement type.

[0029] S103, performing statement transformation on the SQL statement to be detected corresponding to the target statement type by using the transformation regular expression to obtain a target SQL statement.

[0030] S104: When the statement type of the SQL statement to be detected is not a target statement type, obtain a modified regular expression corresponding to the target statement type.

[0031] S105, modifying the SQL statement to be detected by modifying the regular expression to obtain a target SQL statement.

[0032] Understandably, the target statement type refers to the statement type including the IN clause in the SQL statement. The SQL statement to be detected refers to the SQL statement that needs to be tested for similarity.

[0033] Specifically, at least one SQL statement to be detected and the statement type corresponding to each SQL statement to be detected are obtained. First, the SQL statement to be detected is preprocessed, that is, all the characters in the SQL statement to be detected are converted to lowercase, and single-line comments and multi-line comments are removed by regular expressions, so as to obtain the initial SQL statement. Then, it is detected whether the statement type is the target statement type, that is, whether the initial SQL statement contains an IN clause. When it is detected that the initial SQL statement contains an IN clause, the statement type is determined to be the target statement type. Otherwise, it is determined that the statement type is not the target statement type. Further, when the statement type of the initial SQL statement is the target statement type, that is, the initial SQL statement contains an IN clause, the transformation regular expression corresponding to the target statement type is obtained by matching. And the initial SQL statement corresponding to the target statement type is transformed by the transformation regular expression, that is, the content in the IN clause is first converted into a preset symbol, and then the converted symbols are synthesized into one, so as to obtain the target SQL statement. Similarly, when the statement type of the initial SQL statement is not the target statement type, that is, the initial SQL statement does not contain an IN clause, the modified regular expression corresponding to the target statement type is obtained by matching, and the SQL statement to be detected is modified by modifying the regular expression, that is, the regular expression is used to convert strings, numbers, dates, etc. into preset characters, thereby obtaining the target SQL statement.

[0034] That is, in this embodiment, by preprocessing the SQL statement to be detected, the standardization of the SQL statement is achieved. By transforming the regular expression, the SQL statement to be detected corresponding to the target statement type is transformed, and the target SQL statement is obtained, ensuring the subsequent accurate calculation of the IN clause. By modifying the regular expression, the SQL statement to be detected is modified and processed, and the target SQL statement is obtained.

[0035] In one embodiment, in step S20, all the statement fields are sorted and concatenated using a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements, including: S201, extracting the sentence content in the sentence field.

[0036] S202, obtaining a preset sorting dictionary, and re-sorting all the statement contents using the preset sorting dictionary to obtain sorting SQL statements corresponding to the statement fields.

[0037] S203, concatenating all the sorted SQL statements according to the positions of the keywords in the target SQL statements to obtain field SQL statements corresponding to the target SQL statements.

[0038] Understandably, statement fields refer to fields such as SELECT, FROM, and WHERE in SQL statements. Statement content refers to the specific content in statement fields. For example, if WHERE age>30, the statement content is age>30. Sorting SQL statements refer to statements that are reordered and concatenated in lexicographic order. For example, FROM b, c, a; after sorting, FROM a, b, c. Field SQL statements refer to statements that are concatenated after sorting according to statement fields. For example, SELECT 1, 2, 3 *FROM a, b, c, etc.

[0039] Specifically, after obtaining the statement field, extract the statement content in the statement field, that is, split the statement field through sqlparse (non-validating SQL parser), and extract from the split content to obtain the statement content in each statement field. Then, call the preset sorting dictionary, and re-sort all the statement contents through the preset sorting dictionary, that is, sort all the statement contents according to the character order in the preset sorting dictionary, so as to obtain the sorting position corresponding to each statement content, and sort the statement content corresponding to the same statement field according to the sorting position, so as to obtain the sorted SQL statements corresponding to each statement field. Furthermore, all the sorted SQL statements corresponding to the same target SQL statement are concatenated according to the keyword position, that is, all the sorted SQL statements corresponding to the same target SQL statement are concatenated according to the keyword position in the target SQL statement, so as to obtain the field SQL statements corresponding to each target SQL statement.

[0040] That is, in this embodiment, the acquisition of sorted SQL statements is achieved by extracting the statement content in the statement field and re-sorting it using a preset sorting dictionary. The acquisition of field SQL statements is achieved by concatenating all sorted SQL statements corresponding to the same target SQL statement. In one embodiment, in step S30, the sub-library and sub-table names in the field SQL statements are updated to target formats, and update SQL statements corresponding to the field SQL statements are obtained. The target formats include date target formats and number target formats, including: S301, obtaining a date regular expression and a number regular expression corresponding to the sub-library and sub-table names.

[0041] S302: Convert the date of the sub-library and sub-table name in the field SQL statement into a date target format by using the date regular expression to obtain a format SQL statement.

[0042] S303: Convert the numbers in the sub-library and sub-table names into a digital target format using the digital regular expression to obtain an update SQL statement.

[0043] Understandably, a regular expression is a text pattern that uses a single string to describe and match a series of strings that conform to a certain syntactic rule. Date regular expressions refer to strings that convert dates in statements. Number regular expressions refer to strings that convert numbers in statements. Format SQL statements refer to the date format converted in SQL statements. Target formats include date target formats and number target formats. Date target formats refer to converting dates in statements into set string formats. Number target formats refer to converting numbers in statements into set string formats.

[0044] Specifically, after obtaining the sub-library and sub-table names in the field SQL statement, traverse the sub-library and sub-table names, determine the types of the sub-library and sub-table names, and then match the regular expressions by type to obtain the date regular expressions and numeric regular expressions corresponding to the sub-library and sub-table names. Among them, other regular expressions may also be included, which are not limited in this embodiment. Further, the dates in the sub-library and sub-table names are converted into a date target format by using a date regular expression, that is, the matching date part is searched for in the sub-library and sub-table names using a date regular expression, and extracted, and then the date part is converted into a preset date target format, so as to obtain a formatted SQL statement. The numbers in the sub-library and sub-table names in the formatted SQL statement are converted into a numeric target format by using a numeric regular expression, that is, the matching numeric part is searched for in the sub-library and sub-table names using a numeric regular expression, and extracted, and then the numeric part is converted into a preset numeric target format, so as to obtain an update SQL statement.

[0045] That is, in this embodiment, regular expressions are used to process the sub-library and sub-table formats, convert the formats of dates and numbers, and obtain update SQL statements, thereby achieving compatible processing of sub-libraries and sub-tables.

[0046] In one embodiment, in step S30, all update SQL statements are deduplicated to obtain a deduplicated statement set, including: S304, performing hash mapping on each of the update SQL statements through a hash function to obtain a statement hash value corresponding to each of the update SQL statements.

[0047] S305, filtering all the update SQL statements by using the statement hash values ​​to obtain deduplicated SQL statements, and storing the deduplicated SQL statements in a deduplicated statement set.

[0048] It can be understood that the hash value is calculated by a hash function, which converts an input of any length into an output value of fixed length through a specific algorithm. Deduplication of SQL statements refers to filtering out statements that are different from other SQL statements.

[0049] Specifically, after obtaining the update SQL statement, a preset hash function is obtained, and each update SQL statement is hash-mapped by the hash function, that is, when adding the update SQL statement to the first set set, the hash function first calculates the hash value of the update SQL statement, that is, the hash table maps the element to a position in the table by the hash function, thereby obtaining the statement hash value of each update SQL statement. Further, all update SQL statements are filtered by the statement hash value of each update SQL statement, that is, whether the same statement already exists in the position corresponding to the hash value (by comparing the hash value and the equals method, the hashCode and equals methods need to be rewritten for custom objects). If it does not exist, the update SQL statement is added to the position; if it exists, since the first set set does not allow repeated update SQL statements, the update SQL statement will not be added, thereby obtaining a deduplicated SQL statement. After all non-existent deduplicated SQL statements are stored in the first set set, the first set set storing the deduplicated SQL statements is determined as the deduplicated statement set. Among them, when the hash values ​​of multiple deduplication SQL statements are the same, by setting a linked list at each position in the hash table, all deduplication SQL statements with the same hash value will be added to the linked list corresponding to the same position. When adding deduplication SQL statements, in addition to comparing the hash values, it is also necessary to traverse the linked list to compare whether the deduplication SQL statements are equal, so as to ensure that duplicate deduplication SQL statements are not added to the first set.

[0050] That is, in this embodiment, the hash value of each update SQL statement is calculated through the mapping of the hash function, and the mapping of the statement hash value is realized. The update SQL statement is filtered through the statement hash value, and the update SQL statement is filtered, and the deduplication SQL statement is screened and stored, thereby realizing the acquisition of the deduplication statement set.

[0051] In one embodiment, in step S40, that is, traversing the deduplicated statement set to perform similarity calculation on the deduplicated SQL statements in the traversed deduplicated statement set to obtain statement similarity values ​​corresponding to each of the deduplicated SQL statements, including: S401, traversing the deduplication statement set to obtain a first deduplication SQL statement and a second deduplication SQL statement; the first deduplication SQL statement refers to the deduplication SQL statement traversed for the first time, and the second deduplication SQL statement refers to the deduplication SQL statement traversed for the second time; S402, obtaining a similarity calculation model, and performing similarity calculation on the first deduplicated SQL statement and the second deduplicated SQL statement through the similarity calculation model to obtain a statement similarity value; the similarity calculation refers to one minus a target ratio; the target ratio refers to the ratio of the edit distance between the first deduplicated SQL statement and the second deduplicated SQL statement to the longest statement character length between the first deduplicated SQL statement and the second deduplicated SQL statement.

[0052] It is understandable that the similarity calculation model used to calculate the similarity between sentences refers to the one obtained by training the network structure through a preset algorithm, for example, the Levenshtein algorithm, etc. The sentence similarity value refers to the value used to characterize the degree of similarity between two SQL statements. The first deduplicated SQL statement refers to the deduplicated SQL statement traversed for the first time, and the second deduplicated SQL statement refers to the deduplicated SQL statement traversed for the second time. The similarity calculation refers to one minus the target ratio. The target ratio refers to the ratio of the edit distance between the first deduplicated SQL statement and the second deduplicated SQL statement to the longest sentence character length between the first deduplicated SQL statement and the second deduplicated SQL statement.

[0053] Specifically, after obtaining the deduplication statement set, the deduplication statement set is traversed, and the first deduplication SQL statement and the second deduplication SQL statement are obtained from the deduplication statement set. The first deduplication SQL statement and the second deduplication SQL statement can be traversed twice, or the first deduplication SQL statement and the second deduplication SQL statement can be traversed once. Then, a similarity calculation model is obtained, and the obtained first deduplication SQL statement and the second deduplication SQL statement are input into the similarity calculation model. The similarity calculation model is used to calculate the similarity between the first deduplication SQL statement and the second deduplication SQL statement, that is, the similarity between the first deduplication SQL statement and the second deduplication SQL statement is calculated by the similarity calculation algorithm learned by the similarity calculation model during training, so as to obtain the statement similarity value corresponding to each deduplication SQL statement.

[0054] In another embodiment, when the similarity calculation is performed on the first deduplication SQL statement and the second deduplication SQL statement by the Levenshtein algorithm, a matrix of size (m+1) * (n+1) is obtained, where m and n are the lengths of the two deduplication SQL statements (after preprocessing, i.e., removing irrelevant characters and standardizing the format). The matrix is ​​initialized so that the rows of the matrix represent each position of the first deduplication SQL statement (including a virtual starting position), and the columns represent each position of the second deduplication SQL statement (including a virtual starting position). Then, the first row and the first column of the matrix are initialized to an increasing sequence of 0 to m and 0 to n, respectively representing the number of deletion operations required to convert the second string into an empty string when the first string is empty; and the number of deletion operations required to convert the first string into an empty string when the second string is empty. Then, a double loop is used to traverse the rest of the matrix to obtain each position in the matrix. When the current characters are the same, no operation is performed. Otherwise, the minimum value of the replacement, deletion and insertion operations is added by 1 to represent the editing cost of the current position. The last position in the matrix represents the editing distance between the two SQL statements. Finally, by subtracting the difference between the edit distance and the ratio of the longest sentence character length of the two deduplicated SQL statements, that is, first determining the character lengths of the two deduplicated SQL statements, and then comparing the character lengths to determine the deduplicated SQL statement with the longer character length of the two deduplicated SQL statements, then calculating the ratio between the edit distance and the character length of the deduplicated SQL statement with the longer character length, and finally, subtracting the ratio, the statement similarity value corresponding to each deduplicated SQL statement can be obtained.

[0055] In another embodiment, the similarity between deduplicated SQL statements is calculated by using the Jaccard similarity algorithm, that is, all key field contents are extracted from the deduplicated SQL statements respectively, and the field contents are placed in two sets, which respectively represent the field content sets of the two SQL statements. Then, the common field contents in the two sets are found, and the first quantity is calculated. All field contents in the two sets are merged, duplicates are removed, and the second quantity of field contents in the merged set is calculated. The similarity between the two SQL statements is calculated using the Jaccard similarity calculation formula, that is, the quotient of the first quantity and the second quantity, to obtain the statement similarity value corresponding to each deduplicated SQL statement.

[0056] That is, in this embodiment, the deduplication statement set is traversed to obtain the first SQL statement and the second SQL statement. The similarity calculation model is used to calculate the similarity of the first SQL statement and the second SQL statement, thereby calculating the similarity of the statements, thereby improving the accuracy of SQL statement similarity recognition and the efficiency of SQL statement similarity detection.

[0057] In one embodiment, in step S40, the sentence detection result is determined according to the sentence similarity value, and the sentence detection result includes a similar sentence set and a non-similar sentence set; including: S403: Obtain a preset similarity threshold, and compare the statement similarity value corresponding to each of the deduplicated SQL statements with the preset similarity threshold.

[0058] S404: Determine all the deduplicated SQL statements corresponding to statement similarity values ​​greater than or equal to the preset similarity threshold as a similar statement set.

[0059] S405: Determine all the deduplicated SQL statements corresponding to statement similarity values ​​less than the preset similarity threshold as a non-similar statement set.

[0060] Understandably, the preset similarity threshold refers to setting the similarity threshold in advance, for example, 0.8 or 0.9, etc., which can be set according to actual conditions. The similar statement set refers to a set of SQL statements with greater similarity. The dissimilar statement set refers to a set of SQL statements with less similarity. The statement detection result includes a similar statement set and a dissimilar statement set, wherein both sets are set sets, and the statements in the set set are unique.

[0061] Specifically, a preset similarity threshold is obtained, and the preset similarity value corresponding to each deduplication SQL statement is compared with the preset similarity threshold to obtain a threshold comparison result. When the threshold comparison result indicates that the statement similarity value is greater than or equal to the preset similarity threshold, all deduplication SQL statements corresponding to the statement similarity value greater than or equal to the preset similarity threshold are screened out, and all the screened out deduplication SQL statements are stored in a preset second set collection. When stored in the second set collection, the screened out deduplication SQL statements are matched with the objects in the second set collection to determine whether they are repeated. If they do not exist, the screened out deduplication SQL statements are stored in the second set collection, and after all the screened out deduplication SQL statements that do not exist in the second set collection are stored in the second set collection, the second set collection is determined as a similar statement set. Among them, when there are no objects in the second set collection, the screened out deduplication SQL statements can be directly stored in the second set collection.

[0062] Furthermore, when the threshold comparison result indicates that the statement similarity value is less than the preset similarity threshold, all deduplicated SQL statements corresponding to the statement similarity value less than the preset similarity threshold are screened out, and all the screened out deduplicated SQL statements are stored in a preset third set. When stored in the third set, the screened out deduplicated SQL statements are matched with the objects in the third set to determine whether they are repeated. If they are not repeated, the screened out deduplicated SQL statements are stored in the third set, and after all the screened out deduplicated SQL statements that do not exist in the third set are stored in the third set, the third set is determined as a non-similar statement set. Wherein, when there are no objects in the third set, the screened out deduplicated SQL statements can be directly stored in the third set.

[0063] That is, in this embodiment, by comparing the statement similarity value corresponding to each deduplicated SQL statement with the preset similarity threshold, the acquisition of a similar statement set and the acquisition of a non-similar statement set are achieved, thereby ensuring that there are no identical statements in the set, thereby improving the accuracy of SQL statement similarity recognition and the efficiency of SQL statement similarity detection.

[0064] It should be understood that the order of execution of the steps in the above embodiment does not necessarily mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention.

[0065] In one embodiment, a SQL statement detection device is provided, and the SQL statement detection device corresponds one-to-one to the SQL statement detection method in the above embodiment. Figure 8As shown, the SQL statement detection device includes a request interception module 10, a logic judgment module 20, an address modification module 30 and an SQL statement detection module 40. The functional modules are described in detail as follows: A statement parsing module 10 is used to obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword; A sorting and splicing module 20 is used to sort and splice all the statement fields through a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements; A statement deduplication module 30 is used to update the sub-library and sub-table names in the field SQL statements to the target format, obtain update SQL statements corresponding to each field SQL statement, and perform statement deduplication on all the update SQL statements to obtain a deduplication statement set; The similarity calculation module 40 is used to traverse the deduplication statement set to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, obtain statement similarity values ​​corresponding to each deduplication SQL statement, and determine statement detection results based on the statement similarity values.

[0066] Optionally, the statement parsing module 10 includes: A statement type detection unit, used to obtain a SQL statement to be detected and a statement type corresponding to the SQL statement to be detected, and detect whether the statement type is a target statement type; A transformation expression unit, used for obtaining a transformation regular expression corresponding to the target statement type when the statement type of the SQL statement to be detected is a target statement type; A statement transformation unit, used for performing statement transformation on the SQL statement to be detected corresponding to the target statement type by using the transformation regular expression to obtain a target SQL statement; A modification expression unit, used for obtaining a modification regular expression corresponding to the target statement type when the statement type of the SQL statement to be detected is not the target statement type; The statement modification unit is used to modify the SQL statement to be detected by modifying the regular expression to obtain a target SQL statement.

[0067] Optionally, the sorting and splicing module 20 includes: A sentence content unit, used to extract the sentence content in the sentence field; A reordering unit, used to obtain a preset sorting dictionary, and reorder all the statement contents by using the preset sorting dictionary to obtain a sorted SQL statement corresponding to each of the statement fields; The statement concatenation unit is used to concatenate all the sorted SQL statements according to the positions of the keywords in the target SQL statements to obtain field SQL statements corresponding to the target SQL statements.

[0068] Optionally, the statement deduplication module 30, the target format includes a date target format and a number target format; including: An expression acquisition unit, used to acquire a date regular expression and a number regular expression corresponding to the sub-library and sub-table names; A date target format unit, used to convert the date of the sub-library and sub-table name in the field SQL statement into a date target format by using the date regular expression to obtain a format SQL statement; The digital target format unit is used to convert the numbers in the sub-library and sub-table names into digital target formats through the digital regular expressions to obtain update SQL statements.

[0069] Optionally, the statement deduplication module 30 further includes: A hash mapping unit, used to perform hash mapping on each of the update SQL statements through a hash function to obtain a statement hash value corresponding to each of the update SQL statements; A statement filtering unit is used to perform statement filtering on all the update SQL statements through the statement hash values ​​to obtain deduplicated SQL statements, and store the deduplicated SQL statements in a deduplicated statement set.

[0070] Optionally, the similarity calculation module 40 includes: A set traversal unit, used for traversing the deduplication statement set to obtain a first deduplication SQL statement and a second deduplication SQL statement; The similarity calculation unit is used to obtain a similarity calculation model, and perform similarity calculation on the first deduplication SQL statement and the second deduplication SQL statement using the similarity calculation model to obtain a statement similarity value.

[0071] Optionally, the similarity calculation module 40 further includes: A threshold comparison unit, used to obtain a preset similarity threshold, and compare the statement similarity value corresponding to each of the deduplicated SQL statements with the preset similarity threshold; A similarity set unit, used to determine all the deduplicated SQL statements corresponding to statement similarity values ​​greater than or equal to the preset similarity threshold as a similar statement set; The non-similar set unit is used to determine all the deduplicated SQL statements corresponding to statement similarity values ​​less than the preset similarity threshold as a non-similar statement set.

[0072] This application further provides Fig. 9 A computer device is shown, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor is used to execute the SQL statement detection method.

[0073] For the specific definition of computer equipment, processor and its various units and modules, please refer to the definition of SQL statement detection method above, which will not be repeated here. Each module in the above processor can be implemented in whole or in part by software, hardware and their combination. Understandably, the processor includes a processor, a memory, a network interface and a database connected by a device bus. Each module of the processor can be embedded in or independent of the processor in hardware form, or can be stored in the memory in software form, so that the processor can call and execute the operations corresponding to the above modules. Among them, the processor is used to provide computing and control capabilities. The memory includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating device, a computer program and a database. The internal memory provides an environment for the operation of the operating device and the computer program in the non-volatile storage medium. The database is used to store the data used by the SQL statement detection method in the above embodiment. The network interface is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a SQL statement detection method is implemented.

[0074] In one embodiment, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned SQL statement detection method is implemented.

[0075] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0076] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0077] The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A SQL statement detection method, characterized in that: include: Obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword; Sorting and concatenating all the statement fields using a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements; Update the sub-library and sub-table names in the field SQL statements to the target format, obtain update SQL statements corresponding to each field SQL statement, and perform statement deduplication on all the update SQL statements to obtain a deduplication statement set; The deduplication statement set is traversed to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, to obtain statement similarity values ​​corresponding to each of the deduplication SQL statements, and to determine statement detection results according to the statement similarity values.

2. The SQL statement detection method according to claim 1, characterized in that: The obtaining of the target SQL statement includes: Obtaining a SQL statement to be detected and a statement type corresponding to the SQL statement to be detected, and detecting whether the statement type is a target statement type; When the statement type of the SQL statement to be detected is a target statement type, obtaining a transformation regular expression corresponding to the target statement type; Performing statement transformation on the SQL statement to be detected corresponding to the target statement type by using the transformation regular expression to obtain the target SQL statement; When the statement type of the SQL statement to be detected is not the target statement type, obtaining a modified regular expression corresponding to the target statement type; The SQL statement to be detected is modified by modifying the regular expression to obtain the target SQL statement.

3. The SQL statement detection method according to claim 1, characterized in that: The sentence detection result includes a similar sentence set and a non-similar sentence set; Determining the sentence detection result according to the sentence similarity value includes: Obtaining a preset similarity threshold, and comparing the statement similarity value corresponding to each of the deduplicated SQL statements with the preset similarity threshold; Determine all the deduplicated SQL statements corresponding to statement similarity values ​​greater than or equal to the preset similarity threshold as a similar statement set; All the deduplicated SQL statements corresponding to statement similarity values ​​less than the preset similarity threshold are determined as a non-similar statement set.

4. The SQL statement detection method according to claim 1, characterized in that: The traversing the deduplication statement set to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set to obtain statement similarity values ​​corresponding to each of the deduplication SQL statements includes: Traversing the deduplication statement set to obtain a first deduplication SQL statement and a second deduplication SQL statement; the first deduplication SQL statement refers to the deduplication SQL statement traversed for the first time, and the second deduplication SQL statement refers to the deduplication SQL statement traversed for the second time; Obtain a similarity calculation model, and perform similarity calculation on the first deduplicated SQL statement and the second deduplicated SQL statement using the similarity calculation model to obtain a statement similarity value; the similarity calculation refers to one minus a target ratio; the target ratio refers to the ratio of the edit distance between the first deduplicated SQL statement and the second deduplicated SQL statement to the longest statement character length between the first deduplicated SQL statement and the second deduplicated SQL statement.

5. The SQL statement detection method according to claim 1, characterized in that: The method of sorting and concatenating all the statement fields by using a preset sorting dictionary to obtain a field SQL statement corresponding to each of the target SQL statements includes: Extracting the sentence content in the sentence field; Obtaining a preset sorting dictionary, and re-sorting all the statement contents using the preset sorting dictionary to obtain sorting SQL statements corresponding to the statement fields; All the sorted SQL statements are concatenated according to the positions of the keywords in the target SQL statements to obtain field SQL statements corresponding to the target SQL statements.

6. The SQL statement detection method according to claim 1, characterized in that: The target format includes a date target format and a number target format; The updating of the sub-library and sub-table names in the field SQL statements to the target format to obtain update SQL statements corresponding to the field SQL statements includes: Obtaining a date regular expression and a number regular expression corresponding to the sub-library and sub-table names; The date of the sub-library and sub-table name in the field SQL statement is converted into a date target format by using the date regular expression to obtain a format SQL statement; The numbers of the sub-library and sub-table names in the format SQL statement are converted into a digital target format through the digital regular expression to obtain an update SQL statement.

7. The SQL statement detection method according to claim 1, characterized in that: Deduplication of all update SQL statements is performed to obtain a deduplication statement set, including: Perform hash mapping on each of the update SQL statements through a hash function to obtain a statement hash value corresponding to each of the update SQL statements; All the update SQL statements are filtered using the statement hash values ​​to obtain deduplicated SQL statements, and the deduplicated SQL statements are stored in a deduplicated statement set.

8. A SQL statement detection device, characterized in that: include: A statement parsing module, used to obtain a target SQL statement, and parse the target SQL statement to obtain at least one keyword and a statement field corresponding to each keyword; A sorting and splicing module, used to sort and splice all the statement fields through a preset sorting dictionary to obtain field SQL statements corresponding to each of the target SQL statements; A statement deduplication module is used to update the sub-library and sub-table names in the field SQL statements to the target format, obtain the update SQL statements corresponding to each field SQL statement, and perform statement deduplication on all the update SQL statements to obtain a deduplication statement set; The similarity calculation module is used to traverse the deduplication statement set to perform similarity calculation on the deduplication SQL statements in the traversed deduplication statement set, obtain statement similarity values ​​corresponding to each deduplication SQL statement, and determine statement detection results based on the statement similarity values.

9. A computer device, characterized in that: The method comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor is used to execute the SQL statement detection method according to any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the SQL statement detection method according to any one of claims 1 to 7 is implemented.

Images