Logic operation processing method, device and equipment for SQL (Structured Query Language) statement, medium and product
By identifying AND connectors in SQL statements and extracting common subexpressions, the AND predicate is optimized, solving the problem of high computational complexity in complex SQL statements and improving query efficiency.
Patent Information
- Application Number
- CN202511217116.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-08-28
- Publication Date
- 2025-12-12
AI Technical Summary
In complex SQL statements, the AND predicate leads to high computational complexity and reduced statement execution efficiency.
By obtaining the original expression of the SQL statement, identifying the AND concatenation terms and storing them in a linked list, extracting common subexpressions, removing duplicate parts, and using the OR operator to connect the remaining expressions, an optimized target expression is generated.
The filtering conditions have been simplified, improving the query efficiency of SQL statements.
Smart Images
Figure CN121117282A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of database operation, and in particular to a logical operation processing method, device and equipment of SQL statement, medium and product. BACKGROUND
[0002] In the field of database query, Structured Query Language (SQL) is widely used for data retrieval and management. The AND predicate in SQL statement is a common logical operator used to connect multiple conditional expressions, requiring all conditions to be true at the same time, and the entire predicate returns true value. By using the AND operator in the WHERE clause, multiple filtering conditions can be combined to achieve more accurate data filtering.
[0003] However, as the business logic becomes more complex, the AND predicate in SQL query may not only contain simple Boolean judgment conditions, but also involve nested logical operations, subqueries, function calculations or complex expression combinations. For example, an AND condition may be composed of multiple OR logical operators, and may even contain aggregation functions, regular matching or user-defined functions, etc. Such complex condition combination will result in high complexity of SQL statement operation and reduce the efficiency of statement execution. SUMMARY
[0004] The present application provides a logical operation processing method, device, equipment, medium and product of SQL statement to solve the problem of high complexity of complex SQL statement operation containing AND predicate and reduce the efficiency of statement execution.
[0005] In a first aspect, the present application provides a logical operation processing method of SQL statement, comprising:
[0006] Obtaining the original expression corresponding to the SQL statement; the original expression includes the logical AND operator and the AND connection item connected by the AND operator;
[0007] Saving the AND connection item in the AND connection item chain table;
[0008] Determining the common sub-expression in the AND connection item chain table, and saving the common sub-expression in the common sub-expression chain table after determining each common sub-expression, removing the sub-expression in the AND connection item chain table according to the expression of the AND connection item and the common sub-expression;
[0009] The target sub-expression is constituted according to the remaining sub-expressions in the AND connection item linked list, and the common sub-expressions in the common sub-expression linked list and the target sub-expression are connected by a logical OR operator to obtain a target expression corresponding to the SQL statement.
[0010] In a second aspect, an embodiment of the present application provides a logical operation processing apparatus of a SQL statement, comprising:
[0011] An original expression obtaining module is configured to obtain an original expression corresponding to a SQL statement; the original expression comprises an AND operator and an AND connection item connected by the AND operator.
[0012] A first linked list saving module is configured to save the AND connection item in an AND connection item linked list.
[0013] A common sub-expression processing module is configured to determine common sub-expressions in the AND connection item linked list, save the common sub-expressions in a common sub-expression linked list after determining each common sub-expression, and remove sub-expressions in the AND connection item linked list according to the expressions of the AND connection items and the common sub-expressions.
[0014] A target expression determining module is configured to constitute a target sub-expression according to the remaining sub-expressions in the AND connection item linked list, and connect the common sub-expressions in the common sub-expression linked list and the target sub-expression by a logical OR operator to obtain a target expression corresponding to the SQL statement.
[0015] In a third aspect, an embodiment of the present application provides an electronic device, comprising:
[0016] at least one processor;
[0017] and a memory in communication connection with the at least one processor.
[0018] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the logical operation processing method of the SQL statement according to any embodiment of the present application.
[0019] In a fourth aspect, an embodiment of the present application provides a computer readable storage medium, which stores computer instructions for enabling a processor to execute the logical operation processing method of the SQL statement according to any embodiment of the present application.
[0020] In a fifth aspect, an embodiment of the present application provides a computer program product, which comprises a computer program, and the computer program, when executed by a processor, implements the SQL statement logical operation processing method of any of the embodiments of the present application.
[0021] The technical scheme of the embodiment of the present application obtains the original expression corresponding to the SQL statement; the original expression comprises an AND operator and an AND connection item connected by the AND operator; the AND connection item is saved in an AND connection item linked list; the common sub-expression in the AND connection item linked list is determined, and after each common sub-expression is determined, the common sub-expression is saved in a common sub-expression linked list; the sub-expressions in the AND connection item linked list are removed according to the expression of the AND connection item and the common sub-expression; the target sub-expression is constituted according to the remaining sub-expressions in the AND connection item linked list, and the common sub-expressions in the common sub-expression linked list and the target sub-expression are connected by an OR operator to obtain the target expression corresponding to the SQL statement. The common sub-expressions of the AND predicates in the original expression are extracted for logical operation optimization, which can simplify the screening condition, solve the problem of high operation complexity and reduced statement execution efficiency of the complex SQL statement containing the AND predicate, and improve the SQL statement query efficiency.
[0022] It should be understood that the content described in this part is not intended to identify key or important features of the embodiments of the present application, nor is it used to limit the scope of the present application. Other features of the present application will become apparent from the following description. BRIEF DESCRIPTION OF DRAWINGS
[0023] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced as follows. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor on the basis of these drawings.
[0024] Figure 1 A flowchart of a SQL statement logical operation processing method provided for the first embodiment of the present application;
[0025] Figure 2 A flowchart of a SQL statement logical operation processing method provided for the second embodiment of the present application;
[0026] Figure 3 A structural schematic diagram of a SQL statement logical operation processing device provided for the third embodiment of the present application;
[0027] Figure 4A structural schematic diagram of an electronic device for implementing the method for logical operation processing of an SQL statement according to an embodiment of the present application. DETAILED DESCRIPTION
[0028] In order to make the persons skilled in the art better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all the other embodiments obtained by the persons skilled in the art without creative efforts should belong to the protection scope of the present application.
[0029] It should be noted that the terms "first", "second" and the like in the specification and claims of the present application and the above-described drawings are used to distinguish similar objects, and do not necessarily indicate a specific order or a sequence. It should be understood that the data thus used can be interchanged under appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device including a series of steps or units does not necessarily have to include all the steps or units clearly listed, but can include other steps or units not clearly listed or inherent to the process, method, product or device.
[0030] Embodiment one
[0031] Figure 1 A flowchart of a method for logical operation processing of an SQL statement according to the first embodiment of the present application is provided, and the present embodiment can be applied to the case of optimizing the logical operation processing of an SQL statement containing an AND predicate. The method can be executed by a logical operation processing device for an SQL statement, which can be realized in the form of hardware and / or software, and can be configured in an electronic device. As shown in the figure, the method comprises the following steps. Figure 1
[0032] S110, obtaining an original expression corresponding to an SQL statement; the original expression includes a logical AND operator and an AND connection item connected by the AND operator.
[0033] The original expression can be understood as an expression of the SQL statement before logical operation optimization. In this embodiment, the SQL statement contains an AND predicate, and the corresponding original expression is an AND expression. In this embodiment, at least the outermost two layers of logical operation of the original expression are considered, and the first layer of logical operation of the original expression is composed of an AND operator (i.e., an AND operator) and an AND connection item. The AND connection item can be considered as a sub-expression or an operand connected by the AND operator. The result is true only when all AND connection items connected by the AND operator are true, otherwise the result is false.
[0034] The AND connection item can have a second layer of logical operation, which can be composed of an OR operator (i.e., an OR operator) and an OR connection item connected by the OR operator, and the AND connection item can also be considered as an OR expression. The OR connection item can be considered as a sub-expression or an operand connected on both sides of the OR operator, such as the AND connection item in A AND(B OR C OR…) includes A and (B OR C OR…); or (A OR B OR…) AND(C OR D OR…), the AND connection item includes: (A OR B OR…) and (C OR D OR…). The AND connection item and the OR connection item can be a simple Boolean judgment condition or a complex logical operation combination. A, B, C, and D can be a single filter condition such as C1=C2; or a variety of logical operation combinations of filter conditions such as NOT(C1=C2 AND(NOT(C2=C3 OR C3<C4))). If the AND connection items connected by the AND operator are the same, such as A AND A, A can also use the method of this embodiment to optimize the logical operation of the SQL statement even if it is not an OR expression. Specifically, the AND predicate in the SQL statement can be analyzed as an original expression containing an AND operator and an AND connection item at least one AND operator includes an OR operator and an OR connection item connected by the OR operator. In order to facilitate subsequent logical operation optimization, the AND connection item and the OR connection item can be determined from the original expression.
[0035] S120, save the AND connection item in the AND connection item chain table.
[0036] The AND operation chain table can be understood as a data structure for saving the AND connection item in the form of a chain table. The chain table is a linear data structure composed of a series of nodes, each node containing data and a pointer to the next node.
[0037] Specifically, the AND connection item determined in the original expression is saved in the AND connection item chain table, and the operation of the AND connection item is waited for subsequently.
[0038] Optionally, if the AND connection item is an OR expression, the OR connection item in the AND connection item is saved in the OR connection item chain table. The OR operation chain table can be understood as a data structure for saving OR connection items in the form of a chain table.
[0039] For example, the original expression is A AND(A OR B), the AND connection item saved in the AND operation chain table is A-(A OR B), and the chain table length is 2. The OR operation chain table is A-B. The original expression is (A OR B OR C) AND(A OR B OR D), the AND operation chain table is (A OR B OR C)-(A OR B OR D), and the chain table length is 2. There are two OR operation chain tables, the OR connection item saved in the first OR operation chain table is A-B-C, the chain table length is 3, and the OR connection item saved in the first OR operation chain table is A-B-D, the chain table length is 3.
[0040] S130, determine the common sub-expression in the AND connection item chain table, and save the common sub-expression in the common sub-expression chain table after determining each common sub-expression, and remove the sub-expression in the AND connection item chain table according to the expression of the AND connection item and the common sub-expression.
[0041] The common sub-expression can be understood as the common part of each AND connection item in the original expression. For an AND connection item, the common sub-expression is connected to the remaining sub-expressions by the OR operator in the AND connection item, or the common sub-expression itself is an AND connection item. For example, the original expression is A AND(A OR B), and A itself in the AND connection item A and A in the AND connection item (A OR B) are common sub-expressions. The common sub-expression chain table can be understood as a data structure for saving common sub-expressions in the form of a chain table.
[0042] The expression of the AND connection item can be an OR expression or not. For example, in A AND(A OR B), the AND connection item (A OR B) is an OR expression, and the AND connection item A is an expression. The sub-expression removed in the AND connection item chain table can be the entire AND connection item as the common sub-expression, or the common sub-expression contained in the AND connection item. The target sub-expression can be understood as the logical operation combination of the part of each AND connection item other than the common sub-expression. In this embodiment, the target sub-expression and the common sub-expression together constitute the target expression.
[0043] Specifically, common sub-expressions of each AND connection item in the AND connection item chain table are determined, and the common sub-expressions are saved in a common sub-expression chain table. The number of the common sub-expressions can be one or more. According to the expression of the AND connection item and the common sub-expressions, the sub-expressions in the AND connection item chain table are removed, and the removed sub-expressions can be determined according to the expression of the AND connection item and the operation logic.
[0044] For example, the common sub-expressions of the original expression A AND(A OR B) are A, and the common sub-expressions of(A OR B OR C) AND(A OR B OR D) are A and B.
[0045] It should be understood that the embodiment of the present application is mainly aimed at the logical operation optimization of the original expression in which the AND connection item contains common sub-expressions, for example, A AND(A OR B) = A, and(A OR B OR C) AND(A OR B OR D) = A OR B OR(C AND D). If no common sub-expressions that are completely the same are found in all the AND connection items, the logical operation optimization cannot be performed by using the logical operation method provided by the embodiment of the present application, and the original expression can not be processed.
[0046] S140, target sub-expressions are constituted according to the remaining sub-expressions in the AND connection item chain table, the common sub-expressions in the common sub-expression chain table and the target sub-expressions are connected by a logical OR operator, and a target expression corresponding to the SQL statement is obtained.
[0047] The target expression can be understood as the expression of the SQL statement after the logical operation optimization.
[0048] Specifically, after the corresponding sub-expressions are removed from the AND connection item chain table according to each common sub-expression, the remaining sub-expressions in the AND connection item chain table are obtained. It should be understood that the remaining sub-expressions can exist or not exist in the AND connection item chain table. If the remaining sub-expressions do not exist in the AND connection item chain table, the target sub-expressions are naturally empty.
[0049] If the target sub-expressions are not empty, the common sub-expressions in the common sub-expression chain table and the target sub-expressions are connected by a logical OR operator, and a target expression corresponding to the SQL statement is obtained. If the target sub-expressions are empty, since the logical OR of an empty set and any non-empty item is the non-empty item, the common sub-expressions are determined as the target expression corresponding to the SQL statement. For example, for the original expression A AND(A OR B), the common sub-expression chain table is A, the target sub-expressions are empty, and the target expression is A.
[0050] Optionally, if the public sub-expression chain list is empty, it indicates that there is no common sub-expression in each AND connection item in the original expression, and in this case, the original expression of the SQL statement can be output as the target expression without optimization based on the logical operation processing method of the embodiment.
[0051] In the embodiment, for the AND predicate (i.e. the AND connection item) with the common sub-expression, it can be known from the inverse operation of the distribution law of the logical operation that the common sub-expression can be extracted to the outer layer of the AND operation, that is, the existence of the partial repeated statement in the AND predicate of the SQL statement can only be operated once, and in some cases, the final result can be determined without the logical operation with other condition judgments again, but due to the redundant writing of the condition statement, the unnecessary operation is performed multiple times.
[0052] For example, the original expression is (C1=C2 OR C2=C3) AND (C1=C2 OR C3=C4), and it can be seen from the execution plan that complex logical operations are performed in the condition filtering process:
[0053] DROP TABLE T1;
[0054] CREATE TABLE T1(C1 INT,C2 INT,C3 INT,C4 INT);
[0055] EXPLAIN SELECT*FROM T1 WHERE(C1=C2 OR C2=C3)AND(C1=C2OR C3=C4);
[0056] 1#NSET2:[1,1,28]
[0057] 2 #PRJT2:[1,1,28];exp_num(5),is_atom(FALSE)
[0058] 3 #SLCT2:[1,1,28];(T1.C1=T1.C2 OR(T1.C1=T1.C2 AND T1.C3=T1.C4)OR(T1.C2=T1.C3 AND T1.C1=T1.C2)OR(T1.C2=T1.C3AND T1.C3=T1.C4))
[0059] 4#CSCN2:[1,1,28];INDEX33555474(T1);btr_scan(1)
[0060] However, in fact, both of the two AND connection items (C1=C2 OR C2=C3) and (C1=C2 OR C3=C4) of the original expression are OR operations, and a common sub-expression (C1=C2) is included in them, according to the standard of the distributive law of logical algebra, the common item can be extracted, that is, (A OR B) AND (A OR C) => A OR (B AND C) to remove unnecessary logical calculation, and the target expression after logical operation processing is (C1=C2) OR (C2=C3 AND C3=C4), compared with the original expression, one redundant condition judgment is removed, and when A is true, the results of B and C can be ignored, which makes the SQL statement more efficient in the process of data screening.
[0061] The technical scheme of the embodiment of the application comprises the following steps: obtaining an original expression corresponding to a SQL statement; the original expression comprises AND connection items connected by an AND operator and an AND operator; saving the AND connection items in an AND connection item linked list; determining common sub-expressions in the AND connection item linked list, and saving the common sub-expressions in a common sub-expression linked list after determining each common sub-expression; removing sub-expressions in the AND connection item linked list according to the expressions of the AND connection items and the common sub-expressions; constructing target sub-expressions according to the remaining sub-expressions in the AND connection item linked list, and connecting the common sub-expressions in the common sub-expression linked list and the target sub-expressions by an OR operator to obtain a target expression corresponding to the SQL statement. By extracting the common sub-expressions of the AND predicates in the original expression for logical operation optimization, the screening conditions can be simplified, and the query efficiency of the SQL statement can be improved.
[0062] Embodiment two
[0063] Figure 2 A flowchart of a logical operation processing method of a SQL statement is provided for the second embodiment of the application, and the embodiment further refines S130 of the above-mentioned embodiment.
[0064] As shown in Figure 2 , the method comprises the following steps.
[0065] S210, obtaining an original expression corresponding to a SQL statement; the original expression comprises AND connection items connected by an AND operator and an AND operator.
[0066] The AND connection item can comprise an OR operator and an OR connection item connected by the OR operator.
[0067] S220, saving the AND connection items in an AND connection item linked list, and saving the OR connection items in the AND connection items in a corresponding OR connection item linked list.
[0068] Specifically, an AND connection item chain table is created, and the AND connection items connected by AND operators in the original expression are saved in the AND connection item chain table. For each AND connection item, if the AND connection item includes OR operators and OR connection items connected by OR operators, a corresponding OR connection item chain table is created, and the OR connection items in the AND connection item are saved in the corresponding OR connection item chain table.
[0069] For example, the original expression is A AND(A OR B), the AND operation chain table is A-(A OR B), and the OR connection items saved in the OR connection item chain table corresponding to the second AND connection item are A-B. The first AND connection item is not an OR expression, and there is no need to construct an OR connection item chain table.
[0070] The original expression is (A OR B OR C) AND(A OR B OR D), the AND connection item chain table is (A OR B OR C)-(A OR B OR D), the OR connection items saved in the OR connection item chain table corresponding to the first AND connection item are A-B-C, and the OR connection items saved in the OR connection item chain table corresponding to the second AND connection item are A-B-D.
[0071] S230, the first AND connection item in the AND connection item chain table is obtained.
[0072] For example, for the original expression (A OR B OR C) AND(A OR B OR D), the first AND connection item (A OR B OR C) in the AND connection item chain table (A OR B OR C)-(A OR B OR D) is obtained. For the original expression A AND(A OR B), the first AND connection item is A.
[0073] S240, if the first AND connection item is not an OR expression and all AND connection items in the AND connection item chain table include the first AND connection item, the first AND connection item is determined as a common sub-expression; the common sub-expression is saved in a common sub-expression chain table, and the sub-expressions in the AND connection item chain table are removed according to the expression of the AND connection item and the common sub-expression.
[0074] It should be understood that if the first AND connection item in the original expression is not an OR expression and there is at least one AND connection item in the AND connection item chain table that does not include the first AND connection item, it means that there is no common sub-expression in the AND connection item chain table, and the optimization processing of logical operation cannot be performed for this kind of original expression.
[0075] For example, for the original expression A AND(A OR B), the first AND connection item is A, which is not an OR expression. All AND connection items A and (A OR B) in the AND connection item chain list contain A, so the first AND connection item A is determined as a common sub-expression. The common sub-expression is saved in the common sub-expression chain list in the form of a chain list, and the corresponding sub-expressions are removed from the AND connection item chain list according to the expression of the AND connection item and the common sub-expression.
[0076] As an optional embodiment, all AND connection items in the AND connection item chain list contain the first AND connection item, including:
[0077] S241, for the AND connection item in the AND connection item chain list except the first AND connection item, if the AND connection item is not an OR expression and the AND connection item is the same as the first AND connection item, it is determined that the AND connection item contains the first AND connection item.
[0078] S242, if the AND connection item is an OR expression, and any OR connection item in the OR connection item chain list corresponding to the AND connection item is the same as the first AND connection item, it is determined that the AND connection item contains the first AND connection item.
[0079] Specifically, the first AND connection item in the AND connection item chain list does not need to be judged. For the AND connection item in the AND connection item chain list except the first AND connection item, it is judged whether the AND connection item contains the first AND connection item, which needs to judge whether the AND connection item is an OR expression; if the AND connection item is not an OR expression, it is considered to contain only when the AND connection item is the same as the first AND connection item; if the AND connection item is an OR expression, it is considered to contain when any OR connection item in the OR expression chain list is the same as the first AND connection item.
[0080] For example, for the original expression A AND(A OR B), the first AND connection item is A, and if the judged AND connection item is (A OR B), there is an OR connection item A in the OR connection item chain list A-B which is the same as the first AND connection item, it is considered that the AND connection item contains the first AND connection item. If the AND connection item is (NOT A), which is not an OR expression, and is not the same as the first AND connection item, it is considered that the AND connection item does not contain the first AND connection item.
[0081] As another optional embodiment, the sub-expressions in the AND-connector list are removed according to the expression of the AND-connector and the common sub-expressions, including:
[0082] All the AND-connectors containing the common sub-expressions in the AND-connector list are removed.
[0083] Specifically, if the first AND-connector is not an OR expression and all the AND-connectors in the AND-connector list contain the first AND-connector, it is certain that there is an AND-connector that is not an OR expression in the AND-connector list, and all the AND-connectors containing the common sub-expressions in the AND-connector list can be removed directly, at this time the AND-connector list is empty. In order to reduce the calculation, the AND-connector list can be directly set to empty.
[0084] For example, for the original expression A AND(A OR B), the common sub-expression is A, and the AND-connector list is A-(A OR B). The first AND-connector A in the AND-connector list is not an OR expression, so the AND-connector (A OR B) and A containing the common sub-expression A are removed from each AND-connector in the AND-connector list, at this time there is no remaining sub-expression in the AND-connector list, or the AND-connector list is directly set to empty.
[0085] S250, if the first AND-connector is an OR expression, the first OR-connector in the OR-connector list corresponding to the first AND-connector is obtained as the current OR-connector; it is judged whether all the AND-connectors in the AND-connector list contain the current OR-connector; if yes, the current OR-connector is determined as the common sub-expression; the common sub-expression is saved in the common sub-expression list, and the sub-expressions in the AND-connector list are removed according to the expression of the AND-connector and the common sub-expressions; if no, or after removing the sub-expressions in the AND-connector list, the next OR-connector in the OR-connector list corresponding to the first AND-connector is obtained as the current OR-connector and the related steps of judging whether all the AND-connectors in the AND-connector list contain the current OR-connector are returned, until all the OR-connectors in the OR-connector list are traversed.
[0086] Specifically, if the first AND connection item is an OR expression, the first OR connection item in the OR connection item chain corresponding to the first AND connection item is obtained as the current OR connection item. It is determined whether all AND connection items in the AND connection item chain contain the current OR connection item; if so, the current OR connection item is determined as a common sub-expression, and the common sub-expression is saved in the common sub-expression chain. The sub-expressions in the AND connection item chain are removed according to the expression of the AND connection item and the common sub-expression, and the extraction of the common sub-expression is realized. Then, the next OR connection item in the OR connection item chain corresponding to the first AND connection item is obtained as the current OR connection item, and it is determined whether all AND connection items in the AND connection item chain contain the current OR connection item, and whether the next OR connection item is a common sub-expression.
[0087] If not, it indicates that the current OR connection item is not a common sub-expression, and no operation needs to be performed on the common sub-expression chain and the AND connection item chain. The next OR connection item in the OR connection item chain corresponding to the first AND connection item is obtained as the current OR connection item, and it is determined whether all AND connection items in the AND connection item chain contain the current OR connection item, and whether the next OR connection item is a common sub-expression.
[0088] Until all OR connection items in the OR connection item chain are traversed, the process of determining the common sub-expression is ended.
[0089] As an optional embodiment, all AND connection items in the AND connection item chain contain the current OR connection item, including:
[0090] For the rest of the AND connection items in the AND connection item chain except the first AND connection item, if the AND connection item is not an OR expression and is the same as the current OR connection item, it is determined that the AND connection item contains the current OR connection item.
[0091] If the AND connection item is an OR expression, and any OR connection item in the OR connection item chain corresponding to the AND connection item is the same as the current OR connection item, it is determined that the AND connection item contains the current OR connection item.
[0092] Specifically, the first AND connection item in the AND connection item chain list does not need to be judged. For the rest of the AND connection items in the AND connection item chain list except the first AND connection item, it is judged whether the AND connection item contains the current OR connection item, it is needed to judge whether the AND connection item is an OR expression; if the AND connection item is not an OR expression, it is considered to contain only when the AND connection item is the same as the current OR connection item; if the AND connection item is an OR expression, it is considered to contain when any OR connection item in the OR connection item chain list is the same as the current OR connection item. As another optional embodiment, the step of removing the sub-expressions in the AND connection item chain list according to the expression of the AND connection item and the common sub-expressions comprises:
[0093] S251, if all the AND connection items in the AND connection item chain list are OR expressions, removing the common sub-expressions from each AND connection item in the AND connection item chain list.
[0094] For example, for the original expression (A OR B OR C) AND (A OR B OR D), the AND connection item chain list is (A OR B OR C)-(A OR B OR D). The first AND connection item (A OR B OR C) is an OR expression, so the first OR connection item A in the OR connection item chain list A-B-C) corresponding to the first AND connection item (A OR B OR C) is obtained. First, it is judged that all the AND connection items (A OR B OR C) and (A OR B OR D) in the AND connection item chain list (A OR B OR C)-(A OR B OR D) contain the first OR connection item A, so the first OR connection item A is determined as a common sub-expression. Then the common sub-expression A is saved in the common sub-expression chain list, and the common sub-expression A in the AND connection item chain list (A OR B OR C)-(A OR B OR D) is removed, at this time the common sub-expression chain list is A, and the AND connection item chain list is (B OR C)-(B OR D). The next common sub-expression B is determined, the common sub-expression B is saved in the common sub-expression chain list, and the common sub-expression B in the AND connection item chain list (B OR C)-(B OR D) is removed, at this time the common sub-expression chain list is A-B, and the AND connection item chain list is C-D.
[0095] The remaining sub-expressions C and D in the AND connection item chain list are connected by the AND operator to obtain the target sub-expression (C AND D).
[0096] S252, if there is an AND connective in the AND connective chain list which is not an OR expression, all AND connectives in the AND connective chain list containing the common sub-expression are removed.
[0097] Specifically, if there is at least one AND connective in the AND connective chain list which is not an OR expression, the AND connective which is not an OR expression must be the same as the common sub-expression, and thus all AND connectives in the AND connective chain list containing the common sub-expression can be removed, and the AND connective chain list is empty. In order to reduce the calculation, the AND connective chain list can be directly set to empty.
[0098] For example, for the original expression (A OR B) AND A, the AND connective chain list is (A OR B) - A, there is an AND connective A in the AND connective chain list which is not an OR expression, the common sub-expression is A, and thus all AND connectives in the AND connective chain list containing the common sub-expression can be removed or the AND connective chain list can be directly set to empty.
[0099] S260, a target sub-expression is formed according to the remaining sub-expressions in the AND connective chain list, and the common sub-expressions in the common sub-expression chain list and the target sub-expression are connected by an OR operator to obtain a target expression corresponding to the SQL statement.
[0100] As an optional embodiment, forming a target sub-expression according to the remaining sub-expressions in the AND connective chain list comprises: if there is a remaining sub-expression in the AND connective chain list, connecting the remaining sub-expressions in the AND connective chain list by an AND operator to obtain the target sub-expression; and if there is no remaining sub-expression in the AND connective chain list, determining that the target sub-expression is empty. For example, for the original expression (A OR B OR C) AND (A OR B OR D), after determining the common sub-expressions A and B, the common sub-expressions A and B are removed from the first AND connective (A OR B OR C), and the remaining sub-expression in the AND connective chain list is C. The common sub-expressions A and B are removed from the second AND connective (A OR B OR D), and the remaining sub-expression in the AND connective chain list is D. Thus, the target sub-expression is C AND D, the common sub-expressions A and B in the common sub-expression chain list and the target sub-expression (C AND D) are connected by an OR operator to obtain a target expression A OR B OR (C AND D).
[0101] For the original expression A AND(A OR B), the common sub-expression chain table is A, all AND connection items A and (A OR B) containing the common sub-expression A in the AND connection item chain table are removed, or the AND connection item chain table is directly empty, at this time, the remaining sub-expressions in the AND connection item chain table are empty, and therefore the target sub-expression is empty Since the logical or of an empty set and any non-empty item is the non-empty item, The target expression obtained is A.
[0102] The technical scheme of the embodiment of the application comprises the following steps: obtaining an original expression corresponding to a SQL statement; the original expression comprises an AND operator and an AND connection item connected by the AND operator; saving the AND connection item in an AND connection item chain table and saving an OR connection item in the AND connection item in a corresponding OR connection item chain table; obtaining a first AND connection item in the AND connection item chain table; if the first AND connection item is not an OR expression and all AND connection items in the AND connection item chain table contain the first AND connection item, the first AND connection item is determined as a common sub-expression; saving the common sub-expression in a common sub-expression chain table and removing sub-expressions in the AND connection item chain table according to the expression of the AND connection item and the common sub-expression; if the first AND connection item is an OR expression, obtaining a first OR connection item in an OR connection item chain table corresponding to the first AND connection item as a current OR connection item; if all AND connection items in the AND connection item chain table contain the current OR connection item, the current OR connection item is determined as a common sub-expression; saving the common sub-expression in the common sub-expression chain table and removing sub-expressions in the AND connection item chain table according to the expression of the AND connection item and the common sub-expression; returning to the step of obtaining the first OR connection item in the OR connection item chain table corresponding to the first AND connection item as the current OR connection item until all OR connection items in the OR connection item chain table are traversed; and obtaining a target sub-expression according to the remaining AND connection items in the AND connection item chain table, connecting the common sub-expressions in the common sub-expression chain table and the target sub-expression by an OR operator, and obtaining a target expression corresponding to the SQL statement. By extracting the common sub-expressions of AND predicates in the original expression for logical operation optimization, the screening condition can be simplified and the SQL statement query efficiency can be improved.
[0103] Embodiment three
[0104] Figure 3 A structural schematic diagram of a logical operation processing device for a SQL statement provided by the third embodiment of the application is shown in FIG. 3. Figure 3As shown, the device comprises: an original expression obtaining module 310, a first chain table saving module 320, a common sub-expression processing module 330 and a target expression determining module 340; wherein:
[0105] The original expression obtaining module 310 is configured to obtain an original expression corresponding to an SQL statement; the original expression comprises an AND operator and an AND connection item connected by the AND operator;
[0106] The first chain table saving module 320 is configured to save the AND connection item in an AND connection item chain table;
[0107] The common sub-expression processing module 330 is configured to determine a common sub-expression in the AND connection item chain table, save the common sub-expression in a common sub-expression chain table after determining each common sub-expression, and remove a sub-expression in the AND connection item chain table according to an expression of the AND connection item and the common sub-expression;
[0108] The target expression determining module 340 is configured to constitute a target sub-expression according to a remaining sub-expression in the AND connection item chain table, connect common sub-expressions in the common sub-expression chain table and the target sub-expression by an OR operator, and obtain a target expression corresponding to the SQL statement.
[0109] Optionally, at least one of the AND connection items comprises an OR operator and an OR connection item connected by the OR operator, and the device further comprises:
[0110] A second chain table saving module is configured to save the OR connection item in the AND connection item in a corresponding OR connection item chain table.
[0111] Optionally, the common sub-expression processing module 330 comprises:
[0112] An AND connection item obtaining unit is configured to obtain a first AND connection item in the AND connection item chain table;
[0113] A first common sub-expression determining unit is configured to determine the first AND connection item as a common sub-expression if the first AND connection item is not an OR expression and all AND connection items in the AND connection item chain table contain the first AND connection item;
[0114] A first common sub-expression processing unit is configured to save the common sub-expression in a common sub-expression chain table and remove a sub-expression in the AND connection item chain table according to an expression of the AND connection item and the common sub-expression.
[0115] Optionally, all AND connection items in the AND connection item chain table contain the first AND connection item, including:
[0116] For the rest of the AND connection items in the AND connection item chain table except the first AND connection item, if the AND connection item is not an OR expression and is the same as the first AND connection item, it is determined that the AND connection item contains the first AND connection item.
[0117] If the AND connection item is an OR expression, and any OR connection item in the OR connection item chain table corresponding to the AND connection item is the same as the first AND connection item, it is determined that the AND connection item contains the first AND connection item.
[0118] Optionally, the common sub-expression processing module 330 comprises:
[0119] an AND connection item acquisition unit, configured to acquire a first AND connection item in the AND connection item chain table;
[0120] a current OR connection item determination unit, configured to, if the first AND connection item is an OR expression, acquire a first OR connection item in an OR connection item chain table corresponding to the first AND connection item as a current OR connection item;
[0121] a judgment unit, configured to judge whether all AND connection items in the AND connection item chain table contain the current OR connection item;
[0122] a second common sub-expression determination unit, configured to, if yes, determine the current OR connection item as a common sub-expression, save the common sub-expression in a common sub-expression chain table, and remove sub-expressions in the AND connection item chain table according to expressions of the AND connection items and the common sub-expression;
[0123] a loop execution unit, configured to, if no or after removing the sub-expressions in the AND connection item chain table, acquire a next OR connection item in the OR connection item chain table corresponding to the first AND connection item as the current OR connection item, and return to the step of judging whether all AND connection items in the AND connection item chain table contain the current OR connection item, until all OR connection items in the OR connection item chain table are traversed.
[0124] Optionally, all AND connection items in the AND connection item chain table contain the current OR connection item, including:
[0125] If the AND connection item is not an OR expression and is the same as the current OR connection item, it is determined that the AND connection item contains the current OR connection item.
[0126] If the AND connection item is an OR expression and any OR connection item in the OR connection item chain list is the same as the current OR connection item, it is determined that the AND connection item contains the current OR connection item.
[0127] Optionally, the first common sub-expression processing unit and / or the second common sub-expression processing unit are specifically used for:
[0128] If all the AND connection items in the AND connection item chain list are OR expressions, the common sub-expressions are removed from each AND connection item in the AND connection item chain list.
[0129] If there is an AND connection item that is not an OR expression in the AND connection item chain list, all the AND connection items containing the common sub-expressions in the AND connection item chain list are removed.
[0130] Optionally, the target expression determination module 340 comprises:
[0131] The first target sub-expression determination unit is used for connecting the remaining sub-expressions in the AND connection item chain list with an AND operator to obtain a target sub-expression if there are remaining sub-expressions in the AND connection item chain list.
[0132] The second target sub-expression determination unit is used for determining that the target sub-expression is empty if there are no remaining sub-expressions in the AND connection item chain list.
[0133] The SQL statement logical operation processing device provided by the embodiment of the application can execute the SQL statement logical operation processing method provided by any embodiment of the application, and has the corresponding function modules and beneficial effects of the execution method.
[0134] Embodiment four
[0135] Figure 4A structural diagram of an electronic device 10 that can be used to implement embodiments of the present application is shown. The electronic device is intended to represent various forms of digital computers, such as laptops, desktops, tablets, personal digital assistants, servers, blade servers, mainframes, and other appropriate computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular telephones, smartphones, wearable devices (e.g., headsets, glasses, watches, etc.), and other similar computing devices. The components shown here, their connections and relationships, and their functions, are meant to be examples only, and are not meant to limit implementations of the present application described and / or claimed in this document.
[0136] As shown in Figure 4 The electronic device 10 includes at least one processor 11, and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., connected in communication with the at least one processor 11, where the memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer programs stored in the read-only memory (ROM) 12 or loaded into the random access memory (RAM) 13 from the storage unit 18. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0137] Various components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc., an output unit 17, such as various types of displays, a speaker, etc., a storage unit 18, such as a magnetic disk, an optical disk, etc., and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network, such as the Internet, and / or various telecommunication networks.
[0138] The processor 11 can be various general and / or special purpose processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 performs various methods and processes described above, such as the logical operation processing method of SQL statements.
[0139] In some embodiments, the method for logical operation processing of SQL statements can be implemented as a computer program tangibly embodied in a computer readable storage medium, such as storage unit 18. In some embodiments, parts or all of the computer program can be loaded and / or installed onto electronic device 10 via, for example, ROM 12 and / or communication unit 19. When the computer program is loaded onto RAM 13 and executed by processor 11, one or more steps of the above-described method for logical operation processing of SQL statements can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the method for logical operation processing of SQL statements by way of other any suitable means, such as by way of firmware.
[0140] Various implementations of the systems and techniques described above can be realized in digital electronic circuitry, integrated circuitry, specially designed application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include implementation in one or more computer programs that are executable and / or interpretable on a programmable system including at least one programmable processor, which can be special or general purpose, coupled to receive data and instructions from, and to transmit data and instructions to, a storage system, at least one input device, and at least one output device.
[0141] In some embodiments, the method for logical operation processing of SQL statements can be implemented as a computer program tangibly embodied in a computer program product, the computer program implementing the method for logical operation processing of SQL statements of the present application when executed by a processor, the computer program product can be understood as a software product that mainly realizes the solution of the present application by means of the computer program. The computer program for implementing the method of the present application can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the computer program, when executed by the processor, implements the functions / operations specified in the flow charts and / or the block diagrams. The computer program can be executed entirely on a machine, partially on a machine, partially on a machine as a separate software package and partially on a remote machine, or entirely on a remote machine or server.
[0142] In the context of the present application, a computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. A computer-readable storage medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium can be a machine-readable signal medium. More specific examples of a machine-readable storage medium will include one or more lines of a program of instructions in a transitory signal, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0143] To provide for interaction with a user, the systems and techniques described here can be implemented on an electronic device having a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can be used to provide for interaction with a user as well; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form, including acoustic, speech, or tactile input.
[0144] The systems and techniques described here can be implemented in a computing system that includes a back end component (e.g., as a data server), or that includes a middleware component (e.g., an application server), or that includes a front end component (e.g., a user computer having a graphical user interface or a Web browser through which a user can interact with an implementation of the systems and techniques described here), or any combination of such back end, middleware, or front end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.
[0145] The computing system can include clients and servers. A client and server are generally remote from each other and typically interact through a communication network. The relationship of client and server arises by virtue of computer programs running on the respective computers and having a client-server relationship to each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system, to solve the defects of large management difficulty and weak business scalability in traditional physical host and VPS service.
[0146] It should be understood that the various forms of flow shown above can be used to reorder, add or delete steps. For example, each step described in the present application can be executed in parallel, sequentially or in a different order, as long as the desired results of the technical solutions of the present application can be achieved, which is not limited herein.
[0147] The above detailed description does not constitute a limitation on the scope of protection of the present application. Those skilled in the art should understand that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent replacements and improvements made within the spirit and principles of the present application shall be included in the scope of protection of the present application.
Claims
1. A method for processing logical operations on SQL statements, characterized in that, include: Obtain the original expression corresponding to the SQL statement; the original expression includes the logical AND operator and the AND concatenation terms connected by the AND operator. Store the AND connection item in the AND connection necklace table; Determine the common subexpression in the AND join necklace list, and after determining each common subexpression, store the common subexpression in the common subexpression linked list, and remove the subexpression in the AND join necklace list according to the expression of the AND join item and the common subexpression; The target subexpression is constructed by combining the remaining subexpressions in the AND chain list, and then the common subexpressions in the common subexpression chain list and the target subexpression are connected by the logical OR operator to obtain the target expression corresponding to the SQL statement.
2. The method according to claim 1, characterized in that, At least one of the AND concatenation terms includes an OR operator and an OR concatenation term joined by the OR operator, and the method further includes: The OR join entry in the AND join entry is stored in the corresponding OR join necklace table.
3. The method according to claim 2, characterized in that, The step of determining the common sub-expressions in the AND join chain list, and after determining each common sub-expression, storing the common sub-expressions in a common sub-expression linked list, and removing sub-expressions from the AND join chain list based on the expression of the AND join item and the common sub-expressions, includes: Retrieve the first AND join item in the AND join necklace table; If the first AND concatenation item is not an OR expression and all AND concatenation items in the AND concatenation necklace table contain the first AND concatenation item, then the first AND concatenation item is determined as a common subexpression. The common subexpression is stored in a common subexpression linked list, and the subexpression in the AND linked list is removed according to the expression of the AND concatenation item and the common subexpression.
4. The method according to claim 3, characterized in that, All AND join entries in the AND join necklace table include the first AND join entry, including: For the remaining AND entries in the AND chain table other than the first AND entry, if the AND entry is not an OR expression and is the same as the first AND entry, then it is determined that the AND entry contains the first AND entry. If the AND concatenation term is an OR expression, and any OR concatenation term in the OR concatenation necklace table corresponding to the AND concatenation term is the same as the first AND concatenation term, then the AND concatenation term is determined to contain the first AND concatenation term.
5. The method according to claim 2, characterized in that, The step of determining the common sub-expressions in the AND join chain list, and after determining each common sub-expression, storing the common sub-expressions in a common sub-expression linked list, and removing sub-expressions from the AND join chain list based on the expression of the AND join item and the common sub-expressions, includes: Retrieve the first AND join item in the AND join necklace table; If the first AND concatenation item is an OR expression, then the first OR concatenation item in the OR concatenation chain table corresponding to the first AND concatenation item is obtained as the current OR concatenation item; Determine whether all AND join items in the AND join necklace table contain the current OR join item; If so, the current OR concatenation term is identified as a common subexpression; the common subexpression is stored in a common subexpression linked list, and the subexpression in the AND concatenation linked list is removed according to the expression of the AND concatenation term and the common subexpression; If no, or after removing the subexpression in the AND chain table, obtain the next OR chain item in the OR chain table corresponding to the first AND chain item as the current OR chain item, and return the relevant steps for determining whether all AND chain items in the AND chain table contain the current OR chain item, until all OR chain items in the OR chain table are traversed.
6. The method according to claim 5, characterized in that, All AND join entries in the AND join necklace table contain the current OR join entry, including: For the remaining AND entries in the AND chain table other than the first AND entry, if the AND entry is not an OR expression and is the same as the current OR entry, then the AND entry contains the current OR entry. If the AND concatenation term is an OR expression, and any OR concatenation term in the OR concatenation necklace table corresponding to the AND concatenation term is the same as the current OR concatenation term, then the AND concatenation term contains the current OR concatenation term.
7. The method according to any one of claims 1-6, characterized in that, The step of removing sub-expressions from the AND join necklace table based on the expression of the AND join item and the common sub-expression includes: If all AND join items in the AND join necklace table are OR expressions, then remove the common subexpression from each AND join item in the AND join necklace table; If the AND join necklace table contains an AND join item that is not an OR expression, then all AND join items in the AND join necklace table that contain the common subexpression will be removed.
8. The method according to claim 1, characterized in that, The step of constructing the target subexpression based on the remaining subexpressions in the AND chain table includes: If there are remaining subexpressions in the AND join chain, then the remaining subexpressions in the AND join chain are joined together using the AND operator to obtain the target subexpression; If there are no remaining subexpressions in the AND chain table, then the target subexpression is determined to be empty.
9. A logical operation processing device for SQL statements, characterized in that, include: The raw expression retrieval module is used to retrieve the raw expression corresponding to the SQL statement; the raw expression includes logical AND operators and AND concatenation terms connected by AND operators. The first linked list storage module is used to store the AND connection item in the AND connection linked list; A common subexpression processing module is used to determine the common subexpressions in the AND connection necklace list, and after determining each common subexpression, store the common subexpression in the common subexpression linked list, and remove the subexpressions in the AND connection necklace list according to the expression of the AND connection item and the common subexpression; The target expression determination module is used to construct a target sub-expression based on the remaining sub-expressions in the AND connection chain table, and to connect the common sub-expressions in the common sub-expression chain table and the target sub-expression using a logical OR operator to obtain the target expression corresponding to the SQL statement.
10. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to perform the logical operation processing method of the SQL statement according to any one of claims 1-8.
11. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that are used to cause a processor to execute the logical operation processing method of the SQL statement according to any one of claims 1-8.
12. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the logical operation processing method for SQL statements according to any one of claims 1-8.