A method and system for full-text retrieval processing of a database

Abstract

The present application discloses a method and system for full-text retrieval processing in a database. The method includes: after generating a statistical table according to a transaction table, receiving retrieval information, and performing a full-text index query according to the retrieval information, where the retrieval information is generated based on the statistical table; obtaining the retrieval results obtained after performing the full-text index query; obtaining the unique identifier corresponding to each retrieval result, and querying the corresponding information in the transaction table according to the unique identifier; if the corresponding information queried in the transaction table for the unique identifier is different from the retrieval information, discarding the retrieval result, and if the same, displaying the retrieval result. By means of the present application, the problem that the retrieval results sometimes do not match the query conditions used for retrieval when using full-text retrieval in a database is solved, thereby improving the accuracy of the retrieval results.

Description

Technical Field

[0001] This application relates to the field of databases. Specifically, it relates to a method and system for full-text retrieval processing of databases. Background Art

[0002] Data can be divided into two types: Structured data: refers to data with a fixed format or limited length, such as databases, metadata, etc. Unstructured data: refers to data without a fixed format or variable length, such as emails, word documents, etc. There is another name for unstructured data: full-text data.

[0003] According to the classification of data, searches are also divided into two types: searches for structured data and retrievals for unstructured data. For example, using SQL statements to retrieve data from a database is a retrieval for structured data; another example is using keywords to search for file content among numerous files in an operating system, which is a retrieval for unstructured data. Among them, the search for unstructured data is also called the search for full-text data.

[0004] The search for full-text data can also be divided into two types: The first is called sequential scanning. For example, to find a file containing a certain string, it will search through each document from beginning to end; Index scanning: Extract a part of the content from unstructured data and reorganize it to make it structured. This part of the extracted data is called an index. When performing a retrieval, by retrieving the index, the expected result can be obtained.

[0005] Full-text retrieval generally consists of two processes: index creation (Indexer) and index search (Search). Among them, index creation is the process of extracting information from all structured and unstructured data and creating an index. Index search is the process of obtaining the user's query request, searching the created index, and then returning the result.

[0006] When using a database, it involves a transaction table and a statistical table. The transaction table is a table formed by recording received data item by item in the database. The statistical table is a table formed after statistically processing the transaction table according to a predetermined time dimension. The inventor found that when using full-text retrieval in a database, there are sometimes situations where the retrieval result does not match the query condition used for retrieval, which affects the accuracy of the retrieval result. Summary of the Invention

[0007] Embodiments of this application provide a method and system for full-text retrieval processing of databases to at least solve the problem that sometimes the retrieval result does not match the query condition used for retrieval when using full-text retrieval in a database.

[0008] According to one aspect of the present application, a method for processing full-text retrieval of a database is provided, including: after generating a statistical table according to a transaction table, receiving retrieval information, and performing a full-text index query according to the retrieval information, wherein the statistical table is obtained by aggregating the transaction table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; obtaining a retrieval result obtained after performing the full-text index query, wherein each retrieval result meets the retrieval conditions in the retrieval information; obtaining a unique identifier corresponding to each retrieval result, and querying corresponding information in the transaction table according to the unique identifier; if the corresponding information queried in the transaction table based on the unique identifier is different from the retrieval information, discarding the retrieval result, and if it is the same, displaying the retrieval result.

[0009] Further, querying corresponding information in the transaction table according to the unique identifier includes: judging whether the query conditions included in the query information include content that will be updated subsequently; if it includes content that will be updated subsequently, querying corresponding information in the transaction table according to the unique identifier.

[0010] Further, querying corresponding information in the transaction table according to the unique identifier includes: when the query information is used to query session information, querying corresponding information in the session table according to the unique identifier, wherein the transaction table includes the session table.

[0011] Further, querying corresponding information in the transaction table according to the unique identifier includes: when the query information is used to query information corresponding to a statement response, querying corresponding information in the update table according to the unique identifier, wherein the update table records the updated content in the statement table after generating the statistical table, and the transaction table includes the statement table.

[0012] According to another aspect of the present application, a system for processing full-text retrieval of a database is further provided, including: a receiving module, configured to receive retrieval information after generating a statistical table according to a transaction table, and perform a full-text index query according to the retrieval information, wherein the statistical table is obtained by aggregating the transaction table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; an obtaining module, configured to obtain a retrieval result obtained after performing the full-text index query, wherein each retrieval result meets the retrieval conditions in the retrieval information; a query module, configured to obtain a unique identifier corresponding to each retrieval result, and query corresponding information in the transaction table according to the unique identifier; a processing module, configured to discard the retrieval result if the corresponding information queried in the transaction table based on the unique identifier is different from the retrieval information, and display the retrieval result if it is the same.

[0013] Further, the query module is configured to: determine whether the query conditions included in the query information include content that will be updated subsequently; if the content that will be updated subsequently is included, query the corresponding information in the flow table according to the unique identifier.

[0014] Further, the query module is configured to: when the query information is used to query session information, query the corresponding information in the session table according to the unique identifier, where the flow table includes the session table.

[0015] Further, the query module is configured to: when the query information is used to query the information corresponding to the statement response, query the corresponding information in the update table according to the unique identifier, where the update table records the content updated in the statement table after the statistical table is generated, and the flow table includes the statement table.

[0016] According to another aspect of the present application, there is also provided an electronic device, including a memory and a processor; wherein, the memory is used to store one or more computer instructions, and the one or more computer instructions are executed by the processor to implement the above method steps.

[0017] According to another aspect of the present application, there is also provided a readable storage medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the method steps described in any one of claims 1 to 4 are implemented.

[0018] In the embodiments of the present application, after generating a statistical table according to a flow table, retrieval information is received, and a full-text index query is performed according to the retrieval information, where the statistical table is obtained by aggregating the flow table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; obtaining retrieval results obtained after performing the full-text index query, where each retrieval result meets the retrieval conditions in the retrieval information; obtaining the unique identifier corresponding to each retrieval result, and querying the corresponding information in the flow table according to the unique identifier; if the corresponding information queried in the flow table for the unique identifier is different from the retrieval information, discard the retrieval result, and if they are the same, display the retrieval result. The present application solves the problem that sometimes the retrieval result does not match the query conditions used for retrieval when using full-text retrieval in a database, thereby improving the accuracy of the retrieval result. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The drawings constituting a part of this application are used to provide a further understanding of this application. The schematic embodiments of this application and their descriptions are used to explain this application and do not constitute an improper limitation to this application. In the drawings:

[0020] Figure 1It is a flowchart of a method for full-text retrieval processing of a database according to an embodiment of the present application. Detailed implementation manners

[0021] It should be noted that, without conflict, the embodiments in the present application and the features in the embodiments may be combined with each other. The present application will be described in detail below with reference to the drawings and in combination with the embodiments.

[0022] It should be noted that the steps shown in the flowchart of the drawings can be executed in a computer system such as a set of computer-executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in a different order than here.

[0023] In the following implementation manners, retrieval is involved. First, the technologies involved will be described below.

[0024] The process of creating an index for full-text retrieval generally has the following steps: Obtain some original documents that need to create an index, pass the original documents to a tokenization component for tokenization to obtain tokens, pass the obtained tokens to a language processing component for processing to obtain words, and pass the obtained words to an index component to generate an index. The index component mainly does the following things: Create a dictionary using the obtained words and sort the dictionary alphabetically. Merge the same words into a document inverted linked list, and this linked list can be understood as the index of this document.

[0025] Sphinx is the abbreviation of SQLPhraseIndex (query phrase index), and Sphinx is a full-text retrieval engine based on SQL. The solutions in the following implementation manners can be applied to various full-text retrieval engines. In the following text, Sphinx is taken as an example for description, but it is not limited to this.

[0026] When using a database, a transaction table and a statistical table are involved. The transaction table is a table formed by recording the received data (also known as transaction information) item by item into the database. For example, a session transaction table, which parses the database user, client tool, client IP, etc. in the data packet and inserts them into the table; a statement transaction table, which parses the statement parameters, affected row count, response time, etc. in the data packet and inserts them into the table. The statistical table is a table formed after statistically processing the transaction table according to a predetermined time dimension. Transaction information refers to a complete statement information executed by the user at that time, including session, statement, parameters, affected row count, etc. In the following text, logsec is also involved. This logsec is a globally unique identifier and is a complete information identifier of a statement. The same identifier creates an index again.

[0027] For example, each section of the report to be generated can be obtained, and at least one data source corresponding to each section can be obtained according to the data requirements of each section, where the data source is used to indicate the storage location of the data in the database; the data corresponding to each section is obtained according to the at least one data source corresponding to each section, and the obtained data corresponding to each section is processed, where the processing includes: sorting the data by time and removing duplicates of the same data; aggregating the processed data of each section N times, where in the N times of aggregation, the first aggregation is performed using the first time dimension, the second aggregation is performed using the second time dimension on the aggregation result obtained from the first aggregation,..., the Nth aggregation is performed using the Nth time dimension on the result of the (N - 1)th aggregation; where the first time dimension is less than the second time dimension, the second time dimension is less than the third time dimension, and so on, the (N - 1)th time dimension is less than the Nth time dimension; the time dimension of the report to be generated is obtained, and at least one is selected from the aggregation results after the N times of aggregation according to the time dimension of the report to be generated; the selected at least one aggregation result is aggregated according to the time dimension of the report to be generated to obtain the report to be generated. Optionally, aggregating the processed data of each section N times includes: classifying the processed data of each section according to the session water table and the statement water table, and then aggregating the classified data N times.

[0028] In the kernel incoming water table data, data such as rules and session information will be updated. The statistical table is obtained by aggregating data based on the water table. Therefore, the data update in the statistical table is not as rapid and timely as that in the water table. The search engine will build an index based on the statistics. Once the data in the water table is updated, but the statistical data has been completed before the updated data, in this case, the session information of the page query condition, etc., comes from the statistical table, and it will be found that the retrieved data does not match the query condition according to the condition query.

[0029] There are many situations where the data in the water table is updated.

[0030] For example, during an audit, when auditing a statement in the request direction, the statement is updated to the water table. However, if the database response is slow, the data in the water table may have been statistically processed to obtain the statistical table without receiving the response data. That is, in this case, the request may not wait for the response data, and after the subsequent response arrives, the subsequent data is updated in the database, which will result in a difference between the data in the water table and the statistical table. In this case, the retrieval performed may be incorrect.

[0031] Perform protocol parsing on the data packet to analyze information such as session information and statements, and then create a full-text index for the query fields (for example, using Sphinx technology, and of course other existing technologies can also be used to create the full text as shown, which will not be elaborated here one by one). At the same time, insert the session and statement data into the streaming table of the database (for example, Mysql). When the page queries according to conditions, such as querying the database user and the number of affected rows on the page and requesting to the application layer (for example, the Java layer), then transfer the query conditions to the full-text index for asynchronous retrieval and query, and return the globally unique identifier (logsec). According to this identifier, query the statement streaming table and the session table to restore the session information and statement information executed by the user at that time and present it on the page.

[0032] Session information such as the user and the client tool may not be analyzed when analyzing the data packet. At this time, when creating a full index, it will be created as unknown information and inserted into the session streaming table at the same time. Subsequently, specific user and client tool information is analyzed, such as the test user and the Navicat tool. At this time, a new full-text index will be created according to the conditions and the streaming table information will be updated, resulting in the page retrieving unknown user information and the client tool being unknown information, and the page returns the streaming data but displays the test user and the Navicat tool.

[0033] There is also this problem in the statement response direction, such as the number of affected rows and the response time. Since the data query hits the result set, in many cases, the number of affected rows and the response time are constantly changing. The database retrieval product will not wait for all returns to end before processing, but first create a full-text index for the current number of affected rows and the response time and insert it into the statement streaming table. After the subsequent overall query ends, a new full-text index will be created according to the conditions to update the number of affected rows and the response time data in the statement streaming table. This will cause the page display data returned when retrieving the number of affected rows and the response time to not match the query conditions.

[0034] In summary, since information such as the database user and the client tool has been updated and a new full-text index for the query conditions has been created, but the data in the streaming table has been updated, this results in different condition retrievals on the page corresponding to a single data displayed on the page, and the query and the returned result page display before the update do not match. For the statement response direction, the same problem exists with the number of affected rows and the response time. Due to the data being updated, different full-text index query conditions correspond to the same data in the statement streaming table, resulting in the page retrieval conditions not corresponding to the displayed streaming information.

[0035] To solve the above problems, in the following embodiments, the retrieval of updated data is corrected, and data integration and correction are performed after retrieval, etc., so as to improve the accuracy of the retrieval results. In the following embodiments, a database full-text retrieval processing method is provided.Figure 1 is a flowchart of a method for processing full-text retrieval of a database according to an embodiment of the present application. The steps included in the process involved in Figure 1 will be described below.

[0036] Step S102: After generating a statistical table according to the water table, retrieval information is received, and a full-text index query is performed according to the retrieval information. Among them, the statistical table is obtained by aggregating the water table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table.

[0037] Step S104: Obtain the retrieval results obtained after performing the full-text index query. Each retrieval result meets the retrieval conditions in the retrieval information.

[0038] Step S106: Obtain the unique identifier corresponding to each retrieval result, and query the corresponding information in the water table according to the unique identifier.

[0039] In this step, it is possible to judge whether the query conditions included in the query information include content that will be updated later according to the query conditions included in the query information; if it includes content that will be updated later, query the corresponding information in the water table according to the unique identifier.

[0040] For example, querying the corresponding information in the water table according to the unique identifier includes: when the query information is used to query session information, query the corresponding information in the session table according to the unique identifier, where the water table includes the session table.

[0041] Another example, querying the corresponding information in the water table according to the unique identifier includes: when the query information is used to query the information corresponding to the statement response, query the corresponding information in the update table according to the unique identifier, where the update table records the content updated in the statement table after generating the statistical table, and the water table includes the statement table.

[0042] Step S108: If the corresponding information queried in the water table by the unique identifier is different from the retrieval information, discard the retrieval result; if it is the same, display the retrieval result.

[0043] By the above steps, the problem that the retrieval result sometimes does not match the query conditions used for retrieval when using full-text retrieval in the database is solved, thereby improving the accuracy of the retrieval result.

[0044] The following is an illustration with an example. For session information and other information that may change, such as database users, the names of client tools, etc., when querying the page, data comparison and discarding are performed. For the statement response direction, after changes such as the number of affected rows and response time, the globally unique identifier (i.e., logsec) will be recorded in the statement update table. When the kernel updates the data, the unique identifier of this data is separately recorded in the update table. When the page queries again, it will compare whether the unique identifier exists in the update table. If the data does not exist, this data needs to be discarded; if it exists, this data will be retained.

[0045] For session information, when the page queries the database user, client tool, etc., the Java layer converts it into a full-text index query, returns the unique identifier logsec that meets the conditions, and then analyzes whether there are session conditions that may be updated later. If so, it queries the information in the session table according to logsec, and compares the returned information with the page query conditions one by one. If they are inconsistent, this data is discarded, and the same method is used to continue analyzing the next piece of data. If it meets the conditions, it is pushed to the page for display.

[0046] For the statement response direction, the page query conditions include the number of affected rows, the name of the client tool, etc. The application layer (e.g., the Java layer) converts it into a full-text index query, returns the unique identifier logsec that meets the conditions, and then analyzes whether there are session conditions that may be updated later. If so, it compares according to logsec in the update table (the update table records the updated data). If there is the same logsec, it proves that this data has been updated. It needs to query the specific information in the statement flow table according to logsec, and then compare it with the page conditions. If the data is inconsistent, it is discarded; if it is consistent, it is pushed to the page for display.

[0047] In this example, the identifier of the updated data is recorded, and then the unique identifier that meets the conditions is retrieved. If this unique identifier exists in the update table, it is necessary to check whether the page conditions are consistent with the data in the table. If they are consistent, it is retained; if they are inconsistent, it is discarded and the search continues. For session information, for information that may change later, such as database users, the names of client tools, etc., if these query conditions are included, a large amount of data comparison and analysis are performed until the session information of the user at that time is returned. For the statement response direction, for information that changes later, such as the number of affected rows, response time, etc., if there are changes, they are recorded in the update table to facilitate quickly finding whether the full-text index logsec has been updated later. If it has been updated, the data in the table is queried, compared and discarded, and then a large amount of data comparison and analysis are performed until the session information of the user at that time is returned.

[0048] In this embodiment, an electronic device is provided, which includes a memory and a processor. A computer program is stored in the memory, and the processor is configured to run the computer program to execute the method in the above embodiment.

[0049] The above program can run in the processor, or can also be stored in the memory (or referred to as a computer-readable medium). The computer-readable medium includes permanent and non-permanent, removable and non-removable media, and information storage can be implemented by any method or technology. The information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette tapes, magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information accessible by a computing device.

[0050] These computer programs can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to generate computer-implemented processing. Thus, the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one process Figure 1 one process or multiple processes and / or blocks Figure 1 The steps corresponding to different steps can be implemented by different modules.

[0051] In this embodiment, such a device or system is provided. The system is called a database full-text retrieval processing system, including: a receiving module, configured to receive retrieval information after generating a statistical table according to a water table and perform a full-text index query according to the retrieval information, where the statistical table is obtained by aggregating the water table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; an obtaining module, configured to obtain a retrieval result obtained after performing the full-text index query, where each retrieval result meets the retrieval conditions in the retrieval information; a query module, configured to obtain a unique identifier corresponding to each retrieval result and query corresponding information in the water table according to the unique identifier; a processing module, configured to discard the retrieval result if the corresponding information queried in the water table by the unique identifier is different from the retrieval information, and display the retrieval result if they are the same.

[0052] The system or device is used to implement the functions of the methods in the above embodiments. Each module in the system or device corresponds to each step in the method. Those that have been described in the method will not be elaborated here.

[0053] Optionally, the query module is configured to: determine whether the query conditions included in the query information include content that will be updated subsequently according to the query conditions; if it includes content that will be updated subsequently, query the corresponding information in the water table according to the unique identifier.

[0054] Optionally, the query module is configured to: when the query information is used to query session information, query the corresponding information in the session table according to the unique identifier, where the water table includes the session table.

[0055] Optionally, the query module is configured to: when the query information is used to query the information corresponding to the statement response, query the corresponding information in the update table according to the unique identifier, where the update table records the content updated in the statement table after the statistical table is generated, and the water table includes the statement table.

[0056] By the above implementation manner, the problem that the retrieval result sometimes does not match the query conditions used for retrieval when using full-text retrieval in the database is solved, thereby improving the accuracy of the retrieval result.

[0057] The above are only the embodiments of the present application and are not used to limit the present application. For those skilled in the art, various changes and modifications can be made to the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included within the scope of the claims of the present application.

Claims

1. A method for full-text retrieval processing of a database, characterized in that, Including: After generating a statistical table based on a transaction table, receiving retrieval information and performing a full-text index query according to the retrieval information, where the statistical table is obtained by aggregating the transaction table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; Obtaining a retrieval result obtained after performing the full-text index query, where each retrieval result meets the retrieval conditions in the retrieval information; Obtaining a unique identifier corresponding to each retrieval result, and querying corresponding information in the transaction table according to the unique identifier; where querying corresponding information in the transaction table according to the unique identifier includes: judging whether the retrieval conditions included in the retrieval information include content that will be updated subsequently; if it includes content that will be updated subsequently, querying corresponding information in the transaction table according to the unique identifier; If the corresponding information queried in the transaction table based on the unique identifier is different from the retrieval information, discarding the retrieval result, and if it is the same, displaying the retrieval result.

2. The method according to claim 1, wherein Querying corresponding information in the transaction table according to the unique identifier includes: In the case where the retrieval information is used to query session information, querying corresponding information in the session table according to the unique identifier, where the transaction table includes the session table.

3. The method according to claim 1, wherein Querying corresponding information in the transaction table according to the unique identifier includes: In the case where the retrieval information is used to query information corresponding to a statement response, querying corresponding information in an update table according to the unique identifier, where the update table records the content updated in the statement table after generating the statistical table, and the transaction table includes the statement table.

4. A database full-text retrieval processing system, characterized in that, Including: A receiving module, configured to receive retrieval information after generating a statistical table based on a transaction table and perform a full-text index query according to the retrieval information, where the statistical table is obtained by aggregating the transaction table according to a predetermined time dimension, and the retrieval information is generated based on the statistical table; An obtaining module, configured to obtain a retrieval result obtained after performing the full-text index query, where each retrieval result meets the retrieval conditions in the retrieval information; A query module, configured to obtain a unique identifier corresponding to each retrieval result and query corresponding information in the transaction table according to the unique identifier; where querying corresponding information in the transaction table according to the unique identifier includes: judging whether the retrieval conditions included in the retrieval information include content that will be updated subsequently; if it includes content that will be updated subsequently, querying corresponding information in the transaction table according to the unique identifier; A processing module, configured to discard the retrieval result if the corresponding information queried in the transaction table based on the unique identifier is different from the retrieval information, and display the retrieval result if it is the same.

5. The system according to claim 4, characterized in that, The query module is used for: In the case where the retrieval information is used to query session information, querying corresponding information in the session table according to the unique identifier, where the transaction table includes the session table.

6. The system according to claim 4, characterized in that, The query module is used for: In the case where the retrieved information is used to query the information corresponding to the query statement, query the corresponding information in the update table according to the unique identifier, where the update table records the updated content in the statement table after the statistical table is generated, and the streaming table includes the statement table.

7. An electronic device, comprising a memory and a processor; wherein, The memory is used to store one or more computer instructions, wherein the one or more computer instructions are executed by the processor to implement the method steps of any one of claims 1 to 3.

8. A readable storage medium having computer instructions stored thereon, wherein, When the computer instructions are executed by the processor, the method steps of any one of claims 1 to 3 are implemented.

Images