Sub-table data processing method and device

[0026] The following describes exemplary embodiments of the present invention with reference to the accompanying drawings, which include various details of the embodiments of the present invention to facilitate understanding, and should be regarded as merely exemplary. Therefore, those of ordinary skill in the art should realize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of the present invention. Likewise, for clarity and conciseness, descriptions of well-known functions and structures are omitted in the following description.

[0027] figure 1 It is a schematic diagram of the basic flow of the method for processing sub-table data according to an embodiment of the present invention. Such as figure 1 As shown, the embodiment of the present invention provides a method for processing sub-table data, including:

[0028] Step S101. Obtain the data to be processed and the instruction of the dynamic structured query language (SQL, Structured Query Language);

[0029] Step S102. Determine whether the data to be processed carries a primary key; if so, determine the data table corresponding to the data to be processed according to the data table number in the primary key;

[0030] Step S103. In the data table, process the data to be processed according to instructions of the dynamic structured query language.

[0031] The data table number can be the serial number of the data table or the data table ID. The primary key can refer to the unique record of certain data in the database. The embodiment of the present invention adopts the technical means of directly positioning the data table correctly according to the primary key, and executing the instructions of the dynamic structured query language in the data table, without looping all the data tables that need to be operated, and effectively avoiding the structure of the database. The cyclical redundancy caused by complexity improves the efficiency of processing sub-table data, thereby reducing database access pressure and improving database performance.

[0032] In the embodiment of the present invention, the primary key is generated in the following manner: obtain the sub-table field and determine the hash code value of the sub-table field; according to the hash code value of the sub-table field and the total number of data tables, The remainder of the hash code value of the sub-table field divided by the total number of data tables is used as the data table number corresponding to the sub-table field; the media access control address (ie MAC address) of the server is obtained to determine the media access The hash code value of the control address; according to the hash code value of the media access control address and the total number of data tables, the remainder of the hash code value of the media access control address divided by the total number of data tables is taken as Serial number; generate the primary key according to the current time stamp, the serial number, the database number and the data table number. After the primary key is generated, the primary key needs to be added to the data newly added to the data table. The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), so that the primary key can store the information of the database and the data table, and realizes the use of the primary key to locate the database and the data table in the process of processing sub-table data. Beneficial effect. SnowFlake is an algorithm that generates a globally unique ID (ie primary key) in a distributed cluster. In the embodiment of the present invention, the globally unique ID in the distributed cluster can also be generated by a third-party similar distributed application coordination service zookerper.

[0033] In the embodiment of the present invention, the primary key sequentially includes: a sign bit, a 41-bit time stamp, an 8-bit serial number, a 6-bit database number, and an 8-bit data table number. figure 2 Is a schematic diagram of a primary key generation strategy according to an embodiment of the present invention; such as figure 2 As shown, the first bit is the sign bit, which has no business meaning; the following 41 bits represent the timestamp, in milliseconds, the 41 bits can represent the value of 2^41-1 milliseconds, and the unit year is 69 Year; the next 8-digit serial number is used to distinguish the same data generated in the same millisecond. In a cluster environment, when multiple instances have the same time stamp, 8-digits need to be generated according to the mac address of the instance The following 6-digit database number represents the serial number of the generated database, a total of 63 databases are supported; the last 8-digit data table number represents the serial number of the generated data table, a total of 128 database tables are supported . The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), so that the primary key can store the information of the database and the data table, and realizes the use of the primary key to locate the database and the data table in the process of processing sub-table data. Beneficial effect.

[0034] In the embodiment of the present invention, determining the data table corresponding to the data to be processed according to the data table number in the primary key includes: determining, according to the primary key, the last 8 bits in the primary key as the data table number; according to the data table No., locate the data table corresponding to the data to be processed. The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), enables the primary key to store the information of the data table, and realizes the beneficial effect of using the primary key to locate the data table in the process of processing subtable data.

[0035] In the embodiment of the present invention, the method further includes: if the data to be processed does not carry a primary key, determining whether there is a sub-table field; if there is a sub-table field, determining the hash code value of the sub-table field; The hash code value of the sub-table field and the total number of data tables, the remainder of dividing the hash code value of the sub-table field by the total number of data tables, is used as the data table number corresponding to the data to be processed, The data table corresponding to the data to be processed is determined according to the data table number; if there is no sub-table field, the data table corresponding to the data to be processed is cyclically searched in all data tables. The sub-table field is the basis for storing data in one table in blocks into multiple data tables, and determines the result of sub-table. For example, user information is stored in two data tables according to "gender", and in the process of dividing the table, "sex" is the table field. In the case of no primary key, the embodiment of the present invention adopts the technical means of locating data tables according to the sub-table fields, eliminating the need to cycle all the data tables that need to be operated, and effectively avoiding the problem of redundant cycles caused by the complexity of the database structure , Improve the efficiency of processing sub-table data, thereby reducing the access pressure of the database and improving the performance of the database.

[0036] In the embodiment of the present invention, it is determined whether the data to be processed carries a primary key; if so, the data table corresponding to the data to be processed is determined according to the data table number in the primary key, and further includes: if the dynamic structured query The language instruction is an instruction to modify data or delete data or query data, and the data to be processed carries a primary key, then: determine the data table corresponding to the data to be processed according to the data table number in the primary key; if said The instruction of the dynamic structured query language is an instruction to add data, then: the data to be processed does not carry a primary key; obtain the sub-table field, and determine the hash code value of the sub-table field; according to the hash of the sub-table field The code value and the total number of data tables, the remainder of the hash code value of the sub-table field divided by the total number of data tables is used as the data table number corresponding to the data to be processed, and the data table number is determined according to the data table number. The data table corresponding to the data to be processed. For the new data that needs to be added to the data table, because it does not carry the primary key, it is necessary to locate the data table according to the sub-table field (that is, determine the data table to which the new data is to be added); after generating the primary key according to the sub-table field , And then add the primary key to the new data, so that you can use the primary key to directly locate the data table when you modify, delete, and query the new data in the future, which improves the efficiency of processing sub-table data and reduces the database Deal with data pressure.

[0037] An embodiment for realizing newly added data is further proposed, and the steps are as follows:

[0038] Generate dynamic SQL statements (or instructions) with new functions according to the SQL rules of the mybatis framework; the parameters representing the database tables in the dynamic SQL statements are dynamic parameters;

[0039] Calculate the hash code value according to the sub-table field, divide by the total number of data tables and take the remainder to obtain the data table number;

[0040] Generate a unique primary key based on the timestamp, serial number, database sequence and data table sequence, where the serial number is calculated by calculating the hashCode value of the Media Access Control (Media Access Control or Medium Access Control, MAC) address, and dividing it by the hashCode value Take the remainder of the total number of data tables;

[0041] Specifically, the invoke method in the proxy class MapperProxy can be used to initialize the primary key of the entity class object with the generated primary key (the generated primary key will be added to the new data), and the data table determined according to the data table number will be used to obtain the data table Parameter and dynamically replace the parameter with the parameter in the dynamic SQL statement; after the dynamic replacement, the new data is directly added and saved in the data table to complete the data addition.

[0042] Among them, the timestamp represents a long integer binary code of the current time, which can be understood as the current time. The serial number is an 8-bit binary code generated by the MAC address of the physical machine (server). The database table sequence is generated according to routing rules An 8-bit 2 binary code. Dynamic SQL statements support dynamic replacement of database tables that need to be added or that need to be queried, such as the statement: [Table A] in "select*from[Table A]" is dynamically generated according to routing rules. The hashCode value is an int type value calculated by the toolkit JDK based on the address or string or number of the object. Mybatis is a middleware that supports data persistence layer operation and dynamic replacement.

[0043] Further, the implementation examples for realizing other data processing functions besides the newly added functions are as follows:

[0044] Generate dynamic SQL statements (or instructions) with new functions according to the SQL rules of the mybatis framework. The parameters of the database tables in the dynamic SQL statements are dynamic parameters;

[0045] You can use the invoke method of MapperProxy to determine whether there is a primary key. If there is a primary key, then directly locate the corresponding data table based on the primary key, dynamically replace the parameters of the corresponding database table, and then execute the dynamic SQL statement. If there is no primary key, then judge whether the value of the sub-table field exists, if it exists, obtain the data table number according to the sub-table field, so as to locate the corresponding data table, dynamically replace the parameters of the corresponding database table, and then execute the dynamic SQL statement. If it does not exist, then it is necessary to recursively recursively correspond to the data table (that is, loop through all the data tables of the current data structure, query the data that meets the conditions, and the data table where the data is located is the corresponding data table), and execute the dynamic SQL statement.

[0046] image 3 Is a schematic diagram of a preferred flow of the method for processing sub-table data according to an embodiment of the present invention; image 3 As shown, the specific modification, deletion, and query process can be as follows:

[0047] Modification process: Determine whether there is a primary key in the modified condition, if it exists, directly obtain the last 8 digits of the id and directly locate the data table. If there is no primary key, determine whether there is a sub-table field to calculate the hash value and calculate the data table, modify the data and return. If none of them exist, you need to cycle through all tables to modify the data.

[0048] Deletion process: Determine whether there is a primary key in the modified condition. If it exists, directly obtain the last 8 bits of the id and directly define it in the data table. If there is no primary key, determine whether there is a sub-table field to calculate the hash value and calculate the data table, delete the data and return. If none of them exist, then all tables need to be cycled to delete data.

[0049] Query process: Determine whether there is a primary key in the modified condition, if it exists, directly obtain the last 8 digits of the id and directly define it in the data table. If there is no primary key, determine whether there is a sub-table field to calculate the hash value and calculate the data table, query the data and return. If none of them exist, then all tables need to be looped for data query operations.

[0050] The embodiment of the present invention provides an apparatus 400 for processing sub-table data, including: an acquisition module 401, configured to: acquire data to be processed and instructions of a dynamic structured query language; and a determining data table module 402, configured to: determine the Whether the data to be processed carries a primary key; if so, the data table corresponding to the data to be processed is determined according to the data table number in the primary key; the execution instruction module 403 is used for: in the data table, according to the dynamic The instructions of the structured query language process the data to be processed. The embodiment of the present invention adopts the technical means of directly positioning the data table correctly according to the primary key, and executing the instructions of the dynamic structured query language in the data table, without looping all the data tables that need to be operated, and effectively avoiding the structure of the database. The cyclical redundancy caused by complexity improves the efficiency of processing sub-table data, thereby reducing database access pressure and improving database performance.

[0051] In the embodiment of the present invention, the device further includes a primary key generation module, configured to: obtain the sub-table field and determine the hash code value of the sub-table field; according to the hash code value of the sub-table field and the value of the data table The total quantity, the remainder of dividing the hash code value of the sub-table field by the total quantity of the data table, is used as the data table number corresponding to the sub-table field; the media access control address of the server is obtained, and the media access control is determined The hash code value of the address; according to the hash code value of the media access control address and the total number of data tables, the remainder of the hash code value of the media access control address divided by the total number of data tables is used as a sequence Number; Generate the primary key based on the current time stamp, the serial number, the database number, and the data table number. The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), so that the primary key can store the information of the database and the data table, and realizes the use of the primary key to locate the database and the data table in the process of processing sub-table data. Beneficial effect.

[0052] In the embodiment of the present invention, the primary key generation module is further configured to: the generated primary key includes a sign bit, a 41-bit timestamp, an 8-bit serial number, a 6-bit database number, and an 8-bit data table number in sequence. The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), so that the primary key can store the information of the database and the data table, and realizes the use of the primary key to locate the database and the data table in the process of processing sub-table data. Beneficial effect.

[0053] In the embodiment of the present invention, the determining data table module is further used to: determine the last 8 bits in the primary key as the data table number according to the primary key; and locate the data table corresponding to the data to be processed according to the data table number . The embodiment of the present invention optimizes the generation mode of the primary key based on the snowflake algorithm (snowFlake), enables the primary key to store the information of the data table, and realizes the beneficial effect of using the primary key to locate the data table in the process of processing subtable data.

[0054] In the embodiment of the present invention, the determining data table module is further configured to: if the data to be processed does not carry a primary key, determine whether there is a sub-table field; if there is a sub-table field, determine the hash value of the sub-table field. Greek code value; according to the hash code value of the sub-table field and the total number of data tables, the remainder of the hash code value of the sub-table field divided by the total number of data tables is used as the corresponding data to be processed According to the data table number of the data table, the data table corresponding to the data to be processed is determined; if there is no sub-table field, the data table corresponding to the data to be processed is cyclically searched in all data tables. In the case of no primary key, the embodiment of the present invention adopts the technical means of locating data tables according to the sub-table fields, eliminating the need to cycle all the data tables that need to be operated, and effectively avoiding the problem of redundant cycles caused by the complexity of the database structure , Improve the efficiency of processing sub-table data, thereby reducing the access pressure of the database and improving the performance of the database.

[0055] In the embodiment of the present invention, the determining data table module is further used for: if the instruction of the dynamic structured query language is an instruction to modify data or delete data or query data, and the data to be processed carries a primary key, then: Determine the data table corresponding to the data to be processed according to the data table number in the primary key; if the instruction of the dynamic structured query language is an instruction to add data, then: the data to be processed does not carry the primary key; obtain points The table field determines the hash code value of the sub-table field; according to the hash code value of the sub-table field and the total number of data tables, the hash code value of the sub-table field is divided by the total number of data tables The remainder of the quantity is used as the data table number corresponding to the data to be processed, and the data table corresponding to the data to be processed is determined according to the data table number. For the new data that needs to be added to the data table, because it does not carry the primary key, it is necessary to locate the data table according to the sub-table field (that is, determine the data table to which the new data is to be added); after generating the primary key according to the sub-table field , And then add the primary key to the new data, so that you can use the primary key to directly locate the data table when you modify, delete, and query the new data in the future, which improves the efficiency of processing sub-table data, thereby reducing the database Deal with data pressure.

[0056] Figure 5 An exemplary system architecture 500 that can apply the method for processing sub-meter data or the apparatus for processing sub-meter data according to the embodiment of the present invention is shown.

[0057] Such as Figure 5 As shown, the system architecture 500 may include terminal devices 501, 502, 503, a network 504, and a server 505. The network 504 is used to provide a medium for communication links between the terminal devices 501, 502, 503 and the server 505. The network 504 may include various connection types, such as wired, wireless communication links, or fiber optic cables, and so on.

[0058] The user can use the terminal devices 501, 502, and 503 to interact with the server 505 via the network 504 to receive or send messages and so on. Various communication client applications, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, and social platform software, may be installed on the terminal devices 501, 502, and 503.

[0059] The terminal devices 501, 502, and 503 may be various electronic devices that have a display screen and support web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and so on.

[0060] The server 505 may be a server that provides various services, for example, a background management server that provides support for shopping websites browsed by users using the terminal devices 501, 502, and 503. The background management server can analyze and process the received product information query request and other data, and feed back the processing result to the terminal device.

[0061] It should be noted that the method for processing sub-meter data provided by the embodiment of the present invention is generally executed by the server 505, and accordingly, the device for processing sub-meter data is generally set in the server 505.

[0062] Should understand, Figure 5 The numbers of terminal devices, networks, and servers in the are only indicative. According to implementation needs, there can be any number of terminal devices, networks and servers.

[0063] According to the embodiments of the present invention, the present invention also provides an electronic device and a computer-readable medium.

[0064] The electronic device in the embodiment of the present invention includes: one or more processors; a storage device for storing one or more programs. When the one or more programs are executed by the one or more processors, the One or more processors implement the method for processing sub-table data as proposed in the embodiment of the present invention.

[0065] The computer-readable medium of the embodiment of the present invention has a computer program stored thereon, and when the program is executed by a processor, the method for processing sub-table data as proposed in the embodiment of the present invention is implemented.

[0066] Reference below Image 6 , Which shows a schematic structural diagram of a computer system 600 suitable for implementing the terminal device of the embodiment of the present invention. Image 6 The terminal device shown is only an example, and should not bring any limitation to the function and scope of use of the embodiments of the present invention.

[0067] Such as Image 6 As shown, the computer system 600 includes a central processing unit (CPU) 601, which can execute various programs according to a program stored in a read-only memory (ROM) 602 or a program loaded from a storage part 608 to a random access memory (RAM) 603. Kind of appropriate actions and processing. In the RAM 603, various programs and data required for the operation of the system 600 are also stored. The CPU 601, ROM 602, and RAM 603 are connected to each other through a bus 604. An input/output (I/O) interface 605 is also connected to the bus 604.

[0068] The following components are connected to the I/O interface 605: an input part 606 including a keyboard, a mouse, etc.; an output part 607 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc. and speakers, etc.; a storage part 608 including a hard disk, etc. ; And a communication section 609 including a network interface card such as a LAN card, a modem, etc. The communication section 609 performs communication processing via a network such as the Internet. The driver 610 is also connected to the I/O interface 605 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed, so that the computer program read from it is installed into the storage part 608 as needed.

[0069] In particular, according to the disclosed embodiments of the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the disclosed embodiments of the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program may be downloaded and installed from the network through the communication part 609, and/or installed from the removable medium 611. When the computer program is executed by the central processing unit (CPU) 601, the above-mentioned functions defined in the system of the present invention are executed.

[0070] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium, or any combination of the two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections with one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable Programmable read only memory (EPROM or flash memory), optical fiber, portable compact disk read only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination of the above. In the present invention, the computer-readable storage medium may be any tangible medium that contains or stores a program, and the program may be used by or in combination with an instruction execution system, apparatus, or device. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as a part of a carrier wave, and a computer-readable program code is carried therein. This propagated data signal can take many forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the foregoing. The computer-readable signal medium may also be any computer-readable medium other than the computer-readable storage medium. The computer-readable medium may send, propagate, or transmit the program for use by or in combination with the instruction execution system, apparatus, or device . The program code contained on the computer-readable medium can be transmitted by any suitable medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0071] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions, and operations of the system, method, and computer program product according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagram may represent a module, program segment, or part of code, and the above-mentioned module, program segment, or part of code contains one or more for realizing the specified logical function Executable instructions. It should also be noted that, in some alternative implementations, the functions marked in the block may also occur in a different order from the order marked in the drawings. For example, two blocks shown in succession can actually be executed substantially in parallel, or they can sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram or flowchart, and the combination of blocks in the block diagram or flowchart, can be implemented by a dedicated hardware-based system that performs the specified function or operation, or can be It is realized by a combination of dedicated hardware and computer instructions.

[0072] The modules involved in the embodiments described in the present invention can be implemented in software or hardware. The described modules may also be provided in the processor. For example, it may be described as: a processor includes: an acquiring module, a determining data table module, and an executing instruction module. Among them, the names of these modules do not constitute a limitation on the module itself under certain circumstances. For example, the acquisition module can also be described as "a module for acquiring processing data and instructions of a dynamic structured query language."

[0073] As another aspect, the present invention also provides a computer-readable medium. The computer-readable medium may be included in the device described in the foregoing embodiment; or it may exist alone without being assembled into the device. The above-mentioned computer-readable medium carries one or more programs. When the above-mentioned one or more programs are executed by a device, the device includes: Step S101. Obtaining instructions for processing data and a dynamic structured query language; Step S102 Determine whether the data to be processed carries a primary key; if so, determine the data table corresponding to the data to be processed according to the data table number in the primary key; step S103. In the data table, according to the dynamic structure The instructions of the query language process the data to be processed.

[0074] Because the embodiment of the present invention adopts the technical means of directly positioning the data table correctly according to the primary key and executing the instructions of the dynamic structured query language in the data table, there is no need to loop all the data tables that need to be operated, which effectively avoids the structure of the database. The cyclical redundancy caused by the complexity of the database improves the efficiency of processing sub-table data, thereby reducing the pressure of processing data in the database and improving the performance of the database.

[0075] The foregoing specific implementations do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that, depending on design requirements and other factors, various modifications, combinations, sub-combinations, and substitutions can occur. Any modification, equivalent replacement and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.