Multi-table merging storage method and device, storage medium and program product

Abstract

The embodiment of the invention relates to the technical field of data maintenance, and discloses a multi-table merging storage method and device, a storage medium and a program product. According to the method and the device, a plurality of first physical tables with the same primary key fields are obtained, the first physical tables with the same primary keys are associated to obtain an association table group, and a new first combined storage physical table is established in a database to combine and store all the first physical tables with the same primary key fields; the primary keys of the physical tables used for merge storage are the same as those of all the first physical tables in the association table group, so that association and storage of the multiple physical tables are realized through the same primary key, and subsequent data recording and retrieval are facilitated; moreover, the non-common primary key fields of all the first physical tables in the association table group are stored in the first physical tables, so that data of a plurality of tables can be stored through one table, resource occupation of table data is reduced, service logic is simplified, and development cost is reduced.

Description

Technical Field

[0001] Embodiments of the present invention relate to the technical field of data maintenance, and in particular to a multi-table merging storage method, device, storage medium and program product. Background Art

[0002] In the rapid development of information technology, database systems, as one of the core tools for data management, play a vital role. A database is an organized collection of data designed to efficiently store, retrieve, and manage data. It not only provides support for various applications, but also provides a data foundation for corporate decision-making, data analysis, etc. A data table is the basic unit of data storage in a relational database and has a well-defined structure. Each column in a table has a specified data type, such as an integer, text, or date, which ensures the consistency and integrity of the data. By designing a reasonable data table structure, users can effectively organize and manage data and improve the efficiency of data access. In addition, database systems usually provide rich indexing functions to speed up query operations and further improve performance.

[0003] In actual use, database tables often need to be managed and maintained, otherwise a large amount of data redundancy will be caused in the storage system. However, the inventors found that there are at least the following problems in the related technology: in some information systems, there are many similar business data that need to be stored in the database, and many similar table structures need to be used to store similar business data. There will be redundant data content in the database, which will cause a certain waste of the database's storage resources; in some information systems, many similar table structures need to be used, so there will be some redundancy in the business data stored in the table, which will cause a certain waste of storage resources; in addition, multiple similar table structures are easy to cause management confusion, and the amount of code will also increase significantly, which will increase the complexity of the upper-level software. In embedded systems, storage resources and network resources are both relatively precious resources. In order to save storage resources and network resources, it is necessary to manage data tables reasonably to reduce the occupation of storage resources by data tables. Summary of the invention

[0004] The purpose of the embodiments of the present invention is to provide a multi-table merging storage method, device, storage medium and program product, so that data tables can be reasonably managed to save the storage resources of the data tables and the occupation and consumption of network resources.

[0005] To solve the above technical problems, an embodiment of the present invention provides a multi-table merged storage method, including: obtaining multiple first physical tables with the same primary key field, associating the multiple first physical tables to obtain an associated table group; creating a new first merged storage physical table in the database, the first merged storage physical table is used to store the merged associated table group; the primary key field of the first merged storage physical table is the same as the primary key field of the first physical table; obtaining non-common primary key fields of all first physical tables in the associated table group, and storing the non-common primary key fields in the first merged storage physical table.

[0006] An embodiment of the present invention also provides an electronic device, comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the above-mentioned multi-table merge storage method.

[0007] An embodiment of the present invention further provides a computer-readable storage medium storing a computer program, including: when the computer program is executed by a processor, the multi-table merging storage method described above is implemented.

[0008] An embodiment of the present invention further provides a computer program product, including: computer instructions, which implement the above-mentioned multi-table merging storage method when executed by a processor.

[0009] In an embodiment of the present invention, multiple first physical tables with the same primary key field are obtained, and the first physical tables with the same primary key are associated to obtain an associated table group, and a new first merged storage physical table is established in the database to merge and store all the first physical tables with the same primary key field, and the primary key of the physical table used for merged storage is the same as the primary key of all the first physical tables in the associated table group, so that the association and storage of multiple physical tables are achieved through the same primary key, which is convenient for subsequent data recording and retrieval; and the non-common primary key fields of all the first physical tables in the associated table group are stored in the first physical table, so that the data of multiple tables are stored in one table, which saves the resource occupation of the table data, thereby achieving efficient use of storage resources and network resources, simplifying the business logic, and reducing the development cost.

[0010] In addition, the first merged storage physical table is also used to store a first non-common primary key field, which is a character string of variable length; storing the non-common primary key field in the first merged storage physical table includes: concatenating the values ​​of the non-common primary key fields of all the first physical tables of the associated table group to obtain a concatenated field value; compressing the concatenated field value to obtain a first target storage long character string; storing the first target storage long character string as the first non-common primary key field in the first merged storage physical table. The present application concatenates and compresses the values ​​of the non-common primary key fields of all the first physical tables in the associated table group and stores them in the first merged storage physical table, that is, the first merged storage physical table includes the primary key and the first non-common primary key field, and the content in the first non-common primary key field is obtained by concatenating multiple field values, thereby saving storage space of multiple tables and also improving the security of the storage table.

[0011] In addition, after the first merged storage physical table is newly created in the database, the method also includes: establishing a first merged storage logical table in the business logic layer, the primary key field of the first merged storage logical table is the same as the primary key field of the first merged storage physical table; the non-common primary key field of the first merged storage logical table is a first field collection; wherein the first field collection is a field collection of all non-common primary key fields of the associated table group, and the first field collection includes all non-common primary key fields of the first physical tables in the associated table group. The present application establishes a first merged storage logical table corresponding to the database in the business logic layer, so that the user can first perform operations such as data query records, new records, modification records, and deletion records on the data table in the business logic layer after interacting with the front end, and provides users with an access control mechanism for the data table through the business logic layer, thereby simplifying the complexity of the business and improving development efficiency.

[0012] In addition, the business logic layer also includes a data conversion component; wherein the data conversion component is used to update the data of the first merged storage logical table and the first merged storage physical table; the method also includes: obtaining the user's read and write data operations on the first merged storage logical table at the business logic layer; after the user writes data, the values ​​of all non-primary key fields of the first merged storage logical table are spliced ​​and compressed by the data conversion component to obtain a second target storage long string; and the second target storage long string is updated and stored in the first merged storage physical table by the data conversion component. The present application realizes data writing of the data tables of the business logic layer and the database through the data conversion component, thereby realizing efficient data intercommunication of the data of the database and the business logic layer, and improving the efficiency of data management and updating in the data tables during user business operations.

[0013] In addition, after the second target storage long string is updated and stored in the first merged storage physical table through the data conversion component, the method also includes: obtaining the user's read data operation on the first merged storage logical table at the business logic layer; decompressing the first non-common primary key field updated and stored in the first merged storage physical table through the data conversion component, and disassembling the second field collection according to the field structure of the first non-common primary key field; wherein the second field collection is the collection of all non-common primary key fields of the first merged storage logical table after the user writes data; returning the second field collection to the first merged storage logical table for update and storage through the data conversion component. The present application uses the data conversion component to re-disassemble the field structure of the non-common primary key field updated to the first merged storage physical table and return it to the business logic layer during the reading process after the data is updated, thereby achieving efficient data intercommunication between the business logic layer and the database and improving the efficiency of data table query, management and update.

[0014] In addition, the method further includes: obtaining a plurality of second physical tables having less than a preset threshold number of fields and having no identical primary key fields, and collecting them to obtain a non-associated table group; abstracting all second physical tables in the non-associated table group and storing them in a second merged storage physical table in the database. The present application reduces the number of data tables with less data volume by integrating and storing multiple non-associated second physical tables having a small number of fields and no identical primary key fields in the same physical table, thereby reducing the resource occupation of small physical tables in the database and saving data resources of the database.

[0015] In addition, the abstraction of all second physical tables in the non-associated table group and storage in the second merged storage physical table in the database specifically includes: obtaining the key fields and non-key fields of the second physical table, and storing the key fields and the non-key fields in the second merged storage physical table according to preset sequence rules; the key fields include the table name and table keywords of the second physical table; the non-key fields include the data length and data content of the second physical table. The present application is conducive to improving the efficiency of data storage, reading, retrieval, modification and other operations of the non-associated table group by storing in the second merged storage physical table according to preset sequence rules, and realizing efficient operation and management of table data. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0017] Figure 1This is the process of the multi-table merging storage method provided in an embodiment of the present application Figure 1 ;

[0018] Figure 2 This is the process of the multi-table merging storage method provided in an embodiment of the present application Figure 2 ;

[0019] Figure 3 This is a schematic diagram of the structure of a multi-table merging storage system provided in an embodiment of the present application. Figure 1 ;

[0020] Figure 4 This is a schematic diagram of the structure of a multi-table merging storage system provided in an embodiment of the present application. Figure 2 ;

[0021] Figure 5 It is a schematic diagram of the internal structure of an electronic device provided in one embodiment of the present application. DETAILED DESCRIPTION

[0022] In some information systems, many similar table structures are needed to store similar business data, which will result in redundant data content in the database, causing a certain waste of database storage resources; in addition, when there are many tables, the amount of code will also increase significantly, thereby increasing the complexity of the upper-level software. In embedded systems, it is necessary to reduce storage resources and network resources as much as possible. Therefore, a multi-table merge storage method, device, storage medium and program product are needed to reasonably manage data tables to reduce the occupation of storage resources by data tables.

[0023] To make the purpose, technical scheme and advantages of the embodiments of the present invention clearer, the embodiments of the present invention will be described in detail below in conjunction with the accompanying drawings. However, it will be appreciated by those skilled in the art that in the embodiments of the present invention, many technical details are proposed in order to enable the reader to better understand the present application. However, even without these technical details and various changes and modifications based on the following embodiments, the technical scheme claimed in the present application can be implemented. The division of the following embodiments is for the convenience of description, and should not constitute any limitation on the specific implementation of the present invention. The various embodiments can be combined and referenced with each other without contradiction.

[0024] An embodiment of the present invention relates to a multi-table merging storage method, which can be applied to devices of embedded systems, such as large-scale software systems that provide basic services to the public, or small APP software for personal use. The multi-table merging storage method includes: obtaining multiple first physical tables with the same primary key field; associating multiple first physical tables to obtain an associated table group; creating a new first merged storage physical table in the database, the first merged storage physical table is used to store the merged associated table group; the primary key field of the first merged storage physical table is the same as the primary key field of the first physical table; obtaining all non-common primary key fields of the first physical tables in the associated table group, and storing the non-common primary key fields in the first merged storage physical table. The implementation details of the multi-table merging storage method of an embodiment of the present invention are described in detail below. The following content is only the implementation details provided for the convenience of understanding, and is not necessary for the implementation of this solution.

[0025] like Figure 1 As shown, in step 101, multiple first physical tables with the same primary key field are obtained, and the multiple first physical tables are associated to obtain an associated table group.

[0026] Specifically, the physical table is the structure of the actual storage data in the database, which involves how the data is stored on the disk, the indexing method, the storage format, etc. The data in the physical table is stored in the database according to the specific implementation method. In this application, the physical table is a table built on the SQLite database. SQLite is a lightweight, relational database suitable for small to medium-sized projects or applications that need rapid development. The size of the entire SQLite database is only a few hundred KB, which is suitable for resource-constrained environments, such as mobile devices and embedded systems. It should be noted that the physical table can also be built in other databases, such as H2 Database, Apache Derby, RocksDB, LevelDB, Berkeley DB, etc., which are not limited in this application. Usually, the database system can usually be abstracted into a physical layer (Physical Layer), a logical layer (Logical Layer) and an external layer (External Layer). Among them, the physical layer is the database layer, which involves the actual storage method of data, such as data format, reading and writing technology, storage devices, etc.; the logical layer is the business logic layer, which is used to define the structure and relationship of data, and is used to handle the code ownership of detailed business logic. It is only related to the design and structure of the database and does not involve how the data is actually stored; the external layer is also the front-end display layer, which is the interface for users to interact with the database, usually including a user interface or API. The primary key is a column or combination used to uniquely identify each row of data in the database table, and has the properties of uniqueness, non-emptiness, and immutability. In an embodiment of the present application, an associated table group is obtained by associating the first physical tables with a common primary key, so that all the first physical tables in the associated table group form a table group with a common primary key according to the same primary key, which is convenient for data integration, recording, retrieval and management.

[0027] In step 102, a first merged storage physical table is newly created in the database, where the first merged storage physical table is used to store the merged associated table group; the primary key field of the first merged storage physical table is the same as the primary key field of the first physical table.

[0028] Among them, the primary key field is the specific column (column name) or field that constitutes the primary key. In the database system, the field is sometimes also called a "domain", which refers to the indivisible basic unit in the data. A domain contains a value. The field can be described by two attributes: data type (such as binary, character, string, etc.) and length (the number of bytes occupied). In a table, the primary key can be composed of one or more fields, but the primary key fields refer to the specific columns that participate in the composition of the primary key.

[0029] In step 103, all non-common primary key fields of the first physical tables in the association table group are obtained, and the non-common primary key fields are stored in the first merged storage physical table.

[0030] In some embodiments, the first merged storage physical table is also used to store a first non-common primary key field, and the first non-common primary key field is a character string of variable length; the storing of the non-common primary key field in the first merged storage physical table includes: concatenating the values ​​of the non-common primary key fields of all the first physical tables of the association table group to obtain a concatenated field value; compressing the concatenated field value to obtain a first target storage long character string; and storing the first target storage long character string as the first non-common primary key field in the first merged storage physical table. The present application concatenates and compresses the values ​​of the non-common primary key fields of all the first physical tables in the association table group and stores them in the first merged storage physical table, that is, the first merged storage physical table includes the primary key and the first non-common primary key field, and the content in the first non-common primary key field is obtained by concatenating multiple field values, thereby saving storage space of multiple tables and also improving the security of the storage table.

[0031] In some embodiments, after the first merged storage physical table is newly created in the database, the method further includes: establishing a first merged storage logical table in the business logic layer, wherein the primary key field of the first merged storage logical table is the same as the primary key field of the first merged storage physical table; the non-common primary key fields of the first merged storage logical table are a first field collection; wherein the first field collection is a field collection of all non-common primary key fields of the associated table group, and the first field collection includes all non-common primary key fields of the first physical tables in the associated table group. The present application establishes a first merged storage logical table corresponding to the database in the business logic layer, thereby facilitating the user to first perform operations such as data query records, new records, modification records, and deletion records on the data table in the business logic layer after interacting with the front end, and provides the user with an access control mechanism for the data table through the business logic layer, thereby simplifying the complexity of the business and improving development efficiency.

[0032] Specifically, a logical table is a data structure defined and saved in the business logic layer. It is a table whose table structure is defined in the business logic layer but no corresponding physical table is established in the SQLite database. It can be used to structure unstructured data during data parsing, or for data interaction with third-party systems.

[0033] In some embodiments, the business logic layer also includes a data conversion component; wherein the data conversion component is used to update the data of the first merged storage logical table and the first merged storage physical table; the method also includes: obtaining the user's read and write data operations on the first merged storage logical table at the business logic layer; after the user writes data, the values ​​of all non-primary key fields of the first merged storage logical table are concatenated and compressed by the data conversion component to obtain a second target storage long string; and the second target storage long string is updated and stored in the first merged storage physical table by the data conversion component. The present application implements data writing of the business logic layer and the data table of the database through the data conversion component, thereby realizing efficient data intercommunication of the data of the database and the business logic layer, and improving the efficiency of data management and updating in the data table during user business operations.

[0034] Specifically, the updating and storing of the second target stored long string into the first merged storage physical table through the data conversion component specifically includes: taking the second target stored long string as the first non-common primary key field, and updating and storing it into the first merged storage physical table. In the process of compressing the value of the non-primary key field, the field value compression can be performed by RLE algorithm or Huffman coding. It should be noted that those skilled in the art can understand that the value of the non-primary key field can also be compressed by other compression methods, or the compression of the value of the non-primary key field is not performed, and there is no limitation here. In addition, the business logic layer also includes an SQL conversion component, and the data conversion component and the SQL conversion component together convert the user's request into an access request to the physical table.

[0035] In some embodiments, after the second target storage long string is updated and stored in the first merged storage physical table through the data conversion component, the method further includes: obtaining the user's read data operation on the first merged storage logical table at the business logic layer; decompressing the first non-common primary key field updated and stored in the first merged storage physical table through the data conversion component, and disassembling the second field collection according to the field structure of the first non-common primary key field; wherein the second field collection is the collection of all non-common primary key fields of the first merged storage logical table after the user writes data; and returning the second field collection to the first merged storage logical table for update and storage through the data conversion component. The present application uses the data conversion component to re-disassemble the field structure of the non-common primary key field updated to the first merged storage physical table and return it to the business logic layer during the reading process after the data is updated, thereby achieving efficient data intercommunication between the business logic layer and the database and improving the efficiency of data table query, management and update.

[0036] It should be noted that when the business logic layer processes specific business logic operations, such as querying records, adding records, modifying records, deleting records, etc. on the physical table, it can only operate on one or more physical tables in the relevant table group, or it can operate on the first merged storage physical table; and when the operation is performed through the data conversion component, the data conversion component is only used to realize the data intercommunication between the first merged storage logical table and the first merged storage physical table. That is, when the user writes data to the first merged storage logical table through the data conversion component of the business logic layer, the data conversion component concatenates and compresses the values ​​of the non-common primary key fields in the first merged storage logical table to obtain the second target storage long string, and stores it in the first non-common primary key field of the first merged storage physical table. When the user reads data from the first merged storage physical table through the data conversion component of the business logic layer, the data conversion component decompresses the data of the first non-common primary key field of the first merged storage physical table and disassembles the structure of the first non-common primary key field and returns it to the business logic layer.

[0037] In a specific embodiment, for multiple physical tables that are closely related in business logic, a part of the primary keys in all the physical tables in these tables are the same, that is, multiple first physical tables in the associated table group. Now, a first merged storage logical table is established in the business logic layer for the associated table group, and the first merged storage logical table includes all the primary key fields and non-primary key fields of the associated table group. At the same time, a new first merged storage physical table is also created in the database layer. Assume that there are three first physical tables in the associated table group in the SQLite database, namely table1_P, table2_P, and table3_P. The three first physical tables are closely related in business logic, among which the primary keys of table1_P are T1A and T1B, and the non-primary key is T1C, the primary keys of table2_P are T1A, T1B, and T2A, and the non-primary keys are T2B and T2C, and the primary keys of table3_P are T1A, T1B, T2A, T2B, and T3A, and the non-primary keys are T3B, T3C, and T3D.

[0038] The SQL for table definition is as follows:

[0039] CREATE PHYSICAL TABLE Table1_P(

[0040] T1AINTEGER primary key,

[0041] T1B INTEGER primary key,

[0042] T1C TEXT);

[0043] CREATE PHYSICAL TABLE Table2_P(

[0044] T1AINTEGER primary key,

[0045] T1B INTEGER primary key,

[0046] T2AINTEGER primary key,

[0047] T2B TEXT,

[0048] T2C TEXT, );

[0050] CREATE PHYSICAL TABLE Table3_P(

[0051] T1AINTEGER primary key,

[0052] T1B INTEGER primary key,

[0053] T2AINTEGER primary key,

[0054] T3AINTEGER primary key,

[0055] T3B INTEGER,

[0056] T3C TEXT,

[0057] T3D TEXT, );

[0059] The definition information of the three tables is also saved in the logical table corresponding to the first three physical tables in the business logic layer. The definition information is consistent with the definition information in the SQLite database, and the names are table1, table2, and table3. The logical table does not necessarily have a physical table with the same name at the database level. It is just a structure defined and saved in the business logic layer. With the definition of the logical table, when obtaining specific business data in the business logic layer, the data operation can be implemented through the name of the logical table.

[0060] Since the primary keys of some of the logical tables corresponding to the three first physical tables are the same and are also related in terms of business logic, when a user needs to use the information in one of the tables, it is very likely that the user also needs to know the information in the other two tables.

[0061] Therefore, a first merged storage logical table is established in the business logic layer. The first merged storage logical table contains all the fields of the logical tables corresponding to the three first physical tables:

[0062] CREATE LOGICAL TABLE Table123(

[0063] T1AINTEGER primary key,

[0064] T1B INTEGER primary key,

[0065] T1C TEXT,

[0066] T2AINTEGER,

[0067] T2B TEXT,

[0068] T2C TEXT,

[0069] T3AINTEGER,

[0070] T3B INTEGER,

[0071] T3C TEXT,

[0072] T3D TEXT, );

[0074] At the same time, a first merge storage physical table named Table123_P is created in the SQLite database, and its fields are as follows:

[0075] CREATE PHYSICAL TABLE Table123_P(

[0076] T1AINTEGER primary key,

[0077] T1B INTEGER primary key,

[0078] data_length INTEGER,

[0079] data_content TEXT, );

[0081] After establishing the first merged storage logical table and the first merged storage physical table, users can access logical tables table1, table2, and table3 to request data according to specific business processing requirements, or access Table123 to request data. If information from tables table2 and table3 is needed while processing business related to table1, relevant information can be obtained by accessing table123 without accessing table1, table2, and table3 separately. Therefore, the access speed for such associated queries can be greatly improved. The above table table123 is a logical table. There is no table named table123 at the database level. The corresponding table is Table123_P. The access conversion between the logical table table123 and the physical table Table123_P is completed by the data conversion component and SQL conversion component of the business logic layer. Any record query, record addition, record modification, and record deletion operations on the logical table table123 will be automatically converted into operations on the physical table Table123_P. After the operation on the physical table Table123_P is completed, the business logic layer 2 will also automatically update the records in the physical tables table1_P, table2_P, and table3_P to ensure that the records in the tables table1_P, table2_P, and table3_P are consistent with the records in the table Table123_P.

[0082] The primary key in table123 corresponds to the primary key in table123_P. The information of the remaining non-primary key fields in table123 is concatenated and saved in the data_content field of table123_P. Since the data type of the data_content field is TEXT, which is a variable-length data type, text stores variable-length non-Unicode data with a maximum length of 2 31 -1 character, that is, 2147483647 characters. Theoretically, the length of the string that TEXT can store is 2147483647 characters. The data_length field in Table123_P indicates the actual length of the string stored in the data_content field.

[0083] For the convenience of parsing data, when the information of non-primary key fields in table123 is concatenated together, the value of each field is first converted into a string, and then the separator "|" is added between the values ​​of two fields. If the value of the field itself contains the separator "|", it will be converted into "\|" for escape.

[0084] When you need to access the contents of the three logical tables table1, table2, and table3, you need to access these three tables three times. The access SQL is as follows:

[0085] Select T1C from table1 where T1A="QUEC" and T1B="2024";

[0086] Select T2B,T2C from table2 where T1A="QUEC" and T1B="2024" and T2A="AA";

[0087] Select T3B,T3C,T3D from table3 where T1A="QUEC" and T1B="2024" andT2A="AA" and T3A="123456";

[0088] Here, the SQLite database needs to be accessed three times, and the returned data also needs to be associated and processed by the developer in the business logic layer. Now that the first merged storage logic table is available, the logic table table123 can be accessed once instead of accessing table1, table2, and table3 three times respectively. The SQL is as follows:

[0089] Select T1C,T2A,T2B,T2C,T3A,T3B,T3C,T3D from table1 where T1A="QUEC" and T1B="2024";

[0090] The SQL conversion component of the business logic layer converts the access request into an access request for the physical table table123_P based on the information of the saved logical tables table1, table2, table3, and table123 and the mapping relationship with the physical tables table1_P, table2_P, table3_P, and table123_P. The converted SQL is as follows:

[0091] Select data_length, data_content

[0092] from table123_P

[0093] where T1A="QUEC" and T1B="2024";

[0094] The content of the read data_content field is then converted by the data conversion component and returned to the external layer to be displayed to the user on the front end. In the embodiment of the present application, the association table group operates the data through the first merged storage logic table, which is conducive to accessing and reading the functionally interdependent data together, thereby reducing the number of database accesses, making it easier to manage and operate data, improving development efficiency and saving developers' time costs, and having good economic benefits.

[0095] like Figure 2 As shown, in some embodiments, the method further includes:

[0096] Step 201 , obtain a plurality of second physical tables whose number of fields is less than a preset threshold and have no identical primary key fields, and aggregate them to obtain a non-associated table group.

[0097] Step 202, abstract all the second physical tables in the non-associated table group and store them in the second merged storage physical table in the database. The present application reduces the number of data tables with small data volumes by integrating and storing multiple non-associated second physical tables with a small number of fields and no identical primary key fields in the same physical table, thereby reducing the resource occupation of small physical tables in the database and saving data resources of the database.

[0098] In some embodiments, the abstraction of all second physical tables in the non-associative table group and storage in the second merged storage physical table in the database specifically includes: obtaining key fields and non-key fields of the second physical table, and storing the key fields and the non-key fields in the second merged storage physical table according to preset sequence rules; the key fields include the table name and table keywords of the second physical table; the non-key fields include the data length and data content of the second physical table. The present application is conducive to improving the efficiency of data storage, reading, retrieval, modification and other operations of the non-associative table group by storing in the second merged storage physical table according to preset sequence rules, and realizing efficient operation and management of table data.

[0099] Specifically, the number of fields in the second physical table is 1 to 5, that is, the preset threshold value of the number of fields in the second physical table is 6. It is worth noting that the preset threshold value of the number of fields can be adjusted accordingly to the number of fields according to actual business needs, and this application does not impose any restrictions. In an embodiment of the present application, a table with 1 to 5 fields is a physical table with few fields, that is, a small data table. When there are a large number of small data tables in a software system, the burden on the database will be increased, and the resources of the database will not be efficiently utilized. The embodiment of the present application abstracts the small data table and stores it in the same physical table on the database, that is, the second merged storage physical table. The second merged storage physical table includes four fields of the small data table: table name, table keyword, data length, and data content. Among them, the two fields of table name and table keyword are key fields, and the two fields of data length and data content are non-key fields.

[0100] Furthermore, the second merged storage physical table does not need to be separately established or defined in the business logic layer, but the data can be managed directly through the original logical table. In terms of data storage, the resources of the database are saved by reducing the number of small data tables in the database. The business logic layer also includes a data conversion component and an SQL conversion component, and the data conversion component and the SQL conversion component together convert the user's request into an access request to the physical table.

[0101] In a specific embodiment, multiple small tables with few fields and no associated business logic can be combined to form a table. Assume that there are three tables in the SQLite database, namely table1, table2, and table3, where the primary key of table1 is t1A, and the non-primary key is t1B; the primary key of table2 is t2A and t2B, and the non-primary key is t2C and t2D; the primary key of table3 is t3A, t3B, and t3C, and the non-primary key is t3D, t3E, and t3F.

[0102] The SQL for table definition is as follows:

[0103] CREATE PHYSICAL TABLE Table1(

[0104] T1A INTEGER primary key,

[0105] T1B TEXT);

[0106] CREATE PHYSICAL TABLE Table2(

[0107] T2A INTEGER primary key,

[0108] T2B TEXT primary key,

[0109] T2C TEXT,

[0110] T2D TEXT );

[0112] CREATE PHYSICAL TABLE Table3(

[0113] T3A INTEGER primary key,

[0114] T3B INTEGER primary key,

[0115] T3C TEXT primary key,

[0116] T3D TEXT,

[0117] T3E TEXT,

[0118] T3F TEXT );

[0120] Create a physical table in the SQLite database named table123_P. The primary keys are table_name and table_key, and the non-primary keys are data_length and data_content.

[0121] CREATE PHYSICAL TABLE Table123_P(

[0122] table_name TEXT primary key,

[0123] table_key TEXT primary key,

[0124] data_length INTEGER,

[0125] data_content TEXT, );

[0127] The table_name field of table123_P is used to distinguish the names of the small tables. The table_key field is composed of the key fields in each small table, separated by the separator "|". The non-key fields of the three small tables are stored in the data_content field. If the small table has more than one non-key field, the content of each field is separated by a specific separator, such as the separator "|", which is conducive to the later parsing work after reading the data. It can be stored in a compressed form without structure. The field data_length stores the length of the data_content field.

[0128] After that, the three small tables table1, table2, and table3 do not need corresponding physical tables in the SQLite database. Instead, only the relevant definition information of tables table1, table2, and table3 is saved in the business logic layer. The definition information is consistent with the original physical table, as well as the mapping relationship with table table123_P. When users access the data of the original physical tables table1, table2, and table3, they can access the logical tables table1, table2, and table3. The business logic layer converts the user's request into an access request to the physical table table123_P through the data conversion component and the SQL conversion component.

[0129] When accessing logical tables table1, table2, and table3, for example, the SQL to access table1 is as follows:

[0130] Select t1B from table1 where t1A="123456";

[0131] The SQL conversion component of the business logic layer converts the access request into an access request for the physical table table123_P based on the saved information of the logical table table1 and the mapping relationship with the physical table table123_P. The SQL is as follows:

[0132] Select table_name, table_key, data_content

[0133] from table123_P

[0134] where table_name="table1" and table_key="123456";

[0135] The content of the read data_content field is then returned to the external layer for front-end display to the user.

[0136] In another embodiment, the SQL to access table2 is as follows:

[0137] Select t2C,t2D from table2 where t2A="123456" and t2B="abcdef";

[0138] The business logic layer converts the access request into an access request for the physical table table123_P based on the saved definition information of the logical table table2 and the mapping relationship with the physical table table123. The SQL is as follows:

[0139] Select data_content from table123_P where table_name="table2" andtable_key="123456|abcdef";

[0140] The content of the data_content field read is the concatenated value of the two fields of table2. It needs to be extracted separately using delimiters and then returned by the data conversion component to the external layer for front-end display.

[0141] In order to convert the access requests of the logical tables table1, table2, and table3 into access requests for the physical table table123_P, the relevant programs of the SQL conversion component are needed to complete this task. The data conversion component and the SQL conversion component are the intermediaries between the external layer and the database. All user access requests to the logical table will first be submitted to the data conversion component and the SQL conversion component, and then converted to form access requests to the physical tables of the database. The request content returned by the database is also first sent to the data conversion component and the SQL conversion component, and then returned to the external layer for front-end display to the user after being processed by the data conversion component and the SQL conversion component. In an embodiment of the present application, a large number of small data tables in the non-associated table group are not stored as independent physical tables in the database, which reduces the number of small physical tables in the database, thereby reducing the number of fields stored in the database, thereby reducing the difficulty of management, and access to multiple tables is accessed through the same SQL statement, which is conducive to improving the access efficiency of the data table.

[0142] In an embodiment of the present invention, multiple first physical tables with the same primary key field are obtained, and the first physical tables with the same primary key are associated to obtain an associated table group, and a new first merged storage physical table is established in the database to merge and store all the first physical tables with the same primary key field, and the primary key of the physical table used for merged storage is the same as the primary key of all the first physical tables in the associated table group, so that the association and storage of multiple physical tables are achieved through the same primary key, which is convenient for subsequent data recording and retrieval; and the non-common primary key fields of all the first physical tables in the associated table group are stored in the first physical table, so that the data of multiple tables are stored in one table, which saves the resource occupation of the table data, thereby achieving efficient use of storage resources and network resources, simplifying the business logic, and reducing the development cost.

[0143] The steps of the above method are divided only for the purpose of clear description. When implemented, they can be combined into one step or some steps can be split and decomposed into multiple steps. As long as they include the same logical relationship, they are all within the scope of protection of this patent; adding insignificant modifications to the algorithm or process or introducing insignificant designs without changing the core design of the algorithm and process are all within the scope of protection of this patent.

[0144] In addition, the examples mentioned in the above embodiments can be freely combined, and any combination can be understood as an embodiment. The "embodiment" or "example" appearing in various positions in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It can be understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0145] In summary, specific embodiments of the subject matter have been described. Other embodiments are within the scope of the appended claims. In some cases, the actions recited in the claims can be performed in a different order and still achieve the desired results. In addition, the processes depicted in the accompanying drawings do not necessarily require the specific order or sequential order shown to achieve the desired results.

[0146] In the description of the embodiments of the present application, the technical terms "first", "second", etc. are only used to distinguish different objects, and cannot be understood as indicating or implying relative importance or implicitly indicating the number, specific order or primary and secondary relationship of the indicated technical features. In the description of the embodiments of the present application, the meaning of "multiple" is more than two, unless otherwise clearly and specifically defined.

[0147] Another embodiment of the present invention relates to a multi-table merging storage system, such as Figure 3 As shown, including:

[0148] The associated table group acquisition module is used to acquire multiple first physical tables with the same primary key field, and associate the multiple first physical tables to obtain an associated table group.

[0149] The data table establishment module is used to create a first merged storage physical table in the database, wherein the first merged storage physical table is used to store the merged associated table group; the primary key field of the first merged storage physical table is the same as the primary key field of the first physical table.

[0150] The merging storage module is used to obtain the non-common primary key fields of all the first physical tables in the association table group, and store the non-common primary key fields in the first merging storage physical table.

[0151] In a specific embodiment, the multi-table merge storage system in this application involves the physical layer (Physical Layer), logical layer (Logical Layer) and external layer (External Layer) of the entire database system. Among them, the physical layer is the database layer, which involves the actual storage method of data, such as data format, reading and writing technology, storage device, etc.; the logical layer is the business logic layer, which is used to define the structure and relationship of data and the code ownership for processing detailed business logic, which is only related to the design and structure of the database and does not involve how the data is actually stored; the external layer is also the front-end display layer, which is the interface for users to interact with the database, usually including a user interface or API.

[0152] like Figure 4 As shown, the external layer, i.e., the front-end display layer, includes a data input and output module and a first communication module; the logic layer, i.e., the business logic layer, includes a second communication module, a logic table module, a merged storage logic table module, a data conversion component, and an SQL conversion component for interacting with the first communication module; the physical layer, i.e., the database, includes a merged storage physical table module and an associated table group module. When implementing communication, the user operates the data through the front end, and interacts with the business logic layer through the first communication module; the business logic layer directly communicates and interacts with the physical table in the associated table group module of the database through the logic table module according to the user's operation, and can also perform data operations through the merged storage logic table module. At this time, the data operations performed through the merged storage logic table module are further processed by the data conversion component and the SQL conversion component, and then interact with the merged storage physical table module in the database. In addition, the merged storage physical table module and the associated table group module in the database can also realize data intercommunication for data update.

[0153] It is not difficult to find that this embodiment is a device embodiment corresponding to the above method embodiment, and this embodiment can be implemented in conjunction with the above method embodiment. The relevant technical details mentioned in the above method embodiment are still valid in this embodiment, and in order to reduce repetition, they are not repeated here. Accordingly, the relevant technical details mentioned in this embodiment can also be applied in the above method embodiment.

[0154] It is worth mentioning that all modules involved in this embodiment are logic modules. In practical applications, a logic unit can be a physical unit, a part of a physical unit, or a combination of multiple physical units. In addition, in order to highlight the innovative part of the present invention, this embodiment does not introduce units that are not closely related to solving the technical problem proposed by the present invention, but this does not mean that there are no other units in this embodiment.

[0155] Another embodiment of the present invention relates to an electronic device, such as Figure 5 As shown, it includes at least one processor; and a memory connected to the at least one processor in communication; wherein the memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the multi-table merging storage method as described above.

[0156] Among them, the memory and the processor are connected in a bus manner, and the bus may include any number of interconnected buses and bridges, and the bus connects various circuits of one or more processors and memories together. The bus can also connect various other circuits such as peripherals, voltage regulators, and power management circuits, which are well known in the art and are therefore not further described herein. The bus interface provides an interface between the bus and the transceiver. The transceiver can be one element or multiple elements, such as multiple receivers and transmitters, providing a unit for communicating with various other devices on a transmission medium. The data processed by the processor is transmitted on a wireless medium via an antenna, and further, the antenna also receives data and transmits the data to the processor.

[0157] The processor is responsible for managing the bus and general processing, and can also provide various functions, including timing, peripheral interfaces, voltage regulation, power management, and other control functions. Memory can be used to store data used by the processor when performing operations.

[0158] Another embodiment of the present invention relates to a computer-readable storage medium storing a computer program, which implements the above method embodiment when executed by a processor.

[0159] That is, those skilled in the art can understand that all or part of the steps in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a program, and the program is stored in a storage medium, including several instructions to enable a device (which can be a single-chip microcomputer, chip, etc.) or a processor to execute all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program codes.

[0160] Another embodiment of the present invention relates to a computer program product, which stores a computer program including computer instructions, and the computer instructions implement the above method embodiment when executed by a processor.

[0161] Those skilled in the art will appreciate that the above embodiments are specific embodiments for implementing the present invention, and that in actual applications, various changes may be made in form and detail without departing from the spirit and scope of the present invention.

Claims

1. A multi-table merging storage method, characterized in that: include: Acquire multiple first physical tables with the same primary key field, and associate the multiple first physical tables to obtain an associated table group; Create a first merged storage physical table in the database, where the first merged storage physical table is used to store the merged association table group; The primary key field of the first merged storage physical table is the same as the primary key field of the first physical table; Acquire non-common primary key fields of all first physical tables in the association table group, and store the non-common primary key fields in the first merged storage physical table.

2. The multi-table merging storage method according to claim 1 is characterized in that: The first merged storage physical table is further used to store a first non-common primary key field, where the first non-common primary key field is a character string of variable length; The storing the non-common primary key fields into the first merged storage physical table includes: Concatenate the values ​​of the non-common primary key fields of all the first physical tables of the association table group to obtain a concatenated field value; Compressing the concatenated field value to obtain a first target storage long character string; The first target storage long character string is used as the first non-common primary key field and stored in the first merged storage physical table.

3. The multi-table merging storage method according to claim 1 is characterized in that: After the database creates a new first merged storage physical table, the method further includes: Establishing a first merged storage logical table at the business logic layer, wherein the primary key field of the first merged storage logical table is the same as the primary key field of the first merged storage physical table; The non-common primary key fields of the first merged storage logical table are a first field collection; wherein the first field collection is a field collection of all non-common primary key fields of the associated table group, and the first field collection includes the non-common primary key fields of all first physical tables in the associated table group.

4. The multi-table merging storage method according to claim 3 is characterized in that: The business logic layer further includes a data conversion component; wherein the data conversion component is used to update the data of the first merged storage logical table and the first merged storage physical table; The method further comprises: Acquiring a user's read and write data operations on the first merged storage logic table at the business logic layer; After the data conversion component concatenates and compresses the values ​​of all non-primary key fields of the first merged storage logic table after the user writes the data, a second target storage long string is obtained; The second target storage long character string is updated and stored in the first merged storage physical table through the data conversion component.

5. The multi-table merging storage method according to claim 4 is characterized in that: After the second target storage long character string is updated and stored in the first merged storage physical table by the data conversion component, the method further includes: Acquire a user's data read operation on the first merged storage logic table at the business logic layer; Decompressing the first non-common primary key field updated and stored in the first merged storage physical table by the data conversion component, and disassembling the first non-common primary key field according to the field structure of the first non-common primary key field to obtain a second field collection; wherein the second field collection is a collection of all non-common primary key fields of the first merged storage logical table after the user writes data; The second field collection is returned to the first merged storage logic table through the data conversion component for update storage.

6. The multi-table merging storage method according to claim 1 is characterized in that: The method further comprises: Acquire multiple second physical tables whose number of fields is less than a preset threshold and which do not have the same primary key field, and aggregate them to obtain a non-associated table group; All second physical tables in the non-associated table group are abstracted and stored in a second merged storage physical table in the database.

7. The multi-table merging storage method according to claim 6 is characterized in that: The step of abstracting all the second physical tables in the non-associated table group and storing them in a second merged storage physical table in the database specifically includes: Acquire key fields and non-key fields of the second physical table, and store the key fields and the non-key fields into the second merged storage physical table according to a preset sequence rule; The key fields include the table name and table keywords of the second physical table; the non-key fields include the data length and data content of the second physical table.

8. An electronic device, characterized in that: include: at least one processor; as well as, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the multi-table merging storage method as described in any one of claims 1 to 7.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the multi-table merging storage method according to any one of claims 1 to 7 is implemented.

10. A computer program product, characterized in that The method comprises computer instructions, which, when executed by a processor, implements the multi-table merging storage method as claimed in any one of claims 1 to 7.

Images