Statement analysis method and system for database statements
By maintaining the statement similarity table on the client and the database server, using lexical analysis and hash value comparison, and quickly parsing similar SQL statements, the problem of excessive fine parsing and user-friendly reuse in the existing technology is solved, and rapid parsing and efficient database operations are achieved.
Patent Information
- Application Number
- CN202411869818.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2017-01-16
- Publication Date
- 2025-03-28
AI Technical Summary
When processing SQL statements, the existing database parsing method has too fine reuse, resulting in a large amount of cached data, which is unfriendly to users, making it difficult to apply to parsing in other languages.
By maintaining statement similarity tables on the client and the database server, using lexical analysis and hash value comparison, quickly finding and parsing similar statements, reducing the workload of the database server.
It realizes rapid analysis of database statements, improves database response speed and work efficiency, is suitable for various database languages, and reduces development costs.
Smart Images
Figure CN119719139A_ABST
Abstract
Description
[0001] This application is a divisional application based on the invention with application number 201710029732.7, application date January 16, 2017, applicant is China UnionPay Co., Ltd., and invention name is “Statement parsing method for database statements”. Technical Field
[0002] The present invention relates to the field of database technology, and more specifically, to a statement parsing method and system for database statements. Background Art
[0003] Statement parsing is widely used in the computer field and is one of the basic technologies for human-computer interaction. It realizes the translation of natural language to machine language. For example, XML, SQL, C and JAVA languages are all important applications of statement parsing.
[0004] Generally, statement parsing is divided into two stages: lexical analysis and grammatical analysis. Lexical analysis is used to segment a statement into a set of word sequences, while grammatical analysis is used to build a syntax tree or other internal data structures based on the word sequence. The parsing results obtained by grammatical analysis will be used for subsequent data calculations.
[0005] In the database field, a commonly accepted language is SQL, which has a set of SQL92 syntax, and various database products have their own special syntax based on this syntax standard. The basic logic of database processing is as follows: the client sends an SQL statement to the data, the server performs lexical analysis on the SQL statement, and then performs syntax parsing to convert it into an executable data structure inside the computer.
[0006] Since SQL statements are the bridge between database users and database operations, and their statements are complex and vary greatly, a large amount of data resources will be used to parse SQL statements in database processing. In order to reduce the performance overhead of SQL statement parsing, there are two common solutions in the database field: 1) reuse the results of SQL parsing for the same statement (generally the execution plan); 2) provide prepare statements, and let users replace variable data with "?" to reduce the number of statement differences. Through the above two solutions, it is possible to better avoid syntax parsing of all statements and improve the processing performance of the database server.
[0007] However, the above existing solutions have the following shortcomings: 1. The reuse of parsing is too detailed, and the parsing results can only be reused for completely identical statements, resulting in a large amount of cached data; 2. The method of using "?" to replace variable content in statements is not very user-friendly. In addition, the above existing solutions are mainly designed for the characteristics of the SQL language and are difficult to apply in other language parsing. For example, it is difficult for us to require programmers to use "?" to replace variable content in their code just to improve the parsing speed of the compiler. Summary of the invention
[0008] The object of the present invention is to provide a statement parsing method for database statements, which can be applied independently of the database language type, can quickly parse database statements, and further improve the response speed and work efficiency of the database.
[0009] To achieve the above object, the present invention provides the following technical solutions:
[0010] A first aspect of the present invention provides a statement parsing method for database statements, which is applied to a database application system, wherein the database application system includes a database server and a plurality of clients;
[0011] The method comprises: a client server processes an input database statement by using lexical analysis to obtain an input word sequence; the client server searches a client statement similarity table maintained by the client according to the input word sequence to determine whether there is an existing word sequence similar to the input word sequence in the client statement similarity table, the client similarity table includes different existing word sequences, and the existing word sequence is obtained in advance through lexical analysis of database statements; in the case of determining that there is an existing word sequence similar to the input word sequence in the client statement similarity table, the client sends a similarity statement parsing request to the database server; the database server searches a database-side statement similarity table maintained by the database server according to the similarity statement parsing request to determine parsing data corresponding to the similarity statement parsing request, the database-side statement similarity table includes a corresponding relationship between the similarity statement parsing request and the parsing data; the database server executes the database statement input to the client server based on the parsing data corresponding to the similarity statement parsing request.
[0012] In some possible embodiments, the method further includes: when it is determined that there is no existing word sequence similar to the input word sequence in the client statement similarity table, the database server parses the input word sequence, obtains corresponding parsed data, and stores the input word sequence in the client statement similarity table; the database server stores the input word sequence and the corresponding parsed data in the database-side statement similarity table to synchronously update the client-side statement similarity table and the database-side statement similarity table.
[0013] In some possible embodiments, determining whether there is an existing word sequence similar to an input word sequence in a client statement similarity table includes: the client compares the number of words and word positions of the existing word sequence with the input word sequence for each existing word sequence in the client statement similarity table, the number of words and word positions being obtained through lexical analysis; if the number of words and the word positions are the same, determining that there is an existing word sequence similar to the input word sequence in the client statement similarity table; if the number of words or the word positions are not similar, determining that there is no existing word sequence similar to the input word sequence in the client statement similarity table.
[0014] In some possible embodiments, the database-side sentence similarity table further includes a hash subtable, which records the correspondence between hash values of different existing word sequences and parsed data.
[0015] In some possible embodiments, the database server searches the database-side statement similarity table maintained by the database server according to the similarity statement parsing request to determine the parsing data corresponding to the similarity statement parsing request, including: based on the hash value corresponding to the similar existing word sequence, determining the parsing data corresponding to the similar existing word sequence as the parsing data corresponding to the similarity statement parsing request.
[0016] In some possible embodiments, a method for calculating a hash value of a word sequence includes: performing lexical analysis on a database statement to obtain a word sequence; if the word type is a value, the hash value of the word is a fixed value; if the word type is another value, the hash value of the word corresponds to the hash value of the word converted into a lowercase string; initializing the database hash value; sequentially traversing each word in the word sequence from the beginning, performing shift summation on the hash value of each word, and obtaining the hash value of the word sequence.
[0017] In some possible embodiments, the method further includes: the database server pre-assigns similarity markers to database statements, and informs the client of the similarity markers, similar statements have the same similarity markers, and dissimilar statements have different similarity markers; the client performs lexical analysis on the database statements, and locally searches for similarity markers corresponding to the database statements; if the client has a similarity marker corresponding to the database statement, the client sends the similarity marker corresponding to the database statement to the database server; the database server searches for corresponding reusable parsed data based on the similarity marker and directly applies it; if the client does not find the similarity marker corresponding to the database statement, it sends a word sequence obtained through lexical analysis to the database server; the database server parses the received word sequence and generates a new similarity marker.
[0018] In some possible embodiments, the similarity identifier is unique, and the generation rule of the similarity identifier includes: sequential accumulation, or using the memory address corresponding to the cache value in the similarity hash.
[0019] In some possible embodiments, the method further includes: generating a similarity flag when inserting a new record into the statement similarity table on the database side.
[0020] Another object of the present invention is to provide a statement parsing method for database statements, which partially delegates the statement similarity comparison work to the client to reduce the workload of the database.
[0021] In addition, the second aspect of the present invention also discloses a database application system, including: a database server, maintaining a database-side statement similarity table; and multiple clients, each maintaining a client statement similarity table; wherein the database application system utilizes the method of the first aspect to parse and execute the database statements input into the client.
[0022] The statement parsing method for database statements provided by the present invention can quickly parse database statements, which is beneficial to improving the response speed and work efficiency of the database. It has a wide range of applications and low application costs, and is also convenient for developers to carry out database development work more efficiently. BRIEF DESCRIPTION OF THE DRAWINGS
[0023] Figure 1 A schematic diagram showing a flow chart of a statement parsing method for database statements provided by a first embodiment of the present invention;
[0024] Figure 2 A schematic flow chart of a statement parsing method for database statements provided in a third embodiment of the present invention is shown. DETAILED DESCRIPTION
[0025] like Figure 1 As shown, the first embodiment of the present invention provides a statement parsing method for database statements, which includes the following steps:
[0026] Step S10: Perform lexical analysis on the database statement input into the database to obtain an input word sequence.
[0027] Step S11: Search the sentence similarity table according to the input word sequence to determine whether there is an existing word sequence similar to the input word sequence in the sentence similarity table.
[0028] The sentence similarity table records the correspondence between various different existing word sequences and parsing data, the existing word sequences are obtained by performing lexical analysis on database sentences, and the parsing data is used by the database to execute database sentences.
[0029] Specifically, for example, take the following three SQL statements as examples:
[0030] 1:select*from tbl1 where a=123;
[0031] 2:select*from tbl1 where a=1;
[0032] 3:SELECT*FROM tbl1 WHERE A=3;
[0033] When performing lexical analysis to generate word sequences, words are generally divided into: keywords, variable names, and values, and are not case-sensitive. For example, statement 1 will be parsed into a word sequence similar to the following [select,*,from,tbl1,where,a,=,123], where [select,*,from,where,=] is a keyword, [tbl1,a] is a variable name, and
[123] is a value. It can be found that statements 1-3 have similar lexical sequences.
[0034] As a preferred implementation, the sentence similarity table further includes a hash subtable, which records the corresponding relationship between hash values of various different existing word sequences and parsed data.
[0035] Among them, the hash value of the existing word sequence can be calculated based on the following method: based on the position and hash value of each word obtained by lexical analysis of the existing word sequence, the hash value of the existing word sequence is calculated; that is, the hash value of the existing word sequence can be a value obtained by weighted combination calculation.
[0036] Specifically, continuing to take the above three SQL statements as an example, the calculation method of the hash value of the word sequence is as follows:
[0037] 1) Perform lexical analysis on the database statement to obtain a word sequence;
[0038] 2) If the word type is value, the hash value of the word is a fixed value (for example, 1); if the word type is other values, the hash value of the word is the hash value of the corresponding word converted to a lowercase string;
[0039] 3) Initialize the value of sql_hash (for example, sql_hash = 7);
[0040] 4) Start from the beginning and traverse each word in sequence. Execute the following pseudo code to sum the hash values of each word:
[0041] sql_hash=sql_hash<<3+hash(current word)
[0042] 5) Finally, sql_hash is the hash value of the word sequence.
[0043] Through this method, it can be found that the word sequence hash values of the above three SQL statements 1, statement 2 and statement 3 are the same.
[0044] In this step S11, specifically, determining whether there is an existing word sequence similar to the input word sequence in the sentence similarity table can be achieved in the following manner: for each existing word sequence in the sentence similarity table, compare the number of words and word positions obtained by lexical analysis of the existing word sequence and the input word sequence, if the number of words and the word positions are the same, then it is determined that there is an existing word sequence similar to the input word sequence in the sentence similarity table. If the number of words is not similar or the word positions are not similar, then it is determined that there is no existing word sequence similar to the input word sequence.
[0045] In order to improve the response speed of the database, it is preferred to set a copy of the sentence similarity table in the cache of the database. By searching the copy in the cache, it can be determined more quickly whether there is an existing word sequence similar to the input word sequence in the sentence similarity table.
[0046] Step S120: If there is an existing word sequence similar to the input word sequence in the sentence similarity table, then the parsed data corresponding to the similar existing word sequence is obtained from the sentence similarity table.
[0047] In the case where the sentence similarity table also includes a hash subtable, accordingly, in this step, the parsed data corresponding to the similar existing word sequence is determined according to the hash value corresponding to the similar existing word sequence. Those skilled in the art can understand that the efficiency of determining the parsed data can be improved according to the hash value, and the accuracy of determining the parsed data can be improved.
[0048] Step S121, if there is no existing word sequence similar to the input word sequence in the sentence similarity table, the input word sequence is parsed to obtain parsed data corresponding to the input word sequence, and the input word sequence and the corresponding parsed data are stored in the sentence similarity table.
[0049] Step S13: executing a database statement input into the database based on the parsed data corresponding to a similar existing word sequence or corresponding to the input word sequence.
[0050] It can be understood that according to the above-mentioned first embodiment of the present invention, by determining whether there is an existing word sequence similar to the input word sequence in the sentence similarity table, and in the case where there is a similar existing word sequence, the parsed data can be quickly obtained and directly applied. This statement parsing method can quickly parse database statements, which is beneficial to improving the response speed and work efficiency of the database.
[0051] In addition, the first embodiment can be executed independently of the database language, and thus can be adapted to various database languages and has a wider range of applicable scenarios; it also provides a more convenient development mode for database developers.
[0052] As a further improvement to the first embodiment described above, the database may also assign similarity flags to database statements, similar statements have the same flags, and dissimilar statements have different similarity flags. Preferably, the similarity flag is generated when the server inserts a new record into the statement similarity table, and its unique identification generation rule may be, for example, the following method: 1) accumulation in sequence, 2) using the memory address corresponding to the cache value in the similarity hash, etc. The database may maintain the corresponding relationship between the similarity flag and the parsed data, for example, using an array or memory address search, etc., that is, the parsed data corresponding to the similar statement can be uniquely found through the similarity flag.
[0053] The second embodiment of the present invention provides a database (not shown in the drawings), which includes a sentence parsing unit and a sentence similarity table, wherein the sentence parsing unit uses the method provided in the above-mentioned first embodiment and the sentence similarity table to parse the database sentences input into the database.
[0054] A third embodiment of the present invention provides another statement parsing method for database statements, which comprises the following steps:
[0055] Step S30: Perform lexical analysis on the database statement input into the client to obtain an input word sequence.
[0056] Step S31: Search the client sentence similarity table according to the input word sequence to determine whether there is an existing word sequence similar to the input word sequence in the client sentence similarity table.
[0057] The client statement similarity table records various existing word sequences that are different from each other, and the existing word sequences are obtained by performing lexical analysis on database statements.
[0058] Step S32: If there is an existing word sequence similar to the input word sequence in the client sentence similarity table, the client sends a similarity sentence parsing request to the database.
[0059] Step S33: The database searches the database-side sentence similarity table based on the similarity sentence parsing request to determine parsed data corresponding to the similarity sentence parsing request.
[0060] The database-side statement similarity table records the correspondence between similarity statement parsing requests and parsing data, and the parsing data is used by the database to execute database statements.
[0061] Step S34: The database executes the database statement of the input client segment based on the parsing data corresponding to the similarity statement parsing request.
[0062] Step S35: The database returns the execution result to the client.
[0063] According to the third embodiment, the client-side sentence similarity table corresponds to the database-side sentence similarity table one by one and is updated synchronously.
[0064] As an improvement to the third embodiment, the client can send a database statement execution request to the server (database), and the server responds to the client with the execution result. The database assigns similarity marks to the database statements and informs the client of the similarity marks; similar statements have the same marks, while dissimilar statements have different similarity marks.
[0065] Specifically, the client performs lexical analysis on the database statement, and then searches for the sentence similarity flag locally. If there is a corresponding sentence similarity flag, the sentence similarity flag is sent to the server. The server (database) searches for the corresponding reusable parsed data based on the similarity flag and applies it directly. If the client does not find the sentence similarity flag, the word sequence obtained by lexical analysis is directly sent to the server. After receiving the response, the server performs parsing and generates a new sentence similarity flag.
[0066] The third embodiment described above is aimed at the client / server application scenario, and distributes the lexical analysis and sentence similarity table search process to the client for execution, which significantly reduces the workload of the server (database), thereby improving the server's ability to process data.
[0067] The fourth embodiment of the present invention provides a database application system, comprising: a database server, which maintains a database-side statement similarity table; and multiple clients, each of which maintains a client statement similarity table. The database application system uses the method provided in the third embodiment to parse and execute the database statement input into the client.
[0068] The above description is only for the preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Those skilled in the art may make various modified designs without departing from the concept of the present invention and the appended claims.
Claims
1. A statement parsing method for database statements, characterized in that: Applied to a database application system, the database application system includes a database server and multiple clients; The method comprises: The client server processes the input database statement using lexical analysis to obtain an input word sequence; The client server searches the client statement similarity table maintained by the client according to the input word sequence, and determines whether the client statement similarity table has an existing word sequence similar to the input word sequence, wherein the client similarity table includes different existing word sequences, and the existing word sequences are obtained in advance through lexical analysis of the database statement; In the case of determining that there is an existing word sequence similar to the input word sequence in the client sentence similarity table, the client sends a similarity sentence parsing request to the database server; The database server searches the database-side statement similarity table maintained by the database server according to the similarity statement resolution request to determine the resolution data corresponding to the similarity statement resolution request, wherein the database-side statement similarity table includes a correspondence between the similarity statement resolution request and the resolution data; The database server executes the database statement input to the client server based on the parsing data corresponding to the similarity statement parsing request.
2. The method according to claim 1, characterized in that Also includes: In the case of determining that there is no existing word sequence similar to the input word sequence in the client sentence similarity table, the database server parses the input word sequence to obtain the corresponding parsed data, and stores the input word sequence in the client sentence similarity table; The database server stores the input word sequence and the corresponding parsed data in the database-side sentence similarity table to synchronously update the client-side sentence similarity table and the database-side sentence similarity table.
3. The method according to claim 1, characterized in that The determining whether there is an existing word sequence similar to the input word sequence in the client sentence similarity table comprises: The client compares the number of words and word positions of each existing word sequence in the client sentence similarity table with the input word sequence, wherein the number of words and word positions are obtained through lexical analysis; If the number of words and the word positions are the same, determining that there is an existing word sequence similar to the input word sequence in the client sentence similarity table; If the number of words or the word positions are not similar, it is determined that there is no existing word sequence similar to the input word sequence in the client sentence similarity table.
4. The method according to claim 1, characterized in that: The database-side sentence similarity table also includes a hash subtable, which records the corresponding relationship between the hash values of different existing word sequences and the parsed data.
5. The method according to claim 4, characterized in that The database server searches the database-side statement similarity table maintained by the database server according to the similarity statement resolution request to determine the resolution data corresponding to the similarity statement resolution request, including: According to the hash value corresponding to the similar existing word sequence, the parsing data corresponding to the similar existing word sequence is determined as the parsing data corresponding to the similarity statement parsing request.
6. The method according to claim 4, characterized in that The calculation method of the hash value of the word sequence includes: Performing lexical analysis on the database statement to obtain a word sequence; If the word type is value, the hash value of the word is a fixed value; If the word type is other values, the hash value of the word corresponds to the hash value of the word converted to a lowercase string; Initialize the database hash value; Start from the beginning and traverse each word in the word sequence in sequence, perform shift summation on the hash value of each word, and get the hash value of the word sequence.
7. The method according to claim 1, characterized in that Also includes: The database server pre-assigns similarity marks to the database statements and informs the client of the similarity marks, similar statements have the same similarity marks, and dissimilar statements have different similarity marks; The client performs lexical analysis on the database statement and locally searches for a similarity flag corresponding to the database statement; If the client has a similarity mark corresponding to the database statement, the client sends the similarity mark corresponding to the database statement to the database server; The database server searches for corresponding reusable parsed data according to the similarity mark and directly applies it; If the client fails to find a similarity mark corresponding to the database statement, the client sends a word sequence obtained through lexical analysis to the database server; The database server parses the received word sequence and generates a new similarity mark.
8. The method according to claim 7, characterized in that Similarity marks are unique, and the rules for generating similarity marks include: Accumulate them sequentially, or use the memory address corresponding to the cache value in the similarity hash.
9. The method according to claim 7, characterized in that: Also includes: A similarity flag is generated when a new record is inserted into the database-side sentence similarity table.
10. A database application system, characterized in that: include: The database server maintains a database-side sentence similarity table; as well as Multiple clients each maintain a client statement similarity table; The database application system utilizes the methods described in claims 1 to 9 to parse and execute database statements input into the client.