CN115292322A - Data query method, device, equipment and medium - Google Patents
Data query method, device, equipment and mediumDownload PDFInfo
- Publication number
- CN115292322A CN115292322ACN202210938997.XACN202210938997ACN115292322ACN 115292322 ACN115292322 ACN 115292322ACN 202210938997 ACN202210938997 ACN 202210938997ACN 115292322 ACN115292322 ACN 115292322A
- Authority
- CN
- China
- Prior art keywords
- target
- index
- character string
- character
- characters
- 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.)
- Pending
Links
Images
Classifications
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Translated fromChinese
Description
Translated fromChinese技术领域technical field
本申请涉及大数据领域，特别涉及一种数据查询方法、装置、设备及介质。The present application relates to the field of big data, and in particular to a data query method, device, equipment and medium.
背景技术Background technique
联机服务往往对请求时间有一定要求，要求后台需在一定时间内返回请求结果。举例来说，在需要将场景系统中的一支联机交易作为报表查询导出时，具体功能为根据查询条件查询相关数据(模糊查询)写入excel中，并下载excel文件。由于数据量较大，查询、写入excel、下载等方面均十分耗时，容易发生系统超时问题。通过联机请求生成报表往往比较耗时，联机请求生成报表包括两个方面，一方面从数据库中查询数据并返回到后台，另一方面将查询回来的数据写到excel表格中并返回给前端。在现有技术中，往往使用数据库提供的like关键字进行模糊查询，匹配数据库数据，而数据库使用like时往往不走索引，需要进行全表扫描查询，数据量大时将严重影响查询效率，查询速度慢。Online services often have certain requirements on the request time, requiring the background to return the request result within a certain period of time. For example, when it is necessary to export an online transaction in the scenario system as a report query, the specific function is to query relevant data according to the query conditions (fuzzy query) and write it into excel, and download the excel file. Due to the large amount of data, querying, writing to excel, and downloading are time-consuming, and system timeout problems are prone to occur. Generating reports through online requests is often time-consuming. Generating reports through online requests includes two aspects. On the one hand, query data from the database and return to the background; In the existing technology, the like keyword provided by the database is often used to perform fuzzy query and match database data. However, when the database uses like, the index is often not used, and a full table scan query is required. When the amount of data is large, the query efficiency will be seriously affected. slow.
发明内容Contents of the invention
有鉴于此，本申请的目的在于提供一种数据查询方法、装置、设备及介质，提高数据查询速度。其具体方案如下：In view of this, the purpose of the present application is to provide a data query method, device, equipment and medium to improve the data query speed. The specific plan is as follows:
第一方面，本申请提供了一种数据查询方法，包括：In the first aspect, the present application provides a data query method, including:
对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；Indexing at least one attribute in the data table; one attribute in the data table corresponds to at least one first character string; continuously cutting the first character string according to the number of characters to obtain a plurality of second character strings, the index is established according to the second character string;
获取用户输入的查询条件，所述查询条件包括目标字符串；Acquiring query conditions input by the user, where the query conditions include a target character string;
根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；According to the character number and character content of the target character string, search the index to obtain the target index; the character number and character content of the second character string in the target index are the same as the character number and character content of the target character string ; The target index stores a primary key;
根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。Obtain target data from the data table according to the primary key, so as to write the target data into an excel form.
可选地，所述根据所述目标字符串的字符数查找所述索引得到目标索引，包括：Optionally, the searching the index according to the number of characters of the target character string to obtain the target index includes:
利用二分查找算法，根据所述目标字符串的字符数查找所述索引得到目标索引。A binary search algorithm is used to search the index according to the number of characters of the target character string to obtain the target index.
可选地，在所述第一字符串的字符数为3个，且字符的种类为3种时，所述第二字符串的数量为6个。Optionally, when the number of characters in the first character string is 3 and the types of characters are 3, the number of the second character string is 6.
可选地，所述索引按照字符数进行分类存储；所述根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引，包括：Optionally, the index is classified and stored according to the number of characters; the searching the index according to the number of characters and character content of the target character string to obtain the target index includes:
根据所述目标字符串的字符数和字符内容查找目标索引集合得到目标索引；所述目标索引集合包括具有相同字符数的索引。Searching the target index set according to the character number and character content of the target character string to obtain the target index; the target index set includes indexes with the same character number.
可选地，所述对数据表中至少一个属性建立索引，包括：Optionally, said indexing at least one attribute in the data table includes:
基于全文搜索引擎对数据表中至少一个属性建立索引。At least one attribute in the data table is indexed based on a full-text search engine.
第二方面，本申请实施例还提供了一种数据查询装置，包括：In the second aspect, the embodiment of the present application also provides a data query device, including:
建立单元，用于对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；A building unit configured to index at least one attribute in the data table; one attribute in the data table corresponds to at least one first character string; continuously cut the first character string according to the number of characters to obtain a plurality of second characters string, the index is established according to the second string;
第一获取单元，用于获取用户输入的查询条件，所述查询条件包括目标字符串；a first acquiring unit, configured to acquire a query condition input by a user, where the query condition includes a target character string;
查找单元，用于根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；A search unit, configured to search the index according to the character number and character content of the target character string to obtain a target index; the character number and character content of the second character string in the target index are the same as the characters of the target character string The number and character content are the same; the target index stores a primary key;
第二获取单元，用于根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。The second obtaining unit is configured to obtain target data from the data table according to the primary key, so as to write the target data into an excel form.
可选地，所述查找单元，用于：Optionally, the search unit is configured to:
利用二分查找算法，根据所述目标字符串的字符数查找所述索引得到目标索引。A binary search algorithm is used to search the index according to the number of characters of the target character string to obtain the target index.
可选地，在所述第一字符串的字符数为3个，且字符的种类为3种时，所述第二字符串的数量为6个。Optionally, when the number of characters in the first character string is 3 and the types of characters are 3, the number of the second character string is 6.
第三方面，本申请实施例还提供了一种计算机设备，所述计算机设备包括处理器以及存储器：In the third aspect, the embodiment of the present application also provides a computer device, the computer device includes a processor and a memory:
所述存储器用于存储程序代码，并将所述程序代码传输给所述处理器；The memory is used to store program codes and transmit the program codes to the processor;
所述处理器用于根据所述程序代码中的指令执行所述的方法。The processor is configured to execute the method according to instructions in the program code.
第四方面，本申请实施例还提供了一种计算机可读存储介质，所述计算机可读存储介质用于存储计算机程序，所述计算机程序用于执行所述的方法。In a fourth aspect, the embodiment of the present application further provides a computer-readable storage medium, where the computer-readable storage medium is used to store a computer program, and the computer program is used to execute the method.
本申请实施例提供了一种数据查询方法、装置、设备及介质，对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；获取用户输入的查询条件，所述查询条件包括目标字符串；根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。可见，在本申请实施例中，根据字符数量对字符串进行连续切割，根据切割后得到的字符串对数据表建立索引，根据查询条件查找到目标索引，进而根据目标索引查找到数据表主键，再利用主键从关系型数据库中查找到完整的记录信息即目标数据，这样，通过建立索引来优化数据查询方式，将模糊匹配转为精确匹配，可以避免关系型数据库全表扫描，能够加快数据查询速度，从而加快报表生成速度。The embodiment of the present application provides a data query method, device, device and medium, which establishes an index for at least one attribute in the data table; one attribute in the data table corresponds to at least one first character string; the first character The string is continuously cut according to the number of characters to obtain a plurality of second character strings, and the index is established according to the second character string; the query condition input by the user is obtained, and the query condition includes the target character string; according to the target character string The number of characters and character content of the index are searched to obtain the target index; the number of characters and character content of the second character string in the target index are the same as the number of characters and character content of the target character string; the target index stores There is a primary key; the target data is obtained from the data table according to the primary key, so as to write the target data into an excel form. It can be seen that in the embodiment of the present application, the character string is continuously cut according to the number of characters, the data table is indexed according to the cut character string, the target index is found according to the query condition, and the primary key of the data table is found according to the target index. Then use the primary key to find the complete record information, that is, the target data, from the relational database. In this way, by building an index to optimize the data query method and convert fuzzy matching to precise matching, it can avoid full table scanning of the relational database and speed up data query. speed, resulting in faster report generation.
附图说明Description of drawings
为了更清楚地说明本申请实施例或现有技术中的技术方案，下面将对实施例或现有技术描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图是本申请的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其它的附图。In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings that need to be used in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are For some embodiments of the present application, those skilled in the art can also obtain other drawings based on these drawings without creative effort.
图1示出了本申请实施例提供的一种数据查询方法的流程示意图；FIG. 1 shows a schematic flow diagram of a data query method provided by an embodiment of the present application;
图2为本申请实施例提供的一种数据查询装置的结构框图。FIG. 2 is a structural block diagram of a data query device provided by an embodiment of the present application.
具体实施方式Detailed ways
为使本申请的上述目的、特征和优点能够更加明显易懂，下面结合附图对本申请的具体实施方式做详细的说明。In order to make the above-mentioned purpose, features and advantages of the present application more obvious and understandable, the specific implementation manners of the present application will be described in detail below in conjunction with the accompanying drawings.
在下面的描述中阐述了很多具体细节以便于充分理解本申请，但是本申请还可以采用其它不同于在此描述的其它方式来实施，本领域技术人员可以在不违背本申请内涵的情况下做类似推广，因此本申请不受下面公开的具体实施例的限制。In the following description, a lot of specific details are set forth in order to fully understand the application, but the application can also be implemented in other ways different from those described here, and those skilled in the art can do it without violating the content of the application. By analogy, the present application is therefore not limited by the specific embodiments disclosed below.
基于以上技术问题，本申请实施例提供了一种数据查询方法、装置、设备及介质，对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；获取用户输入的查询条件，所述查询条件包括目标字符串；根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。可见，在本申请实施例中，根据字符数量对字符串进行连续切割，根据切割后得到的字符串对数据表建立索引，根据查询条件查找到目标索引，进而根据目标索引查找到数据表主键，再利用主键从关系型数据库中查找到完整的记录信息即目标数据，这样，通过建立索引来优化数据查询方式，将模糊匹配转为精确匹配，可以避免关系型数据库全表扫描，能够加快数据查询速度，从而加快报表生成速度。Based on the above technical problems, the embodiment of the present application provides a data query method, device, device and medium, which establishes an index for at least one attribute in the data table; an attribute in the data table corresponds to at least one first character string; The first character string is continuously cut according to the number of characters to obtain a plurality of second character strings, and the index is established according to the second character string; the query condition input by the user is obtained, and the query condition includes the target character string; according to The character number and character content of the target character string are searched for the index to obtain the target index; the character number and character content of the second character string in the target index are the same as the character number and character content of the target character string; A primary key is stored in the target index; target data is obtained from the data table according to the primary key, so as to write the target data into an excel form. It can be seen that in the embodiment of the present application, the character string is continuously cut according to the number of characters, the data table is indexed according to the cut character string, the target index is found according to the query condition, and the primary key of the data table is found according to the target index. Then use the primary key to find the complete record information, that is, the target data, from the relational database. In this way, by building an index to optimize the data query method and convert fuzzy matching to precise matching, it can avoid full table scanning of the relational database and speed up data query. speed, resulting in faster report generation.
为了便于理解，下面结合附图对本申请实施例提供的一种数据查询方法、装置、设备及介质进行详细的说明。For ease of understanding, a data query method, device, device, and medium provided in the embodiments of the present application will be described in detail below with reference to the accompanying drawings.
参考图1所示，为本申请实施例提供的一种数据查询方法的流程示意图，该方法可以包括以下步骤。Referring to FIG. 1 , which is a schematic flowchart of a data query method provided by an embodiment of the present application, the method may include the following steps.
S101，对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串，对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立。S101, indexing at least one attribute in the data table; one attribute in the data table corresponds to at least one first character string, and the first character string is continuously cut according to the number of characters to obtain a plurality of second character strings, so The index is established according to the second character string.
在本申请实施例中，可以在每天晚上批量加载数据后，对数据库建立索引。在批量加载数据之后，数据库会进行更新，可以对更新后的数据库建立索引，即可以每天定时更新索引。In this embodiment of the application, after loading data in batches every night, the database can be indexed. After the data is loaded in batches, the database will be updated, and the updated database can be indexed, that is, the index can be updated regularly every day.
数据库用于存储数据，数据库可以是关系型数据库，关系型数据库中可以包括多个数据表，每个数据表中包括至少一个属性，比如姓名，一个属性对应至少一个第一字符串，比如，在属性为姓名时，属性值即第一字符串可以为TOMMY、TOM，按照表中的字段数据进行切割记录属性值，对所述第一字符串按照字符数进行连续切割得到多个第二字符串，然后根据第二字符串建立索引。The database is used to store data. The database can be a relational database. The relational database can include multiple data tables, and each data table includes at least one attribute, such as a name. An attribute corresponds to at least one first character string, for example, in When the attribute is a name, the attribute value, that is, the first character string can be TOMMY, TOM, cut and record the attribute value according to the field data in the table, and continuously cut the first character string according to the number of characters to obtain multiple second character strings , and then index based on the second string.
具体地，根据数据表某列数据进行文本切割，将切割结果作为索引的键，也就是如上所说的第二字符串。比如，学生信息表中某一记录的ID属性(主键)取值为10，姓名属性取值为“TOMMY”作为第一字符串，则第一字符串TOMMY的长度为x，此时x＝5，则将把该字符串切割成字符数为1-5的第二字符串，字符数为1时，第二字符串可以为“T”、“O”、“M”、“Y”；字符数为2时，第二字符串可以为“TO”、“OM”、“MM”、“MY”；字符数为3时，第二字符串可以为“TOM”、“OMM”、“MMY”；字符数为4时，第二字符串可以为“TOMM”、“OMMY”；字符数为5时，第二字符串为“TOMMY”。根据第二字符串来建立索引，索引内容为数据表中主键的键值，比如为10。Specifically, the text is cut according to a column of data in the data table, and the cut result is used as the key of the index, that is, the second character string as mentioned above. For example, if the value of the ID attribute (primary key) of a record in the student information table is 10, and the value of the name attribute is "TOMMY" as the first string, then the length of the first string TOMMY is x, and x=5 at this time , then the character string will be cut into a second character string of 1-5 characters, when the character number is 1, the second character string can be "T", "O", "M", "Y"; characters When the number is 2, the second character string can be "TO", "OM", "MM", "MY"; when the number of characters is 3, the second character string can be "TOM", "OMM", "MMY" ; When the number of characters is 4, the second character string can be "TOMM" or "OMMY"; when the number of characters is 5, the second character string is "TOMMY". An index is established according to the second character string, and the index content is the key value of the primary key in the data table, for example, 10.
此外，若针对不同的第一字符串切割会得到相同的第二字符串，则可以对相同的字符串进行索引合并。例如，如果学生信息表某一记录的ID属性(主键)取值为15，姓名属性取值为“TOM”，则按照字符数切割时，在字符数为1时，第二字符串可以为“T”、“O”、“M”，可以发现，对“TOMMY”和TOM”进行字符切割，可以得到相同的字符串“T”、“O”、“M”，则，可以对字符串“T”进行索引合并，字符串“T”对应的索引内容包括10和15，同理，字符串“O”对应的索引内容也包括10和15，字符串“M”对应的索引内容也包括10和15。在本申请实施例中，在所述第一字符串的字符数为3个，且字符的种类为3种时，所述第二字符串的数量为6个。比如，第一字符串为“TOM”，则第二字符串的数量为6个，分别为“T”、“O”、“M”、“TO”、“OM”、“TOM”。In addition, if different first character strings are cut to obtain the same second character string, index merging may be performed on the same character strings. For example, if the value of the ID attribute (primary key) of a record in the student information table is 15, and the value of the name attribute is "TOM", when cutting according to the number of characters, when the number of characters is 1, the second character string can be " T", "O", and "M", it can be found that the character strings "T", "O", and "M" can be obtained by cutting the characters of "TOMMY" and TOM", then the string " T" for index merging, the index content corresponding to the string "T" includes 10 and 15, similarly, the index content corresponding to the string "O" also includes 10 and 15, and the index content corresponding to the string "M" also includes 10 and 15. In the embodiment of the present application, when the number of characters of the first character string is 3 and the types of characters are 3, the number of the second character string is 6. For example, the first character string If the string is "TOM", the number of second character strings is 6, which are "T", "O", "M", "TO", "OM", and "TOM".
还比如，在第一字符串为“TOMMY”时，通过上述的切割之后，字符数为1时，得到4种切割结果，即4个第二字符串；字符数为2时，得到4个第二字符串；字符数为3时，得到3个第二字符串；字符数为4时，得到2个第二字符串；字符数为5时，得到1个第二字符串，总共可以得到14种切割结果，即14个第二字符串。Also for example, when the first character string is "TOMMY", after the above-mentioned cutting, when the number of characters is 1, 4 kinds of cutting results are obtained, that is, 4 second character strings; when the number of characters is 2, 4 second character strings are obtained. Two strings; when the number of characters is 3, get 3 second strings; when the number of characters is 4, get 2 second strings; when the number of characters is 5, get 1 second string, a total of 14 can be obtained Cutting results, that is, 14 second character strings.
在本申请实施例中，索引可以按照字符数进行分类存储，也可以按照字符种类进行存储。在索引按照字符数进行分类存储时，所述根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引，包括：根据所述目标字符串的字符数和字符内容查找目标索引集合得到目标索引；所述目标索引集合包括具有相同字符数的索引。In the embodiment of the present application, the index can be classified and stored according to the number of characters, or can be stored according to the type of characters. When the index is classified and stored according to the number of characters, searching the index according to the number of characters and character content of the target character string to obtain the target index includes: searching the target index set according to the number of characters and character content of the target character string A target index is obtained; the target index set includes indexes with the same number of characters.
具体地，可以按照字符数对索引进行分类存储，比如，在第一索引集合中，包括全部的字符数为1的索引，有关“T”、“O”、“M”、“Y”的索引，在第二索引集合中包括全部的字符数为4的索引，有关“TOMM”、“OMMY”的索引。Specifically, indexes can be classified and stored according to the number of characters. For example, in the first index set, all indexes with a character number of 1 are included, and indexes related to "T", "O", "M", and "Y" , the second index set includes all indexes whose number of characters is 4, and indexes related to "TOMM" and "OMMY".
在本申请实施例中，所述对数据表中至少一个属性建立索引，包括：基于全文搜索引擎(Lucene)对数据表中至少一个属性建立索引具体地，可以基于Lucene自建一套索引，并将索引按照字符数进行分类。In the embodiment of the present application, said indexing at least one attribute in the data table includes: indexing at least one attribute in the data table based on a full-text search engine (Lucene). Specifically, a set of indexes can be built by itself based on Lucene, and Sort the index by character count.
在本申请实施例中，索引可以使用B+树的结构来存储，叶子节点(最下层)存储关键字信息即属性值，以及对应的全部数据记录即目标数据，非叶子节点存储指针信息。In the embodiment of the present application, the index can be stored using a B+ tree structure. Leaf nodes (lowest layer) store keyword information, that is, attribute values, and all corresponding data records, that is, target data, and non-leaf nodes store pointer information.
S102，获取用户输入的查询条件，所述查询条件包括目标字符串。S102. Obtain a query condition input by a user, where the query condition includes a target character string.
在本申请实施例中，可以获取用户输入的查询条件，查询条件包括目标字符串。具体地，用户输入查询条件，发送请求，后台接收到请求，获取查询条件中目标字符串的长度。In the embodiment of the present application, the query condition input by the user may be obtained, and the query condition includes the target character string. Specifically, the user enters the query condition, sends a request, and the background receives the request, and obtains the length of the target string in the query condition.
S103，根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引存储有主键。S103. Search the index according to the character number and character content of the target character string to obtain a target index; the target index stores a primary key.
在本申请实施例中，所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同，所述目标索引存储有主键。具体地，首先根据目标字符串的字符数查找包括相同字符数的索引集合，比如，目标字符串为“OMMY”，字符数为4个，则确定索引集合为第二索引集合，在第二索引集合中包括有关“OMMY”、“TOMM”的索引，然后，在第二索引集合中去匹配字符内容是否为“OMMY”，则可以确定出目标索引为有关“OMMY”的索引，对应的主键键值为10。In this embodiment of the present application, the number of characters and character content of the second character string in the target index are the same as the number of characters and character content of the target character string, and the target index stores a primary key. Specifically, first search for an index set including the same number of characters according to the number of characters of the target character string. For example, if the target string is "OMMY" and the number of characters is 4, then the index set is determined to be the second index set. In the second index The set includes indexes related to "OMMY" and "TOMM", and then, in the second index set to match whether the character content is "OMMY", it can be determined that the target index is an index related to "OMMY", and the corresponding primary key key The value is 10.
在本申请实施例中，所述根据所述目标字符串的字符数查找所述索引得到目标索引，包括：利用二分查找算法，根据所述目标字符串的字符数查找所述索引得到目标索引。具体地，查询时，首先对查询字段长度选择对应类别的索引，利用二分查找算法对索引数据进行完全匹配，查找出主键后，将查询得到的主键作为查询条件再对数据库进行查找。In this embodiment of the present application, the searching the index according to the number of characters of the target character string to obtain the target index includes: using a binary search algorithm to search the index according to the number of characters of the target character string to obtain the target index. Specifically, when querying, first select the index corresponding to the length of the query field, use the binary search algorithm to completely match the index data, find out the primary key, and use the primary key obtained from the query as the query condition to search the database.
S104，根据所述主键从所述数据库中获取目标数据，以便将所述目标数据写入excel表格中。S104. Obtain target data from the database according to the primary key, so as to write the target data into an excel sheet.
在本申请实施例中，可以根据获取到的记录主键从数据表获取目标数据，并写入excel表格中，再由后台传输给前端，生成报表文件。具体地，根据获取的主键，生成数据库查询语句，根据数据表主键查询目标数据。In this embodiment of the application, the target data can be obtained from the data table according to the obtained record primary key, and written into an excel form, and then transmitted from the background to the front end to generate a report file. Specifically, a database query statement is generated according to the obtained primary key, and the target data is queried according to the primary key of the data table.
在本申请实施例中，报表数据来源为关系型数据库中的某一个数据表，可以根据用户的查询条件，将数据表中相关数据导出形成报表。In this embodiment of the application, the source of the report data is a certain data table in the relational database, and the relevant data in the data table can be exported to form a report according to the user's query conditions.
本申请实施例提供了一种数据查询方法，对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；获取用户输入的查询条件，所述查询条件包括目标字符串；根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。可见，在本申请实施例中，根据字符数量对字符串进行连续切割，根据切割后得到的字符串对数据表建立索引，根据查询条件查找到目标索引，进而根据目标索引查找到数据表主键，再利用主键从关系型数据库中查找到完整的记录信息即目标数据，这样，通过建立索引来优化数据查询方式，将模糊匹配转为精确匹配，可以避免关系型数据库全表扫描，能够加快数据查询速度，从而加快报表生成速度。The embodiment of the present application provides a data query method, which establishes an index for at least one attribute in the data table; one attribute in the data table corresponds to at least one first character string; cutting to obtain a plurality of second character strings, the index is established according to the second character strings; the query condition input by the user is obtained, the query condition includes the target character string; according to the character number and character content of the target character string Searching the index to obtain a target index; the number of characters and character content of the second character string in the target index are the same as the number of characters and character content of the target character string; the target index is stored with a primary key; according to the The primary key obtains target data from the data table, so as to write the target data into an excel form. It can be seen that in the embodiment of the present application, the character string is continuously cut according to the number of characters, the data table is indexed according to the cut character string, the target index is found according to the query condition, and the primary key of the data table is found according to the target index. Then use the primary key to find the complete record information, that is, the target data, from the relational database. In this way, by building an index to optimize the data query method and convert fuzzy matching to precise matching, it can avoid full table scanning of the relational database and speed up data query. speed, resulting in faster report generation.
基于以上数据查询方法，本申请实施例还提供了一种数据查询装置，参考图2所示，为本申请实施例提供的一种数据查询装置的结构框图，该装置可以包括：Based on the above data query method, the embodiment of the present application also provides a data query device. Referring to FIG. 2 , it is a structural block diagram of a data query device provided in the embodiment of the present application. The device may include:
建立单元100，用于对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；The
第一获取单元200，用于获取用户输入的查询条件，所述查询条件包括目标字符串；The first acquiring
查找单元300，用于根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；The
第二获取单元400，用于根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。The second obtaining
可选地，所述查找单元，用于：Optionally, the search unit is configured to:
利用二分查找算法，根据所述目标字符串的字符数查找所述索引得到目标索引。A binary search algorithm is used to search the index according to the number of characters of the target character string to obtain the target index.
可选地，在所述第一字符串的字符数为3个，且字符的种类为3种时，所述第二字符串的数量为6个。Optionally, when the number of characters in the first character string is 3 and the types of characters are 3, the number of the second character string is 6.
本申请实施例提供了一种数据查询装置，包括建立单元，用于对数据表中至少一个属性建立索引；所述数据表中的一个属性对应至少一个第一字符串；对所述第一字符串按照字符数进行连续切割得到多个第二字符串，所述索引是根据所述第二字符串建立；第一获取单元，用于获取用户输入的查询条件，所述查询条件包括目标字符串；查找单元，用于根据所述目标字符串的字符数和字符内容查找所述索引得到目标索引；所述目标索引中的第二字符串的字符数和字符内容，与所述目标字符串的字符数和字符内容相同；所述目标索引存储有主键；第二获取单元，用于根据所述主键从所述数据表中获取目标数据，以便将所述目标数据写入excel表格中。可见，在本申请实施例中，根据字符数量对字符串进行连续切割，根据切割后得到的字符串对数据表建立索引，根据查询条件查找到目标索引，进而根据目标索引查找到数据表主键，再利用主键从关系型数据库中查找到完整的记录信息即目标数据，这样，通过建立索引来优化数据查询方式，将模糊匹配转为精确匹配，可以避免关系型数据库全表扫描，能够加快数据查询速度，从而加快报表生成速度。An embodiment of the present application provides a data query device, including a building unit for building an index for at least one attribute in a data table; one attribute in the data table corresponds to at least one first character string; The string is continuously cut according to the number of characters to obtain a plurality of second character strings, and the index is established according to the second character string; the first acquisition unit is used to acquire the query condition input by the user, and the query condition includes the target character string ; The search unit is used to search the index according to the character number and character content of the target character string to obtain the target index; the character number and character content of the second character string in the target index are the same as those of the target character string The number of characters is the same as the character content; the target index stores a primary key; the second obtaining unit is used to obtain target data from the data table according to the primary key, so as to write the target data into an excel form. It can be seen that in the embodiment of the present application, the character string is continuously cut according to the number of characters, the data table is indexed according to the cut character string, the target index is found according to the query condition, and the primary key of the data table is found according to the target index. Then use the primary key to find the complete record information, that is, the target data, from the relational database. In this way, by building an index to optimize the data query method and convert fuzzy matching to precise matching, it can avoid full table scanning of the relational database and speed up data query. speed, resulting in faster report generation.
又一方面，本申请实施例提供了一种计算机设备，所述计算机设备包括处理器以及存储器：In another aspect, the embodiment of the present application provides a computer device, the computer device includes a processor and a memory:
所述存储器用于存储程序代码，并将所述程序代码传输给所述处理器；The memory is used to store program codes and transmit the program codes to the processor;
所述处理器用于根据所述程序代码中的指令执行上述实施例提供的数据查询方法。The processor is configured to execute the data query method provided in the foregoing embodiments according to instructions in the program code.
该计算机设备可以包括终端设备或服务器，前述的数据查询装置可以配置在该计算机设备中。The computer equipment may include terminal equipment or a server, and the aforementioned data query device may be configured in the computer equipment.
又一方面，本申请实施例还提供了一种存储介质，所述存储介质用于存储计算机程序，所述计算机程序用于执行上述实施例提供的数据查询方法。In yet another aspect, the embodiments of the present application further provide a storage medium, the storage medium is used to store a computer program, and the computer program is used to execute the data query method provided in the above embodiments.
另外，本申请实施例还提供了一种包括指令的计算机程序产品，当其在计算机上运行时，使得计算机执行上述实施例提供的数据查询方法。In addition, the embodiments of the present application also provide a computer program product including instructions, which, when run on a computer, cause the computer to execute the data query method provided in the foregoing embodiments.
本领域普通技术人员可以理解：实现上述方法实施例的全部或部分步骤可以通过程序指令硬件来完成，前述程序可以存储于一计算机可读取存储介质中，该程序在执行时，执行包括上述方法实施例的步骤；而前述的存储介质可以是下述介质中的至少一种：只读存储器(英文：Read-only Memory，缩写：ROM)、RAM、磁碟或者光盘等各种可以存储程序代码的介质。Those of ordinary skill in the art can understand that all or part of the steps to realize the above-mentioned method embodiments can be completed by program instruction hardware, and the aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the execution includes the above-mentioned method The steps of the embodiment; and the foregoing storage medium can be at least one of the following mediums: read-only memory (English: Read-only Memory, abbreviation: ROM), RAM, magnetic disk or optical disk, etc. can store program codes medium.
需要说明的是，本发明提供的一种数据查询方法、装置、设备及介质可用于大数据领域或金融领域。上述仅为示例，并不对本发明提供的一种数据查询方法、装置、设备及介质的应用领域进行限定。It should be noted that the data query method, device, equipment and medium provided by the present invention can be used in the field of big data or the field of finance. The foregoing is only an example, and does not limit the application field of a data query method, device, device, and medium provided by the present invention.
本说明书中的各个实施例均采用递进的方式描述，各个实施例之间相同相似的部分互相参见即可，每个实施例重点说明的都是与其它实施例的不同之处。尤其，对于装置实施例而言，由于其基本相似于方法实施例，所以描述得比较简单，相关之处参见方法实施例的部分说明即可。Each embodiment in this specification is described in a progressive manner, the same and similar parts of each embodiment can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, as for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and for relevant parts, please refer to part of the description of the method embodiment.
以上所述仅是本申请的优选实施方式，虽然本申请已以较佳实施例披露如上，然而并非用以限定本申请。任何熟悉本领域的技术人员，在不脱离本申请技术方案范围情况下，都可利用上述揭示的方法和技术内容对本申请技术方案做出许多可能的变动和修饰，或修改为等同变化的等效实施例。因此，凡是未脱离本申请技术方案的内容，依据本申请的技术实质对以上实施例所做的任何的简单修改、等同变化及修饰，均仍属于本申请技术方案保护的范围内。The above descriptions are only the preferred embodiments of the present application. Although the present application has been disclosed as above with preferred embodiments, it is not intended to limit the present application. Any person familiar with the art, without departing from the scope of the technical solution of the application, can use the methods and technical content disclosed above to make many possible changes and modifications to the technical solution of the application, or to modify the equivalent of equivalent changes Example. Therefore, any simple modifications, equivalent changes and modifications made to the above embodiments based on the technical essence of the present application that do not deviate from the content of the technical solution of the present application still fall within the protection scope of the technical solution of the present application.
Claims (10)
Translated fromChinesePriority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210938997.XACN115292322A (en) | 2022-08-05 | 2022-08-05 | Data query method, device, equipment and medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210938997.XACN115292322A (en) | 2022-08-05 | 2022-08-05 | Data query method, device, equipment and medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115292322Atrue CN115292322A (en) | 2022-11-04 |
Family
ID=83828629
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210938997.XAPendingCN115292322A (en) | 2022-08-05 | 2022-08-05 | Data query method, device, equipment and medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115292322A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116662348A (en)* | 2023-06-29 | 2023-08-29 | 中国工商银行股份有限公司 | Financial database index construction method, device, equipment and storage medium |
| CN117195262A (en)* | 2023-09-07 | 2023-12-08 | 湖南长银五八消费金融股份有限公司 | A string encryption method, device, equipment and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101727502A (en)* | 2010-01-25 | 2010-06-09 | 中兴通讯股份有限公司 | Data query method, data query device and data query system |
| CN107436911A (en)* | 2017-05-24 | 2017-12-05 | 阿里巴巴集团控股有限公司 | Fuzzy query method, device and inquiry system |
| US20190324961A1 (en)* | 2016-12-30 | 2019-10-24 | Huawei Technologies Co., Ltd. | Storage method and query method for database, and apparatus |
| WO2021184572A1 (en)* | 2020-03-20 | 2021-09-23 | 平安国际智慧城市科技股份有限公司 | Query method and apparatus, computer device and storage medium |
- 2022
- 2022-08-05CNCN202210938997.XApatent/CN115292322A/enactivePending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101727502A (en)* | 2010-01-25 | 2010-06-09 | 中兴通讯股份有限公司 | Data query method, data query device and data query system |
| US20190324961A1 (en)* | 2016-12-30 | 2019-10-24 | Huawei Technologies Co., Ltd. | Storage method and query method for database, and apparatus |
| CN107436911A (en)* | 2017-05-24 | 2017-12-05 | 阿里巴巴集团控股有限公司 | Fuzzy query method, device and inquiry system |
| WO2021184572A1 (en)* | 2020-03-20 | 2021-09-23 | 平安国际智慧城市科技股份有限公司 | Query method and apparatus, computer device and storage medium |
Non-Patent Citations (1)
| Title |
|---|
| 苏新宁: "信息检索理论与技术", 30 September 2004, 科学技术文献出版社, pages: 267 - 269* |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116662348A (en)* | 2023-06-29 | 2023-08-29 | 中国工商银行股份有限公司 | Financial database index construction method, device, equipment and storage medium |
| CN117195262A (en)* | 2023-09-07 | 2023-12-08 | 湖南长银五八消费金融股份有限公司 | A string encryption method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11030242B1 (en) | Indexing and querying semi-structured documents using a key-value store | |
| US11256852B2 (en) | Converting portions of documents between structured and unstructured data formats to improve computing efficiency and schema flexibility | |
| CN108509547B (en) | Information management method, information management system and electronic equipment | |
| US10691753B2 (en) | Memory reduced string similarity analysis | |
| CN111708805B (en) | Data query method, device, electronic device and storage medium | |
| US10671671B2 (en) | Supporting tuples in log-based representations of graph databases | |
| US11698918B2 (en) | System and method for content-based data visualization using a universal knowledge graph | |
| CN112115232A (en) | A data error correction method, device and server | |
| US9218394B2 (en) | Reading rows from memory prior to reading rows from secondary storage | |
| US20180144061A1 (en) | Edge store designs for graph databases | |
| US12079181B2 (en) | Efficient indexing for querying arrays in databases | |
| US11630829B1 (en) | Augmenting search results based on relevancy and utility | |
| US20250068644A1 (en) | Automated extract, transform, and load process | |
| CN115292322A (en) | Data query method, device, equipment and medium | |
| Dharavath et al. | Entity resolution-based jaccard similarity coefficient for heterogeneous distributed databases | |
| US10445370B2 (en) | Compound indexes for graph databases | |
| Wang et al. | Rencoder: A space-time efficient range filter with local encoder | |
| CN104391908A (en) | Locality sensitive hashing based indexing method for multiple keywords on graphs | |
| US20180357278A1 (en) | Processing aggregate queries in a graph database | |
| CN110720097A (en) | Functional equivalence of tuples and edges in graph databases | |
| CN115062047A (en) | Data optimization method and device, computer equipment and storage medium | |
| Schildgen et al. | NotaQL is not a query language! it’s for data transformation on wide-column stores | |
| CN117609468A (en) | Method and device for generating search statement | |
| CN114579573B (en) | Information retrieval method, information retrieval device, electronic equipment and storage medium | |
| Zhong et al. | 3sepias: a semi-structured search engine for personal information in dataspace system |
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 |