A processing method and device for batch SQL instruction auditing

Abstract

An embodiment of the present invention relates to a method and apparatus for processing batch SQL instruction audits. The method comprises: pre-configuring a service interface for auditing batch operation instructions of a first MySQL database, recorded as a corresponding first audit interface; receiving a first audit application sent by a first applicant through the first audit interface; utilizing the transaction processing mechanism of the MySQL database to pre-execute a first instruction sequence on the first MySQL database to obtain a corresponding first pre-execution feedback list; performing executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; and forming a corresponding first audit feedback from the first application time of the first audit application, the first instruction batch, and the corresponding first authorization result; and sending the first audit feedback back to the first applicant interface of the first audit application through the first audit interface. The present invention can reduce the probability of data omissions.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a processing method and device for batch SQL instruction auditing. Background Art

[0002] The updated data for each order from an IoT service provider may reside in one or more records in one or more tables within the order database. To fully update each order, a specified batch of SQL commands must be generated based on the current order. The order update is considered complete only after all commands in the batch have been executed. In practice, IoT service providers often use a MySQL database as their order database. Each order update is completed by executing a batch of SQL commands based on the MySQL database's transaction processing mechanism.

[0003] The transaction processing mechanism of the MySQL database provides a set of transaction management instructions, including the BEGIN instruction or START TRANSACTION instruction for starting database transactions, the COMMIT instruction for committing transactions, and the ROLLBACK instruction for rolling back transactions. One or more business instructions for processing specific data update / read tasks, such as SELECT instructions, INSERT instructions, UPDATE instructions, and DELETE instructions, can be inserted between the BEGIN / START TRANSACTION instructions and the COMMIT / ROLLBACK instructions. The function of the BEGIN / START TRANSACTION instruction is to start a transaction processing process, the function of the COMMIT instruction is to confirm the execution results of all data update business instructions (INSERT, UPDATE, DELETE) in the current transaction processing process in the database and end the current transaction processing process, and the function of the ROLLBACK instruction is to cancel the execution effects of all data update business instructions (INSERT, UPDATE, DELETE) in the current transaction processing process in the database and end the current transaction processing process.

[0004] Currently, IoT service providers typically update their order databases using a transaction-based mechanism by first initiating a transaction using the BEGIN / START TRANSACTION command, then executing each business instruction in the current batch of instructions one by one, and then using the COMMIT command to terminate the current transaction and confirm the final update results for the target database. However, in actual business processing, we discovered several deficiencies in this approach: 1) It fails to verify the actual execution status of each business instruction. For example, if the instruction parameters for a business instruction are incorrect and the update is not successful, submitting the database update results with the COMMIT command may result in missed order data updates. 2) It fails to consider the impact of database load on business instructions. For example, if the order database is currently under high load, a single or batch of instructions may fail due to a processing timeout. In this case, submitting the database update results with the COMMIT command may also result in missed order data updates. Summary of the Invention

[0005] The purpose of the present invention is to address the shortcomings of the existing technology and provide a processing method, device, electronic device and computer-readable storage medium for batch SQL instruction auditing. The present invention pre-configures a service interface for auditing batch SQL instructions; and after the audit interface receives a group of batch SQL instructions, it processes an audit transaction process based on the BEGIN / START TRANSACTION+ROLLBACK instruction combination of the transaction processing mechanism of the MySQL database (the transaction isolation level is set to the RC level or the RR level); and at the start time of each audit transaction process, the current audit transaction is started by executing the BEGIN / START TRANSACTION instruction, and during the processing of each audit transaction process, the single processing time and single execution status of each instruction are counted; and at the end time of each audit transaction process, the current audit transaction is closed by the ROLLBACK instruction and the database update effect of the current instruction sequence is canceled, thereby achieving the purpose of not performing a real update on the database during preprocessing; and after the end of each audit transaction process, whether there is a single processing time timeout, whether there is a batch average processing time timeout, whether there is a single execution status failure, etc. in the process statistical data is analyzed, and the final executable authorization result (authorization passed, authorization failed) is confirmed based on the analysis result. According to the present invention, before each batch data update of the database is performed based on the conventional processing method, the batch SQL instruction sequence of the current time can be sent to the audit interface of the present invention for audit analysis. After obtaining the executable authorization result that specifically indicates that the authorization is passed from the audit interface, the database can be updated according to the batch SQL instruction sequence of the current time in the conventional processing method. In this way, the present invention can make up for the shortcomings of the conventional processing method, thereby achieving the purpose of reducing the probability of data omission and improving and ensuring the success rate of data update.

[0006] To achieve the above objectives, a first aspect of an embodiment of the present invention provides a method for processing batch SQL instruction auditing, the method comprising:

[0007] A service interface for auditing batch operation instructions of the first MySQL database is pre-configured as a corresponding first audit interface; the interface address format of the first audit interface is a URL address format;

[0008] receiving a first audit application sent by a first applicant through the first audit interface; the first audit application includes a first applicant interface, a first application time, a first instruction batch, and a first instruction sequence; the interface address format of the first applicant interface is a URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction that performs a database operation on the first MySQL database;

[0009] Pre-executing the first instruction sequence on the first MySQL database using a transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list; and performing executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; the first authorization result includes authorization passed and authorization failed;

[0010] A corresponding first audit feedback is composed of the first application time of the first audit application, the first instruction batch, and the corresponding first authorization result; and the first audit feedback is sent back to the first applicant interface of the first audit application through the first audit interface.

[0011] Preferably, the transaction isolation level of the first MySQL database is set to RC level or RR level;

[0012] The first pre-execution feedback list includes a first instruction feedback sequence, a first total duration, a first expected total number of successes, and a first actual total number of successes;

[0013] The first instruction feedback sequence includes one or more first instruction feedbacks;

[0014] The first instruction feedback corresponds one-to-one to the first instruction in the corresponding first instruction sequence; the first instruction feedback includes the corresponding first instruction, first instruction line number feedback, first instruction duration feedback and first instruction execution status;

[0015] The first instruction row number feedback is a preset invalid feedback value, a zero value, or an integer value greater than zero; the invalid feedback value is a preset negative integer value less than zero;

[0016] The first instruction execution status includes success and failure;

[0017] The first total duration is the sum of all first instruction duration feedbacks;

[0018] The first expected total number of successes is the total number of the first instructions in the corresponding first instruction sequence;

[0019] The first actual total number of successes is the total number of successful first instruction execution states in the first instruction feedback sequence.

[0020] Preferably, the step of pre-executing the first instruction sequence on the first MySQL database using the transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list specifically includes:

[0021] Step 31: Execute an SQL instruction for starting a database transaction on the first MySQL database; and record the currently started database transaction as the corresponding current transaction; the instruction type of the SQL instruction for starting a database transaction is a BEGIN instruction or a START TRANSACTION instruction;

[0022] Step 32: taking the first instruction of the first instruction sequence as the corresponding current SQL instruction;

[0023] Step 33: Identify whether the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction; if so, set the corresponding current monitoring switch to an on state; otherwise, set the corresponding current monitoring switch to an off state; the current monitoring switch includes an on state and an off state;

[0024] Step 34, obtain the current time as the corresponding first time by calling the MICROSECOND function on the first MySQL database; and execute the current SQL instruction on the first MySQL database when the first time is successfully obtained; and identify the current monitoring switch at the end of the execution of the current SQL instruction. If the current monitoring switch is on, obtain the operation row identifier of the most recent SQL instruction as the corresponding first instruction row count feedback by calling the ROW_COUNT function on the first MySQL database. If the current monitoring switch is off, set the corresponding first instruction row count feedback to the invalid feedback value; and when the first instruction row count feedback is successfully obtained Then, the current time is obtained as the corresponding second time by calling the MICROSECOND function on the first MySQL database; the absolute time difference between the first and second times is calculated and the calculation result is fed back as the corresponding first instruction duration; the first instruction row number feedback obtained this time is identified, and if the first instruction row number feedback is the invalid feedback value or an integer value greater than zero, the corresponding first instruction execution status is set to success; if the first instruction row number feedback is zero, the corresponding first instruction execution status is set to failure; the time accuracy of the first and second times is both in the millisecond level; the value of the operation row identifier of the most recent SQL instruction is a zero value or an integer value greater than zero;

[0025] Step 35: The first instruction corresponding to the current SQL instruction, the first instruction row number feedback, the first instruction duration feedback, and the first instruction execution status are used to form a corresponding first instruction feedback;

[0026] Step 36: Identify whether the current SQL instruction is the last first instruction in the first instruction sequence. If so, go to step 37. If not, extract the next first instruction in the first instruction sequence as the new current SQL instruction and return to step 33.

[0027] Step 37: Execute an SQL instruction for ending the current transaction on the first MySQL database; the instruction type of the SQL instruction for ending the current transaction is a ROLLBACK instruction;

[0028] Step 38: All the first instruction feedbacks obtained are sorted in chronological order to form a corresponding first instruction feedback sequence; and the sum of all the first instruction duration feedbacks in the first instruction feedback sequence is calculated to obtain the corresponding first total duration; and the total number of the first instructions in the first instruction sequence is counted and the statistical result is used as the corresponding first expected total success; and the total number of the first instruction execution states that are specifically successful in the first instruction feedback sequence is counted and the statistical result is used as the corresponding first actual total success; and the first instruction feedback sequence, the first total duration, the first expected total success and the first actual total success obtained this time form the corresponding first pre-execution feedback list.

[0029] Preferably, performing executable authorization analysis on the first instruction sequence according to the first pre-execution feedback list to obtain a corresponding first authorization result specifically includes:

[0030] All the first instruction feedbacks of the first pre-execution feedback list are traversed; and during the traversal, the first instruction feedback currently traversed is used as the corresponding current instruction feedback; and the first instruction and the first instruction duration feedback of the instruction feedback are used as the corresponding current SQL instruction and the current instruction duration; and when the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction, whether the current instruction duration exceeds a preset single instruction duration threshold is identified, and if so, the corresponding first instruction check result is set to failure; if not, the corresponding first instruction check result is set to success; and when the instruction type of the current SQL instruction is not an INSERT instruction, an UPDATE instruction, or a DELETE instruction, the corresponding first instruction check result is set to success; and at the end of the traversal, the total number of the first instruction check results specifically set to failure is counted to obtain a corresponding first statistical number; and the first statistical number is identified, and if the first statistical number is zero, the corresponding single instruction duration check result is set to success; if the first statistical number is greater than zero, the corresponding single instruction duration check result is set to failure;

[0031] and dividing the first total duration of the first pre-execution feedback list by the first expected total number of successes as the corresponding average instruction duration; and identifying whether the average instruction duration exceeds a preset average instruction duration threshold; if so, setting the corresponding batch instruction average duration check result to failure; if not, setting the corresponding batch instruction average duration check result to success;

[0032] and comparing the first expected total number of successes and the first actual total number of successes in the first pre-execution feedback list; if the first expected total number of successes is greater than the first actual total number of successes, setting the corresponding batch instruction execution status check result to failure; and if the first expected total number of successes is equal to the first actual total number of successes, setting the corresponding batch instruction execution status check result to success;

[0033] And identify whether the obtained single instruction duration check result, the batch instruction average duration check result and the batch instruction execution status check result are all successful; if the single instruction duration check result, the batch instruction average duration check result and the batch instruction execution status check result are all successful, set the corresponding first authorization result to passed authorization; if the single instruction duration check result, the batch instruction average duration check result or the batch instruction execution status check result is failed, set the corresponding first authorization result to failed authorization.

[0034] A second aspect of an embodiment of the present invention provides a device for implementing the processing method for batch SQL instruction auditing described in the first aspect, the device comprising: an audit interface configuration module, an audit application receiving module, an audit processing module, and an audit result feedback module;

[0035] The audit interface configuration module is used to pre-configure a service interface for auditing batch operation instructions of the first MySQL database as a corresponding first audit interface; the interface address format of the first audit interface is a URL address format;

[0036] The audit application receiving module is configured to receive a first audit application sent by a first applicant through the first audit interface; the first audit application includes a first applicant interface, a first application time, a first instruction batch, and a first instruction sequence; the interface address format of the first applicant interface is a URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction that performs a database operation on the first MySQL database;

[0037] The audit processing module is configured to pre-execute the first instruction sequence on the first MySQL database using a transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list; and perform executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; the first authorization result includes authorization passed and authorization failed;

[0038] The audit result feedback module is used to form a corresponding first audit feedback based on the first application time and the first instruction batch of the first audit application and the corresponding first authorization result; and send the first audit feedback back to the first applicant interface of the first audit application through the first audit interface.

[0039] A third aspect of an embodiment of the present invention provides an electronic device, including: a memory, a processor, and a transceiver;

[0040] The processor is configured to be coupled to the memory, read and execute instructions in the memory, so as to implement the method steps described in the first aspect above;

[0041] The transceiver is coupled to the processor, and the processor controls the transceiver to send and receive messages.

[0042] A fourth aspect of an embodiment of the present invention provides a computer-readable storage medium, which stores computer instructions. When the computer instructions are executed by a computer, the computer executes the instructions of the method described in the first aspect above.

[0043] Embodiments of the present invention provide a processing method, device, electronic device, and computer-readable storage medium for batch SQL instruction auditing. As can be seen from the above content, the embodiment of the present invention pre-configures a service interface for auditing batch SQL instructions; and after the audit interface receives a group of batch SQL instructions, it processes an audit transaction process based on the BEGIN / START TRANSACTION+ROLLBACK instruction combination of the transaction processing mechanism of the MySQL database (transaction isolation level is set to RC level or RR level); and at the start time of each audit transaction process, the BEGIN / START TRANSACTION instruction is executed to start the current audit transaction, and during the processing of each audit transaction process, the single processing time and single execution status of each instruction are counted, and at the end time of each audit transaction process, the ROLLBACK instruction is used to close the current audit transaction and cancel the database update effect of the current instruction sequence, thereby achieving the purpose of not actually updating the database during preprocessing; and after each audit transaction process ends, the process statistical data is analyzed to determine whether there is a single processing time timeout, whether there is a batch average processing time timeout, whether there is a single execution status failure, and other issues, and the final executable authorization result (authorized or not) is confirmed based on the analysis result. Through the embodiment of the present invention, before each batch data update of the database based on the conventional processing method, the batch SQL instruction sequence of the current time can be sent to the audit interface of the embodiment of the present invention for audit analysis, and after obtaining the executable authorization result that is specifically authorized from the audit interface, the database can be updated according to the batch SQL instruction sequence of the current time in the conventional processing method; in this way, the shortcomings of the conventional processing method can be compensated by the embodiment of the present invention, the probability of data omission of the MySQL database is reduced, and the success rate of data update of the MySQL database is improved and guaranteed. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Figure 1 A schematic diagram of a method for processing batch SQL command auditing provided in Example 1 of the present invention;

[0045] Figure 2 A module structure diagram of a processing device for batch SQL instruction auditing provided in the second embodiment of the present invention;

[0046] Figure 3 This is a structural diagram of an electronic device provided in Example 3 of the present invention. DETAILED DESCRIPTION

[0047] To make the objectives, technical solutions, and advantages of the present invention more apparent, the present invention will be further described in detail below with reference to the accompanying drawings. It should be understood that the embodiments described herein are merely some, rather than all, of the present invention. All other embodiments derived by persons of ordinary skill in the art based on the embodiments of the present invention without inventive effort are intended to fall within the scope of protection of the present invention.

[0048] The first embodiment of the present invention provides a method for processing batch SQL instruction audits, such as Figure 1 A schematic diagram of a method for batch SQL command auditing provided in the first embodiment of the present invention is shown. The method mainly includes the following steps:

[0049] Step 1: pre-configure a service interface for auditing batch operation instructions of the first MySQL database and record it as the corresponding first audit interface.

[0050] Here, the interface address format of the first audit interface in the embodiment of the present invention is a URL address format. The first audit interface can be a database local service interface of the first MySQL database, or a service interface of a service end, server, platform or system connected to the first MySQL database.

[0051] The first MySQL database in the embodiment of the present invention is a MySQL database, and the version of the MySQL database is a database version that can support the transaction processing mechanism instruction set, the MICROSECOND function and the ROW_COUNT function (also referred to as the ROWCOUNT function);

[0052] The transaction isolation level of the first MySQL database in the embodiment of the present invention is pre-set to the Read Committed (RC) level or the Repeatable Read (RR) level. The description of the transaction isolation level of the transaction processing mechanism can be obtained by referring to the public technical literature on the transaction processing mechanism of MySQL database and will not be further elaborated here. The reason why the transaction isolation level of the first MySQL database is preset to the RC level or the RR level in the embodiment of the present invention is to maintain good data consistency.

[0053] Step 2: Receive the first audit application sent by the first applicant through the first audit interface;

[0054] Among them, the first audit application includes the first applicant interface, the first application time, the first instruction batch and the first instruction sequence; the interface address format of the first applicant interface is the URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction for performing database operations on the first MySQL database.

[0055] Here, the first applicant is the processor that will perform batch data updates on the first MySQL database based on the first instruction sequence. The processor may be a local database service interface of the first MySQL database, or a service interface of a service end, server, platform, or system connected to the first MySQL database.

[0056] In an embodiment of the present invention, before performing a batch data update on the first MySQL database based on the first instruction sequence, the first applicant encapsulates a first audit application based on the data format of the first audit application and sends it to the first audit interface implemented by the present invention for an audit to ensure the success rate of the batch data update. Subsequently, the first applicant confirms whether to immediately execute the first instruction sequence based on the first audit feedback returned by the first audit interface.

[0057] In the first audit application of the embodiment of the present invention, the first application time is the time when the first applicant initiates the application, the first instruction batch is a unique batch number assigned by the first applicant to the current first instruction sequence, and the first instruction sequence is the batch SQL instruction sequence corresponding to the batch data update that the first applicant plans to execute at that time.

[0058] Step 3: Pre-execute the first instruction sequence on the first MySQL database using the transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list; and perform executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result;

[0059] Specifically, it includes: step 31, using the transaction processing mechanism of the MySQL database to pre-execute a first instruction sequence on the first MySQL database to obtain a corresponding first pre-execution feedback list;

[0060] Among them, the first pre-execution feedback list includes a first instruction feedback sequence, a first total duration, a first expected total number of successes and a first actual total number of successes; the first instruction feedback sequence includes one or more first instruction feedbacks; the first instruction feedback corresponds one-to-one to the first instruction in the corresponding first instruction sequence; the first instruction feedback includes the corresponding first instruction, the first instruction line number feedback, the first instruction duration feedback and the first instruction execution status; the first instruction line number feedback is a preset invalid feedback value, a zero value or an integer value greater than zero; the first instruction execution status includes success and failure; the first total duration is the sum of all first instruction duration feedbacks; the first expected total number of successes is the total number of first instructions in the corresponding first instruction sequence; the first actual total number of successes is the total number of first instruction execution states that are specifically successful in the first instruction feedback sequence;

[0061] Here, the invalid feedback value is a preset negative integer value less than zero, such as -1;

[0062] Specifically, the process includes: step 311, executing an SQL instruction for starting a database transaction on the first MySQL database; and recording the currently started database transaction as the corresponding current transaction;

[0063] The SQL command used to start a database transaction is of the BEGIN command or the STARTTRANSACTION command type.

[0064] Step 312: Use the first instruction of the first instruction sequence as the corresponding current SQL instruction;

[0065] Step 313: Identify whether the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction; if so, set the corresponding current monitoring switch to an on state; otherwise, set the corresponding current monitoring switch to an off state;

[0066] Among them, the current monitoring switch includes an on state and an off state;

[0067] Step 314: Obtain the current time as the corresponding first time by calling the MICROSECOND function on the first MySQL database; execute the current SQL instruction on the first MySQL database when the first time is successfully obtained; and identify the current monitoring switch at the end of the execution of the current SQL instruction. If the current monitoring switch is on, obtain the operation row identifier of the most recent SQL instruction by calling the ROW_COUNT function on the first MySQL database as the corresponding first instruction row count feedback; if the current monitoring switch is off, set the corresponding first instruction row count feedback to an invalid feedback value; and when the first instruction row count feedback is successfully obtained, obtain the current time as the corresponding second time by calling the MICROSECOND function on the first MySQL database again; calculate the absolute time difference between the first and second times and use the calculation result as the corresponding first instruction duration feedback; and identify the first instruction row count feedback obtained this time. If the first instruction row count feedback is an invalid feedback value or an integer value greater than zero, set the corresponding first instruction execution status to success; if the first instruction row count feedback is zero, set the corresponding first instruction execution status to failure;

[0068] The time precision of the first and second times returned by the MICROSECOND function is both at the millisecond level; the value of the operation row identifier of the most recent SQL instruction returned by the ROW_COUNT function is a zero value or an integer value greater than zero; here, the return value of the ROW_COUNT function in the embodiment of the present invention is either 0 or an integer greater than 0. If the return value of the ROW_COUNT function is 0, it means that the operation row identifier of the most recent SQL instruction (INSERT instruction, UPDATE instruction, or DELETE instruction) is 0, that is, an error occurred during the execution of the most recent SQL instruction (INSERT instruction, UPDATE instruction, or DELETE instruction), resulting in the inability to generate an operation row identifier greater than 0. If the return value of the ROW_COUNT function is an integer greater than 0, it means that the operation row identifier of the most recent SQL instruction is not 0, that is, the most recent SQL instruction was successfully executed and an operation row identifier greater than 0 was obtained;

[0069] It should be noted that there are multiple ways to express the SQL instruction for obtaining the current time as the corresponding first / second time by calling the MICROSECOND function in the current step 314. One expression is: SET@a=MICROSECOND(), where a is a preset variable corresponding to the first / second time, and another expression is: SELECTMICROSECOND(), where the return value of the SELECT instruction corresponds to the first / second time. It should also be noted that there are multiple ways to express the SQL instruction for obtaining the operation row identifier of the most recent SQL instruction as the corresponding first instruction row count feedback by calling the ROW_COUNT function in the current step 314. One expression is: SET@b=ROW_COUNT(), where b is a preset variable corresponding to the first instruction row count feedback, and another expression is: SELECT ROW_COUNT(), where the return value of the SELECT instruction corresponds to the first instruction row count feedback.

[0070] Step 315 , forming a corresponding first instruction feedback by including the first instruction corresponding to the current SQL instruction, the first instruction row number feedback, the first instruction duration feedback, and the first instruction execution status;

[0071] Step 316: Identify whether the current SQL instruction is the last first instruction of the first instruction sequence. If so, go to step 317. If not, extract the next first instruction of the first instruction sequence as the new current SQL instruction and return to step 313.

[0072] Step 317: Execute an SQL instruction for ending the current transaction on the first MySQL database;

[0073] Among them, the instruction type of the SQL instruction used to end the current transaction is the ROLLBACK instruction;

[0074] Step 318: All first instruction feedbacks obtained are sorted in chronological order to form a corresponding first instruction feedback sequence; the sum of all first instruction duration feedbacks in the first instruction feedback sequence is calculated to obtain a corresponding first total duration; the total number of first instructions in the first instruction sequence is counted and the statistical result is used as the corresponding first expected total number of successes; the total number of first instruction execution states that are specifically successful in the first instruction feedback sequence is counted and the statistical result is used as the corresponding first actual total number of successes; and the first instruction feedback sequence obtained this time, the first total duration, the first expected total number of successes, and the first actual total number of successes form a corresponding first pre-execution feedback list;

[0075] Step 32: performing an executable authorization analysis on the first instruction sequence according to the first pre-execution feedback list to obtain a corresponding first authorization result;

[0076] The first authorization result includes authorization passed and authorization failed;

[0077] Specifically, it includes: step 321, traversing all first instruction feedbacks of the first pre-execution feedback list; and during the traversal, taking the currently traversed first instruction feedback as the corresponding current instruction feedback; and taking the first instruction and the first instruction duration feedback of the instruction feedback as the corresponding current SQL instruction and the current instruction duration; and when the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction, identifying whether the current instruction duration exceeds a preset single instruction duration threshold, if so, setting the corresponding first instruction check result to failure, and if not, setting the corresponding first instruction check result to success; and when the instruction type of the current SQL instruction is not an INSERT instruction, an UPDATE instruction, or a DELETE instruction, setting the corresponding first instruction check result to success; and at the end of the traversal, counting the total number of first instruction check results specifically set to failure to obtain a corresponding first statistical number; and identifying the first statistical number, if the first statistical number is zero, setting the corresponding single instruction duration check result to success, and if the first statistical number is greater than zero, setting the corresponding single instruction duration check result to failure;

[0078] Here, the single instruction duration threshold is a preset duration parameter;

[0079] Step 322: The quotient of the first total duration of the first pre-execution feedback list divided by the first expected total number of successes is used as the corresponding average instruction duration; and whether the average instruction duration exceeds a preset average instruction duration threshold is identified. If so, the corresponding batch instruction average duration check result is set to failure; if not, the corresponding batch instruction average duration check result is set to success.

[0080] Here, the average instruction duration threshold is a preset duration parameter;

[0081] Step 323 , the first expected total number of successes and the first actual total number of successes in the first pre-execution feedback list are compared. If the first expected total number of successes is greater than the first actual total number of successes, the corresponding batch instruction execution status check result is set to failure. If the first expected total number of successes is equal to the first actual total number of successes, the corresponding batch instruction execution status check result is set to success.

[0082] Step 324, and identify whether the obtained single instruction duration check result, batch instruction average duration check result and batch instruction execution status check result are all successful; if the single instruction duration check result, batch instruction average duration check result and batch instruction execution status check result are all successful, set the corresponding first authorization result to passed authorization; if the single instruction duration check result, batch instruction average duration check result or batch instruction execution status check result is failed, set the corresponding first authorization result to failed authorization.

[0083] Step 4: A corresponding first audit feedback is formed by the first application time and the first instruction batch of the first audit application and the corresponding first authorization result; and the first audit feedback is sent back to the first applicant interface of the first audit application through the first audit interface.

[0084] Here, when the first applicant receives the first audit feedback sent back by the first audit interface at the corresponding first applicant interface, it extracts the corresponding first application time, first instruction batch and first authorization result from the first audit feedback received at that time; and saves it in one or more first instruction sequences locally on the applicant, and the first instruction sequence corresponding to the first instruction batch of this time is used as the current instruction sequence; and identifies the first authorization result; if the first authorization result is authorization passed, the first MySQL database is immediately updated based on the current instruction sequence; if the first authorization result is authorization failed, after a preset waiting time, a new first audit application is encapsulated based on the current instruction sequence and sent to the first audit interface for a second audit; the preset waiting time here is a pre-set time parameter.

[0085] Figure 2This is a module structure diagram of a processing device for batch SQL instruction auditing provided in the second embodiment of the present invention. The device is a terminal device or server that implements the aforementioned method embodiment, or can be a device that enables the aforementioned terminal device or server to implement the aforementioned method embodiment. For example, the device can be a device or chip system of the aforementioned terminal device or server. Figure 2 As shown, the device includes: an audit interface configuration module 201, an audit application receiving module 202, an audit processing module 203 and an audit result feedback module 204.

[0086] The audit interface configuration module 201 is used to pre-configure a service interface for auditing batch operation instructions of the first MySQL database as the corresponding first audit interface; the interface address format of the first audit interface is a URL address format.

[0087] The audit application receiving module 202 is used to receive the first audit application sent by the first applicant through the first audit interface; the first audit application includes the first applicant interface, the first application time, the first instruction batch and the first instruction sequence; the interface address format of the first applicant interface is the URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction for performing database operations on the first MySQL database.

[0088] The audit processing module 203 is used to utilize the transaction processing mechanism of the MySQL database to pre-execute the first instruction sequence on the first MySQL database to obtain a corresponding first pre-execution feedback list; and perform executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; the first authorization result includes passed authorization and failed authorization.

[0089] The audit result feedback module 204 is used to form a corresponding first audit feedback based on the first application time and first instruction batch of the first audit application and the corresponding first authorization result; and send the first audit feedback back to the first applicant interface of the first audit application through the first audit interface.

[0090] An embodiment of the present invention provides a processing device for batch SQL instruction auditing, which can execute the method steps in the above method embodiment. Its implementation principles and technical effects are similar and will not be repeated here.

[0091] It should be noted that it should be understood that the division of the various modules of the above device is merely a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. Moreover, these modules can all be implemented in the form of software called by processing elements; or they can all be implemented in the form of hardware; or some modules can be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the audit interface configuration module can be a separately established processing element, or it can be integrated into a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called by a processing element of the above device to execute the functions of the above-mentioned module. The implementation of other modules is similar. In addition, these modules can all or partly be integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each of the above modules can be completed by the hardware integrated logic circuit in the processor element or by instructions in the form of software.

[0092] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application-specific integrated circuits (ASICs), one or more digital signal processors (DSPs), or one or more field programmable gate arrays (FPGAs). For another example, when a module is implemented by scheduling program code through a processing element, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).

[0093] In the above embodiments, all or part of the embodiments may be implemented using software, hardware, firmware, or any combination thereof. When implemented using software, all or part of the embodiments may be implemented in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the processes or functions described in the above method embodiments are generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The above-mentioned computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the above-mentioned computer instructions can be transmitted from one website, computer, server or data center to another website, computer, server or data center via wired (such as coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (such as infrared, wireless, Bluetooth, microwave, etc.) means. The above-mentioned computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device such as a server or data center that includes one or more available media. The above-mentioned available medium can be a magnetic medium (such as a floppy disk, hard disk, tape), an optical medium (such as a DVD), or a semiconductor medium (such as a solid state disk (SSD)), etc.

[0094] Figure 3 This is a schematic diagram of the structure of an electronic device provided in the third embodiment of the present invention. The electronic device can be a terminal device or server that implements the method of the aforementioned embodiment, or it can be a terminal device or server that implements the method of the aforementioned embodiment connected to the aforementioned terminal device or server. Figure 3 As shown, the electronic device may include: a processor 301 (such as a CPU), a memory 302, and a transceiver 303; the transceiver 303 is coupled to the processor 301, and the processor 301 controls the transceiver 303's transceiver actions. Various instructions may be stored in the memory 302 for completing various processing functions and implementing the processing steps described in the aforementioned embodiment method. Preferably, the electronic device involved in the embodiment of the present invention further includes: a power supply 304, a system bus 305, and a communication port 306. The system bus 305 is used to realize communication connections between components. The above-mentioned communication port 306 is used for connecting and communicating between the electronic device and other peripherals.

[0095] exist Figure 3The system bus 305 mentioned in the figure can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The system bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus. The communication interface is used to realize communication between the database access device and other devices (such as clients, read-write libraries, and read-only libraries). The memory may include random access memory (RAM) and may also include non-volatile memory (Non-Volatile Memory), such as at least one disk storage.

[0096] The above-mentioned processors can be general-purpose processors, including central processing units (CPUs), network processors (NPs), graphics processing units (GPUs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0097] It should be noted that an embodiment of the present invention further provides a computer-readable storage medium, which stores instructions. When the computer-readable storage medium is run on a computer, it enables the computer to execute the methods and processing procedures provided in the above embodiments.

[0098] An embodiment of the present invention further provides a chip for executing instructions, which is used to execute the processing steps described in the above method embodiment.

[0099] Embodiments of the present invention provide a processing method, device, electronic device, and computer-readable storage medium for batch SQL instruction auditing. As can be seen from the above content, the embodiment of the present invention pre-configures a service interface for auditing batch SQL instructions; and after the audit interface receives a group of batch SQL instructions, it processes an audit transaction process based on the BEGIN / START TRANSACTION+ROLLBACK instruction combination of the transaction processing mechanism of the MySQL database (transaction isolation level is set to RC level or RR level); and at the start time of each audit transaction process, the BEGIN / START TRANSACTION instruction is executed to start the current audit transaction, and during the processing of each audit transaction process, the single processing time and single execution status of each instruction are counted, and at the end time of each audit transaction process, the ROLLBACK instruction is used to close the current audit transaction and cancel the database update effect of the current instruction sequence, thereby achieving the purpose of not actually updating the database during preprocessing; and after each audit transaction process ends, the process statistical data is analyzed to determine whether there is a single processing time timeout, whether there is a batch average processing time timeout, whether there is a single execution status failure, and other issues, and the final executable authorization result (authorized or not) is confirmed based on the analysis result. Through the embodiment of the present invention, before each batch data update of the database based on the conventional processing method, the batch SQL instruction sequence of the current time can be sent to the audit interface of the embodiment of the present invention for audit analysis, and after obtaining the executable authorization result that is specifically authorized from the audit interface, the database can be updated according to the batch SQL instruction sequence of the current time in the conventional processing method; in this way, the shortcomings of the conventional processing method can be compensated by the embodiment of the present invention, the probability of data omission of the MySQL database is reduced, and the success rate of data update of the MySQL database is improved and guaranteed.

[0100] Professionals should also be further aware that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the above description has generally described the components and steps of each example according to their functions. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians 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 the present invention.

[0101] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein may be implemented using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0102] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for processing batch SQL instruction audit, characterized in that: The method comprises: A service interface for auditing batch operation instructions of the first MySQL database is pre-configured as a corresponding first audit interface; the interface address format of the first audit interface is a URL address format; receiving a first audit application sent by a first applicant through the first audit interface; the first audit application includes a first applicant interface, a first application time, a first instruction batch, and a first instruction sequence; the interface address format of the first applicant interface is a URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction that performs a database operation on the first MySQL database; Pre-executing the first instruction sequence on the first MySQL database using a transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list; and performing executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; the first authorization result includes authorization passed and authorization failed; A corresponding first audit feedback is formed by the first application time and the first instruction batch of the first audit application and the corresponding first authorization result; and the first audit feedback is sent back to the first applicant interface of the first audit application through the first audit interface. Among them, the executable authorization analysis of the first instruction sequence according to the first pre-execution feedback list to obtain the corresponding first authorization result specifically includes: performing three types of checks according to the first pre-execution feedback list to obtain the corresponding single instruction duration check result, batch instruction average duration check result and batch instruction execution status check result; and identifying whether the obtained single instruction duration check result, batch instruction average duration check result and batch instruction execution status check result are all successful; if the single instruction duration check result, the batch instruction average duration check result and the batch instruction execution status check result are all successful, then setting the corresponding first authorization result to passed authorization; if the single instruction duration check result, the batch instruction average duration check result or the batch instruction execution status check result is a failure, then setting the corresponding first authorization result to failed authorization.

2. The method for processing batch SQL instruction audit according to claim 1, characterized in that: The transaction isolation level of the first MySQL database is set to RC level or RR level; The first pre-execution feedback list includes a first instruction feedback sequence, a first total duration, a first expected total number of successes, and a first actual total number of successes; The first instruction feedback sequence includes one or more first instruction feedbacks; The first instruction feedback corresponds one-to-one to the first instruction in the corresponding first instruction sequence; the first instruction feedback includes the corresponding first instruction, first instruction line number feedback, first instruction duration feedback and first instruction execution status; The first instruction row number feedback is a preset invalid feedback value, a zero value, or an integer value greater than zero; the invalid feedback value is a preset negative integer value less than zero; The first instruction execution status includes success and failure; The first total duration is the sum of all first instruction duration feedbacks; The first expected total number of successes is the total number of the first instructions in the corresponding first instruction sequence; The first actual total number of successes is the total number of successful first instruction execution states in the first instruction feedback sequence.

3. The processing method for batch SQL instruction auditing according to claim 2 is characterized in that: Pre-executing the first instruction sequence on the first MySQL database using the transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list specifically includes: Step 31: Execute an SQL instruction for starting a database transaction on the first MySQL database; and record the currently started database transaction as the corresponding current transaction; the instruction type of the SQL instruction for starting a database transaction is a BEGIN instruction or a START TRANSACTION instruction; Step 32: taking the first instruction of the first instruction sequence as the corresponding current SQL instruction; Step 33: Identify whether the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction; if so, set the corresponding current monitoring switch to an on state; otherwise, set the corresponding current monitoring switch to an off state; the current monitoring switch includes an on state and an off state; Step 34, obtain the current time as the corresponding first time by calling the MICROSECOND function on the first MySQL database; and execute the current SQL instruction on the first MySQL database when the first time is successfully obtained; and identify the current monitoring switch at the end of the execution of the current SQL instruction. If the current monitoring switch is on, obtain the operation row identifier of the most recent SQL instruction as the corresponding first instruction row count feedback by calling the ROW_COUNT function on the first MySQL database. If the current monitoring switch is off, set the corresponding first instruction row count feedback to the invalid feedback value; and when the first instruction row count feedback is successfully obtained Then, the current time is obtained as the corresponding second time by calling the MICROSECOND function on the first MySQL database; the absolute time difference between the first and second times is calculated and the calculation result is fed back as the corresponding first instruction duration; the first instruction row number feedback obtained this time is identified, and if the first instruction row number feedback is the invalid feedback value or an integer value greater than zero, the corresponding first instruction execution status is set to success; if the first instruction row number feedback is zero, the corresponding first instruction execution status is set to failure; the time accuracy of the first and second times is both in the millisecond level; the value of the operation row identifier of the most recent SQL instruction is a zero value or an integer value greater than zero; Step 35: The first instruction corresponding to the current SQL instruction, the first instruction row number feedback, the first instruction duration feedback, and the first instruction execution status are used to form a corresponding first instruction feedback; Step 36: Identify whether the current SQL instruction is the last first instruction in the first instruction sequence. If so, go to step 37. If not, extract the next first instruction in the first instruction sequence as the new current SQL instruction and return to step 33. Step 37: Execute an SQL instruction for ending the current transaction on the first MySQL database; the instruction type of the SQL instruction for ending the current transaction is a ROLLBACK instruction; Step 38: All the first instruction feedbacks obtained are sorted in chronological order to form a corresponding first instruction feedback sequence; and the sum of all the first instruction duration feedbacks in the first instruction feedback sequence is calculated to obtain the corresponding first total duration; and the total number of the first instructions in the first instruction sequence is counted and the statistical result is used as the corresponding first expected total success; and the total number of the first instruction execution states that are specifically successful in the first instruction feedback sequence is counted and the statistical result is used as the corresponding first actual total success; and the first instruction feedback sequence, the first total duration, the first expected total success and the first actual total success obtained this time form the corresponding first pre-execution feedback list.

4. The method for processing batch SQL instruction audit according to claim 2, characterized in that: The three types of checks performed according to the first pre-execution feedback list to obtain corresponding single instruction duration check results, batch instruction average duration check results, and batch instruction execution status check results specifically include: All first instruction feedbacks of the first pre-execution feedback list are traversed; and during the traversal, the first instruction feedback currently traversed is used as the corresponding current instruction feedback; and the first instruction and the first instruction duration feedback of the instruction feedback are used as the corresponding current SQL instruction and the current instruction duration; and when the instruction type of the current SQL instruction is an INSERT instruction, an UPDATE instruction, or a DELETE instruction, whether the current instruction duration exceeds a preset single instruction duration threshold is identified, and if so, the corresponding first instruction check result is set to failure; if not, the corresponding first instruction check result is set to success; and when the instruction type of the current SQL instruction is not an INSERT instruction, an UPDATE instruction, or a DELETE instruction, the corresponding first instruction check result is set to success; and at the end of the traversal, the total number of the first instruction check results specifically set to failure is counted to obtain a corresponding first statistical number; and the first statistical number is identified, and if the first statistical number is zero, the corresponding single instruction duration check result is set to success; if the first statistical number is greater than zero, the corresponding single instruction duration check result is set to failure; and dividing the first total duration of the first pre-execution feedback list by the first expected total number of successes as the corresponding average instruction duration; and identifying whether the average instruction duration exceeds a preset average instruction duration threshold; if so, setting the corresponding batch instruction average duration check result to failure; if not, setting the corresponding batch instruction average duration check result to success; The first expected total number of successes and the first actual total number of successes in the first pre-execution feedback list are compared; if the first expected total number of successes is greater than the first actual total number of successes, the corresponding batch instruction execution status check result is set to failure; if the first expected total number of successes is equal to the first actual total number of successes, the corresponding batch instruction execution status check result is set to success.

5. A device for executing the processing method for batch SQL instruction auditing according to any one of claims 1 to 4, characterized in that: The device includes: an audit interface configuration module, an audit application receiving module, an audit processing module and an audit result feedback module; The audit interface configuration module is used to pre-configure a service interface for auditing batch operation instructions of the first MySQL database as a corresponding first audit interface; the interface address format of the first audit interface is a URL address format; The audit application receiving module is configured to receive a first audit application sent by a first applicant through the first audit interface; the first audit application includes a first applicant interface, a first application time, a first instruction batch, and a first instruction sequence; the interface address format of the first applicant interface is a URL address format; the first instruction sequence includes one or more first instructions; each first instruction is an SQL instruction that performs a database operation on the first MySQL database; The audit processing module is configured to pre-execute the first instruction sequence on the first MySQL database using a transaction processing mechanism of the MySQL database to obtain a corresponding first pre-execution feedback list; and perform executable authorization analysis on the first instruction sequence based on the first pre-execution feedback list to obtain a corresponding first authorization result; the first authorization result includes authorization passed and authorization failed; The audit result feedback module is used to form a corresponding first audit feedback based on the first application time and the first instruction batch of the first audit application and the corresponding first authorization result; and send the first audit feedback back to the first applicant interface of the first audit application through the first audit interface.

6. An electronic device, characterized in that: include: memory, processors, and transceivers; The processor is configured to be coupled to the memory, read and execute instructions in the memory, so as to implement the method according to any one of claims 1 to 4; The transceiver is coupled to the processor, and the processor controls the transceiver to send and receive messages.

7. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and when the computer instructions are executed by a computer, the computer is caused to execute the method according to any one of claims 1 to 4.

Images