SQL (Structured Query Language) statement evaluation method and device, computer equipment and readable storage medium

Abstract

The invention relates to an SQL statement evaluation method and device, computer equipment, a computer readable storage medium and a computer program product. The method comprises the steps of obtaining a to-be-verified structured query language (SQL) statement based on a preset grabbing tool; splicing the SQL statement and the execution parameter corresponding to the SQL statement to obtain a spliced target SQL statement; copying the target SQL statement into a database of the production environment, and testing the target SQL statement based on the test case in the database of the production environment to obtain an execution result; and evaluating the target SQL statement based on the execution result to obtain an evaluation result. By adopting the method, the accuracy and efficiency of SQL statement review can be improved.

Description

Technical Field

[0001] This application relates to the field of application service technology, and in particular to an SQL statement evaluation method, apparatus, computer equipment, computer-readable storage medium, and computer program product. Background Technology

[0002] During the daily deployment and launch of application services, a large number of SQL statements are often embedded in the program code. These statements directly carry out core business operations such as data query, addition, modification, and deletion, and are a key component to ensure the interaction between the application system and the database and to realize business functions.

[0003] In traditional technologies, after the development team completes all code development based on the application system's business requirements, they will initiate code review and verification during the production phase. This review focuses on core dimensions such as code logic coherence and syntax compliance, and also includes a preliminary check for obvious issues with embedded SQL statements.

[0004] However, because SQL statements are nested in program code, they cannot be fully validated and reviewed, resulting in poor validation accuracy. Summary of the Invention

[0005] Therefore, it is necessary to provide an SQL statement evaluation method, apparatus, computer device, computer-readable storage medium, and computer program product to address the aforementioned technical problems.

[0006] Firstly, this application provides a method for evaluating SQL statements, the method comprising:

[0007] The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool;

[0008] The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement.

[0009] The target SQL statement is copied to the database in the production environment, and the target SQL statement is tested in the database in the production environment based on test cases to obtain the execution results;

[0010] The target SQL statement is evaluated based on the execution result to obtain the evaluation result.

[0011] In one embodiment, the step of obtaining Structured Query Language (SQL) statements based on a preset crawling tool includes:

[0012] The SQL execution information of each microservice is obtained from the target SQL data source through the SQL acquisition platform, and the SQL execution information is stored in the first in-memory database.

[0013] Based on the built-in SQL collection function of the preset database connection pool, SQL statements in the first database are captured in real time.

[0014] In one embodiment, concatenating the SQL statement with its corresponding execution parameters to obtain the concatenated target SQL statement includes:

[0015] Obtain the associated data table structure and field types of the target SQL data source corresponding to the SQL statement;

[0016] Based on the data table structure and the field types, the SQL statement and its corresponding execution parameters are concatenated to obtain the target SQL statement.

[0017] In one embodiment, the step of testing the target SQL statement in the database of the production environment based on test cases to obtain execution results includes:

[0018] In the production environment database, load test cases designed for the target SQL statement and determine the input parameters and expected verification dimensions in the test cases;

[0019] The target SQL statement is matched with the input parameters in the test case and executed, and the execution verification is performed based on the expected verification dimension to obtain the test execution result.

[0020] In one embodiment, the evaluation of the target SQL statement based on the execution result to obtain the evaluation result includes:

[0021] The execution result corresponding to the target SQL statement is input into a pre-configured large model. The large model analyzes and evaluates the execution result according to the evaluation rules, and outputs the evaluation score corresponding to the execution result of the target SQL statement to obtain the evaluation result.

[0022] In one embodiment, the method further includes:

[0023] Retrieve the characteristic information of the newly added SQL statement;

[0024] The feature information is concatenated to obtain the feature concatenation result, and a hash processing is performed on the feature concatenation result to obtain the identifier of the newly added SQL statement;

[0025] The identifier of the newly added SQL statement is compared with the identifiers of each SQL statement already stored in the first database to obtain a consistency comparison result.

[0026] Based on the consistency comparison results, determine whether to add the new SQL statement to the first database.

[0027] In one embodiment, the step of concatenating the feature information to obtain a feature concatenation result, and then performing hash processing based on the feature concatenation result to obtain the identifier of the newly added SQL statement, includes:

[0028] The feature information of the newly added SQL statement is concatenated in a preset order to obtain the feature concatenation result;

[0029] Based on preset segmentation rules, a string of preset length is extracted from the feature concatenation result, and a hash calculation is performed on the extracted string to obtain the identifier of the newly added SQL statement.

[0030] Secondly, this application also provides an SQL statement evaluation apparatus, the apparatus comprising:

[0031] The acquisition module is used to acquire the structured query language (SQL) statement to be verified based on a preset crawling tool;

[0032] The concatenation module is used to concatenate the SQL statement with the execution parameters corresponding to the SQL statement to obtain the concatenated target SQL statement;

[0033] The testing module is used to copy the target SQL statement to the database in the production environment, and to test the target SQL statement in the database in the production environment based on test cases to obtain the execution results;

[0034] The evaluation module is used to evaluate the target SQL statement based on the execution result and obtain the evaluation result.

[0035] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0036] The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool;

[0037] The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement.

[0038] The target SQL statement is copied to the database in the production environment, and the target SQL statement is tested in the database in the production environment based on test cases to obtain the execution results;

[0039] The target SQL statement is evaluated based on the execution result to obtain the evaluation result.

[0040] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:

[0041] The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool;

[0042] The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement.

[0043] The target SQL statement is copied to the database in the production environment, and the target SQL statement is tested in the database in the production environment based on test cases to obtain the execution results;

[0044] The target SQL statement is evaluated based on the execution result to obtain the evaluation result.

[0045] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:

[0046] The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool;

[0047] The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement.

[0048] The target SQL statement is copied to the database in the production environment, and the target SQL statement is tested in the database in the production environment based on test cases to obtain the execution results;

[0049] The target SQL statement is evaluated based on the execution result to obtain the evaluation result.

[0050] The aforementioned SQL statement evaluation method, apparatus, computer equipment, computer-readable storage medium, and computer program product acquire a Structured Query Language (SQL) statement to be verified using a preset crawling tool; concatenate the SQL statement with its corresponding execution parameters to obtain a concatenated target SQL statement; copy the target SQL statement to a database in a production environment, and test the target SQL statement in the production environment database based on test cases to obtain execution results; evaluate the target SQL statement based on the execution results to obtain an evaluation result. This method, through pre-detection rules, acquires the SQL statement using a crawling tool before generating program code, concatenates the SQL statement, and tests the concatenated SQL statement based on test cases to obtain test execution results. Then, it evaluates the target SQL statement based on the execution results to obtain the final evaluation result. This avoids the problems of incomplete coverage and inaccurate performance prediction of embedded SQL in traditional manual review after program code generation, significantly improving the accuracy and efficiency of SQL statement review. Attached Figure Description

[0051] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0052] Figure 1 This is a diagram illustrating the application environment of the SQL statement evaluation method in one embodiment;

[0053] Figure 2 This is a flowchart illustrating the SQL statement evaluation method in one embodiment;

[0054] Figure 3 This is a flowchart illustrating the steps involved in managing the entire lifecycle of SQL statements in one embodiment.

[0055] Figure 4 This is a flowchart illustrating the steps for obtaining the SQL statement to be verified in one embodiment;

[0056] Figure 5 This is a flowchart illustrating the steps involved in generating the concatenated target SQL statement in one embodiment.

[0057] Figure 6 This is a flowchart illustrating the steps for testing an SQL statement and obtaining the execution result in one embodiment.

[0058] Figure 7This is a flowchart illustrating the steps for obtaining the evaluation results in one embodiment;

[0059] Figure 8 This is a flowchart illustrating the SQL duplicate verification steps in one embodiment;

[0060] Figure 9 This is a flowchart illustrating the steps for generating SQL identifiers in one embodiment;

[0061] Figure 10 This is a schematic diagram illustrating a specific example of the SQL statement evaluation method in one embodiment;

[0062] Figure 11 This is a structural block diagram of an SQL statement evaluation device in one embodiment;

[0063] Figure 12 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0064] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0065] The SQL statement evaluation method provided in this application can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. Server 104 establishes data interaction links with the production environment database 106, the local database (e.g., the first data) 108, and the unified SQL acquisition platform 110. The data storage system can store data such as SQL statements, execution parameters, data source table structures, test cases, and evaluation rules required by server 104 during processing. This data storage system can be integrated on server 104 or deployed in the cloud or other network servers.

[0066] Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, IoT devices, and portable wearable devices. IoT devices can be smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can be smartwatches, smart bracelets, head-mounted devices, etc. Head-mounted devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. Developers can use terminal 102 to submit application service code to be reviewed, view SQL crawling results, configure test cases, and view evaluation reports.

[0067] Server 104 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services. It can deploy preset crawling tools (such as an SQL collection module based on the Druid database connection pool), SQL splicing engine, test execution module and evaluation module to crawl, assemble, test and evaluate SQL statements, and coordinate with the unified SQL acquisition platform 110 to collect SQL execution information from various microservices.

[0068] The production environment database 106 is used to carry the actual business data of the application services, providing a real running environment for the target SQL statements and supporting test case execution and execution plan generation; the local database 108 is used to temporarily store the SQL execution information captured in real time to ensure that the data is not lost; the unified SQL acquisition platform 110 is used to aggregate the SQL execution data of different microservices and provide data support for full SQL review.

[0069] In one exemplary embodiment, such as Figure 2 As shown, a method for evaluating SQL statements is provided, which can be applied to... Figure 1 Taking the terminal in the example, the explanation includes the following steps 202 to 206. Wherein:

[0070] Step 202: Obtain the structured query language (SQL) statement to be verified based on the preset crawling tool.

[0071] In implementation, in the high availability domain of application services, the daily deployment of application services involves writing a large number of embedded SQL statements in the program code. Since it is impossible to fully verify the embedded SQL statements in the program code, this application adopts the principle of pre-verification. The terminal connects to a unified SQL (Structured Query Language) acquisition platform. Through the interface of this SQL acquisition platform, SQL execution information generated by the target data source of each microservice is pulled to ensure that all embedded SQL statements are covered. The pulled SQL execution information is stored in a local memory storage database. Subsequently, the terminal starts a preset crawling tool (such as a tool that integrates Druid collection capabilities) to filter out the SQL statement text to be verified from the storage database storing SQL execution information for subsequent SQL statement verification.

[0072] Step 204: Concatenate the SQL statement with the corresponding execution parameters to obtain the concatenated target SQL statement.

[0073] In implementation, the terminal first accesses the data source corresponding to the SQL statement to be concatenated through the database connection protocol, executes the table structure query command (such as SHOW COLUMNS FROM table_name), and obtains the field names, data types (such as int, varchar, datetime) and constraint rules (such as NOT NULL, primary key) of the related data table; then, it extracts the corresponding execution parameters from the SQL execution information, adapts the parameter format according to the field type (such as adding single quotes to string type parameters, matching the database date format to date type parameters), and finally embeds the format-adapted execution parameters into the placeholder positions of the SQL statement to generate the target SQL statement that can be executed directly.

[0074] Step 206: Copy the target SQL statement to the production environment database, and test the target SQL statement in the production environment database based on test cases to obtain the execution results.

[0075] In implementation, the terminal synchronizes the target SQL statement to the production environment database via secure transmission protocols (such as SSH and SSL). Subsequently, the terminal loads a pre-set test case set, which includes test cases covering normal business data (such as query conditions that conform to field rules), boundary data (such as maximum and minimum field values), and abnormal data (such as illegal characters and parameters exceeding the field length). The terminal then executes the target SQL statement in the order of the test cases. During the execution of the test cases, the terminal records the SQL's running status (success / failure), returned data (query result set, number of updated rows), and performance data (execution time, CPU utilization, and IO read volume) in real time, integrating them to form a complete execution result document.

[0076] Step 208: Evaluate the target SQL statement based on the execution result to obtain the evaluation result.

[0077] In implementation, the terminal first analyzes the running status and returned data in the execution results to determine whether the target SQL statement meets the business function requirements. For example, does the query statement return the expected data, and does the delete statement accurately apply to the specified record? Then, it compares the SQL's execution efficiency with preset performance thresholds (such as execution time ≤ 500ms, avoiding full table scans). Finally, it combines functional and performance dimensions to generate a comprehensive score for the SQL statement, thus obtaining the evaluation result.

[0078] The above SQL statement evaluation method involves: acquiring the Structured Query Language (SQL) statement to be verified using a pre-defined crawling tool; concatenating the SQL statement with its corresponding execution parameters to obtain the concatenated target SQL statement; copying the target SQL statement to the production environment database and testing it in the production environment database based on test cases to obtain execution results; and evaluating the target SQL statement based on the execution results to obtain the evaluation result. This method, through pre-detection rules, acquires the SQL statement using a crawling tool before generating program code, concatenates the SQL statement, and tests the assembled SQL statement based on test cases to obtain test execution results. Then, the target SQL statement is evaluated based on the execution results to obtain the final evaluation result. This avoids the problems of incomplete coverage and inaccurate performance prediction in embedded SQL code reviews after traditional code generation, significantly improving the accuracy and efficiency of SQL statement review.

[0079] This application provides a method for evaluating SQL statements, such as... Figure 3 As shown, this SQL statement evaluation method encompasses the entire lifecycle management of SQL statements and can be applied to the complete process from development to operation and maintenance: During development, developers consciously adhere to individual code development standards and database development standards; in the testing phase (pre-testing), the SQL statement evaluation method of this application is used to identify which new SQL statements have been added, their performance, and whether any known defects exist; in the production phase (during production), monitoring mechanisms are used to quickly identify and resolve SQL issues; and in the operation and maintenance phase (post-testing), data analysis (such as AWR analysis) is performed regularly. This end-to-end management ensures that problematic SQL statements are identified earlier, reducing modification costs and minimizing the impact on production stability, thereby achieving full-cycle quality control and risk prevention for SQL statements from development to operation and maintenance.

[0080] In one exemplary embodiment, such as Figure 4 As shown, step 202 includes steps 402 to 406. Wherein:

[0081] Step 402: Obtain the SQL execution information of each microservice from the target SQL data source through the SQL acquisition platform, and store the SQL execution information in the first in-memory database.

[0082] In implementation, the terminal first configures the access permissions and interface parameters of the SQL acquisition platform. Based on these permissions and parameters, a scheduled task (e.g., triggered every 5 minutes) calls the SQL acquisition platform interface to batch retrieve SQL execution information generated by target data sources from various microservices. This SQL execution information includes one or more of the following: SQL statement text, execution timestamp, microservice ID, data source name, execution parameter types, and parameter value ranges. Subsequently, the terminal writes this SQL execution information into a first database in its memory in real time, while setting a data expiration policy (e.g., automatically cleaning up expired data every 1 minute) to prevent memory overflow and ensure that the first database always stores the latest SQL execution information, providing real-time data support for subsequent data capture steps.

[0083] Step 404: Based on the SQL collection function built into the preset database connection pool, capture the SQL statements in the first database in real time.

[0084] In implementation, the terminal pre-deploys a Druid database connection pool locally and enables its built-in SQL collection module. This SQL collection module has SQL collection capabilities, allowing continuous monitoring of the data stream of the first in-memory database. When new SQL execution information is detected being written, the terminal triggers the collection logic: first, the SQL statement text is preprocessed (removing extra spaces and standardizing keyword case, such as converting "select" to "SELECT"), then dynamic parameters within single quotes are removed (replaced with "?"), and a standardized SQL statement template is extracted. Subsequently, the terminal associates and stores the microservice name, data source name, and other identifying information corresponding to the statement, ensuring that the source of each captured SQL statement can be accurately traced, avoiding omissions in subsequent reviews due to missing information, thus obtaining the captured SQL statement.

[0085] In this embodiment, the terminal obtains the data sources of various microservices through the SQL platform, enabling batch collection of full SQL execution information; the SQL execution information is stored in the first in-memory database, ensuring the efficiency of data reading and writing and subsequent calls based on the characteristics of in-memory storage; at the same time, the structured storage method provides a clear index and complete association dimensions for subsequent SQL fetching and parameter concatenation.

[0086] In one embodiment, such as Figure 5 As shown, the specific processing steps of step 204 include:

[0087] Step 501: Obtain the associated data table structure and field types of the target SQL data source corresponding to the SQL statement.

[0088] In implementation, the terminal, based on the "data source name" recorded when retrieving SQL statements, selects an appropriate database connection method according to the type of the target data source, using standardized database connection protocols such as JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity), and establishes a secure and stable connection with the target SQL data source based on this appropriate connection method, ensuring the reliability of data interaction. Subsequently, the terminal automatically executes table structure query commands. After receiving the query results returned by the database, the terminal uses a query result set parsing tool to extract and structure the core information of the target data table, including the name, data type, nullability, default value, and field constraints of each field, ultimately forming a structured document with the data table name as the index and the field information as the content.

[0089] Step 502: Based on the data table structure and field types, concatenate the SQL statement with the corresponding execution parameters to obtain the target SQL statement.

[0090] In implementation, the terminal, based on the extracted data table structure and field types, concatenates the SQL statement with its corresponding execution parameters to obtain the target SQL statement. Specifically, the execution parameters for the SQL statement include the parameter values, the names of the fields associated with the parameters, and the order in which the parameters are passed. The terminal then matches the type of each parameter's associated field against a pre-generated table structure template: if the field type is a numeric type such as "int" or "decimal," the original numeric format of the parameter is directly retained; if it is a string type such as "varchar" or "char," single quotes are added around the parameter. Finally, the terminal embeds the formatted parameters one by one into their corresponding positions according to the order of the placeholders in the SQL statement, completing the concatenation. For example, concatenating "INSERT INTO order (user_id,order_amount, create_time) VALUES(?,?,?)" with the corresponding execution parameters (1001, 999.99, 2024-06-01) results in "INSERT INTO order (user_id, order_amount,create_time)VALUES (1001, 999.99, '2024-06-01 00:00:00')", ultimately generating the target SQL statement that can be directly executed in the database.

[0091] In this embodiment, the terminal obtains the complete data table structure and field types, and then performs format calibration on the execution parameters according to the field types. This ensures that the concatenated target SQL statement fully conforms to the database syntax specifications and field requirements, allowing it to be executed directly in the database without relying on embedding program code for format adjustment and accuracy verification. This operation completely changes the traditional model where SQL must be embedded in program code before it can be tested in a production environment. It enables SQL statements to be tested independently in a production environment before being embedded in program code, fundamentally ensuring the accuracy of SQL statement testing, intercepting SQL defects that do not meet production environment requirements in advance, and laying the foundation for the stability of subsequent program code development and version releases.

[0092] In one embodiment, such as Figure 6 As shown, the specific processing steps of step 206 include:

[0093] Step 601: In the production environment database, load the test cases designed for the target SQL statement and determine the input parameters and expected verification dimensions in the test cases.

[0094] During implementation, the terminal establishes an encrypted connection with the production environment database through a preset secure transmission protocol, ensuring the security of test data transmission and the stability of production environment data. Subsequently, the terminal loads a customized test case set for the current target SQL statement from either the local test case library or the cloud-based test management platform. This test case set is designed based on the business functions of the SQL statement (such as query, insert, update, and delete) and potential risk points, covering multiple scenarios: including normal business scenario test cases, boundary scenario test cases, abnormal scenario test cases, and concurrent scenario test cases. The terminal automatically parses the structure of each test case, extracts the corresponding input parameters, and clarifies the expected verification dimensions for each test case: functional dimension (e.g., query test cases are expected to return a specified number of data rows, insert test cases are expected to return "affected rows 1"), performance dimension (e.g., single execution time ≤ 500ms, CPU utilization ≤ 60% during concurrent execution), and syntax dimension (no SQL syntax error prompts), providing clear judgment criteria for subsequent execution verification.

[0095] Step 602: Match the target SQL statement with the input parameters in the test case and execute it, and perform execution verification based on the expected verification dimensions to obtain the test execution results.

[0096] In implementation, the terminal sequentially matches the target SQL statement with the input parameters of each test case according to the determined test case order. For different types of target SQL statements, corresponding matching operations are performed: for example, if the target SQL statement is a fixed template, placeholders are embedded in the test case input parameters; if it is a dynamically generated statement, the parameter passing method is adjusted to ensure matching and execution. Subsequently, the terminal runs the matched test cases, performing execution verification on the matched target SQL statement based on the expected verification dimensions. During execution, the terminal monitors and records feedback data from the production environment database in real time: for example, in the functional verification dimension, it records the SQL's running status (successful execution / execution error), returned data (query result set, number of affected rows, error code and description), and determines whether it matches the expected functional results; in the performance verification dimension, it uses database performance monitoring tools to collect performance indicators such as SQL execution time, logical / physical read counts, CPU utilization, and whether a full table scan is triggered, and compares the collected performance indicators with the expected performance thresholds to determine whether they are the same as the expected performance results. Thus, after all test cases have been executed, the execution result of each test case is obtained. Furthermore, after each test case is executed and verified, the execution results are integrated into a structured execution result document, clearly indicating the specific issues that caused the test cases to fail.

[0097] In this embodiment, by loading customized test cases and combining multi-dimensional verification test logic, the terminal can fully simulate the real running scenario of the target SQL statement in the production environment. This not only accurately judges the functional correctness of the SQL, but also discovers potential performance risks and syntax risks in advance, providing dual protection for the safe deployment of SQL statements.

[0098] In one embodiment, such as Figure 7 As shown, the specific processing steps of step 208 include:

[0099] Step 701: Input the execution result corresponding to the target SQL statement into the pre-configured large model. The large model analyzes and evaluates the execution result according to the evaluation rules, and outputs the evaluation score corresponding to the execution result of the target SQL statement to obtain the evaluation result.

[0100] In implementation, a pre-configured large model is introduced into the testing process for the target SQL statement to automatically evaluate the execution result of the target SQL statement and determine the initial evaluation result. This pre-configured large model has been trained with a massive dataset of SQL evaluation samples and evaluation rules. For example, this large model is an SQL evaluation model that has been trained based on model fine-tuning or customization, and has the ability to efficiently parse SQL execution data and accurately match evaluation rules.

[0101] Specifically, the terminal first performs structured preprocessing on the execution results of the target SQL statement. For example, the terminal organizes key information such as "running status (success / failure)," "returned data volume," "execution time," "CPU utilization," "whether a full table scan was triggered," and "error code (if any)" from the execution results into input data that the large model can recognize, according to a preset format (such as JSON), ensuring that the data logic is clear and without redundancy. Subsequently, the terminal calls the pre-configured large model, not only inputting the preprocessed execution result data into the large model, but also simultaneously passing in pre-designed prompts and predefined evaluation rules. The prompts adopt a combination of instructions and structured data, clearly informing the large model that "you need to act as an automated SQL evaluation expert and, based on the provided evaluation rules, perform multi-dimensional scoring and evaluation of the input SQL execution results." At the same time, it limits the output format requirements to "return a comprehensive score out of 100, as well as detailed scores including functional, performance, and security dimensions. Each deduction item must explain the specific reason, and at the same time, provide actionable optimization suggestions for the problem. All content is output in JSON format without additional explanation." This prompt design effectively guides large models to focus on evaluating core tasks, improving the standardization and usability of the output results.

[0102] In this way, after receiving input, the large model first parses the core indicators in the execution results, then matches and scores them item by item against the evaluation rules, and finally generates a comprehensive evaluation score for the target SQL statement through weighted calculation. At the same time, it outputs the structured evaluation results according to the prompts. Subsequently, the terminal stores the evaluation results in the evaluation report database and pushes them to the developer's terminal interface for review, providing clear guidance for SQL optimization.

[0103] In this embodiment, the terminal inputs the structured execution results into a pre-configured large model. By leveraging the large model's learning ability and precise rule matching capabilities on massive evaluation samples, it can quickly complete multi-dimensional analysis and evaluation of the execution results, directly outputting evaluation results that include a comprehensive score, detailed scores, and optimization suggestions. This not only improves the efficiency and accuracy of SQL evaluation but also provides developers with clear optimization directions.

[0104] In one embodiment, such as Figure 8 As shown, the method also includes:

[0105] Step 801: Obtain the feature information of the newly added SQL statement.

[0106] In implementation, when a new or modified SQL statement appears in the program code, it must first undergo "duplication verification" to avoid wasting evaluation resources. The core of this verification is to determine whether the SQL statement is truly a new or modified statement: the terminal first queries the SQL statements already stored in the first database, comparing the identifier of the SQL statement to be verified (i.e., the hash value generated based on feature information) with the identifiers of existing SQL statements in the database. If a matching identifier exists, it means the SQL statement is already stored in the first database, and subsequent verification and evaluation are unnecessary; if no matching identifier is found, the SQL statement can be determined to be a new or modified statement, requiring further verification and evaluation according to the complete process to ensure that resources are only allocated to SQL statements that truly need evaluation.

[0107] Specifically, the terminal first obtains the new SQL statement to be processed, and then extracts its feature information according to preset dimensions. These extraction dimensions include: the name of the microservice to which the new SQL statement belongs, the corresponding data source identifier, the preprocessed text content of the SQL statement, and the SQL statement type. The terminal ensures the accuracy of each feature by parsing the SQL statement's metadata, calling microservice interfaces to obtain service information, and matching the data source configuration table to determine the identifier. This provides complete and reliable foundational data for subsequent feature concatenation and identifier generation.

[0108] Step 802: Concatenate the feature information to obtain the feature concatenation result, and perform hash processing based on the feature concatenation result to obtain the identifier of the newly added SQL statement.

[0109] In implementation, the terminal concatenates the extracted SQL statement feature information in a preset fixed order to obtain the feature concatenation result. For example, if the microservice name is "order_service", the data source identifier is "order_db", the SQL statement type is "SELECT", and the preprocessed SQL text is "SELECT order_noFROMt_orderWHERE create_time>'2024-06-01'", then concatenating this feature information will result in "order_service|order_db|SELECT|SELECT order_noFROMt_orderWHERE create_time>'2024-06-01'" (resulting in a long string). Subsequently, the terminal calls a preset hash algorithm to perform hash calculation on the feature concatenation result, generating a fixed-length hash value. This hash value is used as a unique identifier for newly added SQL statements to quickly distinguish different SQL statements and avoid duplicate storage.

[0110] Step 803: Compare the identifier of the newly added SQL statement with the identifiers of each SQL statement already stored in the first database to obtain a consistency comparison result.

[0111] In implementation, after obtaining the unique identifier of the newly added SQL statement, the terminal reads the identifiers of all stored SQL statements (i.e., the hash values ​​generated when each SQL statement is written into the database) from the first in-memory database, establishing a temporary identifier comparison database. Subsequently, the terminal compares the generated identifier of the new SQL statement with each existing identifier in the temporary identifier comparison database one by one. This comparison process uses a "full character matching" method. If there is an existing identifier that is exactly the same as the new identifier, it is determined to be "consistent"; if all existing identifiers are different from the new identifier, it is determined to be "inconsistent". The terminal records the comparison process and results in real time, generates a consistency comparison report, and clearly marks the "consistent / inconsistent" conclusion.

[0112] Optionally, for the consistency comparison results of SQL statements, if the comparison result is "consistent", the terminal can further associate the storage location, entry time and other information of the matched SQL statements to provide a basis for subsequent judgment on whether to enter them into the database.

[0113] Step 804: Based on the consistency comparison results, determine whether to add the new SQL statement to the first database.

[0114] During implementation, the terminal performs differentiated operations based on the consistency comparison results of the obtained SQL statements: if the comparison result is "inconsistent", it means that the newly added SQL statement is not stored in the first database. The terminal then stores the newly added SQL statement and its associated feature information (microservice name, data source identifier, etc.), and the generated unique identifier in the first database according to a preset format, and updates the identifier index of the database to ensure that subsequent comparisons can be quickly retrieved; if the comparison result is "consistent", it means that the newly added SQL statement already exists in the first database. The terminal then triggers the "duplicate filtering" logic, does not store duplicates, and generates a "duplicate prompt log" to record the source of the newly added SQL statement, the comparison time, and the information of the matched existing statements, so as to avoid excessive memory consumption or duplicate processing in subsequent capture and testing processes due to redundant data storage in the database.

[0115] In this embodiment, a pre-set duplicate verification mechanism is used to accurately identify whether newly added or modified SQL statements are repeated in the first database. This not only avoids duplicate SQL statements being entered into the database and subsequent repeated evaluations through a unique hash identifier, reducing the waste of evaluation resources, but also ensures that only truly newly added or modified SQL statements are further verified. This not only ensures the comprehensiveness of SQL evaluation, but also significantly improves the efficiency of the evaluation process and the cleanliness of the data in the first database.

[0116] In an optional embodiment, after the terminal completes the repetitive verification and production environment testing of the newly added SQL statement, it can also automatically trigger the review collaboration process: First, it extracts the execution plan (including core information such as execution time, index usage, and data scanning method) corresponding to the newly added SQL statement from the evaluation report library, and synchronizes the execution plan and evaluation results to the version automated review system through a preset interface; The automated review system automatically verifies the execution plan of the newly added SQL statement according to the pre-configured review rules and generates review results; Subsequently, the automated review system sends the review results back to the version automated exit platform through the interface, deeply integrating with the verification capabilities of the automated exit platform: For example, if the review result is "passed", the version associated with the newly added SQL statement is allowed to enter the subsequent exit process; if it is "to be optimized" or "rejected", a verification interception is triggered, and optimization suggestions are pushed to the developer's terminal. After the SQL statement is optimized, the review is re-initiated. The entire process ensures the availability and correctness of the newly added SQL statement through automated collaboration between devices, while improving the efficiency of version exit.

[0117] In one embodiment, such as Figure 9 As shown, the specific processing results of step 802 include:

[0118] Step 901: Concatenate the feature information of the newly added SQL statement in a preset order to obtain the feature concatenation result;

[0119] In implementation, the terminal extracts feature information of preset dimensions from the acquired new SQL statement association data. This feature information must comprehensively and uniquely identify the SQL statement, specifically including: the unique ID of the microservice to which the new SQL statement belongs, the corresponding data source name, the operation type of the SQL statement, and the preprocessed SQL statement text. Subsequently, the terminal concatenates these feature information sequentially in a preset fixed order using preset delimiters, ultimately forming a well-structured and unambiguous feature concatenation result, such as "ms_order_001#order_prod_db#SELECT#SELECTorder_noFROMt_orderWHEREid=1", providing standardized input data for subsequent identifier generation.

[0120] Step 902: Based on the preset segmentation rules, extract a string of preset length from the feature concatenation result, perform hash calculation on the extracted string, and obtain the identifier of the newly added SQL statement.

[0121] In implementation, after obtaining the feature concatenation result of the SQL feature information, the terminal first loads a preset segmentation rule. This rule specifies the starting position of string truncation (e.g., starting from the first character of the feature concatenation result) and the preset length (e.g., truncating the first 50 characters). The terminal precisely truncates the feature concatenation result according to this segmentation rule. If the length of the feature concatenation result is less than the preset length, the complete concatenation result is directly retained; if it exceeds the preset length, it is truncated to the preset length of characters. Subsequently, the terminal calls a preset high-efficiency hash algorithm (e.g., SHA-1 or MD5 algorithm, which has the characteristics of fast calculation speed and low collision probability) to perform a hash operation on the truncated string, generating a fixed-length hash value (e.g., SHA-1 algorithm generates a 40-digit hexadecimal string). This hash value is the unique identifier of the newly added SQL statement, which can be used to quickly compare the identifiers with the identifiers of SQL statements already stored in the first database, ensuring the uniqueness of the identifier and the efficiency of the comparison.

[0122] In this embodiment, an SQL statement identifier is generated by combining sequential feature concatenation, fixed-length truncation, and hash calculation. This ensures the uniqueness of the newly added SQL statement identifier, optimizes the efficiency of hash calculation through string truncation, avoids excessive system resource consumption due to calculation of excessively long texts, and the fixed-length hash identifier also facilitates subsequent database storage and rapid comparison, further improving the overall efficiency of the SQL statement duplication verification process.

[0123] In a specific exemplary embodiment, such as Figure 10 As shown, the specific example process of this SQL statement evaluation method includes:

[0124] Step 1001: In the delivery environment (green), data collection is performed through automated interfaces and manual test case execution to trigger the execution of SQL in the code program;

[0125] Step 1002: Collect SQL information using Druid in both the verification and delivery environments, and generate an SQL identifier using a hash method (microservice + data source + SQL); compare this identifier with existing SQL identifiers in the first database.

[0126] Step 1003: Obtain and display the newly added SQL; Developers manually fill in the execution plan information and execution cost information of the SQL on the SQL review page, triggering the review of the performance information of the newly added SQL and obtaining the evaluation result of the SQL statement;

[0127] Step 1004: The program code version delivery integration system automatically connects to the SQL review process. That is, when the interface automated testing equipment executes the program code test, it simultaneously detects the SQL review status associated with the version to be released. The version release access control system, based on the detection results of the program code, automatically intercepts the version release process if there are any SQL statements that have not been reviewed. Only when all newly added SQL statements have passed the review is the current version of the program code allowed to enter the release stage.

[0128] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0129] Based on the same inventive concept, this application also provides an SQL statement evaluation apparatus for implementing the SQL statement evaluation method described above. The solution provided by this apparatus is similar to the implementation described in the above method; therefore, the specific limitations in one or more SQL statement evaluation apparatus embodiments provided below can be found in the limitations of the SQL statement evaluation method described above, and will not be repeated here.

[0130] In one exemplary embodiment, such as Figure 11 As shown, an SQL statement evaluation device 1100 is provided, including: an acquisition module 1101, a splicing module 1102, a testing module 1103, and an evaluation module 1104, wherein:

[0131] The acquisition module 1101 is used to acquire the structured query language (SQL) statement to be verified based on a preset crawling tool;

[0132] The concatenation module 1102 is used to concatenate the SQL statement with the execution parameters corresponding to the SQL statement to obtain the concatenated target SQL statement;

[0133] Test module 1103 is used to copy the target SQL statement to the database in the production environment, and to test the target SQL statement in the database in the production environment based on test cases to obtain the execution results;

[0134] Evaluation module 1104 is used to evaluate the target SQL statement based on the execution results and obtain the evaluation results.

[0135] In one embodiment, the acquisition module 1101 is specifically used to acquire the SQL execution information of each microservice from the target SQL data source through the SQL acquisition platform, and store the SQL execution information in the first in-memory database;

[0136] Based on the built-in SQL collection function of the preset database connection pool, SQL statements in the first database are captured in real time.

[0137] In one embodiment, the splicing module 1102 is specifically used to obtain the associated data table structure and field types of the target SQL data source corresponding to the SQL statement;

[0138] Based on the data table structure and field types, the SQL statement and its corresponding execution parameters are concatenated to obtain the target SQL statement.

[0139] In one embodiment, the test module 1103 is specifically used to load test cases designed for the target SQL statement in the database of the production environment, and determine the input parameters and expected verification dimensions in the test cases;

[0140] The target SQL statement is matched with the input parameters in the test case and executed. The execution is then verified based on the expected verification dimensions to obtain the test execution results.

[0141] In one embodiment, the evaluation module 1104 is specifically used to input the execution result corresponding to the target SQL statement into a pre-configured large model, analyze and evaluate the execution result according to the evaluation rules through the large model, output the evaluation score corresponding to the execution result of the target SQL statement, and obtain the evaluation result.

[0142] In one embodiment, the device 1100 further includes:

[0143] The acquisition module is used to obtain the characteristic information of newly added SQL statements;

[0144] The processing module is used to concatenate the feature information to obtain the feature concatenation result, and perform hash processing based on the feature concatenation result to obtain the identifier of the newly added SQL statement;

[0145] The comparison module is used to compare the identifier of the newly added SQL statement with the identifiers of each SQL statement already stored in the first database to obtain a consistency comparison result.

[0146] The determination module is used to determine whether to add the new SQL statement to the first database based on the consistency comparison results.

[0147] In one embodiment, the comparison module is specifically used to concatenate the feature information of the newly added SQL statement in a preset order to obtain the feature concatenation result;

[0148] Based on preset segmentation rules, a string of preset length is extracted from the feature concatenation result. The extracted string is then hashed to obtain the identifier of the newly added SQL statement.

[0149] Each module in the aforementioned SQL statement evaluation device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can invoke and execute the operations corresponding to each module.

[0150] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 12 As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. When the computer program is executed by the processor, it implements an SQL statement evaluation method. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0151] Those skilled in the art will understand that Figure 12The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0152] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0153] The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool;

[0154] The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement.

[0155] Copy the target SQL statement to the production environment database, and test the target SQL statement in the production environment database based on test cases to obtain the execution results;

[0156] The target SQL statement is evaluated based on the execution results to obtain the evaluation results.

[0157] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0158] The SQL execution information of each microservice is obtained from the target SQL data source through the SQL acquisition platform, and the SQL execution information is stored in the first in-memory database.

[0159] Based on the built-in SQL collection function of the preset database connection pool, SQL statements in the first database are captured in real time.

[0160] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0161] Obtain the associated data table structure and field types of the target SQL data source corresponding to the SQL statement;

[0162] Based on the data table structure and field types, the SQL statement and its corresponding execution parameters are concatenated to obtain the target SQL statement.

[0163] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0164] In the production environment database, load the test cases designed for the target SQL statement and determine the input parameters and expected verification dimensions in the test cases;

[0165] The target SQL statement is matched with the input parameters in the test case and executed. The execution is then verified based on the expected verification dimensions to obtain the test execution results.

[0166] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0167] The execution result of the target SQL statement is input into the pre-configured large model. The large model analyzes and evaluates the execution result according to the evaluation rules, and outputs the evaluation score corresponding to the execution result of the target SQL statement to obtain the evaluation result.

[0168] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0169] Retrieve the characteristic information of the newly added SQL statement;

[0170] The feature information is concatenated to obtain the feature concatenation result, and then hashed based on the feature concatenation result to obtain the identifier of the newly added SQL statement;

[0171] The consistency comparison result is obtained by comparing the identifier of the newly added SQL statement with the identifiers of each SQL statement already stored in the first database.

[0172] Based on the consistency comparison results, determine whether to add the new SQL statement to the first database.

[0173] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0174] The feature information of the newly added SQL statement is concatenated in a preset order to obtain the feature concatenation result;

[0175] Based on preset segmentation rules, a string of preset length is extracted from the feature concatenation result. The extracted string is then hashed to obtain the identifier of the newly added SQL statement.

[0176] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.

[0177] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0178] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0179] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0180] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0181] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed. However, they should not be construed as limiting the scope of this application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for evaluating SQL statements, characterized in that, The method includes: The structured query language (SQL) statement to be verified is obtained based on a preset crawling tool; The SQL statement is concatenated with its corresponding execution parameters to obtain the concatenated target SQL statement. The target SQL statement is copied to the database in the production environment, and the target SQL statement is tested in the database in the production environment based on test cases to obtain the execution results; The target SQL statement is evaluated based on the execution result to obtain the evaluation result.

2. The method according to claim 1, characterized in that, The process of obtaining Structured Query Language (SQL) statements based on a preset crawling tool includes: The SQL execution information of each microservice is obtained from the target SQL data source through the SQL acquisition platform, and the SQL execution information is stored in the first in-memory database. Based on the built-in SQL collection function of the preset database connection pool, SQL statements in the first database are captured in real time.

3. The method according to claim 1, characterized in that, The step of concatenating the SQL statement with its corresponding execution parameters to obtain the concatenated target SQL statement includes: Obtain the associated data table structure and field types of the target SQL data source corresponding to the SQL statement; Based on the data table structure and the field types, the SQL statement and its corresponding execution parameters are concatenated to obtain the target SQL statement.

4. The method according to claim 1, characterized in that, The process involves testing the target SQL statement in the database of the production environment based on test cases to obtain execution results, including: In the production environment database, load test cases designed for the target SQL statement and determine the input parameters and expected verification dimensions in the test cases; The target SQL statement is matched with the input parameters in the test case and executed, and the execution verification is performed based on the expected verification dimension to obtain the test execution result.

5. The method according to claim 1, characterized in that, The evaluation of the target SQL statement based on the execution result to obtain the evaluation result includes: The execution result corresponding to the target SQL statement is input into a pre-configured large model. The large model analyzes and evaluates the execution result according to the evaluation rules, and outputs the evaluation score corresponding to the execution result of the target SQL statement to obtain the evaluation result.

6. The method according to claim 2, characterized in that, The method further includes: Retrieve the characteristic information of the newly added SQL statement; The feature information is concatenated to obtain the feature concatenation result, and a hash processing is performed on the feature concatenation result to obtain the identifier of the newly added SQL statement; The identifier of the newly added SQL statement is compared with the identifiers of each SQL statement already stored in the first database to obtain a consistency comparison result. Based on the consistency comparison results, determine whether to add the new SQL statement to the first database.

7. The method according to claim 6, characterized in that, The step of concatenating the feature information to obtain the feature concatenation result, and then performing hash processing based on the feature concatenation result to obtain the identifier of the newly added SQL statement, includes: The feature information of the newly added SQL statement is concatenated in a preset order to obtain the feature concatenation result; Based on preset segmentation rules, a string of preset length is extracted from the feature concatenation result, and a hash calculation is performed on the extracted string to obtain the identifier of the newly added SQL statement.

8. An SQL statement evaluation device, characterized in that, The device includes: The acquisition module is used to acquire the structured query language (SQL) statement to be verified based on a preset crawling tool; The concatenation module is used to concatenate the SQL statement with the execution parameters corresponding to the SQL statement to obtain the concatenated target SQL statement; The testing module is used to copy the target SQL statement to the database in the production environment, and to test the target SQL statement in the database in the production environment based on test cases to obtain the execution results; The evaluation module is used to evaluate the target SQL statement based on the execution result and obtain the evaluation result.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

Images