CN110704437B - Method, device, equipment and storage medium for modifying database query statement - Google Patents
Method, device, equipment and storage medium for modifying database query statement Download PDFInfo
- Publication number
- CN110704437B CN110704437B CN201910917565.9A CN201910917565A CN110704437B CN 110704437 B CN110704437 B CN 110704437B CN 201910917565 A CN201910917565 A CN 201910917565A CN 110704437 B CN110704437 B CN 110704437B
- Authority
- CN
- China
- Prior art keywords
- expression
- query
- index
- target column
- sub
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
Abstract
The application relates to a method, a device, equipment and a storage medium for modifying a database query statement. The method comprises the following steps: acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column; acquiring an index expression of the target column; when the index of the target column cannot be used in the data query process, modifying the first query condition expression into a second query condition expression, wherein a first sub-expression in the second query condition expression is the same as the index expression, and the first sub-expression is an expression which represents the left side of a magnitude relation symbol in the second query condition expression. The method improves the efficiency of data query.
Description
Technical Field
The present application relates to the field of computers, and in particular, to a method, an apparatus, a device, and a storage medium for modifying a database query statement.
Background
In daily work, databases are indispensable tools for storing data. People can use the database to store important data and can inquire the required data from the database.
When there are a large number of storage records in a database table, in order to query the database table more efficiently, an index may be generally established in the database table, and then an index value meeting the query condition is found in the index, and a corresponding record in the database table is quickly found according to the index value.
However, when the query statement input by the user does not meet the use condition of the index, the data query efficiency is still low.
Disclosure of Invention
Based on this, it is necessary to provide a method, an apparatus, a device and a storage medium for modifying a database query statement to solve the technical problem that when a query statement input by a user does not meet the use condition of an index, the data query efficiency is still low.
A modification method of a database query statement comprises the following steps:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of the magnitude relation symbol.
An apparatus for modifying a database query statement, comprising:
the system comprises a first acquisition module, a second acquisition module and a third acquisition module, wherein the first acquisition module is used for acquiring a query statement input by a user, and the query statement comprises a first query conditional expression of a target column;
the second acquisition module is used for acquiring the index expression of the target column;
and the processing module is used for modifying the first query conditional expression into a second query conditional expression when determining that the index of the target column cannot be used in the data query process, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression representing the left side of the size relation symbol in the second query conditional expression.
A computer device comprising a memory and a processor, the memory storing a computer program, the processor implementing the following steps when executing the computer program:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of the magnitude relation symbol.
A computer-readable storage medium, on which a computer program is stored which, when executed by a processor, carries out the steps of:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of the magnitude relation symbol.
According to the modification method, device, equipment and storage medium for the database query statement, when the computer equipment determines that the index of the target column cannot be used in the data query process, the first query condition expression in the query statement input by the user is modified into the second query condition expression, so that the expression on the left side of the second query condition expression is consistent with the index expression of the target column, the modified query statement meets the use condition of the index of the target column, the index of the target column can be used for data query, the data query is not performed in a full-table comparison mode, and the efficiency of the data query is improved.
Drawings
FIG. 1 is a flowchart illustrating a method for modifying a database query statement according to an embodiment;
FIG. 2 is a storage form of an index expression for a target column provided by an embodiment;
FIG. 3 is a flowchart illustrating a method for modifying a database query statement according to another embodiment;
FIG. 4 is a flowchart illustrating a method for modifying a database query statement according to another embodiment;
FIG. 5 is a schematic diagram illustrating an internal structure of an apparatus for modifying a database query statement according to an embodiment;
fig. 6 is a schematic internal structural diagram of a computer device according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the technical solutions in the embodiments of the present application are further described in detail by the following embodiments in combination with the accompanying drawings. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
It should be noted that the execution subject of the method embodiments described below may be a modification apparatus of a database query statement, and the apparatus may be implemented as part of or all of a computer device by software, hardware, or a combination of software and hardware. The method embodiments described below are described by way of example with the execution subject being a computer device.
Fig. 1 is a flowchart illustrating a method for modifying a database query statement according to an embodiment. The present embodiment is directed to a process of how a computer device modifies a database query statement. As shown in fig. 1, the method includes:
s101, acquiring a query statement input by a user.
The query statement comprises a first query condition expression of a target column, and the target column is a specified column to be queried. When a user has a query request, a query statement may be entered via a user interface component provided by a data query system operating in a computer device. The type of the Query statement input by the user may be a Structured Query Language (SQL) statement, or may be other types of statements.
Taking the type of the query statement as an example of an SQL statement, assuming that the query statement acquired by the computer device is select c1 from t1 where c1 × 2-1>5, where c1 is a target column and c1 × 2-1>5 is a first query condition expression of the target column, that is, the meaning of the query statement is c1 column in t1, and data satisfying c1 × 2-1>5 is queried from c1 column.
And S102, acquiring an index expression of the target column.
In order to facilitate data query, an index may be established in advance for the target column, and one index may be established or multiple indexes may be established. Illustratively, the following may be done by the statements: create index l1 on t1(c1 × 2+1) indexes the target column c1, the statement of which means that the index l1 is established for the column c1in table t1, and the index expression of the established index l1 is c1 × 2+ 1. The index may be stored in a data structure of a binary tree in a computer device, and taking an index expression of the index of the target column c1 as c1 × 2+1 as an example, the storage form of the index in the computer device may be as shown in fig. 2.
When only one index exists in the target column, acquiring an index expression of the index; when a plurality of indexes exist in the target column, one target index is arbitrarily selected from the plurality of indexes, and an index expression of the target index is obtained.
S103, when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression and represents the left side of the size relation symbol.
The computer device determines that the query statement does not conform to the use condition of the index of the target column according to a first query condition expression and an index expression of the target column in the query statement input by a user, namely, the index of the target column cannot be used for querying in the data query process, at this time, the computer device modifies the first query condition expression, so that a first sub-expression (the first sub-expression is an expression on the left side of a size relation symbol in the second query condition expression) in the second query condition expression is the same as the index expression. The above-mentioned sign indicating the size relationship may be any one of ">", "<", "═ and ≧" and "≦".
As an alternative implementation, the determining, by the computer device, that the index of the target column cannot be used in the data query process may include: comparing a second sub-expression in the first query conditional expression with the index expression; and when the second sub-expression is not the same as the index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression which represents the left side of the size relation symbol in the first query conditional expression.
When the index expression is multiple, as another optional implementation, the determining, by the computer device, that the index of the target column cannot be used in the data query process may include: comparing a second sub-expression in the first query conditional expression with each index expression respectively; and when the second sub-expression is different from each index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression representing the left side of the size relation symbol in the first query conditional expression.
For example, assuming that the query statement obtained by the computer device is select c1 from t1 where c1 x 2-1>5, and the index expression of the obtained target column is 1-2 x c1, the computer device compares the second sub-expression (the second sub-expression is c1 x 2-1) in the first query conditional expression with the index expression 1-2 x c1 of the target column, and since the second sub-expression c1 x 2-1 is not the same as the index expression 1-2 x c1 of the target column, the computer device determines that the index of the target column cannot be used in the data query process. Then, the computer device modifies the first query conditional expression so that the first sub-expression in the obtained second query conditional expression is the same as the index expression 1-2 × c1 of the target column. Therefore, the second query conditional expression in the modified query statement meets the use condition of the index of the target column, and the index of the target column can be used for data query in the data query process.
According to the modification method for the database query statement, when the computer device determines that the index of the target column cannot be used in the data query process, the first query conditional expression in the query statement input by the user is modified into the second query conditional expression, so that the expression on the left side of the second query conditional expression is consistent with the index expression of the target column, the modified query statement conforms to the use condition of the index of the target column, the index of the target column can be used for data query, the data query is not performed in a full-table comparison mode, and the efficiency of data query is improved.
Fig. 3 is a flowchart illustrating a method for modifying a database query statement according to another embodiment. The embodiment relates to a specific process of how the computer device determines the second query conditional expression. On the basis of the foregoing embodiment, as shown in fig. 3, before the foregoing S103, optionally, the method further includes:
s201, calculating the value range of the target column according to the first query condition expression.
And calculating the value range of the target column based on the first query conditional expression. It is to be understood that, when the size relation symbol included in the first query conditional expression is "equal", a specific value of the target column is calculated, and when the size relation symbol included in the first query conditional expression is not "equal", a value range of the target column is calculated.
Continuing to take the example that the query statement obtained by the computer device is select c1 from t1 where c1 × 2-1>5, the first query conditional expression is c1 × 2-1>5, and based on c1 × 2-1>5, the value range of the calculation target column c1 is c1> 3.
S202, determining the second query condition expression according to the value range of the target column and the index expression.
In an alternative embodiment, the step S202 may be: substituting the value range of the target column into the index expression to obtain the value range of the index expression, and combining the value ranges of the index expression and the index expression to obtain the second query condition expression.
Specifically, continuing with the example in S201, the computer device substitutes the calculated range c1>3 of the target column c1 into the index expression 1-2 × c1 to obtain the range of the index expression "< -5", and merges the range of the index expression 1-2 × c1 and the range of the index expression "< -5" to obtain 1-2 × c1< -5, and 1-2 × c1< -5 "which is the second query condition expression.
Then, the first query conditional expression c1 × 2-1>5 is modified into the second query conditional expression 1-2 × c1< -5, that is, the query statement select c1 from t1 where c1 × 2-1>5 is modified into select c1 from t1 where 1-2 × c1< -5, and the modified query statement meets the index use condition of the target column c1, so that data query can be performed through the index of the target column c1in the data query process, and the efficiency of data query is improved.
In practical applications, there is also a case where: the where clause in the query sentence acquired by the computer device contains between. For this case, the process can be processed by referring to the following embodiment, specifically:
when the query statement includes a third query conditional expression of the target column, and the third query conditional expression does not include a symbol representing a size relationship, before S102, the method further includes: and dividing the third query conditional expression to obtain a plurality of sub-query conditional expressions, and taking each sub-query conditional expression as the first query conditional expression. Optionally, the third query conditional expression may be segmented by using an and to obtain a plurality of sub-query conditional expressions connected by an and; or, the third query conditional expression is segmented by the or to obtain a plurality of sub-query conditional expressions connected by the or.
Specifically, when the third query conditional expression contains between. Illustratively, taking the third query conditional expression in the query statement acquired by the computer device as c1 between a and b as an example, the computer device divides the third query conditional expression c1 between a and b to obtain c1 ≧ a and c1 ≤ b, and respectively uses c1 ≥ a and c1 ≤ b as the first query conditional expression in the above embodiment. When c1 ≧ a and c1 ≦ b cannot utilize the index of c1, c1 ≧ a and c1 ≦ b may be modified with reference to the procedures of the above-described embodiments.
When the third query conditional expression contains in, utilizing or to divide the third query conditional expression to obtain a plurality of sub query conditional expressions connected through the or. For example, taking the third query conditional expression in the query statement acquired by the computer device as c1in (a, b, c) as an example, the computer device divides the third query conditional expression c1in (a, b, c) to obtain c1 ═ a or c1 ═ b or c1 ═ c, and takes c1 ═ a, c1 ═ b, and c1 ═ c as the first query conditional expression in the above embodiment. When the index of c1 cannot be used for the above 3 sub-query conditional expressions, the 3 sub-query conditional expressions may be modified by referring to the process of the above embodiment.
In the modification method for the query statement of the database provided by this embodiment, when the third query conditional expression of the query statement acquired by the computer device does not include the sign indicating the magnitude relationship, the computer device may segment the third query conditional expression to obtain a plurality of sub-query conditional expressions, and modify the sub-query condition when it is determined that the sub-query conditional expression cannot use the index of the target column, so that the expression on the left side of the modified sub-query condition is consistent with the index expression of the target column, and thus the modified query statement conforms to the use condition of the index of the target column, and further, the index of the target column may be used for data query, and data query is no longer performed in a full-table comparison manner, thereby improving the efficiency of data query.
In order to facilitate understanding of those skilled in the art, the following describes in detail a process of modifying a database query statement by a computer device by taking the query statement as an SQL query statement, as shown in fig. 4, specifically:
s301, obtaining the SQL query statement input by the user.
The SQL query statement comprises a first query condition expression of a target column. And the computer equipment performs lexical and syntactic analysis and check on the obtained SQL query statement, and executes S302 when the SQL query statement is checked to be in accordance with the lexical and syntactic. Otherwise, outputting prompting information of grammar error.
S302, dividing the query condition expression in the where clause through and or.
For each of the divided sub-query conditional expressions, the processing may be performed with reference to the procedure of S303 to S306 described below.
S303, judging whether the index of the target column can be used.
If yes, the flow ends, otherwise, S304 is executed.
And S304, calculating the value range of the target column according to the sub-query conditional expression.
S305, substituting the value range of the target column into the index expression of the target column to obtain the value range of the index expression.
S306, merging the index expression and the value range of the index expression to obtain a target query condition expression.
S307, modifying the sub-query condition expression into a target query condition expression.
And the expression on the left side of the size relation symbol in the target query conditional expression is the same as the index expression of the target column.
In the modification method for the query statement of the database provided by this embodiment, when the query condition expression of the SQL query statement acquired by the computer device does not include the sign indicating the size relationship, the computer device may segment the query condition expression to obtain a plurality of sub-query condition expressions, and modify the sub-query condition when it is determined that the sub-query condition expression cannot use the index of the target column, so that the expression on the left side of the modified sub-query condition is consistent with the index expression of the target column, and thus the modified query statement conforms to the use condition of the index of the target column, and further, the index of the target column may be used for data query, and data query is no longer performed in a full-table comparison manner, thereby improving the efficiency of data query.
It should be understood that, although the steps in the flowcharts of fig. 1 to 4 are shown in sequence as indicated by the arrows, the steps are not necessarily performed in sequence as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least some of the steps in fig. 1-4 may include multiple sub-steps or multiple stages that are not necessarily performed at the same time, but may be performed at different times, and the order of performing the sub-steps or stages is not necessarily sequential, but may be performed in turn or alternately with other steps or at least some of the sub-steps or stages of other steps.
Fig. 5 is a schematic internal structural diagram of an apparatus for modifying a database query statement according to an embodiment, as shown in fig. 5, the apparatus may include: a first acquisition module 10, a second acquisition module 11 and a processing module 12.
Specifically, the first obtaining module 10 is configured to obtain a query statement input by a user, where the query statement includes a first query conditional expression of a target column;
the second obtaining module 11 is configured to obtain an index expression of the target column;
the processing module 12 is configured to modify the first query conditional expression into a second query conditional expression when it is determined that the index of the target column cannot be used in the data query process, where a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression that represents the left side of the magnitude relation symbol.
According to the modification device for the database query statement, when the computer device determines that the index of the target column cannot be used in the data query process, the first query conditional expression in the query statement input by the user is modified into the second query conditional expression, so that the expression on the left side of the second query conditional expression is consistent with the index expression of the target column, the modified query statement conforms to the use condition of the index of the target column, the index of the target column can be used for data query, the data query is not performed in a full-table comparison mode, and the efficiency of data query is improved.
On the basis of the above embodiment, optionally, the apparatus further includes: the device comprises a calculation module and a determination module.
Specifically, the calculating module is configured to calculate a value range of the target column according to the first query conditional expression before the processing module 12 modifies the first query conditional expression into a second query conditional expression;
the determining module is configured to determine the second query conditional expression according to the value range of the target column and the index expression.
On the basis of the foregoing embodiment, optionally, the determining module is specifically configured to substitute the value range of the target column into the index expression to obtain the value range of the index expression; and combining the index expression and the value range of the index expression to obtain the second query condition expression.
On the basis of the foregoing embodiment, optionally, when the query statement includes a third query conditional expression of the target column, and the third query conditional expression does not include a symbol representing a size relationship, the apparatus further includes: and (5) a partitioning module.
Specifically, the dividing module is configured to divide the third query conditional expression to obtain a plurality of sub-query conditional expressions before the second obtaining module 11 obtains the index expression of the target column, and use each sub-query conditional expression as the first query conditional expression.
On the basis of the foregoing embodiment, optionally, the dividing module is specifically configured to divide the third query conditional expression by and to obtain a plurality of sub-query conditional expressions connected by and; or the dividing module is specifically configured to divide the third query conditional expression by using or to obtain a plurality of sub-query conditional expressions connected by or.
On the basis of the foregoing embodiment, optionally, the processing module 12 is specifically configured to compare the second sub-expression in the first query conditional expression with the index expression; and when the second sub-expression is not the same as the index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression which represents the left side of the size relation symbol in the first query conditional expression.
On the basis of the foregoing embodiment, optionally, when the index expressions are multiple, the processing module 12 is specifically configured to compare the second sub-expressions in the first query conditional expression with each index expression respectively; and when the second sub-expression is different from each index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression representing the left side of the size relation symbol in the first query conditional expression.
In one embodiment, a computer device is provided, the internal structure of which may be as shown in FIG. 6. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The database of the computer device is used for storing data in the modification process of the database query statement. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement a method of modifying a database query statement.
It will be appreciated by those skilled in the art that the configuration shown in fig. 6 is a block diagram of only a portion of the configuration associated with the present application, and is not intended to limit the computing device to which the present application may be applied, and that a particular computing device may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, a computer device is provided, comprising a memory and a processor, the memory having a computer program stored therein, the processor implementing the following steps when executing the computer program:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of the magnitude relation symbol.
In one embodiment, the processor, when executing the computer program, further performs the steps of: calculating the value range of the target column according to the first query condition expression; and determining the second query condition expression according to the value range of the target column and the index expression.
In one embodiment, the processor, when executing the computer program, further performs the steps of: substituting the value range of the target column into the index expression to obtain the value range of the index expression; and combining the index expression and the value range of the index expression to obtain the second query condition expression.
In one embodiment, when the query statement contains a third query conditional expression for the target column and the third query conditional expression does not contain a size relation symbol, the processor when executing the computer program further performs the steps of: and segmenting the third query condition expression to obtain a plurality of sub-query condition expressions, and taking each sub-query condition expression as the first query condition expression.
In one embodiment, the processor, when executing the computer program, further performs the steps of: dividing the third query conditional expression by using the sum to obtain a plurality of sub query conditional expressions connected by the sum; or, the third query conditional expression is segmented by using or to obtain a plurality of sub-query conditional expressions connected by or.
In one embodiment, the processor, when executing the computer program, further performs the steps of: comparing a second sub-expression in the first query conditional expression with the index expression; and when the second sub-expression is not the same as the index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression which represents the left side of the size relation symbol in the first query conditional expression.
In one embodiment, when the index expression is multiple, the processor executes the computer program to further perform the following steps: comparing a second sub-expression in the first query conditional expression with each index expression respectively; and when the second sub-expression is different from each index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression representing the left side of the size relation symbol in the first query conditional expression.
In one embodiment, a computer-readable storage medium is provided, having a computer program stored thereon, which when executed by a processor, performs the steps of:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of the magnitude relation symbol.
In one embodiment, the computer program when executed by the processor further performs the steps of: calculating the value range of the target column according to the first query condition expression; and determining the second query condition expression according to the value range of the target column and the index expression.
In one embodiment, the computer program when executed by the processor further performs the steps of: substituting the value range of the target column into the index expression to obtain the value range of the index expression; and combining the index expression and the value range of the index expression to obtain the second query condition expression.
In one embodiment, when the query statement contains a third query conditional expression for the target column and the third query conditional expression does not contain a symbol representing a size relationship, the computer program when executed by the processor further performs the steps of: and dividing the third query conditional expression to obtain a plurality of sub-query conditional expressions, and taking each sub-query conditional expression as the first query conditional expression.
In one embodiment, the computer program when executed by the processor further performs the steps of: dividing the third query conditional expression by using the sum to obtain a plurality of sub query conditional expressions connected by the sum; or, the third query conditional expression is segmented by using or to obtain a plurality of sub-query conditional expressions connected by or.
In one embodiment, the computer program when executed by the processor further performs the steps of: comparing a second sub-expression in the first query conditional expression with the index expression; and when the second sub-expression is not the same as the index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression which represents the left side of the size relation symbol in the first query conditional expression.
In one embodiment, when the index expression is plural, the computer program when executed by the processor further performs the steps of: comparing a second sub-expression in the first query conditional expression with each index expression respectively; and when the second sub-expression is different from each index expression, determining that the index of the target column cannot be used in the data query process, wherein the second sub-expression is an expression representing the left side of the size relation symbol in the first query conditional expression.
The modification device, the computer device and the storage medium for the database query statement provided in the above embodiments may execute the modification method for the database query statement provided in any embodiment of the present application, and have corresponding functional modules and beneficial effects for executing the method. For technical details that are not described in detail in the above embodiments, reference may be made to a method for modifying a database query statement provided in any embodiment of the present application.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory, among others. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).
The technical features of the embodiments described above may be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the embodiments described above are not described, but should be considered as being within the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the present application. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (8)
1. A method for modifying a database query statement, comprising:
acquiring a query statement input by a user, wherein the query statement comprises a first query condition expression of a target column;
acquiring an index expression of the target column;
when it is determined that the index of the target column cannot be used in the data query process, modifying the first query conditional expression into a second query conditional expression, wherein a first sub-expression in the second query conditional expression is the same as the index expression, and the first sub-expression is an expression in the second query conditional expression, which represents the left side of a magnitude relation symbol;
before the modifying the first query conditional expression into a second query conditional expression, further comprising:
calculating the value range of the target column according to the first query condition expression;
determining the second query condition expression according to the value range of the target column and the index expression;
determining the second query conditional expression according to the value of the target column and the index expression, including:
substituting the value range of the target column into the index expression to obtain the value range of the index expression;
and combining the index expression and the value range of the index expression to obtain the second query condition expression.
2. The method of claim 1, wherein when the query statement contains a third query conditional expression for a target column and the third query conditional expression does not contain a size-representing relation symbol therein, further comprising, before the obtaining an index expression for the target column:
and dividing the third query conditional expression to obtain a plurality of sub-query conditional expressions, and taking each sub-query conditional expression as the first query conditional expression.
3. The method of claim 2, wherein said splitting the third query conditional expression into a plurality of sub-query conditional expressions comprises:
dividing the third query conditional expression by using the sum to obtain a plurality of sub query conditional expressions connected by the sum; alternatively, the first and second electrodes may be,
and utilizing or to divide the third query conditional expression to obtain a plurality of sub query conditional expressions connected through or.
4. The method of any of claims 1-3, wherein the determining that the index of the target column cannot be used during the data query comprises:
comparing a second sub-expression in the first query conditional expression with the index expression, wherein the second sub-expression is an expression representing the left side of a magnitude relation symbol in the first query conditional expression;
when the second sub-expression is not the same as the index expression, determining that the index of the target column cannot be used in the data query process.
5. The method of any one of claims 1 to 3, wherein when the index expression is plural, the determining that the index of the target column cannot be used in the data query process comprises:
comparing second sub-expressions in the first query conditional expression with each index expression respectively, wherein the second sub-expressions are expressions which represent the left sides of the magnitude relation symbols in the first query conditional expression;
and when the second sub-expression is different from each index expression, determining that the index of the target column cannot be used in the data query process.
6. An apparatus for modifying a database query statement, comprising:
the system comprises a first acquisition module, a second acquisition module and a third acquisition module, wherein the first acquisition module is used for acquiring a query statement input by a user, and the query statement comprises a first query conditional expression of a target column;
the second acquisition module is used for acquiring the index expression of the target column;
the processing module is used for modifying the first query condition expression into a second query condition expression when determining that the index of the target column cannot be used in the data query process, wherein a first sub-expression in the second query condition expression is the same as the index expression, and the first sub-expression is an expression which represents the left side of a size relation symbol in the second query condition expression;
the calculation module is used for calculating the value range of the target column according to the first query conditional expression before the processing module modifies the first query conditional expression into a second query conditional expression;
the determining module is used for determining the second query condition expression according to the value range of the target column and the index expression;
the determining module is specifically configured to substitute the value range of the target column into the index expression to obtain the value range of the index expression; and combining the index expression and the value range of the index expression to obtain the second query condition expression.
7. A computer device comprising a memory and a processor, the memory storing a computer program, wherein the processor implements the steps of the method of any one of claims 1 to 5 when executing the computer program.
8. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910917565.9A CN110704437B (en) | 2019-09-26 | 2019-09-26 | Method, device, equipment and storage medium for modifying database query statement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910917565.9A CN110704437B (en) | 2019-09-26 | 2019-09-26 | Method, device, equipment and storage medium for modifying database query statement |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110704437A CN110704437A (en) | 2020-01-17 |
| CN110704437B true CN110704437B (en) | 2022-05-20 |
Family
ID=69196467
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910917565.9A Active CN110704437B (en) | 2019-09-26 | 2019-09-26 | Method, device, equipment and storage medium for modifying database query statement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110704437B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111597189B (en) * | 2020-05-13 | 2023-09-01 | 北京三快在线科技有限公司 | Method, device, equipment and storage medium for acquiring data |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103226610A (en) * | 2013-05-07 | 2013-07-31 | 华为技术有限公司 | Method and device for querying database table |
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
| CN105701098A (en) * | 2014-11-25 | 2016-06-22 | 国际商业机器公司 | Method and apparatus for generating index for table in database |
| CN107402987A (en) * | 2016-09-21 | 2017-11-28 | 广州特道信息科技有限公司 | A kind of method of full-text search and distributed NewSQL Database Systems |
| CN109947770A (en) * | 2018-08-14 | 2019-06-28 | 武汉斗鱼网络科技有限公司 | A kind of data base query method, terminal device and storage medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JP5994490B2 (en) * | 2012-08-29 | 2016-09-21 | 富士通株式会社 | Data search program, database device, and information processing system |
-
2019
- 2019-09-26 CN CN201910917565.9A patent/CN110704437B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
| CN103226610A (en) * | 2013-05-07 | 2013-07-31 | 华为技术有限公司 | Method and device for querying database table |
| CN105701098A (en) * | 2014-11-25 | 2016-06-22 | 国际商业机器公司 | Method and apparatus for generating index for table in database |
| CN107402987A (en) * | 2016-09-21 | 2017-11-28 | 广州特道信息科技有限公司 | A kind of method of full-text search and distributed NewSQL Database Systems |
| CN109947770A (en) * | 2018-08-14 | 2019-06-28 | 武汉斗鱼网络科技有限公司 | A kind of data base query method, terminal device and storage medium |
Non-Patent Citations (1)
| Title |
|---|
| 基于索引的SQL语句查询优化方法;朱君;《东莞理工学院学报》;20031030(第02期);第18-20页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110704437A (en) | 2020-01-17 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112559554B (en) | Query statement optimization method and device | |
| CN110990400A (en) | Database query method and device, computer equipment and storage medium | |
| CN106897343B (en) | Searching method, storing method and device for execution plan | |
| CN110674319A (en) | Label determination method and device, computer equipment and storage medium | |
| US8631500B2 (en) | Generating minimality-attack-resistant data | |
| CN112883030A (en) | Data collection method and device, computer equipment and storage medium | |
| CN112579602A (en) | Multi-version data storage method and device, computer equipment and storage medium | |
| CN111107042B (en) | Message parsing method and device, computer equipment and storage medium | |
| CN112560444A (en) | Text processing method and device, computer equipment and storage medium | |
| CN109656947B (en) | Data query method and device, computer equipment and storage medium | |
| CN111190901A (en) | Business data storage method and device, computer equipment and storage medium | |
| CN110704437B (en) | Method, device, equipment and storage medium for modifying database query statement | |
| CN111881477A (en) | Indexing method and device of data content, computer equipment and storage medium | |
| CN108595437B (en) | Text query error correction method and device, computer equipment and storage medium | |
| CN114090589A (en) | Data query method and device based on HBase and computer equipment | |
| CN109213775B (en) | Search method, search device, computer equipment and storage medium | |
| CN109033295A (en) | The merging method and device of super large data set | |
| CN112559529A (en) | Data storage method and device, computer equipment and storage medium | |
| CN112597162B (en) | Data set acquisition method, system, equipment and storage medium | |
| CN114297233A (en) | Database query method, database query device, electronic device, medium, and program product | |
| CN111061875B (en) | Super parameter determination method, device, computer equipment and storage medium | |
| CN114238334A (en) | Heterogeneous data encoding method and device, heterogeneous data decoding method and device, computer equipment and storage medium | |
| CN111949684A (en) | SQL statement execution method, device, equipment and storage medium | |
| CN112579705A (en) | Metadata acquisition method and device, computer equipment and storage medium | |
| CN112181831A (en) | Script performance verification method, device and equipment based on keywords and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |