Fast-in method, device and equipment of SQL statement and storage medium

Abstract

The application provides a method, device and equipment for quickly storing SQL statements in a database, and a storage medium. The method comprises the following steps: a client writes all the obtained SQL statements in a cache into a text file, uploads the text file to a server, and sends a control message to the server; the server starts an integrity check process based on the obtained control message, and performs integrity check on the text file based on the control message; the server analyzes the text file that passes the integrity check based on the control message to obtain at least one SQL statement, and assigns the same type of SQL statements to the same local thread; the local thread executes all the obtained SQL statements, the server controls all the local threads to execute a two-stage commit program, executes the storage of all the obtained SQL statements, and returns the storage result of the local thread that completes the two-stage commit program to the client. According to the technical scheme of the embodiment of the application, one-time file transmission is realized through a text file, thereby reducing the time consumption, and the storage of the SQL statements is realized quickly.

Description

Technical Field

[0001] This invention relates to the field of SQL statement technology for QDM interfaces, and particularly to a method, apparatus, device, and storage medium for quickly loading SQL statements into a database. Background Technology

[0002] The Qt Database Model (QDM) interface under the Qt framework has been widely used in data interface scenarios between business systems and databases. In existing technologies, the QDM interface uses a serial mode to execute and commit SQL statements. The client sends an SQL statement to the server, the server executes the SQL statement and returns a confirmation character, which triggers the client to send a new SQL statement to the server. That is, in existing technologies, SQL statements are executed sequentially, and the client waits for the server to return a confirmation character for each statement before committing the execution result. In summary, existing technologies are characterized by single connection, synchronous blocking, and serial communication.

[0003] However, each SQL statement requires a certain round-trip time (RTT) to send and return. In serial mode, the total time to execute and submit all SQL statements (i.e., the total round-trip time) is equal to the single round-trip time of the SQL statement multiplied by the number of SQL statements. In low-speed network conditions, the total round-trip time is further extended, resulting in amplified serial RTT and longer processing time. Summary of the Invention

[0004] This invention aims to at least solve one of the technical problems existing in the prior art. To this end, this invention proposes a method, apparatus, device, and storage medium for fast SQL statement insertion into the database. By using a text file for one-time file transfer, the required execution time is reduced to the sum of the round-trip time of the text file and the round-trip time of the control message, thereby reducing the required time consumption and achieving fast SQL statement insertion into the database.

[0005] In a first aspect, embodiments of the present invention provide a method for fast SQL statement import into a database, applied to a high-speed SQL import system, wherein the high-speed SQL import system includes a client and a server that are interconnected; the method includes: The client writes all the cached SQL statements into a text file, uploads the text file to the server, and sends a control message to the server, wherein the number of SQL statements is at least one. The server initiates an integrity verification process based on the acquired control message, and performs integrity verification on the text file based on the control message; When the text file passes the integrity check, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, wherein the server includes multiple local threads; After the local thread finishes executing all the obtained SQL statements, the server controls all the local threads to execute a two-phase commit procedure to insert all the obtained SQL statements into the database. After completing the two-phase commit procedure, the local thread returns the insertion result to the client.

[0006] According to some embodiments of the present invention, the client includes a file generation module, a file transfer module, and a message module connected in series, wherein the message module is communicatively connected to the server. The client writes all cached SQL statements into a text file, uploads the text file to the server, and sends control messages to the server, including: The file generation module writes all the SQL statements from the different target tables to the text file based on a sharding strategy, wherein the target table includes at least one of the SQL statements; The file transfer module uploads the text file to the designated directory on the server using a secure file transfer protocol; After the text file is uploaded, the message module sends the control message to the server.

[0007] According to some embodiments of the present invention, the server includes a file monitoring module and a classification and parallel execution module that are interconnected, and the file monitoring module is communicatively connected to the message module; The server initiates an integrity verification process based on the acquired control message, performing integrity verification on the text file based on the control message, including: When the file monitoring module obtains the control message from the message module, it triggers the file monitoring module to start the integrity verification process. The file monitoring module performs integrity verification on the text file based on the checksum of the control message. The control message includes the file path and the checksum, and the file path is the path of the text file. When the text file passes the integrity check, the file monitoring module sends the text file and the control message to the classification and parallel execution module.

[0008] According to some embodiments of the present invention, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, including: The classification and parallel execution module parses the control message to obtain the file path, and parses the text file based on the file path to obtain at least one SQL statement; All SQL statements are categorized based on their statement type, and SQL statements belonging to the same statement type are assigned to the same local thread.

[0009] According to some embodiments of the present invention, the server further includes a two-phase commit module, which is communicatively connected to the classification and parallel execution module; After the local thread finishes executing all the obtained SQL statements, the server controls all the local threads to execute a two-phase commit procedure to insert all the obtained SQL statements into the database, including: Based on any one of the local threads, the local thread executes local transactions in parallel, wherein the local transaction is all the SQL statements obtained by the local thread; When all the local threads have finished executing all the SQL statements they have obtained, the two-phase commit module controls all the local threads to pre-commit the corresponding local transactions. After all the local threads have completed the pre-commit of the local transaction, the server controls all the local threads to commit the local transaction. If at least one of the local threads fails to commit the local transaction, the server controls all the local threads to roll back the local transaction.

[0010] According to some embodiments of the present invention, the server further includes a result callback module, which is communicatively connected to the two-phase commit module and the client. The local thread that completes the two-phase commit process returns the database entry result to the client, including: The result callback module obtains the execution results of all the SQL statements from the two-stage submission module, and generates the database entry result based on all the execution results. The execution result can indicate whether the corresponding local thread successfully performed the database entry or failed to perform the database entry. The database entry result can indicate whether all the local threads successfully performed the database entry or at least one local thread failed to perform the database entry. The result callback module returns the data entry result to the client. When at least one of the local threads fails to execute the data entry, the data entry result includes breakpoint information.

[0011] According to some embodiments of the present invention, the control message is in JSON format, and the fields of the control message include request identity and file length. The length of the control message is fixed and is less than or equal to 1024 bytes. The classification and parallel execution module is a thread pool architecture, the number of local threads is equal to twice the number of CPU cores, and the local threads are bound to an independent physical database and connected to the physical database through the AUTO_COMMIT=OFF parameter and the REUSE_STMT=ON parameter.

[0012] Secondly, embodiments of the present invention provide a device for rapidly loading SQL statements into a database, including at least one control processor and a memory for communicatively connecting to the at least one control processor; the memory stores instructions executable by the at least one control processor, which, when executed by the at least one control processor, enables the at least one control processor to execute the rapid loading method for SQL statements as described in the first aspect above.

[0013] Thirdly, embodiments of the present invention provide an electronic device including a device for quickly loading SQL statements as described in the second aspect above.

[0014] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing computer-executable instructions for executing a fast database insertion method for SQL statements as described in the first aspect above.

[0015] The fast SQL statement import method according to embodiments of the present invention has at least the following beneficial effects: applied to a high-speed SQL import system, the high-speed SQL import system including a client and a server connected to each other; the method includes: the client writing all cached SQL statements into a text file, uploading the text file to the server, and sending a control message to the server, wherein the number of SQL statements is at least one; the server initiating an integrity verification process based on the obtained control message, and performing integrity verification on the text file based on the control message; when the text file passes the integrity verification, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, wherein the server includes multiple local threads; after the local threads have executed all the obtained SQL statements, the server controls all the local threads to execute a two-phase commit procedure to import all the obtained SQL statements into the database, and the local thread that has completed the two-phase commit procedure returns the import result to the client. According to the technical solution of the present invention, one-time file transfer is achieved through text files, improving network utilization. Server-side triggering is achieved through control messages, eliminating the need for continuous serial triggering by the client. This reduces the required execution time to the sum of the round-trip time of the text file and the round-trip time of the control message, thereby reducing the required time consumption. Furthermore, the two-phase commit procedure maintains transaction consistency. By executing batch SQL statements in parallel through multiple local threads, the total time for SQL statement insertion is further reduced, further improving the SQL statement insertion speed and achieving fast SQL statement insertion. Attached Figure Description

[0016] Figure 1 This is a flowchart of a method for quickly inserting SQL statements into the database according to an embodiment of the present invention; Figure 2 This is a structural diagram of a high-speed SQL data import system provided in another embodiment of the present invention; Figure 3 This is a complete flowchart of a high-speed SQL data import method provided in another embodiment of the present invention; Figure 4 This is a structural diagram of a device for quickly loading SQL statements into a database according to another embodiment of the present invention. Detailed Implementation

[0017] Embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and should not be construed as limiting the present invention.

[0018] In the description of this invention, it should be understood that the orientation descriptions, such as up, down, front, back, left, right, etc., are based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limiting this invention.

[0019] In the description of this invention, "several" means one or more, "more than" means two or more, "greater than," "less than," and "exceeding" are understood to exclude the stated number, while "above," "below," and "within" are understood to include the stated number. The use of "first" and "second" in the description is merely for distinguishing technical features and should not be construed as indicating or implying relative importance, or implicitly indicating the number of indicated technical features, or implicitly indicating the order of the indicated technical features.

[0020] In the description of this invention, unless otherwise explicitly defined, terms such as "set up," "install," and "connect" should be interpreted broadly, and those skilled in the art can reasonably determine the specific meaning of the above terms in this invention in conjunction with the specific content of the technical solution.

[0021] This invention provides a method, apparatus, device, and storage medium for fast SQL statement import into a database. The method is applied to a high-speed SQL import system, which includes a client and a server connected to each other. The fast SQL statement import method includes: the client writing all cached SQL statements into a text file, uploading the text file to the server, and sending a control message to the server, wherein the number of SQL statements is at least one; the server initiating an integrity verification process based on the acquired control message, performing integrity verification on the text file based on the control message; when the text file passes the integrity verification, the server parses the text file based on the control message to obtain at least one SQL statement, assigning similar SQL statements to the same local thread, wherein the server includes multiple local threads; after the local threads have executed all the acquired SQL statements, the server controls all the local threads to execute a two-phase commit procedure to import all the acquired SQL statements into the database; and the local threads that have completed the two-phase commit procedure return the import result to the client. According to the technical solution of the present invention, one-time file transfer is achieved through text files, improving network utilization. Server-side triggering is achieved through control messages, eliminating the need for continuous serial triggering by the client. This reduces the required execution time to the sum of the round-trip time of the text file and the round-trip time of the control message, thereby reducing the required time consumption. Furthermore, the two-phase commit procedure maintains transaction consistency. By executing batch SQL statements in parallel through multiple local threads, the total time for SQL statement insertion is further reduced, further improving the SQL statement insertion speed and achieving fast SQL statement insertion.

[0022] First, based on Figure 2 , Figure 2 This is a structural diagram of a high-speed SQL import system provided in another embodiment of the present invention; illustrating the structure of the high-speed SQL import system of this application: The SQL high-speed data import system includes a client and a server that are interconnected. The client includes a file generation module, a file transfer module, and a message module connected in series, with the message module communicating with the server. The server includes a file monitoring module and a classification and parallel execution module that are interconnected, with the file monitoring module communicating with the message module. The server also includes a two-phase commit module, which is connected with the classification and parallel execution module. Finally, the server includes a result callback module, which is connected to both the two-phase commit module and the client.

[0023] It should be noted that the communication connection between the client and the service enables the transmission of text files and control messages.

[0024] It should be noted that the client includes a file generation module, a file transfer module, and a message module, which are connected in series to establish communication. The server includes a file monitoring module, a classification and parallel execution module, a two-phase commit module, and a result callback module, all connected in series. The message module and the file monitoring module are interconnected to establish communication between the client and server. The result callback module is connected to the client to return the input results, thus supporting resume transmission in case of input failure.

[0025] It should be noted that the file generation module is used to write SQL statements from different target tables into a single text file according to a sharding strategy; the file generation module is a client-side file generation module.

[0026] It should be noted that the file transfer module is used to upload text files in bulk to a specified directory on the server via the Secure File Transfer Protocol (SFTP).

[0027] It should be noted that the control message module is used to send a control message to the server via a short connection after the text file has been uploaded. The control message contains the file path and a checksum, and the file path is the path to the text file.

[0028] It should be noted that the file monitoring module is used to initiate file integrity verification after receiving a control message. The control message is the trigger signal for the integrity verification of the file monitoring module; the file monitoring module is a server-side file monitoring module.

[0029] It should be noted that the classification and parallel execution module is the SQL classification and parallel execution module, which is used to parse the text file that has passed the integrity check into an array of SQL statements and classify them according to the statement type, and distribute all SQL statements to different local threads.

[0030] It should be noted that the two-phase commit module is used to commit local transactions uniformly after all local threads have completed the pre-commit. If any local thread fails, the entire transaction is rolled back.

[0031] It should be noted that the result callback module is used to return the input results to the client to support resume transmission in case of failure; the input results include the execution results and breakpoint information.

[0032] It should be noted that the SQL high-speed data import system of this application is scalable, stateless, and can be horizontally scaled into a multi-node cluster; it supports MySQL, PostgreSQL, and Oracle, and only requires replacing the corresponding writer0 module.

[0033] The technical solutions of the embodiments of the present invention will be further illustrated in the following figures.

[0034] Reference Figure 1 , Figure 1 The flowchart illustrates a method for quickly inserting SQL statements into a database, as provided in this embodiment of the invention. This method includes, but is not limited to, the following steps: S10, the client writes all the cached SQL statements to a text file, uploads the text file to the server, and sends a control message to the server, wherein the number of SQL statements is at least one.

[0035] It should be noted that on the client side, three steps are completed: caching the SQL statements and writing all the cached SQL statements to a text file; the client uploads the text file to the server; and after the upload is complete, the client sends a control message to the server.

[0036] It should be noted that in this application, the text file and the control message are not sent simultaneously. Instead, the control message is sent only after the text file has been uploaded. This allows the server to obtain the complete text file before performing integrity verification, thus avoiding errors in the verification result caused by performing verification before the upload is complete.

[0037] It should be noted that this application transmits multiple SQL statements to the server via a text file, rather than the serial transmission used in the prior art, thereby eliminating the accumulation of serial RTT and improving network utilization. In other words, it can greatly reduce the total time spent loading SQL statements into the database under low-speed network conditions. The transmission time of the text file depends on the size of the text file and the bandwidth, and the total time depends only on the transmission time of the text file and the control message RTT.

[0038] S20, the server initiates the integrity verification process based on the acquired control message, and performs integrity verification on the text file based on the control message.

[0039] It should be noted that the control message is a socket message; the control message includes a checksum, which is used to verify the integrity of the text file.

[0040] It should be noted that by using control messages as the start signal for the server's integrity verification process, unnecessary overhead caused by the server polling and scanning the directory is avoided. Thus, only one control message RTT is consumed to complete the text file upload and trigger the server to execute the integrity verification process.

[0041] S30, when the text file passes the integrity check, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread. The server includes multiple local threads.

[0042] It should be noted that when a text file passes integrity verification, it means that the SQL statements written in the text file are complete and all SQL statements are cached by the client.

[0043] It should be noted that after parsing the text file, all SQL statements written to the text file are obtained, i.e., at least one SQL statement, and SQL statements of the same category are obtained in the same local thread. That is, in this application, SQL statements from different target tables can be mixed in the same text file, and the server automatically executes SQL classification and SQL statements in parallel, which solves the defect of QDM in the prior art that cannot perform cross-table batch processing.

[0044] S40: After the local thread finishes executing all the retrieved SQL statements, the server controls all local threads to execute the two-phase commit procedure, inserting all the retrieved SQL statements into the database. The local thread that has completed the two-phase commit procedure returns the insertion result to the client.

[0045] It should be noted that through the two-phase commit procedure, all local threads execute the pre-commit and commit sequentially, thereby ensuring transaction consistency.

[0046] It should be noted that in existing technologies, the Qt Database Model (QDM) interface under the Qt framework has been widely used in data interface scenarios between business systems and databases. The QDM interface adopts a "serial execution of SQL statements, with the client waiting for the server's response for each statement before committing the transaction" model. The QDM interface relies on the TDS (Tabular Data Stream) protocol, meaning each SQL statement must complete one round-trip time (RTT). In other words, the existing technical solution involves the client sending the first SQL statement to the server, the server executing the first SQL statement and returning an acknowledgment (ACK) to the client, and then the client sending the second SQL statement. This existing technical solution is characterized by single-connection, synchronous blocking, and serial communication. In existing technical solutions, specifically in the official QT 4.8 / 5.x examples, developers disable the `auto-commit` parameter of `QSqlDatabase` (a class in the Qt framework used to manage database connections, providing an interface for accessing the database through a database connection) and call the `QSqlQuery::execBatch()` function to send 100 to 200 pre-compiled SQL statements to the DM driver at once. The DM driver still writes socket messages in a "statement-by-statement, synchronous waiting" manner, but does not actually package them. In summary, the shortcomings of existing technologies are: in serial transmission, the serial RTT is amplified; when each RTT has a 100ms delay, the total delay of serial transmission of 1000 SQL statements equals 100s; furthermore, existing technologies cannot achieve cross-table batch execution and insertion of SQL statements into the database; different target tables must be split into multiple batches, still affected by the accumulated serial RTT; and the driver layer does not provide a "whole file" interface, resulting in low network utilization (less than 5%).

[0047] It should be noted that in low-speed links or networks, such as bandwidth less than 1 megabit per second (Mbps) and a single round-trip time (RTT) of 100ms to 800ms, this application, unlike existing technologies that use SQL statement streams, combines a single text file transfer with high-speed local database insertion on the server side. This reduces the total round-trip time for inserting SQL statements from multiple mixed tables from minutes to seconds, while maintaining transaction consistency. Unlike existing technologies where execution is client-driven, this application uses server-side multi-threaded batch execution, reducing the RTT between the client and server and the time for inserting batch SQL statements, thus further improving the speed of SQL statement insertion and achieving high-speed SQL statement insertion. Unlike existing technologies that use single-connection long-lived transactions, this application combines server-side parallel processing with a single two-phase commit, enabling high-speed writing of batch SQL statements while maintaining transaction consistency.

[0048] It should be noted that when 1000 SQL statements need to be executed and inserted into the database, the round-trip time is 100 milliseconds (ms). In existing technologies, the total round-trip time is equal to 100ms / execution multiplied by 1000 executions, which equals 100 seconds (s). In this application, the total round-trip time required to generate a text file and control messages based on 1000 SQL statements is equal to the upload time of the text file and the round-trip time of the control messages. According to practical applications, in an environment with a round-trip time of 300ms and a bandwidth of 512kbps, the total time (including but not limited to the total round-trip time) is reduced from 180 seconds to 4.3 seconds, thereby reducing the total time and achieving fast SQL statement insertion into the database.

[0049] In another embodiment, step S10 specifically includes, but is not limited to, the following steps: S11, the file generation module writes all the SQL statements of the different target tables obtained into a text file based on the sharding strategy, wherein the target table includes at least one SQL statement; S12, the file transfer module uploads the text file to the specified directory on the server using a secure file transfer protocol; S13, After the text file upload is complete, the message module sends a control message to the server.

[0050] It should be noted that the existing technology uses the Tabular Data Stream (TDS) protocol to achieve interaction, while the existing technology uses the SFTP protocol to upload the entire text file and combines it with private control messages to complete the transmission and anti-tampering of the text file, thereby ensuring the confidentiality of data transmission.

[0051] It should be noted that the method of this application has cross-table batch processing capability, which can mix SQL statements from different target tables in the same text file. After the server obtains the text file, it automatically classifies and processes the SQL statements obtained from parsing the text file in parallel; thus, it can solve the technical problem that QDM in the prior art cannot perform cross-table batch processing.

[0052] It should be noted that this application uses a caching method on the client side to obtain at least one SQL statement and uses a sharding strategy to assemble the text file. By mixing SQL statements from different target tables in the same text file, the server automatically categorizes and processes them in parallel, thus overcoming the limitation of existing technologies where QDM cannot perform batch processing across tables.

[0053] In another embodiment, step S20 specifically includes, but is not limited to, the following steps: S21, when the file monitoring module obtains a control message from the message module, it triggers the file monitoring module to start the integrity verification process. The file monitoring module performs integrity verification on the text file based on the checksum of the control message. The control message includes the file path and the checksum, and the file path is the path of the text file. S22, when the text file passes the integrity check, the file monitoring module sends the text file and control message to the classification and parallel execution module.

[0054] It should be noted that the integrity verification is initiated by triggering the file listening module through the control message. This means that the execution subject of this application is the local multi-threaded batch execution on the server side, which means that SQL statements can be processed in batches in parallel, thereby further improving the speed of batch SQL statement insertion into the database.

[0055] In another embodiment, in step S30, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, specifically including but not limited to the following steps: S31, the classification and parallel execution module parses the control message to obtain the file path, and parses the text file based on the file path to obtain at least one SQL statement; S32 categorizes all SQL statements based on statement type and assigns SQL statements belonging to the same statement type to the same local thread.

[0056] It should be noted that the server includes multiple local threads, which execute SQL statements of the same statement type through the same local thread, thereby enabling the reuse of precompiled handles.

[0057] On the server side, the arrival of a text file triggers the file listening module, which performs syntax type classification, thus providing a technical foundation for the next step of multi-local thread parallel pipeline processing.

[0058] In another embodiment, in step S40, after the local thread has finished executing all the obtained SQL statements, the server controls all local threads to execute the two-phase commit procedure to insert all the obtained SQL statements into the database. This includes, but is not limited to, the following steps: S41, based on any local thread, the local thread executes local transactions in parallel, where the local transaction is all the SQL statements obtained by the local thread; S42, when all local threads have finished executing all the SQL statements they have obtained, the two-phase commit module controls all local threads to pre-commit the corresponding local transactions. S43: After all local threads have completed the pre-commit of their local transactions, the server controls all local threads to commit their local transactions. If at least one local thread fails to commit its local transaction, the server controls all local threads to roll back their local transactions.

[0059] It should be noted that this application employs a two-phase commit mechanism. After all local threads have completed executing their local transactions (i.e., executing all retrieved SQL statements), all local threads are pre-committed. After the pre-commit is complete, all local threads are then committed, thus ensuring transaction consistency across all local threads. Because this application involves multiple local threads executing in parallel, a unified two-phase commit mechanism ensures that all local threads commit successfully, or that a rollback occurs if any local thread fails to commit. This prevents transaction inconsistencies caused by the failure of some local threads under low-speed network conditions.

[0060] In another embodiment, in step S40, the local thread that completes the two-phase submission process returns the database entry result to the client, which specifically includes, but is not limited to, the following steps: S44, the result callback module obtains the execution results of all SQL statements from the two-phase commit module, and generates the database entry result based on the execution results. The execution result can indicate whether the corresponding local thread successfully executed the database entry or failed to execute the database entry. The database entry result can indicate whether all local threads successfully executed the database entry or at least one local thread failed to execute the database entry. S45, the result callback module returns the input result to the client. When at least one local thread fails to execute the input, the input result includes breakpoint information.

[0061] It should be noted that when at least one local thread fails to commit a local transaction, i.e., the local thread fails to commit or commit, the entire process is rolled back, and the data entry results include breakpoint information. This allows the client to resume the data transfer based on the breakpoint information, thus eliminating the need to upload the entire text file and reducing the total time required.

[0062] It should be noted that, referring to Figure 3 , Figure 3 This is a complete flowchart of a high-speed SQL database insertion method provided in another embodiment of the present invention. The complete process of this application is as follows: the client's file generation module generates a text file based on the SQL statement; the file transfer module uploads the text file to the specified directory on the server in one block via the SFTP protocol; after the text file is uploaded, the control message module sends a control message (socket message) to the server's file listening module via a short connection; the server's file listening module receives the control message (socket message), initiates file integrity verification, parses the control message to obtain the file path, the classification and parallel execution module parses the text file that has passed the integrity verification, executes the SQL statement to insert it into the database through the two-phase commit module, and determines whether the execution is successful. If successful, it sends an insertion result (socket message) including the successful execution result to the client, and the client receives the insertion result (success notification); or, if unsuccessful, it sends an insertion result (socket message) including the failed execution result to the client, and the client receives the insertion result (failure notification).

[0063] In another embodiment, the control message is in JSON format, and the fields of the control message include the request identity and file length. The length of the control message is fixed and is less than or equal to 1024 bytes. The classification and parallel execution module is a thread pool architecture, with the number of local threads equal to twice the number of CPU cores. The local threads are bound to an independent physical database and connected to the physical database through the AUTO_COMMIT=OFF and REUSE_STMT=ON parameters.

[0064] It should be noted that the fields of the control message include request identity and file length, where request identity is request_id and file length is file_path.

[0065] It should be noted that the number of local threads is dynamically equal to twice the number of CPU cores.

[0066] It should be noted that the AUTO_COMMIT=OFF parameter is used to disable automatic commit, and the REUSE_STMT=ON parameter is used to reuse the precompiled handle.

[0067] like Figure 4 As shown, Figure 4 This is a structural diagram of a device for quickly loading SQL statements into a database according to an embodiment of the present invention. The present invention also provides a device for quickly loading SQL statements into a database, comprising: The processor 501 can be implemented using a general-purpose central processing unit (CPU), microprocessor, application specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of this application. The memory 502 can be implemented as a read-only memory (ROM), static storage device, dynamic storage device, or random access memory (RAM). The memory 502 can store the operating system and other applications. When the technical solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 502, and the processor 501 calls and executes the fast database insertion method for SQL statements in the embodiments of this application. The input / output interface 503 is used to implement information input and output; The communication interface 504 is used to enable communication and interaction between this device and other devices. Communication can be achieved through wired means (such as USB, network cable, etc.) or wireless means (such as mobile network, WIFI, Bluetooth, etc.). Bus 505 transmits information between various components of the device (e.g., processor 501, memory 502, input / output interface 503, and communication interface 504); The processor 501, memory 502, input / output interface 503, and communication interface 504 are connected to each other within the device via bus 505.

[0068] This application also provides an electronic device, including a device for quickly loading SQL statements into a database as described above.

[0069] This application embodiment also provides a storage medium, which is a computer-readable storage medium, storing a computer program that, when executed by a processor, implements the above-described method for quickly inserting SQL statements into the database.

[0070] Memory, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs and non-transitory computer-executable programs. Furthermore, memory may include high-speed random access memory, and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, memory may optionally include memory remotely located relative to the processor, and these remote memories can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof. The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separate, and may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0071] It will be understood by those skilled in the art that all or some of the steps and systems in the methods disclosed above can be implemented as software, firmware, hardware, and suitable combinations thereof. Some or all of the physical components can be implemented as software executed by a processor, such as a central processing unit, digital signal processor, or microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit. Such software can be distributed on a computer-readable medium, which can include computer storage media (or non-transitory media) and communication media (or transient media). As is known to those skilled in the art, the term computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disc (DVD) or other optical disc storage, magnetic cartridges, magnetic tape, disk storage or other magnetic storage devices, or any other medium that can be used to store desired information and is accessible to a computer. Furthermore, as is known to those skilled in the art, communication media typically include computer-readable instructions, data structures, program modules, or other data in modulated data signals such as carrier waves or other transmission mechanisms, and may include any information delivery medium.

[0072] The above provides a detailed description of the preferred embodiments of the present invention. However, the present invention is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention. All such equivalent modifications or substitutions are included within the scope defined by the claims of the present invention.

Claims

1. A method for quickly inserting SQL statements into a database, characterized in that, The method is applied to a high-speed SQL data import system, which includes a client and a server that are interconnected; the method includes: The client writes all the cached SQL statements into a text file, uploads the text file to the server, and sends a control message to the server, wherein the number of SQL statements is at least one. The server initiates an integrity verification process based on the acquired control message, and performs integrity verification on the text file based on the control message; When the text file passes the integrity check, the server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, wherein the server includes multiple local threads; After the local thread finishes executing all the obtained SQL statements, the server controls all the local threads to execute a two-phase commit procedure to insert all the obtained SQL statements into the database. After completing the two-phase commit procedure, the local thread returns the insertion result to the client.

2. The method for quickly inserting SQL statements into the database according to claim 1, characterized in that, The client includes a file generation module, a file transfer module, and a message module connected in series, and the message module is communicatively connected to the server. The client writes all cached SQL statements into a text file, uploads the text file to the server, and sends control messages to the server, including: The file generation module writes all the SQL statements from the different target tables to the text file based on a sharding strategy, wherein the target table includes at least one of the SQL statements; The file transfer module uploads the text file to the designated directory on the server using a secure file transfer protocol; After the text file is uploaded, the message module sends the control message to the server.

3. The method for quickly inserting SQL statements into the database according to claim 2, characterized in that, The server includes a file monitoring module and a classification and parallel execution module that are interconnected. The file monitoring module is communicatively connected to the message module. The server initiates an integrity verification process based on the acquired control message, performing integrity verification on the text file based on the control message, including: When the file monitoring module obtains the control message from the message module, it triggers the file monitoring module to start the integrity verification process. The file monitoring module performs integrity verification on the text file based on the checksum of the control message. The control message includes the file path and the checksum, and the file path is the path of the text file. When the text file passes the integrity check, the file monitoring module sends the text file and the control message to the classification and parallel execution module.

4. The method for quickly inserting SQL statements into the database according to claim 3, characterized in that, The server parses the text file based on the control message to obtain at least one SQL statement, and assigns similar SQL statements to the same local thread, including: The classification and parallel execution module parses the control message to obtain the file path, and parses the text file based on the file path to obtain at least one SQL statement; All SQL statements are categorized based on their statement type, and SQL statements belonging to the same statement type are assigned to the same local thread.

5. The method for quickly inserting SQL statements into the database according to claim 3, characterized in that, The server also includes a two-phase commit module, which is communicatively connected to the classification and parallel execution module. After the local thread finishes executing all the obtained SQL statements, the server controls all the local threads to execute a two-phase commit procedure to insert all the obtained SQL statements into the database, including: Based on any one of the local threads, the local thread executes local transactions in parallel, wherein the local transaction is all the SQL statements obtained by the local thread; When all the local threads have finished executing all the SQL statements they have obtained, the two-phase commit module controls all the local threads to pre-commit the corresponding local transactions. After all the local threads have completed the pre-commit of the local transaction, the server controls all the local threads to commit the local transaction. If at least one of the local threads fails to commit the local transaction, the server controls all the local threads to roll back the local transaction.

6. The method for quickly inserting SQL statements into the database according to claim 5, characterized in that, The server also includes a result callback module, which is communicatively connected to the two-phase commit module and the client. The local thread that completes the two-phase commit process returns the database entry result to the client, including: The result callback module obtains the execution results of all the SQL statements from the two-stage submission module, and generates the database entry result based on all the execution results. The execution result can indicate whether the corresponding local thread successfully performed the database entry or failed to perform the database entry. The database entry result can indicate whether all the local threads successfully performed the database entry or at least one local thread failed to perform the database entry. The result callback module returns the data entry result to the client. When at least one of the local threads fails to execute the data entry, the data entry result includes breakpoint information.

7. The method for quickly inserting SQL statements into the database according to claim 3, characterized in that, The control message is in JSON format, and its fields include the request identity and file length. The length of the control message is fixed and is less than or equal to 1024 bytes. The classification and parallel execution module is a thread pool architecture, and the number of local threads is equal to twice the number of CPU cores. The local threads are bound to an independent physical database and connected to the physical database through the AUTO_COMMIT=OFF and REUSE_STMT=ON parameters.

8. A device for rapidly inserting SQL statements into a database, characterized in that, It includes at least one control processor and a memory for communicatively connecting to the at least one control processor; the memory stores instructions executable by the at least one control processor, which, when executed by the at least one control processor, enable the at least one control processor to execute the fast data entry method for SQL statements as described in any one of claims 1 to 7.

9. An electronic device, characterized in that, The device includes the fast database entry device for SQL statements as described in claim 8.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions for causing a computer to execute the fast database insertion method for SQL statements as described in any one of claims 1 to 7.

Images