Query method and device of relational database, electronic equipment and storage medium

Abstract

The invention provides a query method and device of a relational database, electronic equipment and a storage medium, and belongs to the technical field of database information management.The method comprises the steps that a user query request is obtained, and the user query request is divided into a fuzzy query condition and a non-fuzzy query condition; based on a search engine service provided with a fuzzy query data table in the relational database, querying by adopting a fuzzy query condition to obtain an identifier set conforming to the fuzzy query condition; and combining the identifier set with a non-fuzzy query condition to obtain a target query statement, and querying the relational database based on the target query statement. According to the invention, query is carried out based on the fuzzy query condition, and then query is carried out based on the fuzzy query result in combination with the non-fuzzy query condition; the problems that an existing database query method cannot meet the requirement for included query of meaningless character strings, the data size of a data table is very large, and the query requirement for accurate matching of an association table exists can be solved.

Description

Technical Field

[0001] The present invention relates to the technical field of database information management, and particularly to a query method, device, electronic device and storage medium for a relational database. Background Art

[0002] In a database information system, a series of query interfaces are provided to assist users in retrieving data, and the inclusion query (fuzzy query) of strings is a commonly used query component. However, a relational database cannot use a general index to improve the performance of the inclusion query, and a large query latency phenomenon will occur when the data volume reaches millions or tens of millions.

[0003] There are mainly two existing methods to improve the performance of fuzzy queries: 1. Utilize the full-text index function of a relational database. The full-text index of a database generally uses a token-based index matching scheme, and this index method cannot well handle the query of the inclusion relationship of meaningless strings, such as the matching of strings in an ID number; 2. Use a full-text index system for retrieval, and this method does not meet the requirement of performing an association query on a large number of associated tables in an enterprise information system.

[0004] In summary, the existing database query methods cannot meet the requirements of inclusion queries for meaningless strings, a very large data volume of data tables, and the requirement of performing an exact match query on associated tables. Summary of the Invention

[0005] In view of this, it is necessary to provide a query method, device, electronic device and storage medium for a relational database to solve the technical problem that the existing database query methods cannot meet the requirements of inclusion queries for meaningless strings, a very large data volume of data tables, and the requirement of performing an exact match query on associated tables.

[0006] To solve the above problems, on the one hand, the present invention provides a query method for a relational database, including: Obtain a user query request and divide the user query request into a fuzzy query condition and a non-fuzzy query condition; Based on a search engine service, perform a query using the fuzzy query condition to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is set with a fuzzy query data table in the relational database; Combine the set of identifiers with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

[0007] In a possible implementation manner, the query method for a relational database further includes: Obtain the change information corresponding to the relational database, and synchronize the change information to the data set of the search engine service.

[0008] In a possible implementation, obtaining the change information corresponding to the relational database includes: Capture the change information of the relational database based on the log information, triggers or polling components of the relational database.

[0009] In a possible implementation, dividing the user query request into fuzzy query conditions and non-fuzzy query conditions includes: Extract the target field data in the user query request, and based on the target field data, divide the user query request into fuzzy query conditions and non-fuzzy query conditions.

[0010] In a possible implementation, based on the search engine service, query using the fuzzy query conditions to obtain a set of identifiers that meet the fuzzy query conditions; the search engine service is set with a fuzzy query data table in the relational database, including: Based on the name of the data table to be queried in the user query request, query the corresponding data table in the search engine service to obtain a set of identifiers that meet the fuzzy query conditions; the search reference service is an Elasticsearch service or a Solr service.

[0011] In a possible implementation, combining the set of identifiers with the non-fuzzy query conditions to obtain a target query statement includes: Use the set of identifiers as an IN statement and combine it with the non-fuzzy query conditions to obtain a target query statement in SQL form.

[0012] On the other hand, the present invention also provides a query device for a relational database, including: An acquisition module, configured to acquire a user query request, and divide the user query request into fuzzy query conditions and non-fuzzy query conditions; A first query module, configured to query based on the search engine service and use the fuzzy query conditions to obtain a set of identifiers that meet the fuzzy query conditions; the search engine service is set with a fuzzy query data table in the relational database; A second query module, configured to combine the set of identifiers with the non-fuzzy query conditions to obtain a target query statement, and query the relational database based on the target query statement.

[0013] In a possible implementation, the query device for the relational database further includes: A change module, configured to obtain the change information corresponding to the relational database, and synchronize the change information to the data set of the search engine service.

[0014] On the other hand, the present invention further provides an electronic device, including a memory and a processor, wherein, the memory is used for storing a program; the processor is coupled to the memory and is used for executing the program stored in the memory to implement the steps of the query method for the relational database as described in any one of the above.

[0015] On the other hand, the present invention further provides a non-transitory computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the query method for the relational database as described in any one of the above are implemented.

[0016] The beneficial effects of adopting the above implementation manner are as follows: The query method, device, electronic device and storage medium for the relational database provided by the present invention obtain a user query request, and divide the user query request into a fuzzy query condition and a non-fuzzy query condition; based on a search engine service, and perform a query using the fuzzy query condition to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is provided with a fuzzy query data table in the relational database; combine the set of identifiers with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

[0017] The present invention divides the user query request into a fuzzy query condition and a non-fuzzy query condition. First, based on the search engine service and combined with the fuzzy query condition for query, it can avoid the inclusion query of meaningless strings. When the data volume in the data table is very large, it can improve the query efficiency and reduce the query time. Then, based on the result of the fuzzy query and combined with the non-fuzzy query condition for query, it can meet the query requirement of precise matching for the associated table. Therefore, the present invention can solve the technical problems that the existing database query methods cannot meet the inclusion query for meaningless strings, the data volume in the data table is very large, and there is a query requirement for precise matching of the associated table. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments. Obviously, the following drawings are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative efforts.

[0019] Figure 1 It is a flowchart of an embodiment of the query method for the relational database provided by the present invention; Figure 2 It is a flowchart of another embodiment of the query method for the relational database provided by the present invention; Figure 3 A schematic diagram of the principle of an embodiment of the query device for a relational database provided by the present invention; Figure 4 A schematic structural diagram of an embodiment of the electronic device provided by the present invention. Detailed implementation manners

[0020] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative efforts fall within the protection scope of the present invention.

[0021] In the description of the embodiments of the present application, unless otherwise specified, the meaning of "a plurality" is two or more.

[0022] In the embodiments of the present invention, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, device, product, or equipment that includes a series of steps or modules does not necessarily have to be limited to those clearly listed steps or modules, but may include other steps or modules that are not clearly listed or are inherent to these processes, methods, products, or equipment.

[0023] In the embodiments of the present invention, the naming or numbering of steps does not mean that the steps in the method flow must be executed in the time / logical sequence indicated by the naming or numbering. The named or numbered process steps can be changed in the execution order according to the technical purpose to be achieved, as long as the same or similar technical effects can be achieved.

[0024] Referring to "embodiments" herein means that the specific features, structures, or characteristics described in connection with the embodiments may be included in at least one embodiment of the present invention. The phrase appears in various places in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment mutually exclusive with other embodiments. Those skilled in the art explicitly and implicitly understand that the embodiments described herein can be combined with other embodiments.

[0025] The present invention provides a query method, device, electronic device, and storage medium for a relational database, which will be described separately below.

[0026] As Figure 1 shown, the present invention provides a query method for a relational database, including: S101. Obtain a user query request and divide the user query request into a fuzzy query condition and a non-fuzzy query condition; S102. Query based on the search engine service and using the fuzzy query condition to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is set with a fuzzy query data table in the relational database. S103. Combine the set of identifiers with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

[0027] It can be understood that the present invention provides a query method for a relational database, which can be executed by a software application set in a terminal device or by a software application set in a server. The terminal device can be a mobile phone, a tablet computer, or a personal computer, and the server can be an edge server or a cloud server.

[0028] The search engine service can be an Elasticsearch service or a Solr service. The Elasticsearch service is a distributed data search engine, which is not only good at relevance search based on word segmentation, but also provides a wildcard field type, which can perform efficient containment queries on wildcard fields. Solr is an independent enterprise-level search application server that provides API interfaces similar to Web-services externally.

[0029] The present invention synchronizes the change information of the relational database to the search engine service through a data synchronization module. Currently, there are a large number of real-time data synchronization tools, and Flink is one of them. The basic process of data synchronization work is: first, capture the changes in the database through methods such as logs, triggers, or polling; second, transmit the captured changes to the data set of the search engine service; third, regularly clean up the processed change records.

[0030] The present invention organizes the query request submitted by the user into two parts: fuzzy query and non-fuzzy query through a request preprocessing module, and then calls the query interface provided by the query execution module. In an information system, the query request body may be as follows:

[0031] It contains two query conditions. Perform a containment query on the IDNumber field to find the details containing the data "25638589". Perform an equivalent query on the Name field to find the data with the name "Zhang San". The request preprocessing module determines that the condition where the operator is "contains" is the fuzzy query condition. Of course, other methods can also be used for differentiation.

[0032] The present invention converts the query request input by the user into the following call for the query execution module through the request preprocessing module. The following is illustrated with pseudocode:

[0033] Among them, the first parameter is the name of the data table to be queried, the second parameter is the fuzzy query body to be executed by the search engine service, and the query body is the query condition. The third condition is other query bodies (non-fuzzy query bodies) to be executed by the relational database.

[0034] The query execution module provides a standard query interface, such as the queryData function described above. The query execution module is executed in two steps within the interface implementation. It first queries Elasticsearch to obtain a set of identifiers of data that meet the fuzzy query conditions. Then, the obtained set of primary keys is used as an IN statement to combine with other query conditions to form the final SQL. For example:

[0035] The query execution module sends the final SQL condition to the relational database to obtain the final result set.

[0036] In some embodiments, the query method of the relational database further includes: Obtaining the change information corresponding to the relational database and synchronizing the change information to the data set of the search engine service.

[0037] In some embodiments, obtaining the change information corresponding to the relational database includes: Capturing the change information of the relational database based on the log information, triggers or polling components of the relational database.

[0038] In some embodiments, dividing the user query request into fuzzy query conditions and non-fuzzy query conditions includes: Extracting the target field data in the user query request, and based on the target field data, dividing the user query request into fuzzy query conditions and non-fuzzy query conditions.

[0039] In some embodiments, based on the search engine service and using the fuzzy query conditions for query, a set of identifiers that meet the fuzzy query conditions is obtained; the search engine service is set with a fuzzy query data table in the relational database, including: Based on the name of the data table to be queried in the user query request, querying the corresponding data table in the search engine service to obtain a set of identifiers that meet the fuzzy query conditions; the search reference service is an Elasticsearch service or a Solr service.

[0040] In some embodiments, combining the set of identifiers with the non-fuzzy query conditions to obtain a target query statement includes: Use the set of identifiers as an IN statement and combine it with the non-fuzzy query conditions to obtain a target query statement in SQL form.

[0041] In some embodiments, as Figure 2 shown, the method of the present invention is mainly implemented through three modules: data synchronization, request preprocessing, and query execution module. The data synchronization module is used to synchronize the data in the large data table in the relational database that may be subject to fuzzy queries to the search engine service in real time. The request preprocessing module is used to organize the received data query request into a fuzzy query body for the search engine service to execute and other query bodies for the relational database to execute. The query execution module first organizes the fuzzy query body into a request format acceptable to the search engine service and obtains a set of identifiers that meet the fuzzy conditions from it. Then, the obtained set of identifiers is constructed into an IN clause in the SQL statement. Finally, the other query bodies (non-fuzzy query bodies) and the IN clause are organized into a new SQL condition and handed over to the relational database for execution to obtain a query result set.

[0042] As Figure 3 shown, the present invention also provides a query device 300 for a relational database, including: An acquisition module 301, configured to acquire a user query request and divide the user query request into fuzzy query conditions and non-fuzzy query conditions; A first query module 302, configured to perform a query based on the search engine service and using the fuzzy query conditions to obtain a set of identifiers that meet the fuzzy query conditions; the search engine service is set with a fuzzy query data table in the relational database; A second query module 303, configured to combine the set of identifiers with the non-fuzzy query conditions to obtain a target query statement, and query the relational database based on the target query statement.

[0043] In some embodiments, the query device for the relational database further includes: A change module, configured to acquire change information corresponding to the relational database and synchronize the change information to the data set of the search engine service.

[0044] The query device for the relational database provided in the above embodiments can implement the technical solutions described in the query method embodiments of the above relational database. The specific implementation principles of the above modules or units can be referred to the corresponding content in the query method embodiments of the above relational database, which will not be elaborated here.

[0045] As Figure 4 shown, the present invention also correspondingly provides an electronic device 400. The electronic device 400 includes a processor 401, a memory 402, and a display 403. Figure 4Only some components of the electronic device 400 are shown, but it should be understood that it is not required to implement all the shown components, and more or fewer components can be alternatively implemented.

[0046] In some embodiments, the memory 402 may be an internal storage unit of the electronic device 400, such as a hard disk or memory of the electronic device 400. In some other embodiments, the memory 402 may also be an external storage device of the electronic device 400, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) card, a Flash Card, etc. equipped on the electronic device 400.

[0047] Furthermore, the memory 402 may also include both the internal storage unit of the electronic device 400 and the external storage device. The memory 402 is used to store the application software installed in the electronic device 400 and various types of data.

[0048] In some embodiments, the processor 401 may be a central processing unit (CPU), a microprocessor or other data processing chips, and is used to run the program code stored in the memory 402 or process data, such as the query method of the relational database in the present invention.

[0049] In some embodiments, the display 403 may be an LED display, a liquid crystal display, a touch liquid crystal display, and an OLED (Organic Light-Emitting Diode) toucher, etc. The display 403 is used to display the information in the electronic device 400 and to display the visual user interface. The components 401 - 403 of the electronic device 400 communicate with each other through the system bus.

[0050] In some embodiments of the present invention, when the processor 401 executes the query program of the relational database in the memory 402, the following steps can be implemented: Obtain a user query request, and divide the user query request into a fuzzy query condition and a non-fuzzy query condition; Based on the search engine service, and perform a query using the fuzzy query condition to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is provided with a fuzzy query data table in the relational database; Combine the set of identifiers with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

[0051] It should be understood that when the processor 401 executes the query program of the relational database in the memory 402, in addition to the above functions, other functions can also be realized. For specific details, reference can be made to the description of the corresponding method embodiments above.

[0052] Furthermore, the embodiments of the present invention do not specifically limit the type of the mentioned electronic device 400. The electronic device 400 can be a mobile phone, a tablet computer, a personal digital assistant (PDA), a wearable device, a laptop computer, or other portable electronic devices. Exemplary embodiments of the portable electronic device include, but are not limited to, portable electronic devices running IOS, android, microsoft, or other operating systems. The above-mentioned portable electronic devices can also be other portable electronic devices, such as a laptop computer with a touch-sensitive surface (such as a touch panel). It should also be understood that in some other embodiments of the present invention, the electronic device 400 may not be a portable electronic device, but a desktop computer with a touch-sensitive surface (such as a touch panel).

[0053] In another aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it is used to execute the query method of the relational database provided by the above-mentioned various methods. The method includes: Obtain a user query request, and divide the user query request into a fuzzy query condition and a non-fuzzy query condition; Based on the search engine service, and use the fuzzy query condition to perform a query to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is provided with a fuzzy query data table in the relational database; Combine the set of identifiers with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

[0054] Those skilled in the art can understand that all or part of the processes of implementing the above-mentioned embodiment methods can be completed by instructing relevant hardware through a computer program, and the program can be stored in a computer-readable storage medium. Among them, the computer-readable storage medium is a disk, an optical disk, a read-only memory, or a random access memory, etc.

[0055] The above has introduced in detail the query method, device, electronic device and storage medium of the relational database provided by the present invention. Specific examples are used in this article to elaborate on the principle and implementation manner of the present invention. The description of the above embodiments is only used to help understand the method and its core idea of the present invention; at the same time, for those skilled in the art, according to the idea of the present invention, there will be changes in the specific implementation manner and application scope. In summary, the content of this specification should not be construed as a limitation to the present invention.

Claims

1. A query method for a relational database, characterized in that: include: Obtaining a user query request, and dividing the user query request into a fuzzy query condition and a non-fuzzy query condition; Based on the search engine service, the fuzzy query condition is used to perform a query to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is provided with a fuzzy query data table in a relational database; The identification set is combined with the non-fuzzy query condition to obtain a target query statement, and a relational database is queried based on the target query statement.

2. The query method of a relational database according to claim 1, characterized in that: Also includes: The change information corresponding to the relational database is obtained, and the change information is synchronized to the data set of the search engine service.

3. The query method of a relational database according to claim 2, characterized in that: Obtaining the change information corresponding to the relational database includes: The change information of the relational database is captured based on the log information, trigger or polling component of the relational database.

4. The query method of a relational database according to claim 1, characterized in that: The user query request is divided into fuzzy query conditions and non-fuzzy query conditions, including: Target field data in the user query request is extracted, and based on the target field data, the user query request is divided into a fuzzy query condition and a non-fuzzy query condition.

5. The query method of a relational database according to claim 1, characterized in that: Based on the search engine service, the fuzzy query condition is used to perform a query to obtain a set of identifiers that meet the fuzzy query condition; The search engine service is provided with a fuzzy query data table in a relational database, including: Based on the name of the data table to be queried in the user query request, query the corresponding data table in the search engine service to obtain a set of identifiers that meet the fuzzy query conditions; The search reference service is an Elasticsearch service or a Solr service.

6. The query method of a relational database according to any one of claims 1 to 5, characterized in that: Combining the identification set with the non-fuzzy query condition to obtain a target query statement includes: The identification set is used as an IN statement and combined with the non-fuzzy query condition to obtain a target query statement in SQL form.

7. A query device for a relational database, characterized in that: include: An acquisition module, used for acquiring a user query request and dividing the user query request into a fuzzy query condition and a non-fuzzy query condition; A first query module is used to query based on a search engine service and adopt the fuzzy query condition to obtain a set of identifiers that meet the fuzzy query condition; the search engine service is provided with a fuzzy query data table in a relational database; The second query module is used to combine the identification set with the non-fuzzy query condition to obtain a target query statement, and query the relational database based on the target query statement.

8. The query device for a relational database according to claim 7, characterized in that: Also includes: The change module is used to obtain the change information corresponding to the relational database and synchronize the change information to the data set of the search engine service.

9. An electronic device, characterized in that: comprising a memory and a processor, wherein: The memory is used to store programs; The processor is coupled to the memory and is used to execute the program stored in the memory to implement the steps of the relational database query method as described in any one of claims 1 to 6.

10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the query method of a relational database as claimed in any one of claims 1 to 6 are implemented.

Images