SQL rewriting method, device, electronic device and storage medium

Abstract

The embodiments of the present application provide a SQL rewriting method, device, electronic device and storage medium, which relate to the field of database technology. In the present application, based on the target grammatical structure information, the corresponding candidate AST identifier is determined, and then the candidate AST corresponding to the candidate AST identifier is selected according to the candidate AST identifier and the pre-set AST set, and finally the candidate AST is rewritten according to the target parameter information to obtain the corresponding target AST, and then the target SQL corresponding to the target AST is generated. With the present application, after receiving the SQL rewriting request, based on the candidate AST identifier corresponding to the target grammatical structure information, the candidate AST matching the candidate AST identifier is selected from the pre-set AST set, thereby improving the efficiency of SQL rewriting.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to an SQL rewriting method, device, electronic device and storage medium. Background Art

[0002] Structured Query Language (SQL) is widely used in various databases to improve the work quality and efficiency of computer application systems. However, in order to meet specific business needs, it is often necessary to rewrite one SQL into another SQL.

[0003] For example, in order to rewrite SQL, the distributed database Mycat uses the Druid parser to perform lexical analysis and syntactic analysis on the initial SQL in sequence, thereby obtaining the corresponding initial abstract syntax tree (AST); then, based on specific business needs, the structure and content of the initial AST are modified to obtain the target AST, and finally, based on the target AST, the initial SQL is rewritten to generate the target SQL.

[0004] Furthermore, with the rapid growth of business needs, a large number of SQL rewriting operations are required. Therefore, in order to improve the efficiency of SQL rewriting, the distributed database Mycat directly uses SQL as the key to cache the corresponding target AST. When performing SQL rewriting operations, SQL is matched to select the target AST that meets the current business needs from the cached multiple target ASTs, thereby completing the rewriting of the initial SQL and generating the target SQL.

[0005] However, the SQL rewriting method using the above method directly uses SQL as the key to cache the corresponding target AST, which consumes a lot of time because the entire SQL needs to be matched when performing the SQL rewriting operation; and if a large number of highly similar SQLs are cached, the accuracy of SQL matching will be affected.

[0006] Therefore, adopting the above method will reduce the efficiency of SQL rewriting. Summary of the invention

[0007] The present application provides a SQL rewriting method, device, electronic device and storage medium to improve the efficiency of SQL rewriting.

[0008] In a first aspect, an embodiment of the present application provides a SQL rewriting method, the method comprising:

[0009] An SQL rewriting request sent by a terminal device is received, and target grammatical structure information and target parameter information of a target SQL are acquired from the SQL rewriting request.

[0010] Determine the candidate abstract syntax tree AST identifier corresponding to the target grammatical structure information.

[0011] Based on the candidate AST identifier, a candidate AST matching the candidate AST identifier is selected from a preset AST set.

[0012] Based on the target parameter information, the candidate AST is rewritten to obtain the corresponding target AST, and based on the SQL rewrite request, the target SQL corresponding to the target AST is generated.

[0013] In a second aspect, an embodiment of the present application further provides an SQL rewriting device, the device comprising:

[0014] The receiving module is used to receive the SQL rewriting request sent by the terminal device, and obtain the target grammatical structure information and target parameter information of the target SQL from the SQL rewriting request.

[0015] The processing module is used to determine the candidate abstract syntax tree AST identifier corresponding to the target grammatical structure information.

[0016] The matching module is used to select a candidate AST that matches the candidate AST identifier from a preset AST set based on the candidate AST identifier.

[0017] The generation module is used to rewrite the candidate AST based on the target parameter information to obtain the corresponding target AST, and generate the target SQL corresponding to the target AST based on the SQL rewrite request.

[0018] In an optional embodiment, before receiving the SQL rewrite request sent by the terminal device, the generating module is further used to:

[0019] For each original SQL, perform the following operations:

[0020] Get the original syntax structure information of a raw SQL.

[0021] Parse the original grammatical structure information, and obtain at least one historical AST node information from the corresponding parsing result.

[0022] At least one historical AST node information is aggregated to generate a corresponding original AST, and a corresponding AST identifier is added to the original AST; wherein the AST identifier is used to identify an AST having the same original grammatical structure information.

[0023] In an optional embodiment, when aggregating at least one historical AST node information to generate a corresponding original AST, the generation module is specifically used to:

[0024] Obtain the historical node attributes corresponding to at least one historical AST node information; wherein each historical node attribute includes at least: the instruction execution time and the historical node type of the corresponding historical AST node information.

[0025] Based on the obtained node attribute set to which each of the at least one historical node attributes belongs, a corresponding original AST is generated for at least one historical AST node information.

[0026] In an optional embodiment, when generating a corresponding original AST for at least one historical AST node information based on the node attribute set to which the at least one historical node attribute belongs, the generating module is specifically used to:

[0027] Determine respectively the historical node type corresponding to each of at least one historical AST node information and the node type set to which it belongs.

[0028] Based on the instruction execution time corresponding to at least one historical AST node information and the corresponding node type set, the historical node attributes of at least one historical AST node information and the node attribute set to which it belongs are determined respectively.

[0029] Based on the node attribute sets corresponding to at least one historical AST node information, a corresponding original AST is generated for at least one historical AST node information.

[0030] In an optional embodiment, in the process of determining the candidate AST identifier corresponding to the target grammatical structure information, the processing module is further used to:

[0031] If the corresponding candidate AST identifier is not obtained, the following operations are performed on the target grammatical structure information:

[0032] Parse the target grammatical structure information, and obtain at least one target AST node information from the corresponding parsing result.

[0033] Summarize at least one target AST node information and generate a corresponding candidate AST.

[0034] Based on the target parameter information, the candidate AST is rewritten to obtain the corresponding target AST, and based on the SQL rewrite request, the target SQL corresponding to the target AST is generated.

[0035] In an optional embodiment, after generating a corresponding candidate AST based on at least one target AST node information, the processing module is further used to:

[0036] Add the corresponding AST identifier to the candidate AST.

[0037] The candidate AST with the added AST identifier is saved in the preset AST collection.

[0038] In a third aspect, an electronic device is provided, comprising: a processor and a memory; the memory stores computer instructions; and the processor is configured to read the computer instructions and execute the SQL rewriting method as described in the first aspect.

[0039] In a fourth aspect, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to enable a computer to execute the SQL rewriting method as described in the first aspect.

[0040] In a fifth aspect, a computer program product is provided. When the computer program product is called by a computer, the computer executes the SQL rewriting method as described in the first aspect.

[0041] In the embodiment of the present application, the corresponding candidate AST identifier is determined based on the target grammatical structure information, and then the candidate AST corresponding to the candidate AST identifier is selected based on the candidate AST identifier and the pre-set AST set, and finally the candidate AST is rewritten based on the target parameter information to obtain the corresponding target AST, and then the target SQL corresponding to the target AST is generated. In this way, based on the candidate AST identifier, the candidate AST that matches the candidate AST identifier is selected from the pre-set AST set, avoiding the technical defects of directly using SQL as a key to cache the corresponding target AST in the traditional way, requiring matching of the entire SQL, and caching a large number of SQLs with extremely high similarity. Therefore, the SQL rewriting method of the present application is adopted to improve the efficiency of SQL rewriting. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 An example of an SQL rewriting system applicable to an embodiment of the present application is shown;

[0043] Figure 2 A schematic diagram of a method for obtaining an original AST and its AST identifier provided in an embodiment of the present application is exemplarily shown;

[0044] Figure 3 A logical judgment diagram for obtaining grammatical structure information provided by an embodiment of the present application is exemplarily shown;

[0045] Figure 4 An exemplary logic diagram of obtaining AST node information provided by an embodiment of the present application is shown;

[0046] Figure 5 The present invention provides a method based on Figure 2 Schematic diagram of the method flow chart;

[0047] Figure 6 The following is a schematic diagram of a method for generating an original AST provided in an embodiment of the present application;

[0048] Figure 7 The following is a flow chart of a method for SQL rewriting provided by an embodiment of the present application;

[0049] Figure 8 A logic diagram for generating a target SQL provided by an embodiment of the present application is exemplarily shown;

[0050] Fig. 9 The present invention provides a method based on Figure 7 Schematic diagram of the

[0051] Fig.10 The following is a schematic diagram of a processing flow for optimizing SQL rewriting performance provided by an embodiment of the present application;

[0052] Fig.11 An exemplary structural diagram of an SQL rewriting device provided in an embodiment of the present application is shown;

[0053] Fig.12 The schematic diagram of the structure of an electronic device provided in an embodiment of the present application is exemplified. DETAILED DESCRIPTION

[0054] In order to improve the efficiency of SQL rewriting, in an embodiment of the present application, based on the target syntax structure information in the SQL rewrite request, the corresponding candidate AST identifier is determined, and then the candidate AST corresponding to the candidate AST identifier is selected based on the candidate AST identifier and the pre-set AST set, and finally the candidate AST is rewritten according to the target parameter information in the SQL rewrite request to obtain the corresponding target AST, and then the target SQL corresponding to the target AST is generated.

[0055] In order to better understand the embodiments of the present application, the technical terms involved in the embodiments of the present application are first explained below.

[0056] (1) Database: is a software that stores data and can accept data storage and search requests from other applications and respond to them.

[0057] (2) Database proxy: It is a program between the database and the client, acting as the database access portal. On its basis, it can realize functions such as high availability, load balancing, read-write separation, and master-slave switching to enhance database capabilities. It is referred to as the proxy below.

[0058] (3) SQL: It is a language used by users to operate databases. Its full name is Structured Query Language. It is a statement composed of keywords, literal values, operators, comments, separators, etc.

[0059] (4) Lexical analysis: This refers to the process of dividing an SQL string into keywords, literals, operators, comments, separators, etc. through lexical rules. It is the basic unit of syntax analysis.

[0060] (5) Syntax analysis: This refers to analyzing the semantics of the entire statement based on the results of syntax analysis, such as what the filtering conditions are and what content needs to be queried.

[0061] (6) AST: The result of SQL lexical analysis and syntax analysis is AST, which is a multi-branch tree. Each non-leaf node describes the semantics of a SQL fragment.

[0062] (7) Parameterized SQL: The main structure of SQL, usually consisting of keywords, operators, comments, and separators in an SQL statement. The main structure rarely changes, while the literal value is a variable and changes frequently, so the main structure is generally called parameterized SQL.

[0063] For example, after SQL is preprocessed, the main structure will not change, and the literal value is set to the corresponding position before each execution. For the convenience of description, in this article, parameterized SQL is also called target syntax structure information, and the literal value is also called target parameter information.

[0064] (8) Cache: Similar to the function of a dictionary, some complex information is recorded in advance through an index. If the index of the information needed next time is the same, it can be directly taken out and used without recalculating the information.

[0065] (9) SQL parsing: usually refers to the two steps of lexical analysis and syntax analysis, which refers to the process of converting SQL from a string to an abstract syntax tree.

[0066] (10) SQL rewriting: This refers to modifying the structure and content of the abstract syntax tree due to business needs. For example, a distributed database needs to split SQL into different data nodes for execution. Another example is that a MySQL compatible service needs to automatically convert the MySQL syntax entered by the user into the syntax of the target database.

[0067] It should be noted that the above-mentioned naming method of technical terms is only an example, and the embodiments of the present application do not limit the naming method of the above-mentioned technical terms.

[0068] 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 application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0069] It should be noted that in the description of this application, "multiple" is understood as "at least two". "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent three situations: A exists alone, A and B exist at the same time, and B exists alone. A and B are connected, which can represent two situations: A and B are directly connected and A and B are connected through C. In addition, in the description of this application, words such as "first" and "second" are only used for the purpose of distinguishing descriptions, and cannot be understood as indicating or implying relative importance, nor can they be understood as indicating or implying order.

[0070] Figure 1 An example of an SQL rewriting system applicable to an embodiment of the present application is shown. Figure 1 As shown, the SQL rewriting system includes: an SQL parsing module 101, an AST matching module 102, an AST rewriting module 103 and an SQL generating module 104. The SQL parsing module 101 sends the target grammatical structure information of the target SQL to the AST matching module 102, and sends the target parameter information to the AST rewriting module 103; the AST matching module 102 sends the candidate AST matched based on the target grammatical structure information to the AST rewriting module 103; the AST rewriting module 103 rewrites the candidate AST based on the target parameter information, and sends the target AST obtained by rewriting to the SQL generating module 104.

[0071] The above SQL rewriting system is deployed on a server. The functions of each component module in the above SQL rewriting system are briefly described below.

[0072] The SQL parsing module 101 is used to receive an SQL rewriting request sent by a terminal device, and obtain target grammatical structure information and target parameter information of the target SQL from the SQL rewriting request, which can be specifically used for lexical analysis and grammatical analysis in SQL parsing.

[0073] The AST matching module 102 is used to select a candidate AST identifier corresponding to the target grammatical structure information according to the target grammatical structure information and the correspondence between the grammatical structure information and the candidate AST identifiers.

[0074] The AST rewriting module 103 is used to select a candidate AST that matches the candidate AST identifier from a preset AST set according to the candidate AST identifier. It should be noted that a large number of original ASTs are pre-cached in the AST set, and each original AST corresponds to a corresponding AST identifier according to its original grammatical structure information.

[0075] The SQL generation module 104 is used to rewrite the candidate AST according to the target parameter information to obtain the corresponding target AST, and generate the target SQL corresponding to the target AST based on the SQL rewriting request.

[0076] Further, based on the above system architecture, obtain each original AST and its respective AST identifier, see Figure 2 As shown, in the embodiment of the present application, an original AST and its AST identifier are obtained, and the specific steps are as follows:

[0077] S201: Obtain original grammatical structure information of an original SQL.

[0078] For details, see Figure 3 As shown, when executing step S201, the server obtains the grammatical structure information corresponding to the original SQL from the database based on the data type of the grammatical structure information and the feature extraction algorithm of the grammatical structure information.

[0079] S202: After obtaining the original grammatical structure information, parse the original grammatical structure information, and obtain at least one historical AST node information from the corresponding parsing result.

[0080] Specifically, when executing step S202, after obtaining the original grammatical structure information, the server parses the original grammatical structure information through lexical analysis and grammatical analysis in sequence, and then obtains at least one historical AST node information from the obtained parsing result.

[0081] For example, see Figure 4 As shown, the server performs lexical and grammatical analysis on the original grammatical structure information through the SQL parser to obtain the corresponding analysis result; then, according to the feature extraction algorithm of the AST node information and the data type of the AST node information, at least one historical AST node information is obtained from the analysis result. Among them, the SQL parser has at least: grammatical analysis function and lexical analysis function.

[0082] S203: After obtaining at least one historical AST node information, summarize at least one historical AST node information, generate a corresponding original AST, and add a corresponding AST identifier to the original AST.

[0083] Specifically, when executing step S203, the server generates the corresponding original AST for each historical AST node information based on the historical node attributes corresponding to each historical AST node information and the node attribute set to which each historical AST node information belongs, and adds the corresponding AST identifier. The AST identifier is used to identify the AST with the same original grammatical structure information. Figure 5 As shown, in the embodiment of the present application, at least one historical AST node information is summarized to generate the corresponding original AST, and the specific steps are as follows:

[0084] S2031: Obtain the historical node attributes corresponding to at least one historical AST node information.

[0085] Specifically, when executing step S2031, the server can first extract the various corresponding historical node attributes of each historical AST node information from the database based on the feature extraction algorithm of the historical node attributes; further, based on the data type of the historical AST node information, determine the instruction execution time and historical node type of the corresponding historical AST node information.

[0086] Exemplarily, the database contains multiple historical node attributes, wherein each historical node attribute includes: the instruction execution time and the historical node type of the corresponding historical AST node information. For ease of understanding, in the embodiment of the present application, taking 5 historical AST node information as an example, the server adopts the feature extraction algorithm of the historical node attributes, and the corresponding historical node attributes are shown in Table 1:

[0087] Table 1

[0088]

[0089] As can be seen from the above table, the server can extract features of each historical AST node information in the database according to the feature extraction algorithm of the historical node attributes, and obtain the historical node attributes of the corresponding historical AST node information. Taking the historical AST node information AST.HNM1 as an example, the server can extract features of the historical AST node information AST.HNM1 according to the feature extraction algorithm of the historical node attributes, and extract the historical node attributes of the historical AST node information AST.HNM1, that is, AST.HNS1.

[0090] Furthermore, the server obtains five historical node attributes, and can obtain the corresponding instruction execution time and historical node type from each historical node attribute according to the data type of the instruction execution time and the data type of the historical node type. Each historical node attribute and its corresponding instruction execution time and historical node type are shown in Table 2:

[0091] Table 2

[0092] History Node Properties AST.HNS1 AST.HNS2 AST.HNS3 AST.HNS4 AST.HNS5 Instruction execution time AST.T1 AST.T2 AST.T3 AST.T4 AST.T5 History Node Type Type.H.Node1 Type.H.Node2 Type.H.Node3 Type.H.Node4 Type.H.Node5

[0093] It can be seen from the above table that the server can obtain the instruction execution time and historical node type of the corresponding historical AST node information according to the data type of the instruction execution time and the data type of the historical node type. For example, taking the historical node attribute AST.HNS1 as an example, the server extracts the corresponding instruction execution time AST.T1 from the historical node attribute AST.HNS1 based on the data type of the instruction execution time; and then extracts the corresponding historical node type Type.H.Node1 from the historical node attribute AST.HNS1 based on the data type of the historical node type.

[0094] S2032: After obtaining at least one historical node attribute, based on the node attribute set to which the at least one historical node attribute belongs, generate a corresponding original AST for at least one historical AST node information.

[0095] In a possible implementation, when executing step S2032, after obtaining each historical node attribute, the server determines the node attribute set of each corresponding historical node attribute based on the mapping relationship between each historical node attribute and the node attribute set, thereby generating a corresponding original AST for at least one historical AST node information, see Figure 6 As shown in the figure, the process of generating the original AST is as follows:

[0096] S601: Determine the historical node type corresponding to at least one historical AST node information and the node type set to which it belongs.

[0097] Specifically, when executing step S601, the server determines the node type set to which each historical node type belongs according to the historical node type corresponding to each historical AST node information obtained. For example, according to the historical node type, it can be divided into three major node type sets, namely: the first node type set, the second node type set, and the third node type set.

[0098] It should be noted that each large category node type set may include: multiple middle category node type sets and multiple small category node type sets; wherein each middle category node type set may include: multiple small category node type sets, that is, there is an affiliation relationship between the above-mentioned large category node type sets, middle category node type sets and small category node type sets.

[0099] Exemplarily, still taking the five historical node types in Table 2 as an example, the server determines the node type set corresponding to each historical node type based on the division rule of the node type set. The node type set to which each historical node type belongs is shown in Table 3:

[0100] Table 3

[0101] History Node Type Type.H.Node1 Type.H.Node2 Type.H.Node3 Type.H.Node4 Type.H.Node5 Node Type Collection 1.Az 1 1.Aa 1.A 1.C

[0102] It can be seen from the above table that the server can determine the node set type to which the corresponding historical node type belongs based on the historical node type corresponding to each historical AST node information. For example, taking the historical node type Type.H.Node1 as an example, based on the historical node type Type.H.Node1 and the division rules of the node type set, the server can know that the node type set corresponding to the historical AST node information AST.HNM1 is 1.Az, that is, it belongs to the 1st major category, the Ath middle category, and the zth minor category.

[0103] S602: After determining each node type set respectively, based on the instruction execution time corresponding to at least one historical AST node information and in combination with the corresponding node type set, determine the historical node attributes of at least one historical AST node information and the node attribute set to which it belongs.

[0104] Specifically, when executing step S602, after obtaining each node type set, the server determines the node attribute sets corresponding to each historical node attribute according to the instruction execution time corresponding to each historical AST node information. The order of the instruction execution time corresponding to each historical AST node information is: the first node attribute set, the second node attribute set, and the third node attribute set.

[0105] Exemplarily, still taking the five instruction execution times in Table 2 as an example, the server determines the node attribute sets corresponding to the corresponding historical AST node information based on the instruction execution time corresponding to each historical AST node information and the corresponding node type set. The node attribute sets corresponding to each historical node attribute are shown in Table 4:

[0106] Table 4

[0107] Node Type Collection 1.Az 1 1.Aa 1.A 1.C Instruction execution time AST.T1 AST.T2 AST.T3 AST.T4 AST.T5 Node attribute collection Node.Att.C3 Node.Att.C1 Node.Att.C3 Node.Att.C2 Node.Att.C2

[0108] It can be seen from the above table that the server can determine the node attribute set to which each historical node attribute of the corresponding historical AST node information belongs based on the node type set and instruction execution time corresponding to each historical AST node information. For example, assuming that AST.T1 is "2021.12.25 09:00:45", AST.T2 is "2021.12.25 08:53:41", AST.T3 is "2021.12.25 09:05:32", AST.T4 is "2021.12.25 08:58:19", and AST.T5 is "2021.12.25 08:55:27", the server can obtain the corresponding node attribute set Node.Att.C3 based on the above specific time information, which is: Node.Att.C3, Node.Att.C1, Node.Att.C3, Node.Att.C2, Node.Att.C2.

[0109] S603: After determining each node attribute set respectively, based on the node attribute sets corresponding to each of the at least one historical AST node information, generate a corresponding original AST for the at least one historical AST node information.

[0110] Specifically, when executing step S603, the server generates the original AST corresponding to each historical AST node information based on the attribution relationship of each node attribute set and the node attribute set corresponding to each historical AST node information.

[0111] Exemplarily, still taking the 5 historical AST node information in Table 4 as an example, the server can generate the corresponding original AST based on the node attribute sets corresponding to each historical AST node information, wherein the original AST specifically includes three branches: "AST.HNM2→AST.HNM4→AST.HNM1", "AST.HNM2→AST.HNM5" and "AST.HNM2→AST.HNM4→AST.HNM3".

[0112] Further, based on the above pre-operation processing, after the server obtains each original AST and its respective AST identifier, refer to Figure 7 As shown, in the embodiment of the present application, the SQL rewriting method process, the specific steps are as follows:

[0113] S701: Receive an SQL rewriting request sent by a terminal device, and obtain target grammatical structure information and target parameter information of a target SQL from the SQL rewriting request.

[0114] Specifically, when executing step S701, the server parses the target grammatical structure information and target parameter information of the target SQL from the SQL rewrite request sent by the terminal device.

[0115] Exemplarily, the server may parse the target grammar structure information AST.TNM and target parameter information AST.TPM corresponding to the target SQL from the SQL rewriting request based on a feature extraction algorithm of the SQL rewriting request.

[0116] S702: After obtaining the target syntax structure information and target parameter information of the target SQL, determine the candidate AST identifier corresponding to the target syntax structure information.

[0117] Specifically, when executing step S702, the server selects a candidate AST identifier corresponding to the target grammatical structure information based on the target grammatical structure information and the correspondence between the grammatical structure information of the AST matching module and the candidate AST identifier.

[0118] Exemplarily, assuming that the target grammatical structure information is the same as the original grammatical structure information 1, the candidate AST identifier corresponding to the target grammatical structure information, ie, ID1, can be matched from the correspondence between the grammatical structure information and the candidate AST identifier.

[0119] Optional, see Figure 8 As shown, if the corresponding candidate AST identifier is not obtained, the target grammatical structure information is parsed through the SQL parser, and at least one target AST node information is obtained from the corresponding parsing result; then, the at least one target AST node information is summarized to generate the corresponding candidate AST; finally, based on the target parameter information, the candidate AST is rewritten to obtain the corresponding target AST, and based on the SQL rewrite request, the target SQL corresponding to the target AST is generated.

[0120] Furthermore, after generating a corresponding candidate AST based on at least one target AST node information, the server adds a corresponding AST identifier to the candidate AST, and saves or caches the candidate AST with the added AST identifier in a pre-set AST set.

[0121] S703: After determining the candidate AST identifier corresponding to the target grammatical structure information, based on the candidate AST identifier, select a candidate AST that matches the candidate AST identifier from a preset AST set.

[0122] Exemplarily, when executing step S703, the server selects a candidate AST that matches the candidate AST identifier from a preset AST set based on the candidate AST identifier corresponding to the target grammatical structure information. For example, taking the five original ASTs in Table 5 as an example, each candidate AST and its corresponding original AST identifier are shown in Table 5:

[0123] Table 5

[0124] Raw AST Cd.AST1 Cd.AST2 Cd.AST3 Cd.AST4 Cd.AST5 Original AST logo ID1 ID2 ID3 ID4 ID5

[0125] Based on the above table, assuming that the candidate AST identifier is ID3, the original AST corresponding to the original identifier ID3, that is, Cd.AST3, can be used as the candidate AST that matches the candidate AST identifier.

[0126] S704: After selecting the candidate AST, rewrite the candidate AST based on the target parameter information to obtain the corresponding target AST, and generate the target SQL corresponding to the target AST based on the SQL rewrite request.

[0127] Specifically, when executing step S704, the server modifies the content of the candidate AST based on the target parameter information to obtain the corresponding target AST, and then converts the target AST and SQL rewrite request into corresponding machine instructions through the generation module to generate the target SQL corresponding to the target AST.

[0128] Fig. 9 The present invention provides a method based on Figure 7 A logical diagram such as Fig. 9 As shown, the server can determine the corresponding candidate AST identifier based on the target grammatical structure information in the SQL request, and then select the candidate AST corresponding to the candidate AST identifier based on the candidate AST identifier and the pre-set AST set, and finally rewrite the candidate AST according to the target parameter information in the SQL request to obtain the corresponding target AST, and then generate the target SQL corresponding to the target AST.

[0129] It should be noted that, see Fig.10 As shown, the embodiment of the present application decomposes SQL into parameterized SQL and parameters, that is, target grammatical structure information and target parameter information, so that SQL with the same parameterized SQL only needs to be matched with one parameterized SQL, which reduces the time required for the matching operation and avoids caching a large number of SQLs with extremely high similarity, affecting the accuracy of SQL matching.

[0130] In the embodiment of the present application, the corresponding candidate AST identifier is determined based on the target grammatical structure information, and then the candidate AST corresponding to the candidate AST identifier is selected based on the candidate AST identifier and the pre-set AST set, and finally the candidate AST is rewritten based on the target parameter information to obtain the corresponding target AST, and then the target SQL corresponding to the target AST is generated. In this way, based on the candidate AST identifier, the candidate AST that matches the candidate AST identifier is selected from the pre-set AST set, avoiding the technical defects of directly using SQL as a key to cache the corresponding target AST in the traditional way, requiring matching of the entire SQL, and caching a large number of SQLs with extremely high similarity. Therefore, the SQL rewriting method of the present application is adopted to improve the efficiency of SQL rewriting.

[0131] Based on the same technical concept, an embodiment of the present application further provides an SQL rewriting device, which can implement the above method flow of the embodiment of the present application.

[0132] Fig.11 The following is a schematic diagram showing the structure of a SQL rewriting device provided in an embodiment of the present application. Fig.11 As shown, the SQL rewriting device includes: a receiving module 1101, a processing module 1102, a matching module 1103 and a generating module 1104, wherein:

[0133] The receiving module 1101 is used to receive an SQL rewriting request sent by a terminal device, and obtain target grammatical structure information and target parameter information of a target SQL from the SQL rewriting request.

[0134] The processing module 1102 is used to determine the candidate abstract syntax tree AST identifier corresponding to the target grammatical structure information.

[0135] The matching module 1103 is used to select a candidate AST that matches the candidate AST identifier from a preset AST set based on the candidate AST identifier.

[0136] The generation module 1104 is used to rewrite the candidate AST based on the target parameter information to obtain the corresponding target AST, and generate the target SQL corresponding to the target AST based on the SQL rewrite request.

[0137] In an optional embodiment, before receiving the SQL rewrite request sent by the terminal device, the generating module 1104 is further used to:

[0138] For each original SQL, perform the following operations:

[0139] Get the original syntax structure information of a raw SQL.

[0140] Parse the original grammatical structure information, and obtain at least one historical AST node information from the corresponding parsing result.

[0141] At least one historical AST node information is aggregated to generate a corresponding original AST, and a corresponding AST identifier is added to the original AST; wherein the AST identifier is used to identify an AST having the same original grammatical structure information.

[0142] In an optional embodiment, when aggregating at least one historical AST node information to generate a corresponding original AST, the generating module 1104 is specifically used to:

[0143] Obtain the historical node attributes corresponding to at least one historical AST node information; wherein each historical node attribute includes at least: the instruction execution time and the historical node type of the corresponding historical AST node information.

[0144] Based on the obtained node attribute set to which each of the at least one historical node attributes belongs, a corresponding original AST is generated for at least one historical AST node information.

[0145] In an optional embodiment, when generating a corresponding original AST for at least one historical AST node information based on the node attribute set to which the at least one historical node attribute belongs, the generating module 1104 is specifically used to:

[0146] Determine respectively the historical node type corresponding to each of at least one historical AST node information and the node type set to which it belongs.

[0147] Based on the instruction execution time corresponding to at least one historical AST node information and the corresponding node type set, the historical node attributes of at least one historical AST node information and the node attribute set to which it belongs are determined respectively.

[0148] Based on the node attribute sets corresponding to at least one historical AST node information, a corresponding original AST is generated for at least one historical AST node information.

[0149] In an optional embodiment, in the process of determining the candidate AST identifier corresponding to the target grammatical structure information, the processing module 1102 is further used to:

[0150] If the corresponding candidate AST identifier is not obtained, the following operations are performed on the target grammatical structure information:

[0151] Parse the target grammatical structure information, and obtain at least one target AST node information from the corresponding parsing result.

[0152] Summarize at least one target AST node information and generate a corresponding candidate AST.

[0153] Based on the target parameter information, the candidate AST is rewritten to obtain the corresponding target AST, and based on the SQL rewrite request, the target SQL corresponding to the target AST is generated.

[0154] In an optional embodiment, after generating a corresponding candidate AST based on at least one target AST node information, the processing module 1102 is further used to:

[0155] Add the corresponding AST identifier to the candidate AST.

[0156] The candidate AST with the added AST identifier is saved in the preset AST collection.

[0157] Fig.12 The following is a schematic diagram showing the structure of an electronic device provided by an embodiment of the present application. Fig.12 As shown, the electronic device may include:

[0158] At least one processor 1201, and a memory 1202 connected to the at least one processor 1201. The specific connection medium between the processor 1201 and the memory 1202 is not limited in the embodiment of the present application. Fig.12 In the example, the processor 1201 and the memory 1202 are connected via the bus 1200. The bus 1200 is Fig.12 The connections between other components are shown in bold lines, and are not intended to be limiting. The bus 1200 can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Fig.12 Only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus. Alternatively, the processor 1201 can also be called a controller, and there is no limitation on the name.

[0159] In the embodiment of the present application, the memory 1202 stores instructions that can be executed by at least one processor 1201. The at least one processor 1201 can execute a SQL rewriting method discussed above by executing the instructions stored in the memory 1202. The processor 1201 can implement Fig.11 The functions of each module in the device shown.

[0160] Among them, the processor 1201 is the control center of the device, which can use various interfaces and lines to connect the various parts of the entire control device, and monitor the device as a whole by running or executing instructions stored in the memory 1202 and calling the data stored in the memory 1202, the various functions of the device and processing data.

[0161] In one possible design, the processor 1201 may include one or more processing units, and the processor 1201 may integrate an application processor and a modem processor, wherein the application processor mainly processes an operating system, a user interface, and application programs, and the modem processor mainly processes wireless communications. It is understandable that the modem processor may not be integrated into the processor 1201. In some embodiments, the processor 1201 and the memory 1202 may be implemented on the same chip, and in some embodiments, they may also be implemented separately on separate chips.

[0162] Processor 1201 may be a general-purpose processor, such as a CPU (CPU), a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component, and may implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of the present application. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of a SQL rewriting method disclosed in the embodiments of the present application may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0163] Memory 1202, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules. Memory 1202 may include at least one type of storage medium, such as flash memory, hard disk, multimedia card, card-type memory, random access memory (Random Access Memory, RAM), static random access memory (Static Random Access Memory, SRAM), programmable read-only memory (Programmable Read Only Memory, PROM), read-only memory (Read Only Memory, ROM), electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, EEPROM), magnetic memory, disk, optical disk, etc. Memory 1202 is any other medium that can be used to carry or store a desired program code in the form of an instruction or data structure and can be accessed by a computer, but is not limited thereto. The memory 1202 in the embodiment of the present application can also be a circuit or any other device that can realize a storage function, for storing program instructions and / or data.

[0164] By designing and programming the processor 1201, the code corresponding to the SQL rewriting method described in the above embodiment can be fixed into the chip, so that the chip can execute the code when running. Figure 7The steps of an SQL rewriting method in the embodiment shown are as follows: How to design and program the processor 1201 is a technique known to those skilled in the art and will not be described in detail here.

[0165] Based on the same inventive concept, an embodiment of the present application further provides a storage medium, which stores computer instructions. When the computer instructions are executed on a computer, the computer executes an SQL rewriting method discussed above.

[0166] In some possible implementations, various aspects of an SQL rewriting method provided by the present application may also be implemented in the form of a program product, which includes program code. When the program product is run on an apparatus, the program code is used to enable the control device to execute the steps of an SQL rewriting method according to various exemplary implementations of the present application described above in this specification.

[0167] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0168] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0169] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0170] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

Claims

1. A structured query language SQL rewriting method, characterized in that: include: For each original SQL, perform the following operations: Get the original syntax structure information of an original SQL; Parsing the original grammatical structure information, and obtaining at least one historical AST node information from the corresponding parsing result; Obtain the historical node attributes corresponding to each of the at least one historical AST node information; wherein each historical node attribute includes at least: the instruction execution time and the historical node type of the corresponding historical AST node information; Based on the obtained node attribute set to which each of the at least one historical node attribute belongs, generating a corresponding original AST for the at least one historical AST node information, including: respectively determining the historical node type corresponding to each of the at least one historical AST node information and the node type set to which it belongs; Based on the instruction execution time corresponding to each of the at least one historical AST node information and in combination with the corresponding node type set, respectively determine the historical node attribute of each of the at least one historical AST node information and the node attribute set to which it belongs; Based on the node attribute sets corresponding to the at least one historical AST node information, generate a corresponding original AST for the at least one historical AST node information, and add a corresponding AST identifier to the original AST; wherein the AST identifier is used to identify an AST having the same original grammatical structure information; Receiving an SQL rewriting request sent by a terminal device, and acquiring target grammatical structure information and target parameter information of a target SQL from the SQL rewriting request; Determine a candidate abstract syntax tree AST identifier corresponding to the target grammatical structure information; Based on the candidate AST identifier, select a candidate AST that matches the candidate AST identifier from a preset AST set; Based on the target parameter information, the candidate AST is rewritten to obtain a corresponding target AST, and based on the SQL rewrite request, a target SQL corresponding to the target AST is generated.

2. The method according to claim 1, wherein in the process of determining the candidate AST identifier corresponding to the target grammatical structure information, it is characterized in that: Also includes: If the corresponding candidate AST identifier is not obtained, the following operations are performed on the target grammatical structure information: Parse the target grammatical structure information, and obtain at least one target AST node information from the corresponding parsing result; Summarize the at least one target AST node information to generate a corresponding candidate AST; Based on the target parameter information, the candidate AST is rewritten to obtain a corresponding target AST, and based on the SQL rewrite request, a target SQL corresponding to the target AST is generated.

3. The method according to claim 1, characterized in that After generating a corresponding candidate AST based on the at least one target AST node information, the method further includes: Add a corresponding AST identifier to the candidate AST; The candidate AST to which the AST identifier is added is saved in the preset AST set.

4. A SQL rewriting device, characterized in that: include: Generate a module to perform the following operations on each original SQL statement: Get the original syntax structure information of an original SQL; Parsing the original grammatical structure information, and obtaining at least one historical AST node information from the corresponding parsing result; Obtain the historical node attributes corresponding to each of the at least one historical AST node information; wherein each historical node attribute includes at least: the instruction execution time and the historical node type of the corresponding historical AST node information; Based on the obtained node attribute set to which each of the at least one historical node attribute belongs, generating a corresponding original AST for the at least one historical AST node information, including: respectively determining the historical node type corresponding to each of the at least one historical AST node information and the node type set to which it belongs; Based on the instruction execution time corresponding to each of the at least one historical AST node information and in combination with the corresponding node type set, respectively determine the historical node attribute of each of the at least one historical AST node information and the node attribute set to which it belongs; Based on the node attribute sets corresponding to the at least one historical AST node information, generate a corresponding original AST for the at least one historical AST node information, and add a corresponding AST identifier to the original AST; wherein the AST identifier is used to identify an AST having the same original grammatical structure information; A receiving module, used for receiving an SQL rewriting request sent by a terminal device, and obtaining target grammatical structure information and target parameter information of a target SQL from the SQL rewriting request; A processing module, used to determine a candidate abstract syntax tree AST identifier corresponding to the target grammatical structure information; A matching module, for selecting a candidate AST that matches the candidate AST identifier from a preset AST set based on the candidate AST identifier; A generation module is used to rewrite the candidate AST based on the target parameter information to obtain a corresponding target AST, and to generate a target SQL corresponding to the target AST based on the SQL rewrite request.

5. An electronic device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to any one of claims 1 to 3 is implemented.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 3 are implemented.

7. A computer program product, characterized in that When the computer program product is called by a computer, the computer executes the method according to any one of claims 1 to 3.

Images