A SQL error fixing method, device, equipment and medium

Abstract

This application discloses an SQL error repair method, apparatus, device, and medium, relating to the field of database querying. The method includes: determining an initial SQL statement and a current error message; classifying the current error message hierarchically based on a preset mapping table to obtain a target error type; selecting a target repair strategy based on the target error type; extracting target schema information from the initial SQL statement; using the target repair strategy, generating a repaired SQL statement based on the initial SQL statement, target schema information, and current error message using a target large language model; performing feasibility verification, re-execution, and repair confidence assessment on the repaired SQL statement to generate a target confidence score; if the re-execution is successful, returning the repaired SQL statement and the target confidence score to the user; if the re-execution fails, returning a new current error message to the user. This method can identify specific error types and repair SQL statements accordingly.

Description

Technical Field

[0001] This invention relates to the field of database querying, and in particular to a method, apparatus, device, and medium for SQL error repair. Background Technology

[0002] NL2SQL (large language model based natural language to SQL) is one of the core technologies in the field of database querying. Its well-known workflow is as follows: the system receives a user's natural language query, understands the query intent through a large language model, generates a corresponding SQL (Structured Query Language) query statement, and then executes the SQL statement to retrieve the results from the database. The core value of NL2SQL lies in lowering the technical barrier to database querying, enabling non-technical personnel to interact with the database using natural language.

[0003] However, SQL execution errors often occur in NL2SQL systems. To address this issue, the existing technical solutions employ the following steps: Step 1: Receive SQL execution errors. When the system executes an SQL statement and the database returns an error, the error information is captured.

[0004] Step 2: Simple Retry. The system directly sends the error message to the NL2SQL generation module, requesting it to regenerate the SQL statement. During the retry, a simple message may be added, such as "Please correct the SQL error".

[0005] Step 3: Re-execute. The system executes the regenerated SQL statement. If successful, it returns the result; if it fails, it may continue to retry or return an error.

[0006] In conclusion, identifying specific error types to address specific SQL statement repair needs to be addressed urgently. Summary of the Invention

[0007] In view of this, the purpose of this invention is to provide an SQL error repair method, apparatus, device, and medium capable of identifying specific error types and repairing SQL statements accordingly. The specific solution is as follows: Firstly, this application provides a method for fixing SQL errors, including: The initial SQL statement that malfunctions when executed by a preset SQL executor is identified, along with its corresponding current error message. Based on a preset mapping table, the current error message of the initial SQL statement is categorized hierarchically to obtain the target error type of the initial SQL statement. Select the corresponding target repair strategy based on the target error type, and extract the target schema information from the initial SQL statement; Using the target repair strategy, the target large language model generates the corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message; The repaired SQL statement is subjected to feasibility verification and re-execution, and the repair confidence of the repaired SQL statement is evaluated to generate the target confidence of the repaired SQL statement. If the re-execution is successful, the repaired SQL statement and the target confidence level will be returned to the user. If re-execution fails, a new current error message is returned to the user terminal so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

[0008] Optionally, the step of hierarchically classifying the current error message of the initial SQL statement based on a preset mapping table to obtain the target error type of the initial SQL statement includes: Determine whether the current error message contains a preset synthetic verification flag; If it is included, the target error type of the initial SQL statement is determined to be a validation rejection type, and is used as the target error type; if it is not included, it is determined whether the current error message hits the infrastructure error message rule. If a match is found, the target error type of the initial SQL statement is determined to be an infrastructure type, and is used as the target error type; if a match is not found, it is determined whether the current error message contains a vendor error code. If it exists, the target error type of the initial SQL statement is determined to be a vendor error code type; if it does not exist, the current error message is determined to match the preset error type according to the preset error regular expression table. If a match is found, the target error type of the initial SQL statement is determined; if no match is found, the target error type of the initial SQL statement is determined to be an unknown type.

[0009] Optionally, extracting the target schema information from the initial SQL statement includes: Use a preset regular expression to extract the target table name from the clause of the initial SQL statement; If the extraction is successful, the corresponding table is loaded according to the target table name, and the loaded table is formatted as concatenated text to serve as the target schema information; If extraction fails, the target schema information will be returned as empty.

[0010] Optionally, the step of utilizing the target repair strategy to generate a corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message using the target large language model includes: Determine the corresponding preset prompt word template based on the target error type; The initial SQL statement, the current error message, the target schema information, and the target error type are filled into the preset prompt word template to obtain the target prompt word; An initial large language model is determined, and the parameters of the initial large language model are adjusted based on preset requirements to obtain the target large language model; The target prompt word is input into the target large language model to obtain the model output result; Extract the corresponding repaired SQL statements from the output of the model.

[0011] Optionally, the feasibility verification and re-execution operation of the repaired SQL statement includes: Based on the target error type, the error signature is extracted from the current error message, and the signature verification operation is performed on the repaired SQL statement according to the error signature to obtain the first verified SQL statement; The first verified SQL statement is subjected to structure and security checks to obtain the second verified SQL statement; the structure and security checks include any one or more of read-only checks, security checks, and structure consistency checks. The SQL statement after the second verification is re-executed to obtain the corresponding re-execution result.

[0012] Optionally, the step of extracting the error signature from the current error message based on the target error type, and performing a signature verification operation on the repaired SQL statement according to the error signature to obtain a first verified SQL statement includes: If the target error type is a column non-existent error type, then extract the target error column name from the current error message, verify whether the repaired SQL statement has replaced the target error column name based on the target error column name, and obtain the first verified SQL statement according to the corresponding verification result; If the target error type is a table non-existent error type, then extract the target error table name from the current error message, verify whether the repaired SQL statement has replaced the target error table name based on the target error table name, and obtain the first verified SQL statement according to the corresponding verification result; If the target error type is a column name ambiguity error type, then check whether the fixed column name appears in the SQL statement and whether it references an undefined column name, and obtain the first checked SQL statement based on the corresponding check results; If the target error type is a column name or expression type error, then extract the column name or expression involved in the type error from the current error message. Based on the column name or expression involved in the type error, verify whether the correct type conversion function has been used in the repaired SQL statement, or whether the type mismatch problem in the expression has been corrected. Obtain the first verified SQL statement according to the corresponding verification result. If the target error type is a GROUP BY error type, then extract the target ungrouped column name from the current error message, verify whether the GROUP BY clause of the repaired SQL statement already contains the target ungrouped column name, and obtain the first verified SQL statement based on the corresponding verification result.

[0013] Optionally, the step of evaluating the repair confidence of the repaired SQL statement to generate the target confidence corresponding to the repaired SQL statement includes: Determine the current number of times the repaired SQL statement has been repaired; Obtain the corresponding round weight coefficient based on the current number of repairs; The signature coverage of the repaired SQL statement is determined based on the verification result of the signature verification operation. The signature coverage rate is multiplied by the round weight coefficient to generate the target confidence level corresponding to the repaired SQL statement.

[0014] Secondly, this application provides an SQL error repair apparatus, comprising: The type acquisition module is used to determine the initial SQL statement that malfunctions when executed by a preset SQL executor and the corresponding current error message. Based on a preset mapping table, the current error message of the initial SQL statement is hierarchically classified to obtain the target error type of the initial SQL statement. The information extraction module is used to select the corresponding target repair strategy according to the target error type and extract target schema information from the initial SQL statement; The statement repair module is used to generate a corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message using the target large language model, according to the target repair strategy. The confidence generation module is used to perform feasibility verification and re-execution operations on the repaired SQL statement, and to evaluate the repair confidence of the repaired SQL statement in order to generate the target confidence of the repaired SQL statement. The first repair determination module is used to return the repaired SQL statement and target confidence level to the user terminal if the re-execution is successful. The second repair determination module is used to return a new current error message to the user terminal if the re-execution fails, so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

[0015] Thirdly, this application provides an electronic device, comprising: Memory, used to store computer programs; A processor is used to execute the computer program to implement the SQL error repair method as described above.

[0016] Fourthly, this application provides a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by a processor, it implements the SQL error repair method as described above.

[0017] In summary, this application first identifies the initial SQL statement that malfunctions through a preset SQL executor and its corresponding current error message. Based on a preset mapping table, the current error message of the initial SQL statement is categorized hierarchically to obtain the target error type of the initial SQL statement. A corresponding target repair strategy is selected based on the target error type, and target schema information is extracted from the initial SQL statement. Using the target repair strategy, a corresponding repaired SQL statement is generated based on the initial SQL statement, the target schema information, and the current error message using a target large language model. The repaired SQL statement undergoes feasibility verification and re-execution, and a repair confidence assessment is performed to generate the target confidence level corresponding to the repaired SQL statement. If the re-execution is successful, the repaired SQL statement and the target confidence level are returned to the user. If the re-execution fails, a new current error message is returned to the user so that the user can re-execute the step of categorizing the current error message of the initial SQL statement hierarchically based on the preset mapping table to obtain the target error type of the initial SQL statement. As described above, this application first executes the erroneous SQL using a preset executor. Based on error messages and a mapping table, errors are categorized hierarchically to determine their types. Then, a repair strategy is selected based on the error type, and schema information is extracted from the SQL. Next, a large language model is used to generate the repaired SQL statement. Finally, the repair results are validated for feasibility, re-executed, and assessed for confidence. If execution is successful, the repaired SQL and confidence score are returned to the user. If it fails, a new error message is displayed, causing the system to restart the error classification process until successful repair or continuous error feedback. This approach achieves automatic classification through error pattern matching, configures a dedicated repair function for each type of error, and accurately locates the repair point using multi-table schema information and prompt word templates, avoiding blind retries and significantly improving the success rate. Furthermore, the introduction of repair confidence assessment quantifies the results into hierarchical quality scores, supports threshold control and manual review, and incorporates a failure signature history constraint mechanism to reduce repeated LLM errors. The integration of vendor error code normalization and structured error messages enhances classification stability, significantly improving the query success rate and user experience of the NL2SQL system. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0019] Figure 1This is a flowchart of an SQL error repair method disclosed in this application; Figure 2 This application discloses a specific error classification flowchart; Figure 3 This application discloses a specific hierarchical flowchart for repair verification. Figure 4 This application discloses a specific overall framework diagram for SQL error repair. Figure 5 This is a schematic diagram of the structure of an SQL error repair device disclosed in this application; Figure 6 This is a structural diagram of an electronic device disclosed in this application. Detailed Implementation

[0020] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0021] Currently, SQL execution errors are common in NL2SQL systems. To address this issue, existing solutions typically involve the following steps: Step 1: Receiving SQL Execution Errors. The system executes the SQL statement, and when the database returns an error, the error information is captured. Step 2: Simple Retry. The system directly sends the error information back to the NL2SQL generation module, requesting it to regenerate the SQL statement. During the retry, a simple prompt may be added, such as "Please correct the SQL error." Step 3: Re-execution. The system executes the regenerated SQL statement. If successful, it returns the result; if it fails, it may continue retrying or return an error. To solve the aforementioned technical problem, this application discloses an SQL error repair method, apparatus, device, and medium capable of identifying specific error types and repairing SQL statements accordingly.

[0022] See Figure 1 As shown in the figure, an embodiment of the present invention discloses an SQL error repair method, including: Step S11: Determine the initial SQL statement that malfunctioned through the preset SQL executor and its corresponding current error message. Based on the preset mapping table, classify the current error message of the initial SQL statement hierarchically to obtain the target error type of the initial SQL statement.

[0023] In this embodiment, when the preset SQL executor fails to execute the SQL, the initial SQL statement, error message text, and optional structured vendor error messages, such as mysql_errno, pg_sqlstate, and ora_code, are received through the database driver's error callback mechanism or exception capture mechanism.

[0024] Then, the error messages are categorized hierarchically to identify error types. First, it is determined whether the current error message contains a preset synthetic checksum. If it does, the target error type of the initial SQL statement is determined to be a checksum rejection type, and this is used as the target error type. If it does not, it is determined whether the current error message matches an infrastructure error message rule. If it does, the target error type of the initial SQL statement is determined to be an infrastructure type, and this is used as the target error type. If it does not, it is determined whether the current error message contains a vendor error code. If it does, the target error type of the initial SQL statement is determined to be a vendor error code type. If it does not, it is determined whether the current error message matches a preset error type according to a preset error regular expression table. If it matches, the target error type of the initial SQL statement is determined. If it does not match, the target error type of the initial SQL statement is determined to be an unknown type. Specifically, the identification of error messages is divided into five layers, such as... Figure 2 As shown, the first layer checks whether the error message contains a synthetic validation flag, such as [SQL_VALIDATION]. If so, it is classified as a validation rejection type. The second layer checks whether the infrastructure error message rule is hit. If so, it is classified as an infrastructure type and does not proceed to LLM repair. The third layer merges the vendor error code parsed from the string with the structured vendor_error_hints and performs vendor code mapping classification. The fourth layer matches each item in an ordered regular expression table (narrow rules take precedence), including column non-existence, column name ambiguity, table non-existence, type error, GROUP BY error, and syntax error. In the fifth layer, if none of the above are hit, it is classified as an unknown type, and whether to attempt repair depends on the configuration strategy.

[0025] Step S12: Select the corresponding target repair strategy according to the target error type, and extract the target schema information from the initial SQL statement.

[0026] In this embodiment, the corresponding specialized repair function, i.e., the target repair strategy, is selected based on the error type. For example: syntax error: select the syntax error repair function; column not found error: select the column not found repair function; column name ambiguity error: select the column name ambiguity repair function; table not found error: select the table not found repair function; type error: select the type error repair function; GROUP BY error: select the GROUP BY error repair function; validation rejection error: select the validation rejection repair function; unknown type error: if configured to allow, select the unclassified execution error repair function.

[0027] Simultaneously, the target table name is extracted from the clauses of the initial SQL statement using preset regular expressions. If extraction is successful, the corresponding table is loaded based on the target table name, and the loaded table is formatted as concatenated text to serve as the target schema information. If extraction fails, empty target schema information is returned. Specifically, regular expressions are used to extract table names from the FROM, JOIN, UPDATE, and INSERT INTO clauses of the SQL statement, supporting multiple table name formats and filtering CTE temporary table names. The first N tables are loaded according to the configuration, for example, 3 by default and up to 12 by maximum, and formatted as concatenated text after parallel loading. If no table name is extracted or the schema loading is abnormal, a fallback process is performed, returning empty schema text.

[0028] Step S13: Using the target repair strategy, the target large language model generates the corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message.

[0029] In this embodiment, to invoke the large language model, a repaired SQL statement is generated by combining the SQL statement, error message, schema information, and error type. A corresponding preset prompt word template needs to be determined based on the target error type. The initial SQL statement, the current error message, the target schema information, and the target error type are filled into the preset prompt word template to obtain the target prompt word. An initial large language model is determined, and its parameters are adjusted based on preset requirements to obtain the target large language model. The target prompt word is input into the target large language model to obtain the model output. The corresponding repaired SQL statement is extracted from the model output. Specifically, a dedicated prompt word template is constructed for each error type, including the original SQL statement, error message, schema information, and error type-specific guidance information. The initial SQL statement, the current error message, the target schema information, and the target error type are filled into the prompt word template to obtain the corresponding target prompt word. The LLM temperature parameter is set to 0.1 to ensure repair accuracy, and the maximum number of tokens is set to 1024 to limit the response length. The LLM model, with its input parameters adjusted based on the target prompt, responds to the prompt and extracts the repaired SQL statement from the response. Both code block and inline formats are supported. The extracted SQL statement needs to be cleaned up, removing extra whitespace and verifying that it is not empty and differs from the original SQL. Furthermore, during multiple rounds of SQL statement repair, the signature verification results of previous failed rounds are recorded, and the constraint text "Do not retain historical failure signatures" is explicitly injected into the prompts of subsequent rounds.

[0030] Step S14: Perform feasibility verification and re-execution on the repaired SQL statement, and evaluate the repair confidence of the repaired SQL statement to generate the target confidence of the repaired SQL statement.

[0031] In this embodiment, an error signature is extracted from the current error message based on the target error type. A signature verification operation is performed on the repaired SQL statement according to the error signature to obtain a first verified SQL statement. The first verified SQL statement undergoes structural and security verification to obtain a second verified SQL statement. The structural and security verification includes any one or more of read-only verification, security verification, and structural consistency verification. The second verified SQL statement is re-executed to obtain the corresponding re-execution result. Specifically, as shown... Figure 3As shown, the error signature is extracted based on the error message, and the corrected SQL is verified to have completed the corresponding replacement. The corrected SQL that passes the signature verification undergoes read-only verification, restricting it to allowed query operations. Simultaneously, security verification is performed, detecting risk patterns such as dangerous keywords and comment injection. Furthermore, structural consistency verification is conducted, using a whitelist of allowed tables and a phantom detector to verify the consistency between table names / field names and the schema structure. Finally, the corrected SQL that passes verification is re-executed to confirm that it can successfully return results.

[0032] Furthermore, when performing signature verification on the repaired SQL statement, if the target error type is a column non-existent error, the target error column name is extracted from the current error message, and the repaired SQL statement is used to verify whether the target error column name has been replaced based on the target error column name. A first verified SQL statement is obtained based on the corresponding verification result. If the target error type is a table non-existent error, the target error table name is extracted from the current error message, and the repaired SQL statement is used to verify whether the target error table name has been replaced based on the target error table name. A first verified SQL statement is obtained based on the corresponding verification result. If the target error... If the error type is column name ambiguity, then the repaired SQL statement is checked to see if qualified column names appear and whether unqualified column names are referenced. The first verified SQL statement is obtained based on the corresponding verification results. If the target error type is column name or expression type error, then the column names or expressions involving type errors are extracted from the current error message. Based on the column names or expressions involving type errors, the repaired SQL statement is checked to see if the correct type conversion function has been used, or if the type mismatch problem in the expression has been corrected. The first verified SQL statement is obtained based on the corresponding verification results. If the target error type is GROUP BY error, then the target ungrouped column names are extracted from the current error message. The GROUP BY clause of the repaired SQL statement is checked to see if the target ungrouped column names are included. The first verified SQL statement is obtained based on the corresponding verification results.

[0033] Specifically, for column non-existent errors: extract the erroneous column name from the error message, in both forms with and without table prefixes, and verify whether the repair SQL has replaced the erroneous column name. For table non-existent errors: extract the erroneous table name from the error message, in both forms with and without database prefixes, and verify whether the repair SQL has replaced the erroneous table name. For column name ambiguity errors: check whether the repair SQL contains qualified column names, such as alias.column name, and check whether there are still references to unqualified column names in the repair SQL. If qualified column names are present and no unqualified column names remain, the repair is considered effective. For type errors: extract the column names or expressions involving type errors from the error message, and verify whether the repair SQL has used the correct type conversion functions, such as CAST, CONVERT, TO_DATE, etc., or whether type mismatch issues in expressions have been corrected. Verify the type compatibility after repair through the field type definitions in the schema information. For GROUP BY errors: extract the ungrouped column names from the error message and verify whether the GROUP BY clause of the repair SQL contains these column names. Simultaneously verify that all non-aggregate columns in the SELECT list appear in the GROUP BY clause to ensure the semantic correctness of the grouped query. Signature coverage calculation: Calculate the ratio of the number of satisfied signature sub-rules to the total number of signature sub-rules, with a value ranging from 0 to 1.

[0034] In addition, a repair confidence score needs to be calculated based on the error signature coverage and the number of repair attempt rounds. First, determine the current repair count of the repaired SQL statement; obtain the corresponding round weight coefficient based on the current repair count; determine the signature coverage rate of the repaired SQL statement based on the verification results of the signature verification operation; multiply the signature coverage rate by the round weight coefficient to generate the target confidence score for the repaired SQL statement. Specifically, the repair confidence score is calculated jointly by the signature coverage rate and the number of repair attempt rounds. The specific calculation formula is as follows: Confidence score = Signature coverage rate × Round weight coefficient. Where, signature coverage rate = Number of satisfied signature sub-rules / Total number of signature sub-rules; the round weight coefficient is determined according to the number of repair attempt rounds, for example, 1.0 for the first round, 0.85 for the second round, 0.7 for the third round, and so on. For example, if a repair is completed in the first round with a signature coverage rate of 0.8, the confidence score is 0.8 × 1.0 = 0.8; if it is completed in the second round with a signature coverage rate of 0.9, the confidence score is 0.9 × 0.85 = 0.765. The higher the signature coverage rate, the higher the confidence score; the earlier the repair attempt round, the higher the confidence score.

[0035] Step S15: If the re-execution is successful, the repaired SQL statement and target confidence level are returned to the user.

[0036] In this embodiment, if the re-execution is successful, the repaired SQL statement and its corresponding target confidence score will be returned to the user. It's important to understand that the confidence score is used to audit the reliability of the repair, mark low-confidence repairs to trigger manual review, and serve as a data feedback signal for subsequent strategy optimization. The confidence score reflects the credibility of the repair plan, ensuring the security and accuracy of data operations.

[0037] Step S16: If re-execution fails, a new current error message is returned to the user terminal so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

[0038] In this embodiment, if re-execution fails, a new current error message will be returned to the user. Upon receiving this message, the user will initiate a new round of SQL statement repair operations. This cyclical mechanism can automatically adjust the repair strategy for different failure reasons until re-execution succeeds or the termination condition is met, effectively improving the adaptability and accuracy of automated SQL repair.

[0039] As described above, this embodiment first executes the erroneous SQL using a preset executor. Based on error messages and a mapping table, errors are categorized hierarchically to determine their types. Then, a repair strategy is selected based on the error type, and schema information is extracted from the SQL. Next, a large language model is used to generate the repaired SQL statement. Finally, the repair results are verified for feasibility, re-executed, and their confidence is assessed. If execution is successful, the repaired SQL and confidence score are returned to the user. If it fails, a new error message is fed back, causing the system to restart the error classification process until successful repair or continuous error feedback. In this way, automatic classification is achieved through error pattern matching, a dedicated repair function is configured for each type of error, and the repair is accurately located by combining multi-table schema information and prompt word templates, avoiding blind retries and significantly improving the success rate. Simultaneously, the introduction of repair confidence assessment quantifies the results into hierarchical quality scores, supports threshold control and manual review, and a failure signature history constraint mechanism reduces repeated LLM errors. The integration of vendor error code normalization and structured error messages enhances classification stability, significantly improving the query success rate and user experience of the NL2SQL system.

[0040] Based on the previous embodiment, this application discloses an SQL error repair method that can identify specific error types and repair SQL statements accordingly. Next, taking an e-commerce data analysis system as an example, a user submits the initial SQL statement "SELECT order_id, SUM(price) FROM orders GROUP BY order_id", and after execution, it returns the error message "column 'price' does not exist". The method will then address specific errors such as... Figure 4 This paper provides a detailed explanation of an SQL error repair method.

[0041] First, using the vendor error code mapping, issues not classified as vendor errors are identified. The error is then categorized using a pre-defined mapping table, classifying it as a semantic-layer "column name not found" target error type. Based on this type, a schema-based column name correction function is selected, and target schema information is extracted from the initial SQL, i.e., the `orders` table contains columns such as `order_id` and `total_price`. Subsequently, the corrected SQL statement is generated using the target large language model: "SELECT order_id, SUM(total_price) FROM orders GROUP BY order_id". The corrected statement is then validated for feasibility and re-executed, with a repair confidence score of 0.92. If re-execution is successful, the corrected SQL and the confidence score of 0.92 are returned to the user. If it still fails, displaying the message "relation 'orders' does not exist", this new error message is returned, prompting the user to re-execute the categorization steps to enter the next round of the repair cycle.

[0042] See Figure 5 As shown in the figure, an embodiment of the present invention discloses an SQL error repair device, comprising: The type acquisition module 11 is used to determine the initial SQL statement that malfunctions through a preset SQL executor and the corresponding current error message, and to perform hierarchical classification of the current error message of the initial SQL statement based on a preset mapping table to obtain the target error type of the initial SQL statement; Information extraction module 12 is used to select the corresponding target repair strategy according to the target error type and extract target schema information from the initial SQL statement; Statement repair module 13 is used to generate a corresponding repaired SQL statement based on the initial SQL statement, the target schema information and the current error message using the target large language model, by utilizing the target repair strategy. The confidence generation module 14 is used to perform feasibility verification and re-execution operations on the repaired SQL statement, and to evaluate the repair confidence of the repaired SQL statement in order to generate the target confidence of the repaired SQL statement. The first repair determination module 15 is used to return the repaired SQL statement and target confidence level to the user terminal if the re-execution is successful. The second repair determination module 16 is used to return a new current error message to the user terminal if the re-execution fails, so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

[0043] As described above, this application first executes the erroneous SQL using a preset executor. Based on error messages and a mapping table, errors are categorized hierarchically to determine their types. Then, a repair strategy is selected based on the error type, and schema information is extracted from the SQL. Next, a large language model is used to generate the repaired SQL statement. Finally, the repair results are validated for feasibility, re-executed, and assessed for confidence. If execution is successful, the repaired SQL and confidence score are returned to the user. If it fails, a new error message is displayed, causing the system to restart the error classification process until successful repair or continuous error feedback. This approach achieves automatic classification through error pattern matching, configures a dedicated repair function for each type of error, and accurately locates the repair point using multi-table schema information and prompt word templates, avoiding blind retries and significantly improving the success rate. Furthermore, the introduction of repair confidence assessment quantifies the results into hierarchical quality scores, supports threshold control and manual review, and incorporates a failure signature history constraint mechanism to reduce repeated LLM errors. The integration of vendor error code normalization and structured error messages enhances classification stability, significantly improving the query success rate and user experience of the NL2SQL system.

[0044] In some specific implementations, the type acquisition module 11 may specifically include: The first judgment unit is used to determine whether the current error message contains a preset synthetic verification flag; The second judgment unit is used to determine, if included, that the target error type of the initial SQL statement is a validation rejection type, and use it as the target error type; if not included, it is used to determine whether the current error message hits the infrastructure error message rule. The third judgment unit is used to determine, if a match is found, that the target error type of the initial SQL statement is an infrastructure type, and use it as the target error type; if a match is not found, it is used to determine whether the current error message contains a vendor error code. The fourth judgment unit is used to determine, if it exists, that the target error type of the initial SQL statement is a vendor error code type; if it does not exist, it determines whether the current error message matches a preset error type according to a preset error regular expression table. The fifth judgment unit is used to determine the target error type of the initial SQL statement if a match is found, and to determine that the target error type of the initial SQL statement is an unknown type if a mismatch is found.

[0045] In some specific implementations, the information extraction module 12 may specifically include: The table name extraction unit is used to extract the target table name from the clause of the initial SQL statement using a preset regular expression; The first information acquisition unit is used to load the corresponding table according to the target table name if the extraction is successful, and to format the loaded table into concatenated text as target schema information. The second information acquisition unit is used to return empty target schema information if the extraction fails.

[0046] In some specific implementations, the statement repair module 13 may specifically include: The template determination unit is used to determine the corresponding preset prompt word template based on the target error type; The prompt word acquisition unit is used to fill the initial SQL statement, the current error message, the target schema information, and the target error type into the preset prompt word template to obtain the target prompt word; The model acquisition unit is used to determine the initial large language model and adjust the parameters of the initial large language model based on preset requirements to obtain the target large language model. The result acquisition unit is used to input the target prompt word into the target large language model to obtain the model output result; The statement extraction unit is used to extract the corresponding repaired SQL statements from the output results of the model.

[0047] In some specific implementations, the confidence generation module 14 may specifically include: The first statement verification unit is used to extract the error signature from the current error message based on the target error type, and perform a signature verification operation on the repaired SQL statement according to the error signature to obtain the first verified SQL statement. The second statement verification unit is used to perform structure and security verification on the first verified SQL statement to obtain the second verified SQL statement; the structure and security verification includes any one or more of read-only verification, security verification and structure consistency verification. The statement re-execution unit is used to re-execute the SQL statement after the second verification to obtain the corresponding re-execution result.

[0048] In some specific implementations, the first statement verification unit may specifically include: The first statement obtains a sub-unit, which is used to extract the target error column name from the current error message if the target error type is a column non-existent error type, verify whether the repaired SQL statement has replaced the target error column name based on the target error column name, and obtain the first verified SQL statement according to the corresponding verification result; The second statement obtains a sub-unit, which is used to extract the target error table name from the current error message if the target error type is a table non-existent error type, verify whether the repaired SQL statement has replaced the target error table name based on the target error table name, and obtain the first verified SQL statement according to the corresponding verification result; The third statement obtains a sub-unit, which is used to verify whether the fixed column name appears in the SQL statement and whether it references an undefined column name if the target error type is a column name ambiguity error type, and to obtain the first verified SQL statement based on the corresponding verification result. The fourth statement acquisition sub-unit is used to extract the column name or expression involved in the type error from the current error message if the target error type is a column name or expression type error type. Based on the column name or expression involved in the type error, it verifies whether the correct type conversion function has been used in the repaired SQL statement, or whether the type mismatch problem in the expression has been corrected. The first verified SQL statement is obtained according to the corresponding verification result. The fifth statement obtains a sub-unit, which is used to extract the target ungrouped column name from the current error message if the target error type is a GROUP BY error type, verify whether the GROUP BY clause of the repaired SQL statement already contains the target ungrouped column name, and obtain the first verified SQL statement based on the corresponding verification result.

[0049] In some specific implementations, the confidence generation module 14 may specifically include: The number of repairs is determined by a unit used to determine the current number of repairs to the repaired SQL statement. The coefficient acquisition unit is used to acquire the corresponding round weight coefficient based on the current number of repairs. The coverage determination unit is used to determine the signature coverage of the repaired SQL statement based on the verification result of the signature verification operation. The confidence generation unit is used to multiply the signature coverage rate by the round weight coefficient to generate the target confidence level corresponding to the repaired SQL statement.

[0050] Furthermore, embodiments of this application also disclose an electronic device, Figure 6 This is a structural diagram of an electronic device 20 according to an exemplary embodiment. The content of the diagram should not be construed as limiting the scope of this application.

[0051] Figure 6 This is a schematic diagram of the structure of an electronic device 20 provided in an embodiment of this application. Specifically, the electronic device 20 may include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25, and a communication bus 26. The memory 22 stores a computer program, which is loaded and executed by the processor 21 to implement the relevant steps in the SQL error repair method disclosed in any of the foregoing embodiments. Alternatively, the electronic device 20 in this embodiment may specifically be an electronic computer.

[0052] In this embodiment, the power supply 23 is used to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and external devices, and the communication protocol it follows can be any communication protocol applicable to the technical solution of this application, and is not specifically limited here; the input / output interface 25 is used to acquire external input data or output data to the outside world, and its specific interface type can be selected according to specific application needs, and is not specifically limited here.

[0053] In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, disk or optical disk, etc. The resources stored thereon can include operating system 221, computer program 222, etc., and the storage method can be temporary storage or permanent storage.

[0054] The operating system 221 is used to manage and control the various hardware devices on the electronic device 20 and the computer program 222, which may be Windows Server, Netware, Unix, Linux, etc. In addition to including a computer program capable of performing the SQL error repair method executed by the electronic device 20 as disclosed in any of the foregoing embodiments, the computer program 222 may further include computer programs capable of performing other specific tasks.

[0055] Furthermore, this application also discloses a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by a processor, it implements the aforementioned disclosed SQL error repair method. Specific steps of this method can be found in the corresponding content disclosed in the foregoing embodiments, and will not be repeated here.

[0056] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the apparatus disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple; relevant parts can be referred to in the method section.

[0057] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0058] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein can be implemented directly by hardware, a software module executed by a processor, or a combination of both. The software module can be located in random access memory (RAM), main memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disk, removable disk, CD-ROM, or any other form of storage medium known in the art.

[0059] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0060] The technical solutions provided in this application have been described in detail above. Specific examples have been used to illustrate the principles and implementation methods of this application. The descriptions of the above embodiments are only for the purpose of helping to understand the methods and core ideas of this application. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A method for repairing SQL errors, characterized in that, include: The initial SQL statement that malfunctions when executed by a preset SQL executor is identified, along with its corresponding current error message. Based on a preset mapping table, the current error message of the initial SQL statement is categorized hierarchically to obtain the target error type of the initial SQL statement. Select the corresponding target repair strategy based on the target error type, and extract the target schema information from the initial SQL statement; Using the target repair strategy, the target large language model generates the corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message; The repaired SQL statement is subjected to feasibility verification and re-execution, and the repair confidence of the repaired SQL statement is evaluated to generate the target confidence of the repaired SQL statement. If the re-execution is successful, the repaired SQL statement and the target confidence level will be returned to the user. If re-execution fails, a new current error message is returned to the user terminal so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

2. The SQL error repair method according to claim 1, characterized in that, The step of classifying the current error message of the initial SQL statement hierarchically based on a preset mapping table to obtain the target error type of the initial SQL statement includes: Determine whether the current error message contains a preset synthetic verification flag; If it is included, the target error type of the initial SQL statement is determined to be a validation rejection type, and is used as the target error type; if it is not included, it is determined whether the current error message hits the infrastructure error message rule. If a match is found, the target error type of the initial SQL statement is determined to be an infrastructure type, and is used as the target error type; if a match is not found, it is determined whether the current error message contains a vendor error code. If it exists, the target error type of the initial SQL statement is determined to be a vendor error code type; if it does not exist, the current error message is determined to match the preset error type according to the preset error regular expression table. If a match is found, the target error type of the initial SQL statement is determined; if no match is found, the target error type of the initial SQL statement is determined to be an unknown type.

3. The SQL error repair method according to claim 1, characterized in that, The step of extracting target schema information from the initial SQL statement includes: Use a preset regular expression to extract the target table name from the clause of the initial SQL statement; If the extraction is successful, the corresponding table is loaded according to the target table name, and the loaded table is formatted as concatenated text to serve as the target schema information; If extraction fails, the target schema information will be returned as empty.

4. The SQL error repair method according to claim 1, characterized in that, The step involves using the target repair strategy to generate a corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message using the target large language model, including: Determine the corresponding preset prompt word template based on the target error type; The initial SQL statement, the current error message, the target schema information, and the target error type are filled into the preset prompt word template to obtain the target prompt word; An initial large language model is determined, and the parameters of the initial large language model are adjusted based on preset requirements to obtain the target large language model; The target prompt word is input into the target large language model to obtain the model output result; Extract the corresponding repaired SQL statements from the output of the model.

5. The SQL error repair method according to claim 1, characterized in that, The feasibility verification and re-execution operation of the repaired SQL statement includes: Based on the target error type, the error signature is extracted from the current error message, and the signature verification operation is performed on the repaired SQL statement according to the error signature to obtain the first verified SQL statement; The first verified SQL statement is subjected to structure and security checks to obtain the second verified SQL statement; the structure and security checks include any one or more of read-only checks, security checks, and structure consistency checks. The SQL statement after the second verification is re-executed to obtain the corresponding re-execution result.

6. The SQL error repair method according to claim 5, characterized in that, The step of extracting the error signature from the current error message based on the target error type, and performing a signature verification operation on the repaired SQL statement according to the error signature to obtain a first verified SQL statement includes: If the target error type is a column non-existent error type, then extract the target error column name from the current error message, verify whether the repaired SQL statement has replaced the target error column name based on the target error column name, and obtain the first verified SQL statement according to the corresponding verification result; If the target error type is a table non-existent error type, then extract the target error table name from the current error message, verify whether the repaired SQL statement has replaced the target error table name based on the target error table name, and obtain the first verified SQL statement according to the corresponding verification result; If the target error type is a column name ambiguity error type, then check whether the fixed column name appears in the SQL statement and whether it references an undefined column name, and obtain the first checked SQL statement based on the corresponding check results; If the target error type is a column name or expression type error, then extract the column name or expression involved in the type error from the current error message. Based on the column name or expression involved in the type error, verify whether the correct type conversion function has been used in the repaired SQL statement, or whether the type mismatch problem in the expression has been corrected. Obtain the first verified SQL statement according to the corresponding verification result. If the target error type is a GROUP BY error type, then extract the target ungrouped column name from the current error message, verify whether the GROUP BY clause of the repaired SQL statement already contains the target ungrouped column name, and obtain the first verified SQL statement based on the corresponding verification result.

7. The SQL error repair method according to claim 6, characterized in that, The step of evaluating the repair confidence of the repaired SQL statement to generate the target confidence level corresponding to the repaired SQL statement includes: Determine the current number of times the repaired SQL statement has been repaired; Obtain the corresponding round weight coefficient based on the current number of repairs; The signature coverage of the repaired SQL statement is determined based on the verification result of the signature verification operation. The signature coverage rate is multiplied by the round weight coefficient to generate the target confidence level corresponding to the repaired SQL statement.

8. An SQL error repair device, characterized in that, include: The type acquisition module is used to determine the initial SQL statement that malfunctions when executed by a preset SQL executor and the corresponding current error message. Based on a preset mapping table, the current error message of the initial SQL statement is hierarchically classified to obtain the target error type of the initial SQL statement. The information extraction module is used to select the corresponding target repair strategy according to the target error type and extract target schema information from the initial SQL statement; The statement repair module is used to generate a corresponding repaired SQL statement based on the initial SQL statement, the target schema information, and the current error message using the target large language model, according to the target repair strategy. The confidence generation module is used to perform feasibility verification and re-execution operations on the repaired SQL statement, and to evaluate the repair confidence of the repaired SQL statement in order to generate the target confidence of the repaired SQL statement. The first repair determination module is used to return the repaired SQL statement and target confidence level to the user terminal if the re-execution is successful. The second repair determination module is used to return a new current error message to the user terminal if the re-execution fails, so that the user terminal can re-execute the step of classifying the current error message of the initial SQL statement according to the preset mapping table to obtain the target error type of the initial SQL statement.

9. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing the computer program to implement the SQL error repair method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, Used to store computer programs; wherein, when the computer programs are executed by a processor, they implement the SQL error repair method as described in any one of claims 1 to 7.

Images