Search

CN-116257551-B - Processing method, storage medium and device for database query statement

CN116257551BCN 116257551 BCN116257551 BCN 116257551BCN-116257551-B

Abstract

The invention provides a processing method of database query sentences, a storage medium and computer equipment. The method comprises the steps of obtaining query sentences containing UNION ALL keywords, judging whether column properties of data tables related to the UNION ALL keywords are consistent, if not, converting the column properties of the data tables to be consistent, and continuing to execute the query sentences. According to the processing method of the database query statement, column attributes in two query statements related to the UNION ALL keywords in the database query statement are converted into the same data type, so that sub-queries of the UNION ALL are eliminated, the time for querying the database is shortened, the response speed of the database is improved, and the performance of the database can be greatly improved.

Inventors

  • Fan Guoteng
  • ZHANG JINGTAO

Assignees

  • 北京人大金仓信息技术股份有限公司

Dates

Publication Date
20260505
Application Date
20230113

Claims (8)

  1. 1. A method of processing database query statements, comprising: acquiring a query sentence containing UNION ALL keywords; Judging whether the column properties of the data table related to the UNION ALL keywords are consistent; if not, converting the column properties of the data table to be consistent; continuing to execute the query statement; The step of converting the column properties of the data table to be consistent comprises the steps of obtaining a preset conversion rule of the database, judging whether the conversion rule can convert the column properties of the data table, and if so, converting the column properties of the data table according to the conversion rule; The step of obtaining the preset conversion rule of the database comprises the steps of obtaining a system table corresponding to the conversion rule from the database; the step of judging whether the conversion rule can convert the column attribute of the data table comprises the steps of inquiring other types which can be converted by the column attribute of the data table in the system table and generating a type set of the column attribute, taking an intersection of the type set of the column attribute of the data table related to the UNION ALL keyword, and judging whether type information exists in the intersection.
  2. 2. The method for processing a database query statement as claimed in claim 1, wherein, The step of judging whether the column properties of the data table related to the UNION ALL keyword are consistent comprises the following steps: respectively acquiring column attribute IDs of the data tables; and judging whether column attribute IDs of the data tables are equal or not.
  3. 3. The method for processing a database query statement as claimed in claim 1, wherein, The step of judging whether the column properties of the data table related to the UNION ALL keyword are consistent comprises the following steps: If yes, the column attributes of the data table are not processed, and the query statement is continuously executed.
  4. 4. The method for processing a database query statement as claimed in claim 1, wherein, The set of types includes other, more extensive types that the column attributes of the data table can convert to and the types of the column attributes themselves.
  5. 5. The method for processing a database query statement as claimed in claim 1, wherein, The step of determining whether the conversion rule can convert the column attribute of the data table further includes: If not, the conversion processing is not performed, and the query operation is continuously executed.
  6. 6. The method for processing a database query statement as claimed in claim 1, wherein, The step of converting the column attributes of the data table according to the conversion rule includes: generating a new target column according to the converted column attribute; and eliminating the sub-query of the UNION ALL.
  7. 7. A machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of processing a database query statement as claimed in any one of claims 1 to 6.
  8. 8. A computer device comprising a memory, a processor and a machine executable program stored on the memory and running on the processor, and the processor when executing the machine executable program implements a method of processing a database query statement according to any one of claims 1 to 6.

Description

Processing method, storage medium and device for database query statement Technical Field The present invention relates to database technologies, and in particular, to a method, a storage medium, and an apparatus for processing a database query statement. Background In some database usage scenarios, it is desirable to merge information in multiple data tables, and a general method of choice is to merge two result sets using a UNION ALL key in a query statement. But the use of the UNION ALL key has several restrictions that SELECT statements within the UNION ALL must have the same number of columns and that columns must also have similar data types. When the corresponding column attributes in the SELECT statement to which the UNION ALL relates are only similar data types, the internal execution of the UNION ALL can consume a lot of time, resulting in performance degradation and slow database response. Disclosure of Invention It is an object of the invention to improve the performance of a database. It is a further object of the invention to provide a response speed of the database. It is a further object of the present invention that the database be able to autonomously convert the column attributes to which the UNION ALL relates to the same type. In particular, the invention provides a processing method of database query sentences, which comprises the following steps: acquiring a query sentence containing UNION ALL keywords; Judging whether column properties of a data table related to the UNION ALL keywords are consistent or not; if not, converting the column properties of the data table to be consistent; the query statement continues to be executed. Optionally, the step of determining whether column properties of the data table related to the UNION ALL keyword are consistent includes: Respectively acquiring column attribute IDs of a data table; it is determined whether column attribute IDs of the data table are equal. Optionally, the step of determining whether column properties of the data table related to the UNION ALL keywords are consistent includes: If yes, the column attributes of the data table are not processed, and the query statement is continuously executed. Optionally, the step of reconciling column properties of the data table comprises: acquiring a preset conversion rule of a database; judging whether the conversion rule can convert the column attribute of the data table; if yes, converting the column attribute of the data table according to the conversion rule. Optionally, the step of acquiring the conversion rule preset in the database comprises the steps of acquiring a system table corresponding to the conversion rule in the database; The step of judging whether the conversion rule can convert the column attribute of the data table comprises the steps of inquiring other types which can be converted by the column attribute of the data table in the system table and generating a type set of the column attribute, taking an intersection set of the column attribute type set of the data table related to the UNION ALL key word, and judging whether the type information exists in the intersection set. Alternatively, the set of types includes other, more extensive types that the column attributes of the data table can translate and the types of the column attributes themselves. Optionally, the step of determining whether the conversion rule can convert the column attribute of the data table further includes: If not, the conversion processing is not performed, and the query operation is continuously executed. Optionally, the step of converting the column properties of the data table according to the conversion rule comprises: Generating a new target column according to the converted column attribute; sub-queries of UNION ALL are eliminated. According to another aspect of the present invention, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of processing a database query statement of any of the above. According to still another aspect of the present invention, there is also provided a computer device including a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implementing a method of processing a database query statement of any of the above when executing the machine executable program. The processing method of the database query statement comprises the steps of firstly obtaining the database query statement with the UNION ALL keywords, then judging whether the column properties of a data table related to each UNION ALL keyword are consistent, if not, converting the column properties of the data table to be consistent, then generating a new target column according to the type, eliminating sub-queries of the original UNION ALL, and continuing to execute the query statement. By the method, when the database executes