CN109656947B - Data query method and device, computer equipment and storage medium - Google Patents
Data query method and device, computer equipment and storage medium Download PDFInfo
- Publication number
- CN109656947B CN109656947B CN201811330716.2A CN201811330716A CN109656947B CN 109656947 B CN109656947 B CN 109656947B CN 201811330716 A CN201811330716 A CN 201811330716A CN 109656947 B CN109656947 B CN 109656947B
- Authority
- CN
- China
- Prior art keywords
- association
- attributes
- attribute
- main
- sql statement
- 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
Abstract
The application relates to a data query method, a data query device, computer equipment and a storage medium. The method comprises the steps of obtaining an original SQL statement, analyzing the original SQL statement to obtain a table set with associated attributes, selecting a table to be processed from the table set, carrying out replacement processing on the associated attributes in the table to be processed, removing query on the table to be processed from the original SQL statement when detecting that the attributes except the associated attributes in the table to be processed after the replacement processing are not used in the original SQL statement, so as to reduce redundant query on the table, and obtaining a first SQL statement when detecting that the tables which are not selected do not exist in the table set.
Description
Technical Field
The present application relates to the field of database operations, and in particular, to a data query method, apparatus, computer device, and storage medium.
Background
With the development of database operation technology, SQL (Structured Query Language) appears, which is a Structured Query Language for accessing a database, and is widely applied to database operation of various application programs because SQL integrates data Query, data manipulation, data definition, and data control functions, and has great flexibility and good portability.
However, when there are multiple data tables in the database, especially when the relationship between the data in the enterprise application system is tight, accessing one form often requires querying the data of multiple database tables, i.e. in the SQL query, multiple tables need to be queried in association, and the query efficiency is low.
Disclosure of Invention
In view of the above, it is necessary to provide a data query method, an apparatus, a computer device, and a storage medium capable of improving query efficiency in response to the problem of low efficiency of SQL data query.
A method of data query, the method comprising:
acquiring an original SQL statement, and analyzing the original SQL statement to obtain a table set with associated attributes;
selecting a table to be processed from the table set, and performing replacement processing on the associated attributes in the table to be processed;
when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement;
and when the table set is detected to have no unselected table, obtaining a first SQL statement.
In one embodiment, the table to be processed is a main table or an association table; the step of performing replacement processing on the associated attributes in the table to be processed includes:
when the table to be processed is the main table, replacing the association attribute of the main table with the association attribute of the association table corresponding to the main table;
and when the table to be processed is the association table, replacing the association attribute of the association table with the association attribute of the parent table corresponding to the association table.
In one embodiment, the method further comprises:
and transferring the ordering attribute in the first SQL statement to the same table to obtain a second SQL statement after transferring the ordering attribute.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table comprises:
replacing a primary key of an association table with an ordering attribute in the first SQL statement with a foreign key of the association table in the primary table to transfer the ordering attribute to the primary table.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table further comprises:
when the primary key of the association table cannot be replaced by the external key of the primary table, acquiring a first association table, replacing the external key of the first association table in the primary table by the primary key of the first association table, and replacing the primary key of a second association table by the external key of the second association table in the first association table, so as to transfer the sorting attribute to the first association table;
the master table is a parent table corresponding to the first association table, and the first association table is a parent table corresponding to the second association table.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table further comprises:
and transferring the index identification of the table with the sorting attribute to the same table.
In one embodiment, the table to be processed is selected from the table set, and the associated attributes in the table to be processed are replaced; when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement, including:
selecting a main table from the table set with the associated attributes;
replacing the associated attribute in the main table with the associated attribute of the associated table;
detecting whether the original SQL statement uses the attributes except the associated attributes in the main table;
when detecting that the attributes except the associated attributes in the main table are not used, removing the query of the main table, taking the associated table corresponding to the main table as a new main table, and returning to execute the step of replacing the associated attributes in the main table with the associated attributes of the associated table;
when detecting that the attributes except the associated attributes in the main table are used, selecting a target associated table from the table set;
replacing the associated attribute in the target associated table with the associated attribute of a parent table corresponding to the target associated table;
detecting whether the original SQL statement uses the attributes except the associated attributes in the target association table;
when detecting that the attributes except the associated attributes in the target associated table are not used, removing the query of the target associated table, taking the associated table corresponding to the target associated table as a new target associated table, and returning to execute the operation of replacing the associated attributes in the target associated table with the associated attributes of the parent table corresponding to the target associated table.
A data query device, the device comprising:
the analysis module is used for acquiring an original SQL statement and analyzing the original SQL statement to obtain a table set with associated attributes;
the replacing module is used for selecting a table to be processed from the table set and replacing the associated attributes in the table to be processed;
a removing module, configured to remove, when it is detected that the attribute other than the associated attribute in the to-be-processed table after the replacement processing is not used in the original SQL statement, a query for the to-be-processed table from the original SQL statement;
and the detection module is used for obtaining a first SQL statement when the table set is detected to have no unselected tables.
A computer device comprising a memory storing a computer program and a processor implementing any of the above method steps when executing the computer program.
A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out any of the above-mentioned method steps.
According to the data query method, the data query device, the computer equipment and the storage medium, the original SQL statement is obtained, the original SQL statement is analyzed to obtain the table set with the associated attributes, the table to be processed is selected from the table set, the associated attributes in the table to be processed are subjected to replacement processing, when the fact that the attributes except the associated attributes in the table to be processed after the replacement processing are not used in the original SQL statement is detected, the query on the table to be processed is removed from the original SQL statement, the query on redundant tables is reduced, and the first SQL statement is obtained until the fact that the tables which are not selected do not exist in the table set is detected.
Drawings
FIG. 1 is a diagram of an application environment of a data query method in one embodiment;
FIG. 2 is a flow diagram that illustrates a methodology for querying data in one embodiment;
FIG. 3 is a flowchart illustrating the step of replacing an associated attribute in one embodiment;
FIG. 4 is a flowchart illustrating the step of replacing an associated attribute in one embodiment;
FIG. 5 is a tree diagram of the step of replacing an associated attribute in another embodiment;
FIG. 6 is a block diagram showing the structure of a data query apparatus according to an embodiment;
FIG. 7 is a diagram illustrating an internal structure 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 present application is described in further detail below with reference to the accompanying drawings and embodiments. 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.
The data query method provided by the embodiment of the application can be applied to the application environment shown in fig. 1. The computer device 102 may be a terminal or a server, the terminal may be, but is not limited to, various personal computers, notebook computers, smart phones, tablet computers, and portable wearable devices, and the server may be implemented by an independent server or a server cluster formed by a plurality of servers.
In one embodiment, as shown in fig. 2, a data query method is provided, which is described by taking the method as an example applied to the computer device in fig. 1, and includes the following steps:
The original SQL statement is a statement obtained at the beginning and not subjected to any operation. A table refers to a table of data in a database. The association attribute refers to an attribute for associating between two data tables, for example, there is a student table including a school number, a name and a gender, and there is a score table including a school number, a course number and a score, and the student table and the score table are associated through the school number.
Specifically, the computer device obtains an original SQL statement, analyzes the semantics and syntax of the original SQL statement to determine that one or more of a selected attribute, an associated attribute, an and attribute, a where attribute, a having attribute, a grouping attribute, and a sorting attribute exist in the original SQL statement, and obtains a set of tables in which the associated attribute exists.
And 204, selecting a table to be processed from the table set, and performing replacement processing on the associated attributes in the table to be processed.
Specifically, the computer device arbitrarily selects one table from a table set with the associated attributes as a table to be processed, determines that the table to be processed is a main table or an associated table in the table set, and replaces the corresponding associated attributes according to a determination result.
And step 206, when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement.
Specifically, the computer device selects the tables in the table set one by one as the tables to be processed, performs the associated attribute replacement, and when it is detected that the other attributes except the associated attributes in the tables to be processed after the associated attributes are replaced are not used in the original SQL statement, it indicates that the tables to be processed can be removed, and then removes the query on the tables to be processed from the original SQL statement.
And step 208, when it is detected that the unselected table does not exist in the table set, obtaining a first SQL statement.
The first SQL statement refers to the SQL statement when all the queries of the tables in the table set cannot be removed any more.
Specifically, when the computer device detects that all tables in the table set have been selected, which indicates that none of the tables in the table set can be removed any more at this time, the first SQL statement is output.
In the data query method, the table set with the correlation attributes is obtained by analyzing the original SQL sentences, the table to be processed is selected from the table set and the correlation attributes are replaced, and the query on the table to be processed is removed according to the correlation attributes to obtain the first SQL sentences, so that the lookup of the table during the data query is reduced, and the efficiency of the data query is improved.
In one embodiment, the table to be processed is a main table or an association table; the step of replacing the associated attributes in the table to be processed comprises the following steps:
when the table to be processed is a main table, replacing the association attribute of the main table with the association attribute of the association table corresponding to the main table;
and when the table to be processed is the association table, replacing the association attribute of the association table with the association attribute of the parent table corresponding to the association table.
A primary key (primary key for short) is one or more fields in a table, and its value is used to uniquely identify a record in the table. In a two table relationship, the primary key is used to reference a particular record in one table from the other table. If the common key is the primary key in one relationship, then this common key is referred to as the foreign key of the other relationship, which is a dependent relationship with respect to the integrity of references in the database design. The primary table refers to a table established in a database, in which a primary key exists for association with other tables, and serves as a unique identifier in the primary table. The association table refers to a table in which data between two databases and tables thereof have interdependence and influence relationships.
Specifically, the computer device arbitrarily selects one table from the table set as the table to be processed, and determines whether the table to be processed is the main table or the association table according to the logical relationship of the original SQL statement. When the table to be processed is the main table, the associated attribute of the main table is replaced by the associated attribute of the associated table corresponding to the main table, and when the table to be processed is the associated table, the associated attribute of the associated table is replaced by the associated attribute of the parent table corresponding to the associated table. For example, if the association relationship of the three tables queried by the SQL statement is a → B → C, a is the main table of the SQL statement, a is the main table of B, B is the association table of a, B is the parent table of C, C is the association table of B, and B, C is all called the association table of a. When the table to be processed selected from the table set by the computer equipment is A, replacing the associated attribute of A with the associated attribute of B; when the selected to-be-processed table is B, replacing the correlation attribute of B with the correlation attribute of A; and when the selected to-be-processed table is C, replacing the associated attribute of C with the associated attribute of B. And judging whether the query on the replaced table can be removed or not through the associated attribute of the replaced table so as to save the memory space.
In one embodiment, the data query method further comprises:
and transferring the sorting attribute in the first SQL statement to the same table to obtain a second SQL statement after transferring the sorting attribute.
The sorting attribute refers to an attribute for sorting the result set according to the specified column. The second SQL statement refers to the obtained SQL statement after the first SQL statement transfers the sorting attribute.
Specifically, after obtaining a first SQL statement, the computer device determines a table in the first SQL statement that uses the ordering attribute, and transfers the ordering attribute in the first SQL statement to the same table according to the association attribute between the tables that use the ordering attribute, so as to obtain a second SQL statement.
For example, the first SQL statement is as follows:
Select A.*,B.*
from A
left join B on B.id＝A.bid
Order by A.bid,B.number
as can be seen from the association attributes, b.id is equivalent to a.bid, and they can be replaced with each other, so that A, B two tables with Order by attributes are converted into an ordering containing only B table attributes, as follows:
Select A.*,B.*
from A
left join B on B.id＝A.bid
Order by B.id,B.number
by transferring the sorting attributes to the same table, the query of two or more tables is converted into the query of one table, and the data searching speed is further improved.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table comprises: and replacing the main key of the association table with the ordering attribute in the first SQL statement with the external key of the association table in the main table so as to transfer the ordering attribute to the main table.
Specifically, the computer device obtains an association table with ordering attributes in the first SQL statement and a primary table corresponding to the association table, and replaces a primary key of the association table with an external key of the corresponding primary table, so as to transfer the ordering attributes of the association table to the primary table. By replacing the main key of the association table with the corresponding external key of the main table, the sorting attribute can be transferred to the same table, the table query is reduced, the running speed is increased, and the time for searching the data meeting the conditions is reduced.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table further comprises: when the primary key of the association table cannot be replaced by the external key of the primary table, acquiring the first association table, replacing the external key of the first association table in the primary table by the primary key of the first association table, and replacing the primary key of the second association table by the external key of the second association table in the first association table, so as to transfer the ordering attribute to the first association table;
the main table is a parent table corresponding to the first association table, and the first association table is a parent table corresponding to the second association table.
The first association table is an association table in which the primary key cannot be replaced with the foreign key of the primary table, and the second association table is another association table different from the first association table.
Specifically, when the computer device detects that the primary key of the association table cannot be replaced by the external key of the primary table, the association table is used as the first association table, then the external key of the first association table in the primary table is replaced by the primary key of the computer device, and the primary key of the second association table is replaced by the external key of the computer device in the first association table, so that all the sorting attributes are transferred to the first association table. For example, the SQL statement contains queries on A, B, C tables, where a is the main table, B is the association table of a, and C is the association table of B. The step of transferring the order by attribute in the SQL statement to the same table is as follows:
1) because A is the main table of the SQL statement, the main key of B, C in the order by is preferentially replaced by the foreign key in A, whether all order by fields are on the A object is judged, if the judgment result is yes, the transfer is finished, and if the judgment result is no, 2) is tried.
2) And converting the external key of B in A in order by into the main key of B, converting the main key of C into the external key of C in B, judging whether all order by fields are on the B object, if so, finishing the transfer, and if not, trying 3).
3) And (3) converting the external key of C in B in the order by into the main key of C, judging whether all order by fields are on the C object, and if so, ending the transfer.
By converting the primary key or the foreign key of two or more tables with sorting attributes to the same table, unnecessary table query is reduced, and the memory occupation of a database and the time consumption for searching data are reduced.
In one embodiment, the step of transferring the ordering attribute in the first SQL statement to the same table further comprises: and transferring the index identification of the table with the sorting attribute to the same table.
The index identifier is a separate and physical storage structure for sorting one or more columns of values in a database table, and is a collection of one or more columns of values in a table and a corresponding logical pointer list pointing to data pages in the table for physically identifying the values. For example, there is a fee table with a table name of "expense", in which the item type field name is "category", the number field name is "number", the unit price field name is "price", and the date field name is "date", the requirement is to look up the number of each type of items purchased from the fee table, if the index is built by using two columns of category + number, the data meeting the condition can be directly queried by the index identifier "category + number", and it is not necessary to query all the data in the table one by one.
Specifically, the computer device obtains the table with the sorting attribute in the first SQL statement, and transfers the sorting attribute of the table to the same table according to the association attribute, and the index identifier of the table with the transferred sorting attribute is also transferred to the same table accordingly.
In this embodiment, when all the order By attributes cannot be transferred to the same table, the main key of the association table having the order By attributes in the SQL statement is replaced with the foreign key in the main table as much as possible. Because the use of indexes on the main table is better in the design of the management system (such as forms), even if not all of the indexes can be transferred to the same table, the indexes built on the main table are more numerous, and the indexes in the main table are more likely to be used. The index identification of the table with the sorting attribute is transferred, so that when the data of the table is inquired, the index identification of the table can be directly searched to quickly find the table, the occupation of a disk space is reduced, and the searching speed is improved.
In one embodiment, as shown in fig. 3, a table to be processed is selected from a table set, and the associated attributes in the table to be processed are subjected to replacement processing; when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement, including:
Specifically, the computer device extracts the main table of the original SQL statement from the table set having the association attributes, and replaces the association attributes of the main table with the association attributes of the corresponding association table.
Specifically, the computer device detects whether the main table in the original SQL statement has other attributes besides the associated attributes. When the computer device does not detect that the main table in the original SQL statement has the attributes except the associated attributes, the main table only has the associated attributes, the query of the main table in the original SQL statement is directly removed, the associated table corresponding to the main table is used as a new main table, and the step of replacing the associated attributes in the main table with the associated attributes of the associated table is returned.
For example, there are SQL statements:
Select B.*
from A
left join B on B.id＝A.bid
where A.bid＝？B.number＝？
and analyzing the known main table in the SQL statement as A, B as the association table of A, and B.id is equal to A.bid. And the final output result in the SQL statement is A.bid and B.number, and the SQL statement only uses the join on attribute of the main table A, so that the A.bid can be replaced by B.id, the query to the main table A is removed, the B table is directly queried to obtain the result meeting the condition, and the B table is a new main table at the moment. The query statement after removing the main table a is as follows:
Select B.*
from B
where B.id＝？B.number＝？
The target association table is an association table directly corresponding to the main table.
Specifically, when the computer device detects that the main table in the original SQL statement has other attributes besides the associated attributes, a target associated table corresponding to the main table is selected from the table set.
The parent table is a main table directly corresponding to the target association table.
Specifically, a parent table corresponding to the target association table is determined, and the association attribute of the target association table is replaced with the association attribute of the parent table corresponding to the target association table.
When detecting that the attributes except the associated attributes in the target associated table are used, the target associated table is retained, the associated table corresponding to the target associated table is selected from the table set as a new target associated table, and the step 312 is executed again.
Specifically, the computer device detects whether the association table in the SQL statement has other attributes besides the association attribute. When the computer device does not detect that the association table in the original SQL statement has the attributes except the association attributes, the association table only has the association attributes, the query of the target association table is directly removed, the association table corresponding to the target association table is used as a new target association table, and the execution is returned to replace the association attributes in the target association table with the association attributes of the parent table corresponding to the target association table. For example, there are SQL statements:
Select A.*
from A
left join B on B.id＝A.bid
where B.id＝？
similarly, the SQL statement is analyzed, and only join on attributes exist in the association table B, then b.id can be replaced by a.bid, and the query on the association table B is removed after the replacement, so as to obtain a new SQL statement:
Select A.*
from A
where A.bid＝？
the query of the association table B is removed, the time for searching the table B is saved, the result meeting the conditions is ensured to be searched, and the searching efficiency is improved.
According to the data query method, unnecessary query of the main table is removed by replacing the associated attribute of the main table with the associated attribute of the associated table, unnecessary query of the associated table is removed by replacing the associated attribute of the associated table with the associated attribute of the main table, redundant query of the table is removed, consumption of a database is reduced, and data search efficiency is improved.
In an embodiment, as shown in fig. 4, the computer device may select a target association table d from a table set having association attributes, replace the association attributes in the target association table d with the association attributes of a parent table b corresponding to the target association table d, and then detect whether the original SQL statement uses the attributes of the target association table d except the association attributes; when detecting that the attributes except the associated attributes in the target associated table d are not used, removing the query on the target associated table d, taking the parent table b corresponding to the target associated table d as a new target associated table, and returning to execute the step of replacing the associated attributes in the target associated table with the associated attributes of the corresponding parent table; and when detecting that the attributes except the associated attributes in the target associated table d are used, reserving the target associated table, selecting the parent table b corresponding to the target associated table d from the table set as a new target associated table, and returning to execute the step of replacing the associated attributes in the target associated table with the associated attributes of the corresponding parent table. After d → b → a the tables on the associated route are all selected and processed accordingly, the computer device continues to select the associated route of e → b → a, f → c → a or g → c → a, and selects and processes the tables on the associated route accordingly. In the method of the embodiment, unnecessary query of the association table is removed by replacing the association attribute of the target association table with the association attribute of the corresponding parent table, so that system resources are saved.
In one embodiment, as shown in fig. 5, the data query method includes:
Step 510, when detecting that the attributes except the associated attributes in the main table are not used, removing the query to the main table, taking the associated table corresponding to the main table as a new main table, and returning to execute step 506.
And step 512, when detecting that the attributes except the associated attributes in the main table are used, selecting a target associated table from the table set.
And 514, replacing the associated attribute in the target associated table with the associated attribute of the parent table corresponding to the target associated table.
At step 522, the primary key of the associative table with the ordering attribute in the first SQL statement is replaced with the foreign key of the associative table in the primary table to transfer the ordering attribute to the primary table.
In this embodiment, there are original SQL statements such as:
SELECT A.bizDate,B.number,B.desc,C.billId,C.activeType
FROM A
LEFT JOIN B ON B.bizDate＝A.bizDate
LEFT JOIN C ON C.billId＝B.id and C.activeType＝B.activeType
WHERE C.activeType like？
ORDER BY B.number,A.bizDate
optimizing the original SQL statement according to the steps in this embodiment, it is known that b.bizdate is equal to a.bizdate, c.billid is equal to b.id, and c.activtype is equal to b.activtype, then all queries in the A, C table can be replaced with queries to the B table, and the queries to the A, C table can be removed, and only the B table needs to be queried to obtain a result satisfying the condition, and the optimized SQL statement is obtained as follows:
SELECT B.bizDate,B.number,B.desc,B.id,B.activeType
FROM B
WHERE B.activeType like？
ORDER BY B.number,B.bizDate
according to the data query method, unnecessary query of the table is removed by replacing the correlation attributes of the table, the table with the sequencing attributes is further optimized, the second SQL statement after the sequencing attributes are transferred is obtained, the complex and tedious data query process is converted into a simple and quick search process, the data search efficiency is greatly improved, and unnecessary consumption is avoided.
It should be understood that although the various steps in the flow charts of fig. 1-5 are shown in order as indicated by the arrows, the steps are not necessarily performed in order 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-5 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 performance of the sub-steps or stages is not necessarily sequential, but may be performed in turn or alternating with other steps or at least some of the sub-steps or stages of other steps.
In one embodiment, as shown in fig. 6, there is provided a data query apparatus including: a parsing module 602, a replacement module 604, a removal module 606, and a detection module 608, wherein:
the parsing module 602 is configured to obtain an original SQL statement, parse the original SQL statement, and obtain a table set with associated attributes;
a replacing module 604, configured to select a table to be processed from the table set, and perform replacement processing on the associated attributes in the table to be processed;
a removing module 606, configured to remove, when it is detected that the attribute other than the associated attribute in the to-be-processed table after the replacement processing is not used in the original SQL statement, the query for the to-be-processed table from the original SQL statement;
the detecting module 608 is configured to obtain a first SQL statement when it is detected that there is no unselected table in the table set.
The data query device obtains a table set with associated attributes by analyzing the original SQL sentences, selects a table to be processed from the table set, replaces the associated attributes, removes the query of the table to be processed according to the associated attributes to obtain the first SQL sentences, thereby reducing the search of the table when querying data and improving the efficiency of data query.
In one embodiment, the replacement module 604 is further configured to: judging whether the table to be processed is a main table or an association table; when the table to be processed is a main table, replacing the association attribute of the main table with the association attribute of the association table corresponding to the main table; and when the table to be processed is the association table, replacing the association attribute of the association table with the association attribute of the parent table corresponding to the association table. And judging whether the query on the replaced table can be removed or not through the associated attribute of the replaced table so as to save the memory space.
In one embodiment, the data query apparatus further comprises: and a transfer module. The transfer module is used for transferring the sorting attribute in the first SQL statement to the same table to obtain a second SQL statement after transferring the sorting attribute. By replacing and transferring the sorting attributes to the same table, the query of two or more tables is converted into the query of one table, and the data searching speed is further improved.
In one embodiment, the transfer module is further configured to replace a primary key of the associative table having the ordering attribute in the first SQL statement with a foreign key of the associative table in the primary table to transfer the ordering attribute to the primary table. By replacing the main key of the association table with the corresponding external key of the main table, the sorting attribute can be transferred to the same table, the table query is reduced, the running speed is increased, and the time for searching the data meeting the conditions is reduced.
In one embodiment, the transfer module is further configured to, when there is a case where the primary key of the association table cannot be replaced by the foreign key of the primary table, obtain the first association table, replace the foreign key of the first association table in the primary table with the primary key of the first association table, replace the primary key of the second association table with the foreign key of the second association table in the first association table, and transfer the ordering attribute to the first association table; the main table is a parent table corresponding to the first association table, and the first association table is a parent table corresponding to the second association table. The transfer module converts the main keys or the foreign keys of two or more tables with the sorting attribute to the same table so as to reduce the query of unnecessary tables and reduce the memory occupation of a database and the time consumption for searching data.
In one embodiment, the transfer module is further configured to transfer the index identification of the table with the sorting attribute to the same table. The index identification of the table with the sorting attribute is transferred, so that when the data of the table is inquired, the index identification of the table can be directly searched to quickly find the table, unnecessary inquiry steps are reduced, and the searching speed is improved.
In one embodiment, the replacement module and the removal module are further configured to select a master table from the set of tables having associated attributes; replacing the associated attribute in the main table with the associated attribute of the associated table; detecting whether the original SQL statement uses the attributes except the associated attributes in the main table; when detecting that the attributes except the associated attributes in the main table are not used, removing the query of the main table, taking the associated table corresponding to the main table as a new main table, and returning to execute the step of replacing the associated attributes in the main table with the associated attributes of the associated table; when detecting that the attributes except the associated attributes in the main table are used, selecting a target associated table from the table set; replacing the associated attribute in the target associated table with the associated attribute of a parent table corresponding to the target associated table; detecting whether the original SQL statement uses the attributes except the associated attributes in the target associated table; when detecting that the attributes except the associated attributes in the target associated table are not used, removing the query of the target associated table, taking the associated table corresponding to the target associated table as a new target associated table, and returning to execute the operation of replacing the associated attributes in the target associated table with the associated attributes of the parent table corresponding to the target associated table. According to the data query device, the unnecessary tables are removed by replacing the associated attributes of the tables, the tables with the sorting attributes are further optimized, the second SQL statement after the sorting attributes are transferred is obtained, the complex and tedious data query process is converted into a simple and quick search process, the data search efficiency is greatly improved, and unnecessary consumption is avoided.
For the specific definition of the data query device, reference may be made to the above definition of the data query method, which is not described herein again. The modules in the data query device can be wholly or partially implemented by software, hardware and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, which may be a server, the internal structure of which may be as shown in fig. 7. 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. 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 data query method.
Those skilled in the art will appreciate that the architecture shown in fig. 7 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices 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, which comprises a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the data query method when executing the computer program.
In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which computer program, when being executed by a processor, carries out the steps of the above-mentioned data query method.
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 above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as 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 invention. 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 (10)
1. A method of data query, the method comprising:
acquiring an original SQL statement, and analyzing the original SQL statement to obtain a table set with associated attributes;
selecting a table to be processed from the table set, and performing replacement processing on the associated attributes in the table to be processed; the table to be processed is a main table or an association table;
when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement;
when the table set is detected to have no unselected tables, obtaining a first SQL statement;
replacing a main key of an association table with ordering attributes in the first SQL statement with an external key of the association table in the main table to transfer the ordering attributes to the main table to obtain a second SQL statement after the ordering attributes are transferred;
when the main key of the association table cannot be replaced by the external key of the main table, acquiring a first association table, replacing the external key of the first association table in the main table by the main key of the first association table, and replacing the main key of a second association table by the external key of the second association table in the first association table, so as to transfer the ordering attribute to the first association table, and obtain a second SQL statement after transferring the ordering attribute;
the master table is a parent table corresponding to the first association table, and the first association table is a parent table corresponding to the second association table.
2. The method according to claim 1, wherein the step of performing replacement processing on the associated attribute in the table to be processed comprises:
when the table to be processed is the main table, replacing the association attribute of the main table with the association attribute of the association table corresponding to the main table;
and when the table to be processed is the association table, replacing the association attribute of the association table with the association attribute of the parent table corresponding to the association table.
3. The method of claim 1, further comprising:
and transferring the index identification of the table with the sorting attribute to the same table.
4. The method according to claim 1, wherein the table to be processed is selected from the table set, and the associated attributes in the table to be processed are subjected to replacement processing; when detecting that the attributes except the associated attributes in the to-be-processed table after the replacement processing are not used in the original SQL statement, removing the query of the to-be-processed table from the original SQL statement, including:
selecting a main table from the table set with the associated attributes;
replacing the associated attribute in the main table with the associated attribute of the associated table;
detecting whether the original SQL statement uses the attributes except the associated attributes in the main table;
when detecting that the attributes except the associated attributes in the main table are not used, removing the query of the main table, taking the associated table corresponding to the main table as a new main table, and returning to execute the step of replacing the associated attributes in the main table with the associated attributes of the associated table;
when detecting that the attributes except the associated attributes in the main table are used, selecting a target associated table from the table set;
replacing the associated attribute in the target associated table with the associated attribute of a parent table corresponding to the target associated table;
detecting whether the original SQL statement uses the attributes except the associated attributes in the target association table;
when detecting that the attributes except the associated attributes in the target associated table are not used, removing the query of the target associated table, taking the associated table corresponding to the target associated table as a new target associated table, and returning to execute the operation of replacing the associated attributes in the target associated table with the associated attributes of the parent table corresponding to the target associated table.
5. A data query apparatus, characterized in that the apparatus comprises:
the analysis module is used for acquiring an original SQL statement and analyzing the original SQL statement to obtain a table set with associated attributes;
the replacing module is used for selecting a table to be processed from the table set and replacing the associated attributes in the table to be processed; the table to be processed is a main table or an association table;
a removing module, configured to remove, when it is detected that the attribute other than the associated attribute in the to-be-processed table after the replacement processing is not used in the original SQL statement, a query for the to-be-processed table from the original SQL statement;
the detection module is used for obtaining a first SQL statement when the table set is detected to have no unselected tables;
the transfer module is used for replacing a main key of an association table with the ordering attribute in the first SQL statement with an external key of the association table in the main table so as to transfer the ordering attribute to the main table and obtain a second SQL statement after the ordering attribute is transferred;
the transfer module is further configured to, when there is a case that the primary key of the association table cannot be replaced by the foreign key of the primary table, obtain a first association table, replace the foreign key of the first association table in the primary table by the primary key of the first association table, replace the primary key of a second association table by the foreign key of the second association table in the first association table, so as to transfer the ordering attribute to the first association table, and obtain a second SQL statement after the ordering attribute is transferred;
the master table is a parent table corresponding to the first association table, and the first association table is a parent table corresponding to the second association table.
6. The apparatus according to claim 5, wherein the replacing module is further configured to replace the association attribute of the main table with the association attribute of the association table corresponding to the main table when the table to be processed is the main table; and when the table to be processed is the association table, replacing the association attribute of the association table with the association attribute of the parent table corresponding to the association table.
7. The apparatus of claim 5, wherein the transfer module is further configured to transfer the index identifier of the table with ordering attribute to the same table.
8. The apparatus of claim 5, wherein the replacement module is further configured to select a master table from the set of tables having associated attributes; replacing the associated attribute in the main table with the associated attribute of the associated table; detecting whether the original SQL statement uses the attributes except the associated attributes in the main table; when detecting that the attributes except the associated attributes in the main table are not used, removing the query of the main table, taking the associated table corresponding to the main table as a new main table, and returning to execute the step of replacing the associated attributes in the main table with the associated attributes of the associated table; when detecting that the attributes except the associated attributes in the main table are used, selecting a target associated table from the table set; replacing the associated attribute in the target associated table with the associated attribute of a parent table corresponding to the target associated table; detecting whether the original SQL statement uses the attributes except the associated attributes in the target association table;
the removing module is further configured to remove the query to the target association table when detecting that the attributes except the association attribute in the target association table are not used, take the association table corresponding to the target association table as a new target association table, and return to execute the replacement of the association attribute in the target association table with the association attribute in the parent table corresponding to the target association table.
9. 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 4 when executing the computer program.
10. 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 of any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811330716.2A CN109656947B (en) | 2018-11-09 | 2018-11-09 | Data query method and device, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811330716.2A CN109656947B (en) | 2018-11-09 | 2018-11-09 | Data query method and device, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109656947A CN109656947A (en) | 2019-04-19 |
| CN109656947B true CN109656947B (en) | 2020-12-29 |
Family
ID=66110769
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811330716.2A Active CN109656947B (en) | 2018-11-09 | 2018-11-09 | Data query method and device, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109656947B (en) |
Families Citing this family (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111639095B (en) * | 2020-05-27 | 2023-03-31 | 中信银行股份有限公司 | Data table query method, device and storage medium |
| CN112860727B (en) * | 2021-02-20 | 2024-01-12 | 平安科技（深圳）有限公司 | Data query method, device, equipment and medium based on big data query engine |
| CN113672615B (en) * | 2021-07-22 | 2023-06-20 | 杭州未名信科科技有限公司 | Data analysis method and system for automatically generating SQL based on relationships among tree tables |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104662535A (en) * | 2012-07-24 | 2015-05-27 | 起元科技有限公司 | Mapping entities in data models |
| CN105912594A (en) * | 2016-04-05 | 2016-08-31 | 深圳市深信服电子科技有限公司 | SQL sentence processing method and system |
| CN107103007A (en) * | 2016-02-23 | 2017-08-29 | 阿里巴巴集团控股有限公司 | A kind of SQL code conversion method and device |
| CN107133320A (en) * | 2017-05-03 | 2017-09-05 | 广州虎牙信息科技有限公司 | The method for transformation and device of concatenation operation in Hive |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7720840B2 (en) * | 2004-01-08 | 2010-05-18 | International Business Machines Corporation | Method applying transitive closure to group by and order by clauses |
-
2018
- 2018-11-09 CN CN201811330716.2A patent/CN109656947B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104662535A (en) * | 2012-07-24 | 2015-05-27 | 起元科技有限公司 | Mapping entities in data models |
| CN107103007A (en) * | 2016-02-23 | 2017-08-29 | 阿里巴巴集团控股有限公司 | A kind of SQL code conversion method and device |
| CN105912594A (en) * | 2016-04-05 | 2016-08-31 | 深圳市深信服电子科技有限公司 | SQL sentence processing method and system |
| CN107133320A (en) * | 2017-05-03 | 2017-09-05 | 广州虎牙信息科技有限公司 | The method for transformation and device of concatenation operation in Hive |
Non-Patent Citations (1)
| Title |
|---|
| 加密数据库中复杂查询语句的研究与实现;宁建斌;《csdn》;20180601;全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109656947A (en) | 2019-04-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110765275B (en) | Search method, search device, computer equipment and storage medium | |
| US10725981B1 (en) | Analyzing big data | |
| WO2020186786A1 (en) | File processing method and apparatus, computer device and storage medium | |
| CN109992601B (en) | To-do information pushing method and device and computer equipment | |
| US9390176B2 (en) | System and method for recursively traversing the internet and other sources to identify, gather, curate, adjudicate, and qualify business identity and related data | |
| CN108563734B (en) | System information query method, device, computer equipment and storage medium | |
| CN109656947B (en) | Data query method and device, computer equipment and storage medium | |
| JP2013504118A (en) | Information retrieval based on query semantic patterns | |
| US9870394B2 (en) | Query routing method, query routing server performing the same and storage medium storing the same | |
| CN111752955A (en) | Data processing method, device, equipment and computer readable storage medium | |
| CN112883030A (en) | Data collection method and device, computer equipment and storage medium | |
| CN112560444A (en) | Text processing method and device, computer equipment and storage medium | |
| CN110555165B (en) | Information identification method and device, computer equipment and storage medium | |
| CN110659297A (en) | Data processing method, data processing device, computer equipment and storage medium | |
| CN110674200A (en) | Data query method based on annotation, data query equipment and storage medium | |
| CN115062016A (en) | Incidence relation extraction method and device and computer equipment | |
| CN112685475A (en) | Report query method and device, computer equipment and storage medium | |
| CN110457401B (en) | Data storage method and device, computer equipment and storage medium | |
| CN111737981A (en) | Vocabulary error correction method and device, computer equipment and storage medium | |
| CN112948504B (en) | Data acquisition method and device, computer equipment and storage medium | |
| CN113918807A (en) | Data recommendation method and device, computing equipment and computer-readable storage medium | |
| CN109669951B (en) | Object query method and device, computer equipment and storage medium | |
| US11709798B2 (en) | Hash suppression | |
| CN115098503A (en) | Null value data processing method and device, computer equipment and storage medium | |
| CN111460268B (en) | Method and device for determining database query request and computer equipment |
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 |