Database data table management method and device, medium and product

Abstract

The invention provides a data table management method and device of a database, a medium and a product. The data table is provided with a preset column, and the data storage mode of the preset column is defined as sqlvariant. The data table management method of the database comprises the steps of receiving to-be-stored data needing to be stored in a preset column; if the to-be-stored data belongs to the preset data type, converting the to-be-stored data into a binary format; the to-be-stored data is stored in a preset column according to a preset storage structure, the preset storage structure comprises a head information storage area and an actual value storage area, the head information storage area at least stores the data type of the to-be-stored data, and the actual value storage area stores the to-be-stored data in a binary format. According to the method, the B-tree index is conveniently constructed for the data, so that positioning can be directly carried out through the binary value when the data is searched, the data type does not need to be analyzed line by line and then comparison is carried out, the query efficiency is improved, and the query overhead is reduced.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database data table management method, equipment, medium and product. Background Art

[0002] Generally speaking, in the application process of the database, it is necessary to define the data type that can be stored in each column when creating a data table. As the application scenarios of database technology become more and more extensive, the data managed by the database is becoming more and more complex. In order to make data storage more flexible, sql_variant has begun to be used as a special data type in the database, which allows multiple different data types to be stored in the same column of the data table. Specifically, by defining the data type of a column in the table as sql_variant when creating the data table, then in the subsequent operation process, the column can store data of multiple data types, such as numeric values, characters, dates, and other types of data. However, since each data type has its own storage format and size, defining the data type as sql_variant increases the query overhead of the data. Summary of the Invention

[0003] An object of the present invention is to provide a database data table management method, device, medium and product that help reduce the query overhead of sql_variant data columns.

[0004] In particular, the present invention provides a method for managing a data table of a database, wherein the data table is provided with a predetermined column, and the data storage mode of the predetermined column is defined as sql_variant, wherein the method for managing a data table of a database comprises:

[0005] receiving data to be stored that needs to be stored in the predetermined column;

[0006] If the data to be stored belongs to a preset data type, converting the data to be stored into a binary format;

[0007] The data to be stored is stored in the predetermined column according to a preset storage structure, wherein the preset storage structure includes a header information storage area and an actual value storage area, wherein the header information storage area stores at least the data type of the data to be stored, and the actual value storage area stores the data to be stored in binary format.

[0008] Optionally, the header information storage area also stores the precision, scale, actual data length, sorting rules and maximum length of the data to be stored.

[0009] Optionally, receiving an instruction to read data in the predetermined column;

[0010] Read the corresponding data according to the read instruction and record it as data to be processed;

[0011] The data to be processed is converted into data of original data type according to the information stored in the header information storage area of ​​the data to be processed and the binary format data stored in the actual value storage area.

[0012] Optionally, receiving an operation instruction to convert the data in the predetermined column into a set data type;

[0013] Read the data to be converted from the predetermined column and read the data type recorded in the header information storage area of ​​the data to be converted;

[0014] It is determined whether the data type recorded in the header information storage area of ​​the data to be converted can be converted into the set data type. If so, the data to be converted is converted into data of the set data type and outputted.

[0015] Optionally, before the step of reading the data to be converted from the predetermined column and reading the data type recorded in the header information storage area of ​​the data to be converted, the step includes:

[0016] It is determined whether the set data type belongs to the preset data type. If so, the steps of reading the data to be converted from the predetermined column and reading the data type recorded in the header information storage area of ​​the data to be converted are performed.

[0017] Optionally, after the step of receiving the data to be stored in the predetermined column, the step includes:

[0018] Check whether the size of the data to be stored is greater than or equal to a preset byte value. If so, prohibit storing the data to be stored. If not, determine whether the data to be stored belongs to the preset data type.

[0019] Optionally, recording the read frequency of each primitive data type in the predetermined column;

[0020] It is detected that the reading frequency of a primitive data type reaches a preset threshold, and an additional index is built for the primitive data type whose reading frequency reaches the preset threshold.

[0021] According to another aspect of the present invention, a computer device is also provided, comprising a memory, a processor, and a computer executable program stored in the memory and running on the processor, and when the processor executes the computer executable program, the data table management method of the database according to any one of the above items is implemented.

[0022] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer-executable program is stored. When the computer-executable program is executed by a processor, the method for managing a database table according to any one of the above items is implemented.

[0023] According to another aspect of the present invention, a computer program product is provided, comprising a computer executable program, wherein when the computer executable program is executed by a processor, the method for managing a database table according to any one of the above items is implemented.

[0024] The database table management method of the present invention comprises the following steps: upon receiving data to be stored in a predetermined column, if the data to be stored belongs to a preset data type, converting the data to be stored into a binary format, and then storing the data to be stored in the predetermined column according to a preset storage structure. The preset storage structure includes a header information storage area and an actual value storage area. The header information storage area stores at least the data type of the data to be stored, and the actual value storage area stores the data to be stored in binary format. In other words, the data to be stored of the preset data type is uniformly converted into a binary format and stored using a unified preset storage structure. This allows data of the preset data type to have a unified sorting rule, facilitating the construction of a B-tree index for the data. When searching for data, data can be directly located by binary value, eliminating the need to parse the data type row by row and then compare them, thereby improving query efficiency and reducing query overhead. Furthermore, data of multiple data types is stored using a unified preset storage structure, eliminating the need to design a separate storage structure for each data type, reducing storage engine complexity, facilitating unified management, and reducing storage overhead. Furthermore, binary format data is also easier to transmit over a network. Furthermore, reading and writing binary format data is generally faster than processing text format data, which helps improve database performance, especially when processing large amounts of data.

[0025] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0027] Figure 1 is a schematic flow chart of a method for managing a database table according to an embodiment of the present invention;

[0028] Figure 2is a schematic conceptual diagram of a preset storage structure according to an embodiment of the present invention;

[0029] Figure 3 is a schematic flow chart of a method for managing a database table according to another embodiment of the present invention;

[0030] Figure 4 is a schematic flow chart of a method for managing a database table according to yet another embodiment of the present invention;

[0031] Figure 5 is a schematic flow chart of a method for managing a database table according to yet another embodiment of the present invention;

[0032] Figure 6 is a schematic flow chart of a method for managing a database table according to yet another embodiment of the present invention;

[0033] Figure 7 is a schematic diagram of a computer device according to one embodiment of the present invention;

[0034] Figure 8 is a schematic diagram of a computer-readable storage medium according to one embodiment of the present invention;

[0035] Figure 9 is a schematic diagram of a computer program product according to one embodiment of the present invention. DETAILED DESCRIPTION

[0036] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and that these embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0037] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0038] The flowchart provided by the present invention is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical ideas provided by the present embodiment method, additional changes can be made to the above method.

[0039] like Figure 1 As shown, in one embodiment, a data table of a database is provided with a predetermined column, and the data storage mode of the predetermined column is defined as sql_variant. The data table management method of the database generally includes:

[0040] Step S101: receiving data to be stored in a predetermined column, specifically, receiving an operation statement for storing data in a predetermined column.

[0041] Step S102 determines whether the data to be stored belongs to a preset data type. If so, step S103 is executed; if not, step S105 is executed. Specifically, the preset data types include datetime2, datetimeoffset, datetime, smalldatetime, date, time, float, real, decimal, money, smallmoney, bigint, int, smallint, tinyint, bit, nvarchar, nchar, varchar, char, varbinary, binary, and uniqueidentifier. In other words, the process determines whether the original data type of the data to be stored belongs to one of the preset data types.

[0042] Step S103: convert the data to be stored into a binary format. Specifically, convert the data to be stored into a binary format.

[0043] Step S104: The data to be stored is stored in a predetermined column according to a preset storage structure. The preset storage structure includes a header information storage area and an actual value storage area. The header information storage area stores at least the data type of the data to be stored, and the actual value storage area stores the data to be stored in binary format. Specifically, the header information storage area also stores the precision, scale, actual data length, sorting rules, and maximum length of the data to be stored.

[0044] Reference Figure 2The figure shows the concept of the preset storage structure. The header information storage area (Header) stores the following relevant information: data type (basetype), which is the original data type of the data to be stored, which is one of the preset data types mentioned above; precision (precision), which is the total number of digits of the numerical data to be stored; scale (scale), which is the number of digits of the decimal part of the numerical data to be stored, for example, the data 123.45 has a precision of 5 and a scale of 2; actual data length (reallength), which is the number of bytes occupied by the data to be stored; sorting rule (collation), which is the sorting rule of the data column corresponding to the original data type to be stored, such as pinyin order, alphabetical order, etc.; maximum length (maxlength), which is the maximum number of bytes allowed by the original data type to which the data to be stored belongs. The actual value storage area (Value) stores the data to be stored converted into binary format.

[0045] Step S105: prohibiting the storage of the data to be stored. Specifically, if the data type of the data to be stored is not a preset data type, it is prohibited to store it in a predetermined column.

[0046] In the solution of this embodiment, upon receiving data to be stored in a predetermined column, if the data to be stored belongs to a preset data type, the data to be stored is converted to binary format and stored in the predetermined column according to a preset storage structure. The preset storage structure includes a header information storage area and an actual value storage area. The header information storage area stores at least the data type of the data to be stored, and the actual value storage area stores the data to be stored in binary format. In other words, the data to be stored of the preset data type is uniformly converted to binary format and stored using a unified preset storage structure. This allows data of the preset data type to have a unified sorting rule, facilitating the construction of a B-tree index for the data. When searching for data, it is possible to locate it directly by binary value, eliminating the need to parse the data type row by row and then compare it, thereby improving query efficiency and reducing query overhead. Furthermore, data of multiple data types is stored using a unified preset storage structure, eliminating the need to design a separate storage structure for each data type. This reduces the complexity of the storage engine, facilitates unified management, and reduces storage overhead. Furthermore, data in binary format is also easier to transmit across a network. Furthermore, reading and writing binary format data is generally faster than processing text format data, which helps improve database performance, especially when processing large amounts of data. Only the data to be stored that belongs to the preset data type is converted into binary format, ensuring that the original data type of the data to be stored in binary format has a predictable storage size and clear sorting and comparison rules, which is convenient for subsequent management.

[0047] Furthermore, by storing the precision, scale, actual data length, sorting rules and maximum length of the data to be stored in the header information storage area, it is convenient to provide a basis for subsequent conversion from binary format to original data type, thereby helping to ensure the accuracy of the conversion.

[0048] like Figure 3 As shown, in one embodiment, the data table management method of a database generally includes:

[0049] Step S301: receiving an instruction to read data from a predetermined column, specifically, receiving an operation statement to read data from a predetermined column, such as a query statement.

[0050] Step S302: read the corresponding data according to the read instruction and record it as data to be processed. Specifically, the data in the corresponding preset storage structure is read.

[0051] Step S303 converts the data to be processed into data of the original data type based on the information stored in the header information storage area and the binary format data stored in the actual value storage area. Specifically, after reading the data in the preset storage structure, the original data type of the data (i.e., the data type before conversion to binary format) can be obtained based on the data type stored in the header information storage area. The binary format data is then converted into the original data type, thereby obtaining data of the original data type, i.e., the data in its original state.

[0052] After receiving a read instruction for data stored in a preset storage structure in a predetermined column, the corresponding data is read according to the read instruction and recorded as data to be processed. The data to be processed is converted into data of the original data type according to the information stored in the header information storage area of ​​the data to be processed and the binary format data stored in the actual value storage area, so that the data converted into binary format can be accurately converted back to the original data type.

[0053] like Figure 4 As shown, in one embodiment, the data table management method of a database generally includes:

[0054] Step S401 receives an instruction to convert data in a predetermined column to a set data type. Specifically, during data application, it is sometimes necessary to convert data of one data type to another data type for calculation or use. For example, converting 100 of type int to 100.00 of type decimal for financial calculations. This step receives an instruction to convert data stored in a preset storage structure to a set data type.

[0055] Step S402: determine whether the set data type belongs to the preset data type. If yes, execute step S403; if no, execute step S406. Specifically, first determine whether the set data type to be converted belongs to the preset data type. The preset data type is described in the above embodiment.

[0056] Step S403: Read the data to be converted from the predetermined column and read the data type recorded in the header information storage area of ​​the data to be converted, specifically, read the original data type of the data to be converted.

[0057] Step S404: determine whether the data type recorded in the header information storage area of ​​the data to be converted can be converted into the set data type. If so, execute step S405; if not, execute step S406. Specifically, determine whether the original data type of the data to be converted can be converted into the set data type.

[0058] Step S405: convert the data to be converted into data of a set data type. Specifically, first convert the data to be converted in binary format into its original data type, and then convert the data converted into the original data type into the set data type.

[0059] Step S406: Forbid the conversion to the set data type. Specifically, if the set data type is not a preset data type, or the original data type does not support conversion to the set data type, the conversion of the data to the set data type is prohibited. In addition, an error message is output.

[0060] In the solution of this embodiment, two checks are performed during the conversion of data in a predetermined column to a set data type. Conversion to the set data type is permitted only when the set data type is within the preset data type and the data type recorded in the header information storage area of ​​the data to be converted is capable of being converted to the set data type. This prevents illegal data types from bypassing system-level rules and being stored in the data table, thereby ensuring data consistency and preventing data availability from being compromised. Furthermore, by first checking whether the set data type is within the preset data type, if the conditions are not met, there is no need to read the header information storage area, thus saving operating resources.

[0061] like Figure 5 As shown, in one embodiment, the data table management method of a database generally includes:

[0062] Step S501: receiving data to be stored in a predetermined column.

[0063] Step S502: Check whether the size of the data to be stored is greater than or equal to a preset byte value. If so, execute step S506; if not, execute step S503. Specifically, the preset byte value can be set to 8000 bytes, that is, storage is prohibited if the size is greater than 8000 bytes.

[0064] Step S503 , determining whether the data to be stored belongs to a preset data type, if so, executing step S504 , if not, executing step S506 .

[0065] Step S504: convert the data to be stored into binary format.

[0066] Step S505 : storing the data to be stored in a predetermined column according to a preset storage structure.

[0067] Step S506: prohibiting storage of the data to be stored.

[0068] By limiting the size of the data to be stored, it helps to avoid subsequent performance problems caused by converting too much data into binary format for storage, including excessive index nodes, affecting query efficiency, and excessive conversion costs.

[0069] It should be noted that for steps not described in detail, please refer to the above description.

[0070] like Figure 6 As shown, in one embodiment, the data table management method of a database generally includes:

[0071] Step S601: Record the reading frequency of each primitive data type in a predetermined column. Specifically, when reading data, record the primitive data type of the read data, thereby recording the reading frequency of the corresponding primitive data type, that is, the number of reads within a set time interval.

[0072] Step S602 detects that the read frequency of a primitive data type has reached a preset threshold, and constructs an additional index for the primitive data type whose read frequency has reached the preset threshold. Specifically, upon detecting that the primitive data type has been read a preset number of times within a set time interval, an additional index is constructed for the primitive data type. Consequently, subsequent queries for data of this primitive data type can directly use the additional index, avoiding full column scans and significantly improving query performance.

[0073] This embodiment also provides a computer device and a computer-readable storage medium. Figure 7 is a schematic diagram of a computer device 10 according to one embodiment of the present invention. Figure 8 is a schematic diagram of a computer-readable storage medium 20 according to one embodiment of the present invention.

[0074] The computer device 10 may include a memory 110, a processor 120, and a computer executable program 11 stored in the memory 110 and running on the processor 120, and when the processor 120 executes the computer executable program 11, the database data table management method of any of the above embodiments is implemented.

[0075] The computer-readable storage medium 20 stores a computer-executable program 11 thereon. When the computer-executable program 11 is executed by a processor, the method for managing a database table according to any of the above embodiments can be implemented.

[0076] This embodiment also provides a computer program product. Figure 9 1 is a schematic diagram of a computer program product 30 according to an embodiment of the present invention. The computer program product 30 includes a computer executable program 11. When the computer executable program 11 is executed by the processor 120, it implements any of the above-mentioned database data table management methods.

[0077] Specifically, the computer executable program 11 for performing the operations of the present invention can be assembly instructions, instruction set architecture (ISA) instructions, computer instructions, computer-related instructions, microcode, firmware instructions, state setting data, or source code or object code written in any combination of one or more programming languages.

[0078] For the purposes of the description of this embodiment, the computer-readable storage medium 20 can be any device that can contain, store, communicate, propagate, or transmit a program for use with an instruction execution system, device, or apparatus, or in conjunction with such instruction execution systems, devices, or apparatuses. More specific examples (a non-exhaustive list) of computer-readable media include the following: an electrical connection having one or more wirings (electronic devices), a portable computer disk cartridge (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disc read-only memory (CDROM). In addition, the computer-readable storage medium 20 can even be paper or other suitable medium on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, deciphering, or processing in other suitable ways as necessary, and then stored in a computer memory.

[0079] It should be understood that each part of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0080] Computer device 10 may be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smartphone. In some examples, computer device 10 may be a cloud acquisition node. Computer device 10 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Generally, program modules may include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. Computer device 10 may be implemented in a distributed cloud acquisition environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud acquisition environment, program modules may be located on local or remote acquisition system storage media, including storage devices.

[0081] The computer device 10 may include a processor 120 adapted to execute stored instructions, and a memory 110 that provides temporary storage for the instructions during operation. The processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any number of other configurations. The memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0082] The processor 120 may be connected to an I / O interface (input / output interface) suitable for connecting the computer device 10 to one or more I / O devices (input / output devices) via a system interconnect (e.g., PCI, PCI-Express, etc.). The I / O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touchpad or a touch screen, etc. The I / O devices may be built-in components of the computer device 10, or may be devices externally connected to an acquisition device.

[0083] Processor 120 can also be linked to the display interface that is suitable for connecting computer device 10 to display device through system interconnection.Display device can comprise the display screen that is built-in component of computer device 10.Display device can also comprise the computer monitor, television or projector etc. that are externally connected to computer device 10.In addition, network interface controller (network interface controller, NIC) can be suitable for connecting computer device 10 to network through system interconnection.In certain embodiments, NIC can use any suitable interface or protocol (such as Internet Small Computer System Interface etc.) to transmit data.Network can be cellular network, radio network, wide area network (WAN)), local area network (LAN) or Internet etc.Remote device can be connected to computer device through network.

[0084] At this point, those skilled in the art will recognize that, although a number of exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications consistent with the principles of the present invention may be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.

Claims

1. A method for managing a data table of a database, wherein the data table is provided with a predetermined column, and the data storage mode of the predetermined column is defined as sql_variant, wherein: The data table management method of the database includes: receiving data to be stored that needs to be stored in the predetermined column; If the data to be stored belongs to a preset data type, converting the data to be stored into a binary format; The data to be stored is stored in the predetermined column according to a preset storage structure, wherein the preset storage structure includes a header information storage area and an actual value storage area, wherein the header information storage area stores at least the data type of the data to be stored, and the actual value storage area stores the data to be stored in binary format.

2. The method for managing a database table according to claim 1, wherein: The header information storage area also stores the precision, scale, actual data length, sorting rules and maximum length of the data to be stored.

3. The method for managing a database table according to claim 2, wherein: receiving a read instruction for data in the predetermined column; Read the corresponding data according to the read instruction and record it as data to be processed; The data to be processed is converted into data of original data type according to the information stored in the header information storage area of ​​the data to be processed and the binary format data stored in the actual value storage area.

4. The method for managing a database table according to claim 2, wherein: receiving an operation instruction to convert the data in the predetermined column into a set data type; Read the data to be converted from the predetermined column and read the data type recorded in the header information storage area of ​​the data to be converted; It is determined whether the data type recorded in the header information storage area of ​​the data to be converted can be converted into the set data type. If so, the data to be converted is converted into data of the set data type and outputted.

5. The method for managing a database table according to claim 4, wherein: Before the step of reading the data to be converted from the predetermined column and reading the data type recorded in the header information storage area of ​​the data to be converted, the method further comprises: It is determined whether the set data type belongs to the preset data type. If so, the steps of reading the data to be converted from the predetermined column and reading the data type recorded in the header information storage area of ​​the data to be converted are performed.

6. The method for managing a database table according to claim 1, wherein: After the step of receiving the data to be stored in the predetermined column, the following steps are included: Check whether the size of the data to be stored is greater than or equal to a preset byte value. If so, prohibit storing the data to be stored. If not, determine whether the data to be stored belongs to the preset data type.

7. The method for managing a database table according to claim 1, wherein: Record the read frequency of each primitive data type in a predetermined column; It is detected that the reading frequency of a primitive data type reaches a preset threshold, and an additional index is built for the primitive data type whose reading frequency reaches the preset threshold.

8. A computer device comprising a memory, a processor, and a computer executable program stored in the memory and running on the processor, wherein the processor implements the database data table management method according to any one of claims 1 to 7 when executing the computer executable program.

9. A computer-readable storage medium having a computer-executable program stored thereon, wherein when the computer-executable program is executed by a processor, the method for managing a database table according to any one of claims 1 to 7 is implemented.

10. A computer program product, comprising a computer executable program, wherein when the computer executable program is executed by a processor, the method for managing a database table according to any one of claims 1 to 7 is implemented.

Images