A method and device for detecting similarity of SQL statements

Abstract

The present invention discloses a method and device for detecting the similarity of SQL statements, and relates to the field of database technology. A specific implementation of the method includes: obtaining target characters and keyword information of the SQL statement to be tested, and determining the statement type of the SQL statement to be tested according to the acquisition results of the target characters and keyword information; converting the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to the set expression format, and obtaining the corresponding first target SQL statement; obtaining a second target SQL statement of the historical SQL statement having the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format; and calculating the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement. This implementation can take into account the semantic differences brought by the comparison operator, improve the detection accuracy, and has a fast detection speed.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method and device for detecting similarity of SQL statements. Background Art

[0002] Database technology is widely used in various fields, and when using SQL (Structured Query Language) statements to create and import data in the database, it is necessary to ensure that there is no redundant data. Since SQL statements have a certain degree of flexibility, there may be situations where two SQL statements are executed but actually produce the same effect. For example, business personnel A executes SQL_A statement to create a piece of data, and business personnel B executes SQL_B statement to create a piece of data. Since the actual effects of SQL_A statement and SQL_B statement are the same, the same two pieces of data exist in the historical data, causing the downstream task to fail.

[0003] In order to avoid the above situation, it is necessary to perform similarity detection on SQL statements. In the prior art, distance formulas or deep learning methods are usually used to identify and detect similar texts. However, the distance formula method has low detection accuracy; the deep learning method requires labeling of a large number of training samples, which is time-consuming, has high computational complexity, and slow detection speed. Summary of the invention

[0004] In view of this, an embodiment of the present invention provides a method and device for detecting the similarity of SQL statements. The method first determines the statement type of the SQL statement to be tested, and performs format conversion on the original expression containing a comparison operator in the SQL statement to be tested to obtain a first target SQL statement, and then calculates the similarity between the first target SQL statement and a second target SQL statement of the same type of historical SQL statement. The method can take into account the semantic differences brought by the comparison operator, improve the detection accuracy, and increase the detection speed.

[0005] To achieve the above objective, according to one aspect of an embodiment of the present invention, a method for detecting similarity of SQL statements is provided.

[0006] A method for detecting similarity of SQL statements according to an embodiment of the present invention comprises: obtaining target characters and keyword information of a SQL statement to be tested, and determining the statement type of the SQL statement to be tested according to the results of obtaining the target characters and the keyword information;

[0007] According to the set expression format, the original expression containing the comparison operator in the SQL statement to be tested is converted into a new expression to obtain a corresponding first target SQL statement;

[0008] Acquire a second target SQL statement of a historical SQL statement having the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format;

[0009] The similarity between the first target SQL statement and the second target SQL statement is calculated to determine the similarity between the SQL statement to be tested and the historical SQL statement.

[0010] Optionally, the original expression includes the comparison operator, a first string located before the comparison operator, and a second string located after the comparison operator; the expression format is used to convert the original expression into a form of smaller first and larger second or larger first and smaller second according to the size of the first string and the second string.

[0011] Optionally, converting the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to the set expression format includes:

[0012] If the comparison operator included in the SQL statement to be tested is an equal sign, an unequal sign, or an inequality sign, calculating the size of the first string and the size of the second string, determining that the size of the first string is greater than the size of the second string, and swapping the order of the first string and the second string;

[0013] If the comparison operator included in the SQL statement to be tested is a greater than sign, the greater than sign is changed to a less than sign, and the order of the first character string and the second character string is swapped;

[0014] If the comparison operator included in the SQL statement to be tested is a greater than or equal to sign, the greater than or equal to sign is changed to a less than or equal to sign, and the order of the first character string and the second character string is swapped.

[0015] Optionally, before the step of obtaining the corresponding first target SQL statement, the method further includes:

[0016] Calculate the size of multiple character strings between a set first keyword and a set second keyword;

[0017] The order of the multiple character strings is changed according to the sizes of the multiple character strings.

[0018] Optionally, the calculating the similarity between the first target SQL statement and the second target SQL statement includes:

[0019] Using a local sensitive hashing algorithm, feature extraction is performed on the first target SQL statement and the second target SQL statement respectively to obtain a corresponding feature vector to be tested and a reference feature vector;

[0020] The distance between the feature vector to be tested and the reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

[0021] Optionally, the method further comprises:

[0022] Saving a reference feature vector of the second target SQL statement to a database; wherein the reference feature vector is obtained by extracting features from the second target SQL statement using a locality sensitive hashing algorithm;

[0023] The calculating the similarity between the first target SQL statement and the second target SQL statement includes:

[0024] Using the local sensitive hashing algorithm, extracting features from the first target SQL statement to obtain a corresponding feature vector to be tested;

[0025] The distance between the feature vector to be tested and the saved reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

[0026] Optionally, determining the statement type of the SQL statement to be tested according to the acquisition result of the target character and the keyword information includes:

[0027] If the target character and the set target keyword exist in the SQL statement to be tested at the same time, determining that the statement type of the SQL statement to be tested is the first type;

[0028] If the target character exists in the SQL statement to be tested but the target keyword does not exist, determining that the statement type of the SQL statement to be tested is the second type;

[0029] If the target character does not exist in the SQL statement to be tested, but the target keyword exists, determining that the statement type of the SQL statement to be tested is the third type;

[0030] If the target character and the target keyword do not exist in the SQL statement to be tested, it is determined that the statement type of the SQL statement to be tested is the fourth type.

[0031] Optionally, the method further comprises:

[0032] Acquire the target character and the keyword information of the historical SQL statement in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target character and the keyword information;

[0033] According to the expression format, the original expression containing the comparison operator in the historical SQL statement is converted into a new expression to obtain a corresponding second target SQL statement.

[0034] To achieve the above objective, according to another aspect of an embodiment of the present invention, a device for detecting similarity of SQL statements is provided.

[0035] A similarity detection device for SQL statements according to an embodiment of the present invention comprises: a classification module, which is used to obtain target characters and keyword information of a SQL statement to be tested, and to determine the statement type of the SQL statement to be tested according to the acquisition result of the target characters and the keyword information;

[0036] A conversion module, used for converting the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to a set expression format, to obtain a corresponding first target SQL statement;

[0037] An acquisition module, configured to acquire a second target SQL statement that is a historical SQL statement of the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format;

[0038] The calculation module is used to calculate the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement.

[0039] Optionally, the original expression includes the comparison operator, a first string located before the comparison operator, and a second string located after the comparison operator; the expression format is used to convert the original expression into a form of smaller first and larger second or larger first and smaller second according to the size of the first string and the second string.

[0040] Optionally, the conversion module is also used to

[0041] If the comparison operator included in the SQL statement to be tested is an equal sign, an unequal sign, or an inequality sign, calculating the size of the first string and the size of the second string, determining that the size of the first string is greater than the size of the second string, and swapping the order of the first string and the second string;

[0042] If the comparison operator included in the SQL statement to be tested is a greater than sign, the greater than sign is changed to a less than sign, and the order of the first character string and the second character string is swapped; and

[0043] If the comparison operator included in the SQL statement to be tested is a greater than or equal to sign, the greater than or equal to sign is changed to a less than or equal to sign, and the order of the first character string and the second character string is swapped.

[0044] Optionally, the device further comprises: an optimization module for

[0045] Calculating the sizes of a plurality of character strings located between a set first keyword and a set second keyword; and

[0046] The order of the multiple character strings is changed according to the sizes of the multiple character strings.

[0047] Optionally, the computing module is also used to

[0048] Using a locality sensitive hashing algorithm, respectively extracting features from the first target SQL statement and the second target SQL statement to obtain corresponding feature vectors to be tested and reference feature vectors; and

[0049] The distance between the feature vector to be tested and the reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

[0050] Optionally, the device further comprises: a storage module for

[0051] Saving a reference feature vector of the second target SQL statement to a database; wherein the reference feature vector is obtained by extracting features from the second target SQL statement using a locality sensitive hashing algorithm;

[0052] The computing module is further configured to use the local sensitive hashing algorithm to perform feature extraction on the first target SQL statement to obtain a corresponding feature vector to be tested; and

[0053] The distance between the feature vector to be tested and the saved reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

[0054] Optionally, the classification module is used to

[0055] If the target character and the set target keyword exist in the SQL statement to be tested at the same time, determining that the statement type of the SQL statement to be tested is the first type;

[0056] If the target character exists in the SQL statement to be tested but the target keyword does not exist, determining that the statement type of the SQL statement to be tested is the second type;

[0057] If the target character does not exist in the SQL statement to be tested, but the target keyword exists, determining that the statement type of the SQL statement to be tested is the third type; and

[0058] If the target character and the target keyword do not exist in the SQL statement to be tested, it is determined that the statement type of the SQL statement to be tested is the fourth type.

[0059] Optionally, the device further comprises: a preprocessing module for

[0060] Acquire the target character and the keyword information of the historical SQL statement in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target character and the keyword information; and

[0061] According to the expression format, the original expression containing the comparison operator in the historical SQL statement is converted into a new expression to obtain a corresponding second target SQL statement.

[0062] To achieve the above objective, according to another aspect of an embodiment of the present invention, an electronic device is provided.

[0063] An electronic device according to an embodiment of the present invention includes: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement a method for detecting similarity of SQL statements according to an embodiment of the present invention.

[0064] To achieve the above objective, according to another aspect of the embodiments of the present invention, a computer readable medium is provided.

[0065] A computer-readable medium according to an embodiment of the present invention stores a computer program, and when the program is executed by a processor, a method for detecting similarity of SQL statements according to an embodiment of the present invention is implemented.

[0066] To achieve the above objective, according to another aspect of an embodiment of the present invention, a computer program product is provided.

[0067] A computer program product according to an embodiment of the present invention includes a computer program. When the program is executed by a processor, a method for detecting similarity of SQL statements according to an embodiment of the present invention is implemented.

[0068] An embodiment of the above invention has the following advantages or beneficial effects: firstly, the statement type of the SQL statement to be tested is determined, and the format of the original expression containing the comparison operator in the SQL statement to be tested is converted to obtain a first target SQL statement, and then the similarity between the first target SQL statement and the second target SQL statement of the same type of historical SQL statement is calculated, which can take into account the semantic difference caused by the comparison operator, improve the detection accuracy, and increase the detection speed. By converting the original expression into a unified format, the difference caused by the comparison operator during similarity detection is avoided.

[0069] Based on the different types of comparison operators, the original expressions are processed differently to ensure that the original expressions containing different operator types can be converted into a unified format. By unifying the order of the strings between the two keywords in the SQL statement to be tested, the differences caused by different string orders during similarity detection are avoided. Through feature extraction, the SQL statement to be tested and the historical SQL statements are reduced to hash numbers, which facilitates the subsequent rapid calculation of the similarity between the SQL statement to be tested and the historical SQL statements.

[0070] The feature vectors of historical SQL statements are saved to the database to facilitate the subsequent real-time and rapid calculation of the similarity between the SQL statement to be tested and the historical SQL statements. By grouping the SQL statements to be tested, the number of subsequent comparisons with historical SQL statements can be reduced, greatly improving the calculation efficiency. According to the processing method of the SQL statement to be tested, the historical SQL statements in the historical SQL statement set are classified and formatted, so that historical SQL statements similar to the SQL statement to be tested can be obtained quickly and accurately from the historical SQL statement set.

[0071] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0072] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0073] Figure 1 It is a schematic diagram of the main steps of the method for detecting similarity of SQL statements according to the first embodiment of the present invention;

[0074] Figure 2 It is a schematic diagram of the main process of the method for detecting similarity of SQL statements according to the second embodiment of the present invention;

[0075] Figure 3 It is a schematic diagram of the main process of the method for detecting similarity of SQL statements according to the third embodiment of the present invention;

[0076] Figure 4It is a schematic diagram of the main modules of the SQL statement similarity detection device according to an embodiment of the present invention;

[0077] Figure 5 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0078] Figure 6 It is a schematic diagram of the structure of a computer system suitable for implementing an electronic device of an embodiment of the present invention. DETAILED DESCRIPTION

[0079] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0080] The acquisition, storage, use, and processing of data in the technical solution of this application comply with the relevant provisions of national laws and regulations.

[0081] It is understood that the terms "first", "second", etc. used in this application are only used to distinguish descriptions and cannot be understood as indicating or implying relative importance. For example, without departing from the scope of this application, the first target SQL statement can be referred to as the second target SQL statement, and similarly, the second target SQL statement can also be referred to as the first target SQL statement.

[0082] As described in the background technology, due to the flexibility of SQL statements, there may be two SQL statements that are executed but actually produce the same effect. For example, business personnel A uses WHERE OOA.A=STB.B when performing conditional filtering, while business personnel B uses WHERE STB.B=OOA.A, and the rest of the statements are the same.

[0083] The above two SQL statements, OOA.A=STB.B and STB.B=OOA.A, have the same meaning and are equivalent, and their actual execution effects are consistent, that is, the semantics of the two SQL statements are the same. However, from the perspective of the character strings that form the SQL statements, they are different. This is because the existence of the comparison operator (=) interferes with the judgment of the algorithm. Therefore, it is necessary to deduplicate the SQL statements that contain comparison operators and have the same semantics.

[0084] When the existing distance formula or deep learning-based methods are used to detect the similarity of SQL statements, the SQL statements are converted into SQL strings, and then the distance between the strings is calculated to determine the similarity of the SQL statements, or the word vector model is trained using the SQL string to calculate the similarity of the text statements. It is impossible to perform similarity detection on SQL statements containing comparison operators, nor can it solve the problem of SQL semantic differences caused by comparison operator symbols.

[0085] In addition, based on the distance formula, when the character length is too long and the number of samples is too large, the calculation time is very serious. Based on the deep learning method, model training is time-consuming, and the model needs to be retrained when the data changes, resulting in poor user experience. In order to solve the above problems, this embodiment provides a similarity detection method for SQL statements containing comparison operators, which is simple to implement, has low detection calculation complexity, and high detection efficiency. The following is a detailed description with reference to the accompanying drawings.

[0086] Embodiment 1

[0087] Figure 1 FIG. 1 is a schematic diagram of the main steps of the method for detecting similarity of SQL statements according to the first embodiment of the present invention. Figure 1 As shown, the similarity detection method of SQL statements in the first embodiment of the present invention mainly includes the following steps:

[0088] Step S101: Obtain target characters and keyword information of the SQL statement to be tested, and determine the statement type of the SQL statement to be tested according to the acquisition result of the target characters and the keyword information. Traverse the characters and keywords of the SQL statement to be tested, determine whether it contains the set target characters and target keywords, and determine the statement type of the SQL statement to be tested according to the judgment result.

[0089] Exemplarily, the target character may be *, %, etc., which may be set according to actual needs. The target keyword may be where, alter, create, etc., which may be set according to actual needs. In an embodiment, multiple SQL statements may be collected, the number of each keyword may be counted, and the keyword that meets the set target condition (such as being in the median position, having the largest number, etc.) may be used as the target keyword.

[0090] When determining the statement type of the SQL statement to be tested, the following method can be used: if the target character and the target keyword exist in the SQL statement to be tested at the same time, the statement type of the SQL statement to be tested is determined to be the first type; if the target character exists in the SQL statement to be tested but the target keyword does not exist, the statement type of the SQL statement to be tested is determined to be the second type; if the target character does not exist in the SQL statement to be tested but the target keyword exists, the statement type of the SQL statement to be tested is determined to be the third type; if the target character and the target keyword do not exist in the SQL statement to be tested, the statement type of the SQL statement to be tested is determined to be the fourth type.

[0091] Step S102: According to the set expression format, the original expression containing the comparison operator in the SQL statement to be tested is converted into a new expression to obtain the corresponding first target SQL statement. The original expression includes the comparison operator, a first string located before the comparison operator, and a second string located after the comparison operator. The expression format is used to convert the original expression into a form of a small first and a large second or a large first and a small second according to the size of the first string and the second string.

[0092] Among them, "small at the beginning and large at the end" means that after conversion, the size of the string before the comparison operator is larger than the size of the string after the comparison operator. "Big at the beginning and small at the end" means that after conversion, the size of the string before the comparison operator is smaller than the size of the string after the comparison operator.

[0093] Step S103: Obtain a second target SQL statement of a historical SQL statement with the same statement type as the SQL statement to be tested. Pre-store the second target SQL statement corresponding to the historical SQL statements of different statement types. The second target SQL statement is obtained by converting the historical SQL statement according to the above expression format. In this step, it is necessary to find the second target SQL statement of the historical SQL statement with the same statement type according to the statement type of the SQL statement to be tested.

[0094] Step S104: Calculate the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement. Calculate the similarity between the two target SQL statements and use the similarity as the similarity between the SQL statement to be tested and the historical SQL statement. The similarity detection is now complete.

[0095] This embodiment realizes similarity detection of SQL statements containing comparison operators by format conversion of the SQL statements to be tested containing comparison operators, while taking into account the semantic differences caused by the comparison operators to improve the detection accuracy; in addition, by grouping the SQL statements to be tested, it is only necessary to calculate the similarity of historical SQL statements of the same type, thereby improving the detection speed.

[0096] Embodiment 2

[0097] Figure 2 FIG. 1 is a schematic diagram of the main process of the method for detecting similarity of SQL statements in the second embodiment of the present invention. Figure 2 As shown, the similarity detection method of SQL statements in the second embodiment of the present invention mainly includes the following steps:

[0098] Step S201: Obtain target characters and keyword information of historical SQL statements in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target characters and keyword information. Traverse the characters and keywords of each historical SQL statement in the historical SQL statement set, determine whether it contains the set target characters and target keywords, and determine the statement type of each historical SQL statement according to the judgment result.

[0099] When determining the statement type of each historical SQL statement, the following method can be used: if the target character and the target keyword exist in the historical SQL statement at the same time, the statement type of the historical SQL statement is determined to be the first type; if the target character exists in the historical SQL statement but the target keyword does not exist, the statement type of the historical SQL statement is determined to be the second type; if the target character does not exist in the historical SQL statement but the target keyword exists, the statement type of the historical SQL statement is determined to be the third type; if the target character and the target keyword do not exist in the historical SQL statement, the statement type of the historical SQL statement is determined to be the fourth type.

[0100] Step S202: Divide the historical SQL statement set into multiple historical SQL statement subsets according to the statement type of each historical SQL statement. Since there are four statement types, there are also four historical SQL statement subsets obtained by dividing the historical SQL statement set. This operation can reduce the search controls during similar matching and improve the efficiency of similarity detection.

[0101] Exemplarily, historical SQL statements belonging to the first type can be added to the first historical SQL statement subset; historical SQL statements belonging to the second type can be added to the second historical SQL statement subset; historical SQL statements belonging to the third type can be added to the third historical SQL statement subset; historical SQL statements belonging to the fourth type can be added to the fourth historical SQL statement subset.

[0102] Step S203: According to the set expression format, the original expressions containing comparison operators in the historical SQL statements of each historical SQL statement subset are converted into new expressions to obtain the corresponding second target SQL statements. This step is used to convert the original expressions with comparison operators in the historical SQL statements into a unified format, so as to facilitate the subsequent detection of SQL statements with the same semantics and avoid errors in similarity detection caused by comparison operators.

[0103] The same semantics means that two SQL statements that are different in form have the same running effect. Two SQL statements that are different in form refer to two SQL statements with different character strings. For example, the conditional filtering statements: WHERE OOA.A = STB.B and WHERE STB.B = OOA.A are different from the perspective of the character strings that form the SQL statements, but their actual running effects are the same, so the semantics of these two SQL statements are the same.

[0104] The original expression includes the comparison operator, the first non-empty string before the comparison operator (called the first string), and the first non-empty string after the comparison operator (called the second string). There are many types of comparison operators, including the equal sign (=), the unequal sign (<>), the not strictly equal sign (!=), the greater than sign (>), the less than sign (<), the greater than or equal to sign (>=), and the less than or equal to sign (<=). Different types of comparison operators are converted differently. The following takes the conversion of the original expression into the two forms of small first and large second and large first and small second as examples to explain the conversion process of the original expression in detail.

[0105] Smaller first, larger last format: If the comparison operator in the historical SQL statement is an equal sign, an unequal sign, or an incomplete equal sign, the size of the first string and the size of the second string are calculated. If the size of the first string is larger than the size of the second string, the order of the first string and the second string is swapped; if the size of the first string is smaller than or equal to the size of the second string, no adjustment is made.

[0106] If the comparison operator in the historical SQL statement is a greater than sign, the greater than sign will be changed to a less than sign, and the order of the first string and the second string will be swapped; if the comparison operator in the historical SQL statement is a less than sign, no adjustment will be made. If the comparison operator in the historical SQL statement is a greater than or equal sign, the greater than or equal sign will be changed to a less than or equal sign, and the order of the first string and the second string will be swapped; if the comparison operator in the historical SQL statement is a less than or equal sign, no adjustment will be made.

[0107] Bigger first, smaller last format: If the comparison operator in the historical SQL statement is an equal sign, an unequal sign, or an incomplete equal sign, the size of the first string and the size of the second string are calculated. If the size of the first string is smaller than the size of the second string, the order of the first string and the second string is swapped; if the size of the first string is greater than or equal to the size of the second string, no adjustment is made.

[0108] If the comparison operator in the historical SQL statement is the less than sign, the less than sign will be changed to the greater than sign, and the order of the first string and the second string will be swapped; if the comparison operator in the historical SQL statement is the greater than sign, no adjustment will be made. If the comparison operator in the historical SQL statement is the less than or equal sign, the less than or equal sign will be changed to the greater than or equal sign, and the order of the first string and the second string will be swapped; if the comparison operator in the historical SQL statement is the greater than or equal sign, no adjustment will be made.

[0109] In an embodiment, when calculating the size of the first string and the size of the second string, the encoding of each character (such as Unicode encoding) can be obtained first, and the encoding sizes of two characters at the same position in the first string and the second string are compared bit by bit. If they are equal, the subsequent characters are compared; if they are not equal, the comparison is terminated, and the string to which the character with the smaller encoding belongs is the smaller string of the two.

[0110] Step S204: When receiving the SQL statement to be tested, the target characters and keyword information of the SQL statement to be tested are obtained. The SQL statement to be tested is the SQL statement that needs to be tested for similarity, and the statement may or may not contain a comparison operator. The processing of the SQL statement to be tested is the same as that of the historical SQL statement. Specifically, the characters and keywords of the SQL statement to be tested are traversed to determine whether it contains the set target characters and target keywords.

[0111] Step S205: Determine the statement type of the SQL statement to be tested and the historical SQL statement subset with the same statement type according to the acquisition result of the target character and keyword information. In this step, by determining the statement type of the SQL statement to be tested and the historical SQL statement subset with the same statement type as the SQL statement to be tested, the number of subsequent statement comparisons can be greatly reduced, the calculation efficiency can be greatly improved, and the detection speed can be accelerated.

[0112] If both the target character and the target keyword exist in the SQL statement to be tested, the statement type of the SQL statement to be tested is determined to be the first type; if the target character exists in the SQL statement to be tested but the target keyword does not exist, the statement type of the SQL statement to be tested is determined to be the second type; if the target character does not exist in the SQL statement to be tested but the target keyword exists, the statement type of the SQL statement to be tested is determined to be the third type; if neither the target character nor the target keyword exists in the SQL statement to be tested, the statement type of the SQL statement to be tested is determined to be the fourth type.

[0113] Step S206: According to the set expression format, the original expression containing the comparison operator in the SQL statement to be tested is converted into a new expression to obtain the corresponding first target SQL statement. This step is used to convert the original expression with the comparison operator in the SQL statement to be tested into a unified format to avoid errors in similarity detection caused by the comparison operator.

[0114] The conversion method of the original expression in the SQL statement to be tested is the same as the conversion method of the original expression in the historical SQL statement. That is, if the original expression in the historical SQL statement is converted to a form with a small first and a large second, the original expression in the SQL statement to be tested is also converted to a form with a small first and a large second. That is, if the original expression in the historical SQL statement is converted to a form with a large first and a small second, the original expression in the SQL statement to be tested is also converted to a form with a large first and a small second.

[0115] Taking the conversion of the original expression in the SQL statement to be tested into a form with a smaller first and a larger second as an example, the specific conversion process is: if the comparison operator contained in the SQL statement to be tested is an equal sign, an unequal sign, or an incomplete equality, the size of the first string and the size of the second string are calculated. If the size of the first string is larger than the size of the second string, the order of the first string and the second string is swapped; if the size of the first string is less than or equal to the size of the second string, no adjustment is made.

[0116] If the comparison operator in the SQL statement to be tested is a greater than sign, the greater than sign is changed to a less than sign, and the order of the first string and the second string is swapped; if the comparison operator in the SQL statement to be tested is a less than sign, no adjustment is made. If the comparison operator in the SQL statement to be tested is a greater than or equal sign, the greater than or equal sign is changed to a less than or equal sign, and the order of the first string and the second string is swapped; if the comparison operator in the SQL statement to be tested is a less than or equal sign, no adjustment is made.

[0117] When calculating the size of the first string and the size of the second string, the encoding of each character (such as Unicode encoding) can also be obtained first, and the encoding sizes of the two characters at the same position in the first string and the second string are compared bit by bit. If they are equal, the subsequent characters are compared; if they are not equal, the comparison is terminated, and the string to which the character with the smaller encoding belongs is the smaller string of the two.

[0118] Step S207: Calculate the similarity between the first target SQL statement and the second target SQL statement of each historical SQL statement in the determined historical SQL statement subset to determine the similarity between the SQL statement to be tested and each historical SQL statement. In step S205, a historical SQL statement subset having the same statement type as the SQL statement to be tested is determined, and the similarity between the first target SQL statement and the second target SQL statement of each historical SQL statement in the historical SQL statement subset is calculated.

[0119] When calculating the similarity between the first target SQL statement and any second target SQL statement, the local sensitive hashing algorithm (simhash algorithm) is first used to extract features from the first target SQL statement and the second target SQL statement, respectively, to obtain the corresponding feature vector to be tested and the reference feature vector. Then, the distance between the feature vector to be tested and the reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

[0120] Exemplarily, when calculating the distance between the feature vector to be tested and the reference feature vector, it can be calculated by the edit distance, cosine distance, etc. In a preferred embodiment, the edit distance can be calculated first, and then the cosine distance can be calculated, and the average of the two can be used as the final distance between the feature vector to be tested and the reference feature vector to improve the detection accuracy.

[0121] The similarity between the first target SQL statement and each second target SQL statement corresponding to the historical SQL statement subset of the same statement type is calculated in the above manner. The similarity between the first target SQL statement and each second target SQL statement is the similarity between the SQL statement to be tested and the corresponding historical SQL statement.

[0122] Step S208: According to the similarity, filter out historical SQL statements similar to the SQL statement to be tested from the historical SQL statement subset. In this step, the historical SQL statements of the historical SQL statement subset of the same statement type can be sorted according to the similarity, and the first K historical SQL statements with large similarity are selected as historical SQL statements similar to the SQL statement to be tested; or historical SQL statements with similarity greater than a set threshold can be selected as historical SQL statements similar to the SQL statement to be tested. Wherein, K is a positive integer.

[0123] In the embodiment, according to business needs, the determined similar historical SQL statements may be sorted and returned in descending order of similarity.

[0124] The number of characters in an SQL statement containing a comparison operator is relatively large, generally more than 1,000 characters. The similarity detection scheme of the second embodiment simplifies the length of SQL statements by performing operations such as data cleaning and classification without affecting the similarity detection of SQL statements, and can achieve efficient and rapid similarity detection, which is applicable to different types of SQL statements such as MySQL and Oracle. In addition, this embodiment is based on character string operations, which is simple to implement. Based on a large number of historical SQL statements, it can quickly find statements similar to the SQL statement to be tested.

[0125] Embodiment 3

[0126] Figure 3 FIG. 4 is a schematic diagram of the main process of the method for detecting similarity of SQL statements in the third embodiment of the present invention. Figure 3 As shown, the similarity detection method of SQL statements in the third embodiment of the present invention mainly includes the following steps:

[0127] Step S301: Obtain target characters and keyword information of historical SQL statements in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target characters and keyword information. The historical SQL statements in the historical SQL statement set may contain comparison operators or may not contain comparison operators. Usually, the historical SQL statements are stored in a database (such as MySQL), and the required historical SQL statements are exported from the database to obtain the historical SQL statement set. Preferably, the historical SQL statement set is constructed with historical SQL statements containing comparison operators.

[0128] After constructing the historical SQL statement set, preprocess the historical SQL statements, including data cleaning, capitalization unification, and classification. Data cleaning is to delete redundant information in historical SQL statements, such as comment information, multiple consecutive spaces, line breaks, etc., to reduce interference with similarity detection and reduce storage pressure. Capitalization unification, for example, all characters in historical SQL statements (except Chinese characters, wildcards, escape characters, etc.) are uniformly processed as lowercase.

[0129] Classifying historical SQL statements is equivalent to establishing a coarse index, which can reduce the number of comparisons between SQL statements to a certain extent. When classifying, it is necessary to traverse the characters and keywords of each historical SQL statement in the historical SQL statement set to determine whether it contains the set target characters and target keywords, and determine the statement type of each historical SQL statement based on the judgment result.

[0130] Historical SQL statements include various types of commands, such as creating a database, deleting a database, creating a table, deleting a table, querying, updating, sorting, summing, etc. The target character and target keyword can be set according to the command type and actual business needs. In the embodiment, several SQL statements can also be randomly collected, the number of keywords in these SQL statements is counted, and the keyword in the median position is selected as the target keyword.

[0131] Taking the query statement as an example, the target character can be *. In this case, it is necessary to determine whether there is a * between the select keyword and the from keyword in the historical SQL statement; the target keyword can be where. In this case, it is necessary to determine whether the historical SQL statement contains the where keyword. Specifically, it can be divided into four cases as shown in Table 1, where Y represents inclusion and N represents non-inclusion:

[0132] Table 1

[0133] Does select and from contain *? Whether to include the where keyword Y Y Y N N Y N N

[0134] Step S302: According to the statement type of each historical SQL statement, the historical SQL statement set is divided into multiple historical SQL statement subsets. The historical SQL statements are classified one by one into the historical SQL statement subsets to which they belong, and four historical SQL statement subsets are obtained, namely label_1, label_2, label_3 and label_4, thereby completing the classification of the historical SQL statement set. It should be noted that this embodiment is described by dividing into four categories, and in actual applications, the classification categories can be increased or decreased according to actual needs.

[0135] Among them, label_1 is the first historical SQL statement subset, which is used to store the historical SQL statements belonging to the first case in Table 1; label_2 is the second historical SQL statement subset, which is used to store the historical SQL statements belonging to the second case in Table 1; label_3 is the third historical SQL statement subset, which is used to store the historical SQL statements belonging to the third case in Table 1; label_4 is the fourth historical SQL statement subset, which is used to store the historical SQL statements belonging to the fourth case in Table 1.

[0136] Step S303: According to the set expression format, the original expressions containing comparison operators in the historical SQL statements of each historical SQL statement subset are converted into new expressions to obtain the corresponding second target SQL statements. This step is used to convert the original expressions with comparison operators in the historical SQL statements into a unified format, such as converting OOA.A=STB.B and STB.B=OOA.A into a unified format to avoid errors in similarity detection caused by comparison operators.

[0137] As described above, there are differences in the conversion of different types of comparison operators. When the comparison operators included in the historical SQL statement are =, <>, or!=, the conversion methods are the same. When the comparison operators included in the historical SQL statement are > or <, the conversion methods are the same. When the comparison operators included in the historical SQL statement are >= or <=, the conversion methods are the same. The following is an example for illustration.

[0138] Taking the conversion of the original expression of the historical SQL statement where A = B into the form with the smaller one in front and the larger one behind as an example, the original expression it contains is: A = B. Then split the first non-empty strings before and after the equal sign to obtain the string A and the string B. After that, calculate the encodings of each character in the string A and the string B, and compare the encoding sizes of the two characters at the same position in the string A and the string B bit by bit. If they are equal, continue to compare the subsequent characters; if they are not equal, terminate the comparison, and the string to which the character with the smaller encoding belongs is the smaller string of the two. If the size of the string A is greater than the size of the string B, then adjust A = B to B = A; if the size of the string A is equal to or the size of the string B, no adjustment is made.

[0139] Taking the conversion of the original expression of the historical SQL statement where A > B into the form with the smaller one in front and the larger one behind as an example, the original expression it contains is: A > B. Then split the first non-empty strings before and after the equal sign to obtain the string A and the string B. After that, change ">" to "<" and swap the string A and the string B, that is, adjust it to B < A. Taking the conversion of the original expression of the historical SQL statement where A >= B into the form with the smaller one in front and the larger one behind as an example, the original expression it contains is: A >= B. Then split the first non-empty strings before and after the equal sign to obtain the string A and the string B. After that, change ">=" to "<=" and swap the string A and the string B, that is, adjust it to B <= A.

[0140] It should be noted that assume the historical SQL statement is select name from table_name where name = 'Bob'. Since the two sides of "=" are not enclosed by the same quotation marks (""), it indicates that they do not have the same meaning, and no conversion is performed at this time.

[0141] In an optional embodiment, for some services, it is necessary to retain the original historical SQL statement. Therefore, a dictionary mapping can be established to save the mapping relationship between the historical SQL statement and the second target SQL statement, so as to quickly find the original historical SQL statement when performing similarity detection. In addition, this dictionary data can be stored in the Redis database for quick access and update.

[0142] In another preferred embodiment, when using some keywords, the order of the strings may be different due to writing habits, such as: select h,j,c, select j,c,h, etc. These statements are not the same from the perspective of the string, but the execution effect is the same. In order to reduce the impact on the subsequent similarity detection, for this situation, the order of the strings needs to be unified.

[0143] Specifically, the sizes of multiple strings between the set first keyword and the second keyword are calculated; and then the order of the multiple strings is swapped according to the sizes of the multiple strings. In the embodiment, the size (such as Unicode encoding value) of each string between the select keyword and the from keyword is calculated, and then the strings are sorted in order of size from small to large or from large to small.

[0144] After the above format conversion and character string sequence exchange are performed on the original expression in the historical SQL statement, the corresponding second target SQL statement can be obtained. In addition, the execution order of the format conversion and character string sequence conversion is not limited in this embodiment.

[0145] Step S304: Use the local sensitive hashing algorithm to extract features from each second target SQL statement, obtain the corresponding reference feature vector and save it in the database. Remove the spaces from each second target SQL statement, then cut the resulting string into fixed lengths (for example, cut every 15 characters), then use the hash algorithm to calculate the hash value of each character, weight and reduce the dimension of the hash value, and obtain the final reference feature vector. This process can reduce the dimension of the text statement to a hash number, which is convenient for subsequent rapid detection.

[0146] As business data accumulates, the historical SQL statement set becomes larger and larger. If the above-mentioned cleaning, classification, hashing and other processing are performed on the entire historical data each time, the efficiency of similarity detection will be affected and the real-time calculation and return requirements will not be met. Therefore, a persistent file can be generated by reference to the feature vector and saved in the database so that it can be quickly loaded during the detection task and data update is convenient. In addition, since the historical SQL statement set is divided into four categories in the embodiment, the persistent file can also be divided into four parts.

[0147] Step S305: When receiving the SQL statement to be tested, obtain the target character and keyword information of the SQL statement to be tested. Taking the SQL statement to be tested as a query statement as an example, traverse the characters and keywords of the SQL statement to be tested to determine whether there is a * between the select keyword and the from keyword, and whether it contains the where keyword.

[0148] Step S306: Determine the statement type of the SQL statement to be tested and the historical SQL statement subset with the same statement type according to the acquisition result of the target character and keyword information. If there is a * between the select keyword and the from keyword in the SQL statement to be tested, and the where keyword is included, then determine that the statement type of the SQL statement to be tested is the first type, and the historical SQL statement subset with the same statement type is label_1.

[0149] If there is a * between the select keyword and the from keyword in the SQL statement to be tested, but there is no where keyword, it is determined that the statement type of the SQL statement to be tested is the second type, and the historical SQL statement subset with the same statement type is label_2.

[0150] If there is no * between the select keyword and the from keyword in the SQL statement to be tested, but there is a where keyword, it is determined that the statement type of the SQL statement to be tested is the third type, and the historical SQL statement subset with the same statement type is label_3.

[0151] If there is no * between the select keyword and the from keyword in the SQL statement to be tested, and there is no where keyword, it is determined that the statement type of the SQL statement to be tested is the fourth type, and the historical SQL statement subset with the same statement type is label_4.

[0152] Step S307: According to the set expression format, the original expression containing the comparison operator in the SQL statement to be tested is converted into a new expression to obtain the corresponding first target SQL statement. The specific implementation of this step is shown in step S206, which will not be described here.

[0153] Step S308: Use the local sensitive hashing algorithm to extract features from the first target SQL statement to obtain the corresponding feature vector to be tested. Specifically, remove the spaces in the first target SQL statement, then cut the obtained string into fixed lengths (for example, cut every 15 characters), then use the hash algorithm to calculate the hash value of each character, weight and reduce the dimension of the hash value, and obtain the final feature vector to be tested.

[0154] Step S309: Calculate the distance between the feature vector to be tested and the reference feature vector corresponding to each historical SQL statement in the determined historical SQL statement subset, and use the distance as the similarity between the SQL statement to be tested and the corresponding historical SQL statement. The historical SQL statement subset here is the historical SQL statement subset of the same statement type as the SQL statement to be tested determined in step S306. The distance calculation between the feature vector to be tested and the reference feature vector is referred to in step S207, which will not be repeated here.

[0155] Step S310: According to the similarity, historical SQL statements similar to the SQL statement to be tested are screened out from the historical SQL statement subset and returned. The specific implementation of this step is shown in step S208, which will not be described here.

[0156] In the third embodiment, by saving the reference feature vectors corresponding to the historical SQL statements in the database, the reference feature vectors can be obtained from the database in real time when a detection task is received, thereby improving the access speed and realizing real-time similarity calculation.

[0157] Figure 4 Schematic diagram of the main modules of the SQL statement similarity detection device according to the embodiment of the present invention. Figure 4 As shown, the SQL statement similarity detection device 400 according to the embodiment of the present invention mainly includes:

[0158] The classification module 401 is used to obtain the target characters and keyword information of the SQL statement to be tested, and determine the statement type of the SQL statement to be tested according to the acquisition result of the target characters and the keyword information. The characters and keywords of the SQL statement to be tested are traversed to determine whether they contain the set target characters and target keywords, and the statement type of the SQL statement to be tested is determined according to the judgment result.

[0159] When determining the statement type of the SQL statement to be tested, the following method can be used: if the target character and the target keyword exist in the SQL statement to be tested at the same time, the statement type of the SQL statement to be tested is determined to be the first type; if the target character exists in the SQL statement to be tested but the target keyword does not exist, the statement type of the SQL statement to be tested is determined to be the second type; if the target character does not exist in the SQL statement to be tested but the target keyword exists, the statement type of the SQL statement to be tested is determined to be the third type; if the target character and the target keyword do not exist in the SQL statement to be tested, the statement type of the SQL statement to be tested is determined to be the fourth type.

[0160] The conversion module 402 is used to convert the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to the set expression format, and obtain the corresponding first target SQL statement. The original expression includes the comparison operator, the first string located before the comparison operator, and the second string located after the comparison operator. The expression format is used to convert the original expression into a form of a small first and a large second or a large first and a small second according to the size of the first string and the second string.

[0161] Among them, "small at the beginning and large at the end" means that after conversion, the size of the string before the comparison operator is larger than the size of the string after the comparison operator. "Big at the beginning and small at the end" means that after conversion, the size of the string before the comparison operator is smaller than the size of the string after the comparison operator.

[0162] Acquisition module 403 is used to acquire a second target SQL statement of a historical SQL statement having the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format. Pre-store the second target SQL statement corresponding to the historical SQL statements of different statement types. wherein the second target SQL statement is obtained by converting the historical SQL statement according to the above expression format. In this step, it is necessary to search for the second target SQL statement of the historical SQL statement having the same statement type according to the statement type of the SQL statement to be tested.

[0163] The calculation module 404 is used to calculate the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement. The similarity between the two target SQL statements is calculated and used as the similarity between the SQL statement to be tested and the historical SQL statement, and the similarity detection is completed.

[0164] In addition, the SQL statement similarity detection device 400 of the embodiment of the present invention may also include: an optimization module, a storage module and a preprocessing module ( Figure 4 The optimization module is used to calculate the size of multiple character strings between the set first keyword and the second keyword; and to change the order of the multiple character strings according to the size of the multiple character strings.

[0165] A saving module is used to save the reference feature vector of the second target SQL statement to a database; wherein the reference feature vector is obtained by extracting features from the second target SQL statement using a local sensitive hashing algorithm.

[0166] A preprocessing module is used to obtain the target character and the keyword information of the historical SQL statement in the historical SQL statement set, determine the statement type of the historical SQL statement according to the acquisition results of the target character and the keyword information; and convert the original expression containing the comparison operator in the historical SQL statement into a new expression according to the expression format to obtain the corresponding second target SQL statement.

[0167] From the above description, it can be seen that the statement type of the SQL statement to be tested is first determined, and the format of the original expression containing the comparison operator in the SQL statement to be tested is converted to obtain the first target SQL statement, and then the similarity between the first target SQL statement and the second target SQL statement of the same type of historical SQL statement is calculated, which can take into account the semantic differences brought by the comparison operator, improve the detection accuracy, and increase the detection speed.

[0168] Figure 5 An exemplary system architecture 500 is shown to which the method for detecting similarity of SQL statements or the device for detecting similarity of SQL statements according to the embodiments of the present invention can be applied.

[0169] like Figure 5 As shown, system architecture 500 may include terminal devices 501, 502, 503, a network 504 and a server 505. Network 504 is used to provide a medium for communication links between terminal devices 501, 502, 503 and server 505. Network 504 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0170] The user can use the terminal devices 501, 502, 503 to interact with the server 505 through the network 504 to receive or send messages, etc. The server 505 can be a server that provides various services, such as a backend management server that supports shopping websites browsed by the user using the terminal devices 501, 502, 503. The backend management server can analyze and process the received data such as product information query requests, and feed back the processing results (such as target push information, product information) to the terminal device.

[0171] It should be noted that the SQL statement similarity detection method provided in the embodiment of the present invention is generally executed by the server 505 , and accordingly, the SQL statement similarity detection device is generally disposed in the server 505 .

[0172] It should be understood that Figure 5 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to the implementation requirements.

[0173] According to an embodiment of the present invention, the present invention also provides an electronic device, a computer-readable medium and a computer program product.

[0174] The electronic device of the present invention comprises: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement a method for detecting similarity of SQL statements according to an embodiment of the present invention.

[0175] The computer-readable medium of the present invention stores a computer program, and when the program is executed by a processor, a method for detecting similarity of SQL statements according to an embodiment of the present invention is implemented.

[0176] The computer program product of the present invention comprises a computer program, and when the program is executed by a processor, a method for detecting similarity of SQL statements according to an embodiment of the present invention is implemented.

[0177] Reference below Figure 6 , which shows a schematic diagram of the structure of a computer system 600 of an electronic device suitable for implementing an embodiment of the present invention. Figure 6 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0178] like Figure 6 As shown, the computer system 600 includes a central processing unit (CPU) 601, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 602 or a program loaded from a storage part 608 into a random access memory (RAM) 603. In the RAM 603, various programs and data required for the operation of the system 600 are also stored. The CPU 601, the ROM 602, and the RAM 603 are connected to each other via a bus 604. An input / output (I / O) interface 605 is also connected to the bus 604.

[0179] The following components are connected to the I / O interface 605: an input section 606 including a keyboard, a mouse, etc.; an output section 607 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card such as a LAN card, a modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the I / O interface 605 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed, so that a computer program read therefrom is installed into the storage section 608 as needed.

[0180] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 609, and / or installed from the removable medium 611. When the computer program is executed by the central processing unit (CPU) 601, the above-mentioned functions defined in the system of the present invention are executed.

[0181] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0182] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor, for example, they may be described as: a processor including a classification module, a conversion module, an acquisition module and a calculation module. The names of these modules do not constitute a limitation on the modules themselves in some cases. For example, the classification module may also be described as "a module for acquiring target characters and keyword information of the SQL statement to be tested, and determining the statement type of the SQL statement to be tested according to the acquisition results of the target characters and the keyword information".

[0183] As another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently and not be assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device includes: obtaining target characters and keyword information of the SQL statement to be tested, and determining the statement type of the SQL statement to be tested according to the acquisition results of the target characters and the keyword information; converting the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to the set expression format, and obtaining the corresponding first target SQL statement; obtaining a second target SQL statement of the historical SQL statement having the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format; and calculating the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement.

[0184] According to the technical solution of the embodiment of the present invention, the statement type of the SQL statement to be tested is first determined, and the format of the original expression containing the comparison operator in the SQL statement to be tested is converted to obtain a first target SQL statement, and then the similarity between the first target SQL statement and the second target SQL statement of the same type of historical SQL statement is calculated. This can take into account the semantic differences brought by the comparison operator, improve the detection accuracy, and increase the detection speed.

[0185] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for detecting similarity of SQL statements, characterized in that: include: Acquire target characters and keyword information of the SQL statement to be tested, and determine the statement type of the SQL statement to be tested according to the acquisition result of the target characters and the keyword information; According to the set expression format, the original expression containing the comparison operator in the SQL statement to be tested is converted into a new expression, including: if the comparison operator contained in the SQL statement to be tested is an equal sign, an unequal sign or an incomplete equal sign, then the size of the first string and the size of the second string are calculated, and the size of the first string is determined to be greater than the size of the second string, and the order of the first string and the second string is swapped; if the comparison operator contained in the SQL statement to be tested is a greater than sign, then the greater than sign is changed to a less than sign, and the order of the first string and the second string is swapped; if the comparison operator contained in the SQL statement to be tested is a greater than or equal sign, then the greater than or equal sign is changed to a less than or equal sign, and the order of the first string and the second string is swapped; wherein the original expression includes the comparison operator, the first string located before the comparison operator, and the second string located after the comparison operator; the expression format is used to convert the original expression into a form of a smaller first and a larger second or a larger first and a smaller second according to the size of the first string and the second string; Get the corresponding first target SQL statement; Acquire a second target SQL statement of a historical SQL statement having the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format; Calculating the similarity between the first target SQL statement and the second target SQL statement to determine the similarity between the SQL statement to be tested and the historical SQL statement, including: using a local sensitive hashing algorithm to extract features from the first target SQL statement and the second target SQL statement, respectively, to obtain corresponding feature vectors to be tested and reference feature vectors; calculating the distance between the feature vector to be tested and the reference feature vector, and using the distance as the similarity between the first target SQL statement and the second target SQL statement.

2. The method according to claim 1, characterized in that: Before the step of obtaining the corresponding first target SQL statement, the method further includes: Calculate the size of multiple character strings between a set first keyword and a set second keyword; The order of the multiple character strings is changed according to the sizes of the multiple character strings.

3. The method according to claim 1, characterized in that The method further comprises: Saving a reference feature vector of the second target SQL statement to a database; wherein the reference feature vector is obtained by extracting features from the second target SQL statement using a locality sensitive hashing algorithm; The calculating the similarity between the first target SQL statement and the second target SQL statement includes: Using the local sensitive hashing algorithm, extracting features from the first target SQL statement to obtain a corresponding feature vector to be tested; The distance between the feature vector to be tested and the saved reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

4. The method according to claim 1, characterized in that: The step of determining the statement type of the SQL statement to be tested according to the acquisition result of the target character and the keyword information includes: If the target character and the set target keyword exist in the SQL statement to be tested at the same time, determining that the statement type of the SQL statement to be tested is the first type; If the target character exists in the SQL statement to be tested but the target keyword does not exist, determining that the statement type of the SQL statement to be tested is the second type; If the target character does not exist in the SQL statement to be tested, but the target keyword exists, determining that the statement type of the SQL statement to be tested is the third type; If the target character and the target keyword do not exist in the SQL statement to be tested, it is determined that the statement type of the SQL statement to be tested is the fourth type.

5. The method according to any one of claims 1 to 4, characterized in that: The method further comprises: Acquire the target character and the keyword information of the historical SQL statement in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target character and the keyword information; According to the expression format, the original expression containing the comparison operator in the historical SQL statement is converted into a new expression to obtain a corresponding second target SQL statement.

6. A similarity detection device for SQL statements, characterized in that: include: A classification module, used to obtain target characters and keyword information of the SQL statement to be tested, and determine the statement type of the SQL statement to be tested according to the acquisition results of the target characters and the keyword information; A conversion module, used for converting the original expression containing the comparison operator in the SQL statement to be tested into a new expression according to a set expression format, to obtain a corresponding first target SQL statement; The conversion module is further configured to, if the comparison operator included in the SQL statement to be tested is an equal sign, an unequal sign, or an inequality sign, calculate the size of the first string and the size of the second string, determine that the size of the first string is greater than the size of the second string, and swap the order of the first string and the second string; If the comparison operator included in the SQL statement to be tested is a greater than sign, the greater than sign is changed to a less than sign, and the order of the first character string and the second character string is swapped; And if the comparison operator included in the SQL statement to be tested is a greater than or equal sign, the greater than or equal sign is changed to a less than or equal sign, and the order of the first character string and the second character string is swapped; wherein the original expression includes the comparison operator, the first character string located before the comparison operator, and the second character string located after the comparison operator; the expression format is used to convert the original expression into a form of a smaller first character string and a larger second character string or a larger first character string and a smaller second character string according to the size of the first character string and the second character string; An acquisition module, configured to acquire a second target SQL statement that is a historical SQL statement of the same statement type as the SQL statement to be tested; wherein the second target SQL statement is obtained by converting the historical SQL statement according to the expression format; A calculation module, used for calculating the similarity between the first target SQL statement and the second target SQL statement, so as to determine the similarity between the SQL statement to be tested and the historical SQL statement; The calculation module is also used to use a local sensitive hashing algorithm to extract features from the first target SQL statement and the second target SQL statement, respectively, to obtain corresponding feature vectors to be tested and reference feature vectors; and to calculate the distance between the feature vectors to be tested and the reference feature vectors, and use the distance as the similarity between the first target SQL statement and the second target SQL statement.

7. The device according to claim 6, characterized in that The device also includes: an optimization module for Calculating the sizes of a plurality of character strings located between a set first keyword and a set second keyword; and The order of the multiple character strings is changed according to the sizes of the multiple character strings.

8. The device according to claim 6, characterized in that The device also includes a storage module for Saving a reference feature vector of the second target SQL statement to a database; wherein the reference feature vector is obtained by extracting features from the second target SQL statement using a locality sensitive hashing algorithm; The computing module is further configured to use the local sensitive hashing algorithm to perform feature extraction on the first target SQL statement to obtain a corresponding feature vector to be tested; and The distance between the feature vector to be tested and the saved reference feature vector is calculated, and the distance is used as the similarity between the first target SQL statement and the second target SQL statement.

9. The device according to claim 6, characterized in that The classification module is used to If the target character and the set target keyword exist in the SQL statement to be tested at the same time, determining that the statement type of the SQL statement to be tested is the first type; If the target character exists in the SQL statement to be tested but the target keyword does not exist, determining that the statement type of the SQL statement to be tested is the second type; If the target character does not exist in the SQL statement to be tested, but the target keyword exists, determining that the statement type of the SQL statement to be tested is the third type; as well as If the target character and the target keyword do not exist in the SQL statement to be tested, it is determined that the statement type of the SQL statement to be tested is the fourth type.

10. The device according to any one of claims 6 to 9, characterized in that The device also includes a preprocessing module for Acquire the target character and the keyword information of the historical SQL statement in the historical SQL statement set, and determine the statement type of the historical SQL statement according to the acquisition result of the target character and the keyword information; and According to the expression format, the original expression containing the comparison operator in the historical SQL statement is converted into a new expression to obtain a corresponding second target SQL statement.

11. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 5.

12. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

13. A computer program product, comprising a computer program, characterized in that When the program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images