Flob data storage method based on MySQL protocol memory database

Abstract

The invention discloses a Blob data storage method based on a MySQL protocol memory database. The method comprises the following steps: step A, establishing connection between a client and a server of the MySQL protocol memory database; step B, the client sends an Insert SQL preprocessing request to the server by sending a message, and the server receives the Insert SQL preprocessing request, preprocesses the Insert SQL preprocessing request and returns an OK message; c, the client side sends a COMSTMTSENDLONGDATA message, and after the server side receives the COMSTMTSENDLONGDATA message, the Blob data are stored in a memory or a file system according to a server side algorithm; step D, the client sends an Insert SQL execution request to the server by sending a COMSTMTEXECUTE message, the server stores parameter data in a memory after receiving the parameter data, binds the Blob data stored in the step C, and returns an OK message after successful storage; according to the method, the problems of memory overflow and write-in failure possibly caused by occupation of a large amount of memory and long-time write-in of the Blob data are solved.

Description

Technical Field

[0001] The present invention belongs to the technical field of data management, and in particular relates to a Blob data storage method based on a MySQL protocol memory database. Background Art

[0002] MySQL is a widely used relational database management system (RDBMS). MySQL provides rich functions such as data storage and management, data retrieval and query, data processing and optimization, data backup and recovery, making it an ideal choice for enterprises and developers to build applications of all sizes.

[0003] In-Memory Database (IMDB) is a special database management system. Its main feature is that all data is stored in the main memory (RAM) of the computer system, rather than on the traditional hard disk drive or other non-volatile storage media. Since the read and write speed of memory is much higher than that of disk storage, the in-memory database can provide very fast data access and processing speed, and the response time can usually reach microseconds.

[0004] MySQL's MEMORY storage engine (sometimes also called the HEAP storage engine) is an in-memory storage engine that stores the entire table's data in the server's RAM instead of on the hard disk. Because the data is stored in memory, the MEMORY engine provides very fast data access speeds and is not limited by disk I / O. This makes the MEMORY engine very suitable for creating temporary tables and cache tables, especially in scenarios where a large number of read and write operations are required.

[0005] However, the existing MySQL MEMORY storage engine does not support the storage of Blob data, while other common memory databases (such as Redis, Apache Ignite, H2 Database, etc.) support Blob data storage, but do not support the MySQL protocol. In addition, these memory databases have some shortcomings when processing Blob data:

[0006] As a relational database management system, H2 Database supports two modes: memory storage and file storage. When H2 Database adopts the memory storage mode, it can be regarded as an in-memory database. H2 Database supports storing Blob large objects in memory, but when the amount of data stored in memory is too large, H2 Database has a memory overflow problem. At the same time, H2 Database does not support a single-table mixed storage mode of memory storage and file storage, so it cannot solve the memory overflow problem when storing a large amount of Blob data.

[0007] Redis uses memory as its main storage medium. When the amount of data is too large, it uses expiration or elimination strategies to release memory space. Therefore, when the amount of data is large, Redis may face data loss problems. If Redis turns off the data elimination strategy, memory overflow problems may occur. In addition, Redis provides data persistence, but it uses the full data persistence method, which is less efficient.

[0008] Apache Ignite provides a distributed in-memory data grid platform. Similar to Redis, Apache Ignite's data is also mainly stored in memory. Apache Ignite's memory is divided into two parts: on-heap memory and off-heap memory. Apache Ignite first stores data in the on-heap memory, and when the on-heap memory is saturated, it stores the data in the off-heap memory. When the off-heap memory is also saturated, Ignite will begin to overflow older or less accessed data entries to disk. These data can still be accessed through queries and operations, but they no longer reside in memory. In addition, Apache Ignite also supports persistent storage, that is, while storing data in memory, the data is also stored persistently. Apache Ignite's disk storage and persistent storage are all full storage of table data, and are not stored separately for Blob data.

[0009] The Chinese patent with the publication number CN112506937A discloses an online configuration method, device, equipment and medium for a database model, the method comprising: S1, providing a data source configuration interface on a browser, and saving the configuration information after obtaining the connection of the corresponding database after the user configuration is completed; S2, providing a database model configuration interface on a browser, sending the configured model information to the backend, and saving it in the data table and data column model of the corresponding data source; S3, providing an interface for synchronizing database model information on a browser, for selecting the data of the unsynchronized database model and sending it to the backend, and then synchronizing it to the corresponding data source through the database synchronizer by the backend; S4, if the synchronization is successful, updating the synchronization status label on the interface of the synchronization database model information for the user to view. According to the rules described in the method, when the size of the Blob data stored in the MySQL database is greater than a certain threshold, it is automatically converted to MEDIUMBLOB or LONGBLOB, which does not take into account the actual use scenario of the data, which may lead to additional storage overhead and management complexity; and for larger Blob data, MySQL will store it on disk, and accessing these data involves a large number of disk read and write operations, which may lead to slow data retrieval and increased latency. Summary of the invention

[0010] The present invention provides a Blob data storage method based on a MySQL protocol memory database, aiming to solve the problem that Blob data may cause memory overflow and write failure due to occupying a large amount of memory and taking a long time to write.

[0011] In order to solve the above technical problems, the present invention provides a Blob data storage method based on a MySQL protocol memory database, comprising the following steps:

[0012] Step A: The client establishes a connection with the MySQL protocol in-memory database server, and the server generates a unique Connection_ID for each connection.

[0013] Step B: The client sends a COM_STMT_PREPARE message to send an Insert SQL preprocessing request to the server. The Insert SQL preprocessing request contains a Blob type field column. After receiving the COM_STMT_PREPARE message, the server preprocesses the Insert SQL. If the preprocessing is successful, it returns a COM_STMT_PREPARE_OK message, which contains Statement_ID and all parameter information in the Insert SQL.

[0014] Step C: The client sends a COM_STMT_SEND_LONG_DATA message to the server, which contains the Blob data. After receiving the COM_STMT_SEND_LONG_DATA message, the server stores the Blob data in the memory or file system according to the server algorithm.

[0015] Step D: The client sends a COM_STMT_EXECUTE message to the server to execute the Insert SQL request, which contains all parameter data of non-Blob type. After receiving the COM_STMT_EXECUTE message, the server stores the parameter data in the memory and binds the Blob data saved in step C. After the storage is successful, the server returns the COM_STMT_EXECUTE_OK message.

[0016] Preferably, after receiving the message in step B, the server preprocesses the Insert SQL, and after the preprocessing is successful, returns a COM_STMT_PREPARE_OK message specifically as follows:

[0017] Step B1: The server receives the COM_STMT_PREPARE message, which contains the InsertSQL preprocessing request that needs to be preprocessed.

[0018] Step B2: Parse the Insert SQL text string in the message and convert it into SQL string data.

[0019] Step B3: Create a PrepareContext object by parsing the Insert SQL preprocessing request of the COM_STMT_PREPARE message. The object represents the context of this preprocessing request and stores information including Connection_ID and PrepareStatment object. Connection_ID is used to identify each independent connection between the client and the server, and PrepareStatment object is used to store basic information written in this Insert.

[0020] Step B4: Generate Statement_ID, which is used to uniquely identify each Insert write. The created PrepareContext object and Statement_ID are in a one-to-one mapping relationship.

[0021] Step B5: According to the SQL string data parsed in step B2, determine whether the Insert SQL has been preprocessed. Preprocessed means that the client has transmitted the same Insert SQL and generated a PreparedStatment object. If the Insert SQL has been preprocessed, execute step B6; otherwise, execute step B7.

[0022] Step B6: Read the basic information written in this Insert from the cache according to the SQL string data, obtain the cached PreparedStatment object, and jump to step B9.

[0023] Step B7: Parse the basic information in the Insert SQL and create a PreparedStatment object.

[0024] Step B8: Cache the PreparedStatment object in memory, and its cached identifier is the SQL text string.

[0025] Step B9: Store the PreparedStatment object into the PrepareContext object.

[0026] Step B10: Return a COM_STMT_PREPARE_OK message, which includes the Statement_ID generated in step B4 and all parameter information in the Insert SQL, including the parameter type and parameter sequence number.

[0027] Preferably, the basic information written in this Insert operation in step B3 specifically includes: the name of the target table into which the Insert operation writes data, the column fields involved, the value corresponding to each field, and which data are passed in through parameterization.

[0028] Preferably, after the server receives the COM_STMT_SEND_LONG_DATA message in step C, the Blob data is stored in the memory or file system according to the server algorithm as follows:

[0029] Step C1: The server receives the COM_STMT_SEND_LONG_DATA message.

[0030] Step C2: Parse the Statement_ID in the message.

[0031] Step C3: Parse the Param_ID in the message. Param_ID is used to identify the sequence number of the parameter.

[0032] Step C4: Create a Blob object, read binary data from the message, and store the binary data in the Blob object.

[0033] Step C5: Take out Connection_ID from the connection, and then judge whether it is a new parameter according to Connection_ID, Statement_ID and Param_ID. If Connection_ID, Statement_ID and Statement_ID all exist, it means that the parameters of message transmission are existing parameters, and jump to step C6. Otherwise, jump to step C8.

[0034] Step C6: Read the Blob object index from the memory according to Connection_ID, Statement_ID and Param_ID. The Blob object index is used to store the location where the Blob object is stored and includes three attributes: whether the Blob object is stored in the memory, the ID of the Blob object in the memory, and the path of the Blob object in the file.

[0035] Step C7: Determine the storage location of the Blob object according to the Blob object index. If the Blob object is stored in a file, append the binary data to the file storage. If the Blob object is stored in memory, append the binary data to the memory storage. Then jump to step C12.

[0036] Step C8: Determine whether the Blob memory storage area is free, if the Blob memory storage area is free, jump to step C9, otherwise jump to step C10.

[0037] Step C9: Store the Blob object in memory and jump to step C11.

[0038] Step C10: Store the Blob object in the file storage.

[0039] Step C11: Store the Blob object index in the memory. The index stores the location where the Blob object is stored, indicating whether the object is stored in the memory or in a file.

[0040] Step C12: Return COM_STMT_SEND_LONG_DATA_OK message.

[0041] Step C13: If the client continues to transmit the COM_STMT_SEND_LONG_DATA message, jump to step C1 to continue receiving and storing new messages. Otherwise, the process ends.

[0042] Preferably, after receiving the COM_STMT_EXECUTE message in step D, the server stores the parameter data in the memory and binds the Blob data saved in step C. After the storage is successful, the server returns an OK message specifically as follows:

[0043] Step D1: Receive COM_STMT_EXECUTE message.

[0044] Step D2: Parse the message to obtain Statement_ID.

[0045] Step D3: Parse Null_Bitmap from the message, where Null_Bitmap is used to represent Null data in the parameter data.

[0046] Step D4: Parse the non-Null and non-Blob parameter data in the message.

[0047] Step D5: Get the PreparedStatment object according to Statement_ID.

[0048] Step D6: Get the table name according to the PreparedStatment object.

[0049] Step D7: Construct a storage object according to the table name.

[0050] Step D8: Set all parameter data that is Null according to Null_Bitmap in step D3 and store it in the storage object.

[0051] Step D9: store all non-Null and non-Blob parameter data in step D4 into the storage object.

[0052] Step D10: Set the Blob attribute in the storage object, and use all the Blob object indexes stored in step C as the Blob attribute values ​​in the storage object.

[0053] Step D11: Save the storage object to the memory.

[0054] Step D12: Return COM_STMT_EXECUTE_OK message.

[0055] On the other hand, the present invention provides a Blob data storage system based on a MySQL protocol memory database, including a server and a client, and the system implements the Blob data storage method based on a MySQL protocol memory database as described in any embodiment of the present invention.

[0056] In another aspect, the present invention further provides an electronic device, applied to a client, the electronic device comprising: a connection establishment module, an SQL preprocessing request module, a long data transmission module and an SQL execution request module.

[0057] The connection module is used to establish a connection with the server.

[0058] The SQL preprocessing request module is used to send a COM_STMT_PREPARE message, send an Insert SQL preprocessing request to the server, and receive a COM_STMT_PREPARE_OK message returned by the server.

[0059] The long data transmission module is used to send the COM_STMT_SEND_LONG_DATA message to the server and receive the COM_STMT_SEND_LONG_DATA_OK message returned by the server.

[0060] The SQL execution request module is used to send the COM_STMT_EXECUTE message, send the Insert SQL execution request to the server, and receive the COM_STMT_EXECUTE_OK message returned by the server.

[0061] On the other hand, the present invention further provides an electronic device, which is applied to a server, and the electronic device includes: a connection management module, an SQL preprocessing module, a Blob data storage module and an SQL execution processing module.

[0062] The connection management module is used to establish a connection with the client and generate a unique Connection_ID for each connection.

[0063] The SQL preprocessing module is used to receive the COM_STMT_PREPARE message and preprocess the Insert SQL. After the preprocessing is successful, the COM_STMT_PREPARE_OK message is returned.

[0064] The Blob data storage module is used to receive the COM_STMT_SEND_LONG_DATA message, store the Blob data in the memory or file system according to the server algorithm, and return the COM_STMT_SEND_LONG_DATA_OK message.

[0065] The SQL execution processing module is used to receive the COM_STMT_EXECUTE message, store the parameter data in the memory, bind the saved Blob data, and return the COM_STMT_EXECUTE_OK message to the server after the storage is successful.

[0066] Compared with the prior art, the present invention has the following technical effects:

[0067] 1. The solution described in the present invention solves the problem that the memory storage engine in MySQL cannot store Blob data. By uploading in steps through COM_STMT_SEND_LONG_DATA messages, Blob data can be flexibly stored in memory or disk, thereby breaking the limitation of MySQL memory engine on large data types and allowing MySQL memory database to process and store BLOB type data.

[0068] 2. In-memory databases such as Redis and H2 Database are prone to overflow due to insufficient memory when processing large objects of the Blob type. The present invention avoids large objects directly occupying a large amount of memory by independently processing and storing Blob data on disk or in memory (based on the size of the data and memory strategy), thereby effectively preventing the problem of memory overflow. At the same time, Blob data can be allocated and stored on demand, avoiding the memory pressure caused by the full storage strategy. BRIEF DESCRIPTION OF THE DRAWINGS

[0069] Figure 1 It is an overall flow chart of the Blob data storage method based on the MySQL protocol memory database described in the present invention;

[0070] Figure 2 It is an overall flow chart of step B in the Blob data storage method based on the MySQL protocol memory database described in the present invention;

[0071] Figure 3 It is an overall flow chart of step C in the Blob data storage method based on the MySQL protocol memory database described in the present invention;

[0072] Figure 4 It is an overall flow chart of step D in the Blob data storage method based on the MySQL protocol memory database described in the present invention. DETAILED DESCRIPTION

[0073] In order to make the objectives, technical solutions and advantages of the present invention more clear, the technical solutions of the present invention will be clearly and completely described below in combination with specific embodiments of the present application and with reference to the accompanying drawings.

[0074] Embodiment 1

[0075] This embodiment provides a Blob data storage method based on a MySQL protocol memory database, mainly for the Blob storage method in the MySQL protocol memory database and the transmission of Blob data based on the Prepared Statements protocol in the MySQL protocol. Figure 1 As shown, the following steps are included:

[0076] Step A: The client establishes a connection with the server of the MySQL protocol memory database, and the server generates a unique Connection_ID for each connection. The MySQL protocol memory database described in this embodiment can be obtained by modifying the MySQL source code, or a new memory database can be developed based on the MySQL protocol, and the implementation logic can comply with the MySQL protocol.

[0077] Step B: The client sends an Insert SQL preprocessing request to the server by sending a COM_STMT_PREPARE message, and the Insert SQL preprocessing request contains a field column of the Blob type. After receiving the COM_STMT_PREPARE message, the server preprocesses the Insert SQL, and returns a COM_STMT_PREPARE_OK message after successful preprocessing, which contains Statement_ID and all parameter information in the Insert SQL. The Blob data type (binary largeobject), i.e., a binary large object, is a field type that can store any binary data. The Blob field can be used to store any binary large data.

[0078] As a preferred implementation of this embodiment, refer to Figure 2 As shown, in step B, after receiving the message, the server preprocesses the Insert SQL, and returns the COM_STMT_PREPARE_OK message after the preprocessing is successful:

[0079] Step B1: The server receives the COM_STMT_PREPARE message, which contains the InsertSQL preprocessing request that needs to be preprocessed. Further, after receiving the message, the server determines whether it contains the Insert SQL preprocessing request. If it is not Insert SQL, it performs non-Insert SQL preprocessing (not involving storage data) to obtain a PreparedStatment object for non-Insert SQL preprocessing.

[0080] Step B2: Parse the Insert SQL text string in the message and convert it into SQL string data. The COM_STMT_PREPARE message sent by the client contains SQL data, but the message is binary data. The server needs to convert the binary data into string data after reading it.

[0081] Step B3: Create a PrepareContext object by parsing the Insert SQL preprocessing request of the COM_STMT_PREPARE message. The object represents the context of this preprocessing request and stores information including Connection_ID and PrepareStatment object. Connection_ID is used to identify each independent connection between the client and the server, and PrepareStatment object is used to store basic information written in this Insert.

[0082] Step B4: Generate Statement_ID, which is used to uniquely identify each Insert write. Each Insert write will generate a new Statement_ID, and the created PrepareContext object and Statement_ID are in a one-to-one mapping relationship.

[0083] Step B5: According to the SQL string data parsed in step B2, determine whether the Insert SQL has been preprocessed. Preprocessed means that the client has transmitted the same Insert SQL and generated a PreparedStatment object. If the Insert SQL has been preprocessed, execute step B6; otherwise, execute step B7.

[0084] Step B6: Read the basic information written in this Insert from the cache according to the SQL string data, obtain the cached PreparedStatment object, and jump to step B9.

[0085] Step B7: Parse the basic information in the Insert SQL and create a PreparedStatment object.

[0086] Step B8: Cache the PreparedStatment object in the memory, and the cached identifier is the SQL text string, that is, there is a one-to-one mapping relationship between the PreparedStatment object and the SQL text string.

[0087] Step B9: Store the PreparedStatment object into the PrepareContext object to bind the preprocessing information.

[0088] Step B10: Return COM_STMT_PREPARE_OK message, which contains Statement_ID generated in step B4 and all parameter information in Insert SQL, including parameter type and parameter sequence number. Specifically, the parameters include Blob parameters in step C and Null and non-Null and non-Blob parameters in step D. After receiving the COM_STMT_PREPARE_OK message, the client obtains the type and sequence number of these parameters, and first sends a COM_STMT_SEND_LONG_DATA message. After the server receives the parameter value of the Blob type, the client sends a COM_STMT_EXECUTE message, which contains all parameters except the Blob type.

[0089] As a preferred implementation of this embodiment, the basic information written in this Insert in step B3 specifically includes: the name of the target table where the Insert operation writes data, the column fields involved, the value corresponding to each field, and which data are passed in through parameterization.

[0090] Step C: The client sends a COM_STMT_SEND_LONG_DATA message to the server, which contains the Blob data. After receiving the COM_STMT_SEND_LONG_DATA message, the server stores the Blob data in the memory or file system according to the server algorithm.

[0091] As a preferred implementation of this embodiment, refer to Figure 3 As shown, in step C, after the server receives the COM_STMT_SEND_LONG_DATA message, according to the server algorithm, the Blob data is stored in the memory or file system as follows:

[0092] Step C1: The server receives the COM_STMT_SEND_LONG_DATA message.

[0093] Step C2: Parse the Statement_ID in the message.

[0094] Step C3: Parse the Param_ID in the message. Param_ID is used to identify the serial number of the parameter. In actual applications, the 2nd to 5th bytes in the COM_STMT_SEND_LONG_DATA message store the Statement_ID of the int type, and the 6th to 7th bytes store the Param_ID of the int type. Therefore, the Statement_ID can be obtained by parsing the 2nd to 5th bytes in the message, and the Param_ID can be obtained by parsing the 6th to 7th bytes in the message. The Statement_ID is the data returned by the server to the client. After receiving it, the client sends the Statement_ID to the server in the COM_STMT_SEND_LONG_DATA message, indicating that the big data sent this time belongs to the database operation corresponding to the Statement_ID. Since each COM_STMT_SEND_LONG_DATA message can only send one Blob binary data (that is, the value of a parameter in an Insert SQL), it is necessary to identify which specific parameter value is sent in the COM_STMT_SEND_LONG_DATA message, and Param_ID is used to identify the sequence number of the parameter, that is, it indicates the parameter number in the Insert SQL.

[0095] Step C4: Create a Blob object, read binary data from the message, and store the binary data in the Blob object. The binary data is the specific Blob parameter value, which is encapsulated into the COM_STMT_SEND_LONG_DATA message by the client according to the parameter information returned in step B10.

[0096] Step C5: Take out Connection_ID from the connection, and then judge whether it is a new parameter according to Connection_ID, Statement_ID and Param_ID. If Connection_ID, Statement_ID and Statement_ID all exist, it means that the parameters of message transmission are existing parameters, and jump to step C6. Otherwise, jump to step C8.

[0097] Step C6: Read the Blob object index from the memory according to Connection_ID, Statement_ID and Param_ID. The Blob object index is used to store the location where the Blob object is stored, and contains three properties: whether the Blob object is stored in the memory (isInMemery), the ID of the Blob object in the memory (blobId), and the path of the Blob object in the file (filePath). If the object is stored in the memory, isInMemery is true, blobId is not empty, and filePath is empty. If the object is stored in the file storage, isInMemery is false, blobId is empty, and filePath is not empty. A Blob parameter can be uniquely confirmed by Connection_ID, Statement_ID and Param_ID, so here Connection_ID, Statement_ID and Param_ID are concatenated into a string as a key. This key and the Blob object index are in a one-to-one correspondence. The one-to-one correspondence between the key and the Blob object index is stored in the memory by HashMap, so the Blob object index can be found in the HashMap by the key.

[0098] Step C7: Determine the storage location of the Blob object according to the Blob object index. If the Blob object is stored in a file, append the binary data to the file storage. If the Blob object is stored in memory, append the binary data to the memory storage. Then jump to step C12.

[0099] Step C8: Determine whether the Blob memory storage area is free, if the Blob memory storage area is free, jump to step C9, otherwise jump to step C10.

[0100] Step C9: Store the Blob object in memory and jump to step C11.

[0101] Step C10: Store the Blob object in the file storage.

[0102] Step C11: Store the Blob object index in the memory. The index stores the location where the Blob object is stored, indicating whether the object is stored in the memory or in a file.

[0103] Step C12: Return COM_STMT_SEND_LONG_DATA_OK message.

[0104] Step C13: If the client continues to transmit the COM_STMT_SEND_LONG_DATA message, jump to step C1 to continue receiving and storing new messages. Otherwise, the process ends.

[0105] Step D: The client sends a COM_STMT_EXECUTE message to the server to execute the Insert SQL request, which contains all parameter data of non-Blob type. After receiving the COM_STMT_EXECUTE message, the server stores the parameter data in the memory and binds the Blob data saved in step C. After the storage is successful, the server returns the COM_STMT_EXECUTE_OK message.

[0106] Specifically, there are two considerations for storing Blob data and non-Blob data separately. First, since non-Blob data (such as int, float, varchar) is generally small and occupies less storage space, while Blob binary large object data may be relatively large, it is more appropriate to divide it into two storage areas. The amount of non-Blob data is small and can be stored only in memory; the amount of Blob data is large, and the problem of Blob data occupying a large amount of memory space can be avoided by storing a small amount of Blob data in memory and storing most of the large Blob data in file storage. Second, according to the Prepared Statements protocol of MySQL, Blob data and non-Blob data can only be transmitted separately. Non-Blob data is transmitted in the COM_STMT_EXECUTE message, and Blob data is transmitted in the COM_STMT_SEND_LONG_DATA message. The COM_STMT_SEND_LONG_DATA message must be sent before the COM_STMT_EXECUTE message. Therefore, it is only possible to receive and store Blob data first, then receive and store non-Blob data, and then bind Blob data and non-Blob data, and finally merge them into a row of data in the database table. The row of data contains non-Blob data columns and Blob data columns. In actual applications, for example, if the client needs to store a piece of movie information in the MySQL protocol memory database, it needs to send an Insert write request, which contains fields such as movie name, release time, director name, movie type, and promotional photos. Among them, movie name, release time, director name, and movie type are non-Blob data, and promotional photos are Blob data.

[0107] As a preferred implementation of this embodiment, refer to Figure 4 As shown, in step D, after the server receives the COM_STMT_EXECUTE message, it stores the parameter data in the memory and binds the Blob data saved in step C. After the storage is successful, the server returns an OK message as follows:

[0108] Step D1: Receive COM_STMT_EXECUTE message.

[0109] Step D2: Parse the message to obtain Statement_ID.

[0110] Step D3: Parse Null_Bitmap from the message. Null_Bitmap is used to represent Null data in parameter data. The Null_Bitmap technology can save the space of the transmitted data.

[0111] Step D4: Parse the non-Null and non-Blob parameter data in the message. The specific parameters in the message are determined by the data in the COM_STMT_EXECUTE message sent by the client. For example, the COM_STMT_EXECUTE message sent by the client contains a piece of movie data that needs to be inserted and saved. The data contains data in five fields: movie name, release time, director name, film type, and promotional photos. Among them, the director name is Null and the promotional photos are Blob parameters. Then the three fields of movie name, release time, and film type are non-Null and non-Blob parameters.

[0112] Step D5: Get the PreparedStatment object according to Statement_ID.

[0113] Step D6: Get the table name according to the PreparedStatment object. The table name is the target table name (expressed as a string) to be stored in the Insert SQL. For example, the table name "User" can represent the user table, and the table name "Film" can represent the movie table. The table name is parsed in step B7 and stored in the PreparedStatment object.

[0114] Step D7: Construct a storage object according to the table name.

[0115] Step D8: Set all parameter data that are Null according to Null_Bitmap in step D3 and store them in the storage object. The specific attributes of the Null parameter data are determined by the data in the COM_STMT_EXECUTE message sent by the client. For example, the COM_STMT_EXECUTE message sent by the client contains a piece of movie data that needs to be inserted and saved. The data contains data in five fields: movie name, release time, director name, film type, and promotional photos. Among them, the director name is Null, so the above movie data has only one Null attribute: director name.

[0116] Step D9: store all non-Null and non-Blob parameter data in step D4 into the storage object.

[0117] Step D10: Set the Blob attribute in the storage object, and use all the Blob object indexes stored in step C as the Blob attribute values ​​in the storage object. The Blob object index can be used to find the stored Blob big data in the memory or file storage. For example, the promotional photo in the above description is a Blob attribute, and the Blob object index stores the storage location of the Blob big data of a specific promotional photo (whether it is stored in memory or in a file).

[0118] Step D11: Save the storage object to the memory.

[0119] Step D12: Return COM_STMT_EXECUTE_OK message.

[0120] Embodiment 2

[0121] Accordingly, this embodiment provides a Blob data storage system based on a MySQL protocol memory database, including a server and a client. The system is used to implement the Blob data storage method based on a MySQL protocol memory database as described in Example 1.

[0122] Embodiment 3

[0123] This embodiment provides an electronic device for a client, wherein the electronic device is used to implement the Blob data storage method based on a MySQL protocol memory database as described in Embodiment 1, including: a connection establishment module, an SQL preprocessing request module, a long data transmission module, and an SQL execution request module.

[0124] The connection module is used to establish a connection with the server.

[0125] The SQL preprocessing request module is used to send a COM_STMT_PREPARE message, send an Insert SQL preprocessing request to the server, and receive a COM_STMT_PREPARE_OK message returned by the server.

[0126] The long data transmission module is used to send the COM_STMT_SEND_LONG_DATA message to the server and receive the COM_STMT_SEND_LONG_DATA_OK message returned by the server.

[0127] The SQL execution request module is used to send the COM_STMT_EXECUTE message, send the Insert SQL execution request to the server, and receive the COM_STMT_EXECUTE_OK message returned by the server.

[0128] Embodiment 4

[0129] The present embodiment provides an electronic device, which is applied to a server. The electronic device is used to implement the Blob data storage method based on a MySQL protocol memory database as described in the first embodiment, including: a connection management module, an SQL preprocessing module, a Blob data storage module, and an SQL execution processing module.

[0130] The connection management module is used to establish a connection with the client and generate a unique Connection_ID for each connection.

[0131] The SQL preprocessing module is used to receive the COM_STMT_PREPARE message and preprocess the Insert SQL. After the preprocessing is successful, the COM_STMT_PREPARE_OK message is returned.

[0132] The Blob data storage module is used to receive the COM_STMT_SEND_LONG_DATA message, store the Blob data in the memory or file system according to the server algorithm, and return the COM_STMT_SEND_LONG_DATA_OK message.

[0133] The SQL execution processing module is used to receive the COM_STMT_EXECUTE message, store the parameter data in the memory, bind the saved Blob data, and return the COM_STMT_EXECUTE_OK message to the server after the storage is successful.

[0134] In the embodiments of the present application, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent the existence of A alone, the existence of A and B at the same time, and the existence of B alone. Among them, A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" and similar expressions refer to any combination of these items, including any combination of single or plural items. For example, at least one of a, b and c can be represented by: a, b, c, a and b, a and c, b and c, or a and b and c, where a, b, c can be single or multiple.

[0135] Those of ordinary skill in the art will appreciate that the various units and algorithm steps described in the embodiments disclosed herein can be implemented in a combination of electronic hardware, computer software, and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0136] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0137] In several embodiments provided in the present application, if any function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform 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 (Read-Only Memory; hereinafter referred to as: ROM), random access memory (Random Access Memory; hereinafter referred to as: RAM), disk or optical disk, and other media that can store program codes.

[0138] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A Blob data storage method based on a MySQL protocol memory database, characterized in that: The following steps are involved: Step A: The client establishes a connection with the server of the MySQL protocol memory database, and the server generates a unique Connection_ID for each connection; Step B: The client sends a COM_STMT_PREPARE message to send an Insert SQL preprocessing request to the server. The Insert SQL preprocessing request contains a field column of the Blob type. After receiving the COM_STMT_PREPARE message, the server preprocesses the Insert SQL. If the preprocessing is successful, it returns a COM_STMT_PREPARE_OK message. The message contains Statement_ID and all parameter information in the Insert SQL. Step C: The client sends a COM_STMT_SEND_LONG_DATA message to the server, which contains the Blob data. After receiving the COM_STMT_SEND_LONG_DATA message, the server stores the Blob data in the memory or file system according to the server algorithm. Step D: The client sends a COM_STMT_EXECUTE message to the server to execute an Insert SQL request, which contains all parameter data of non-Blob type. After receiving the COM_STMT_EXECUTE message, the server stores the parameter data in memory and binds it to the Blob data saved in step C. After successful storage, the server returns a COM_STMT_EXECUTE_OK message.

2. The Blob data storage method based on the MySQL protocol memory database according to claim 1 is characterized in that: After receiving the message in step B, the server preprocesses the Insert SQL, and returns the COM_STMT_PREPARE_OK message after the preprocessing is successful: Step B1: The server receives the COM_STMT_PREPARE message, which contains the InsertSQL preprocessing request that needs to be preprocessed; Step B2: Parse the InsertSQL text string in the message and convert it into SQL string data; Step B3: Create a PrepareContext object by parsing the Insert SQL preprocessing request of the COM_STMT_PREPARE message. The object represents the context of this preprocessing request and stores information including Connection_ID and PrepareStatment object. Connection_ID is used to identify each independent connection between the client and the server, and PrepareStatment object is used to store basic information written in this Insert. Step B4: Generate Statement_ID, which is used to uniquely identify each Insert write. The created PrepareContext object and Statement_ID are in a one-to-one mapping relationship; Step B5: According to the SQL string data parsed in step B2, determine whether the Insert SQL has been preprocessed. If it has been preprocessed, it means that the client has transmitted the same Insert SQL and generated a PreparedStatment object. If the Insert SQL has been preprocessed, execute step B6; otherwise, execute step B7. Step B6: Read the basic information written in this Insert from the cache according to the SQL string data, obtain the cached PreparedStatment object, and jump to step B9; Step B7: Parse the basic information in the Insert SQL and create a PreparedStatment object; Step B8: Cache the PreparedStatment object into the memory, and its cached identifier is the SQL text string; Step B9: Store the PreparedStatment object into the PrepareContext object; Step B10: Return a COM_STMT_PREPARE_OK message, which includes the Statement_ID generated in step B4 and all parameter information in the Insert SQL, including the parameter type and parameter sequence number.

3. The Blob data storage method based on the MySQL protocol memory database according to claim 1 is characterized in that: The basic information written in this Insert operation in step B3 specifically includes: the name of the target table where the Insert operation writes data, the column fields involved, the value corresponding to each field, and which data are passed in through parameterization.

4. The Blob data storage method based on the MySQL protocol memory database according to claim 1 is characterized in that: After the server receives the COM_STMT_SEND_LONG_DATA message in step C, the server stores the Blob data in the memory or file system according to the server algorithm as follows: Step C1: The server receives the COM_STMT_SEND_LONG_DATA message; Step C2: Parse the Statement_ID in the message; Step C3: Parse the Param_ID in the message. Param_ID is used to identify the serial number of the parameter. Step C4: Create a Blob object, read binary data from the message, and store the binary data in the Blob object; Step C5: Take out Connection_ID from the connection, and then determine whether it is a new parameter according to Connection_ID, Statement_ID and Param_ID. If Connection_ID, Statement_ID and Statement_ID all exist, it means that the parameters of the message transmission are existing parameters, and jump to step C6; Otherwise jump to step C8; Step C6: Read the Blob object index from the memory according to Connection_ID, Statement_ID and Param_ID. The Blob object index is used to store the location where the Blob object is stored and includes three attributes: whether the Blob object is stored in the memory, the ID of the Blob object in the memory, and the path of the Blob object in the file. Step C7: Determine the storage location of the Blob object according to the Blob object index. If the Blob object is stored in a file, append the binary data to the file storage; if the Blob object is stored in the memory, append the binary data to the memory storage; And jump to step C12; Step C8: Determine whether the Blob memory storage area is free. If the Blob memory storage area is free, jump to step C9; Otherwise jump to step C10; Step C9: store the Blob object to the memory and jump to step C11; Step C10: storing the Blob object to the file storage; Step C11: Store the Blob object index in the memory. The index stores the location where the Blob object is stored, indicating whether the object is stored in the memory or in a file. Step C12: Return COM_STMT_SEND_LONG_DATA_OK message; Step C13: If the client continues to transmit the COM_STMT_SEND_LONG_DATA message, jump to step C1 to continue receiving and storing new messages; otherwise, the process ends.

5. The Blob data storage method based on the MySQL protocol memory database according to claim 4 is characterized in that: After receiving the COM_STMT_EXECUTE message in step D, the server stores the parameter data in the memory and binds the Blob data saved in step C. After the storage is successful, the server returns an OK message as follows: Step D1: receiving COM_STMT_EXECUTE message; Step D2: Parse the message to obtain Statement_ID; Step D3: Parse Null_Bitmap from the message, where Null_Bitmap is used to represent Null data in the parameter data; Step D4: Parse the non-Null and non-Blob parameter data in the message; Step D5: Get the PreparedStatment object according to Statement_ID; Step D6: Get the table name according to the PreparedStatment object; Step D7: construct a storage object according to the table name; Step D8: Set all parameter data that are Null according to Null_Bitmap in step D3 and store them in the storage object; Step D9: according to all non-Null and non-Blob parameter data in step D4, store them into the storage object; Step D10: setting the Blob attribute in the storage object, and using all the Blob object indexes stored in step C as the Blob attribute values ​​in the storage object; Step D11: Save the storage object to the memory; Step D12: Return COM_STMT_EXECUTE_OK message.

6. A Blob data storage system based on a MySQL protocol memory database, characterized in that: The system comprises a server and a client, and is used to implement the Blob data storage method based on a MySQL protocol memory database as described in any one of claims 1 to 5.

7. An electronic device, applied to a client, characterized in that: The electronic device is used to implement the Blob data storage method based on the MySQL protocol memory database according to any one of claims 1 to 5, comprising: a connection establishment module, an SQL preprocessing request module, a long data transmission module and an SQL execution request module; Establish a connection module, used to establish a connection with the server; SQL preprocessing request module, used to send COM_STMT_PREPARE message, send Insert SQL preprocessing request to the server, and receive COM_STMT_PREPARE_OK message returned by the server; Long data transmission module, used to send COM_STMT_SEND_LONG_DATA message to the server and receive COM_STMT_SEND_LONG_DATA_OK message returned by the server; The SQL execution request module is used to send the COM_STMT_EXECUTE message, send the Insert SQL execution request to the server, and receive the COM_STMT_EXECUTE_OK message returned by the server.

8. An electronic device, applied to a server, characterized in that: The electronic device is used to implement the Blob data storage method based on the MySQL protocol memory database according to any one of claims 1 to 5, comprising: a connection management module, an SQL preprocessing module, a Blob data storage module and an SQL execution processing module; The connection management module is used to establish a connection with the client and generate a unique Connection_ID for each connection; The SQL preprocessing module is used to receive the COM_STMT_PREPARE message and preprocess the Insert SQL. After the preprocessing is successful, the COM_STMT_PREPARE_OK message is returned. Blob data storage module, used to receive COM_STMT_SEND_LONG_DATA message, store Blob data into memory or file system according to server algorithm, and return COM_STMT_SEND_LONG_DATA_OK message; The SQL execution processing module is used to receive the COM_STMT_EXECUTE message, store the parameter data in the memory, bind the saved Blob data, and return the COM_STMT_EXECUTE_OK message to the server after the storage is successful.

Images