SQL query statement generation system and method

Through the SQL query statement generation system, natural language query is used to generate multiple SQL query statements and select the best performance statement, solving the complexity and accuracy of traditional database query and achieving efficient and accurate query results.

CN118585537BActive Publication Date: 2024-11-26BEIJING TIANDI CHAOYUN TECH CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411074051.9
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-07
Publication Date
2024-11-26
Estimated Expiration
2044-08-07

AI Technical Summary

Technical Problem

Traditional database queries require users to have database language knowledge, which leads to high operational complexity and prone to errors or incomplete query results, which reduces the efficiency, accuracy and credibility of the query.

Method used

Provide a system for generating SQL query statements, including database interaction module, prompt word construction module, language model interaction module and language processing model. Multiple SQL query statements are generated through natural language query statements, and the performance is determined by the verification selection module. Excellent query statements reduce user operation complexity and improve query accuracy and credibility.

Benefits of technology

Without the need for users to manually write complex query statements, the system can generate SQL query statements with the best performance, which improves query efficiency and accuracy, reduces operation complexity, and enhances the credibility of query.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118585537B_ABST
    Figure CN118585537B_ABST
Patent Text Reader

Abstract

The present invention provides a system and method for generating SQL query statements. A user can send a natural language query statement to a database interaction module, and then a corresponding target prompt word is constructed by a prompt word construction module. A language processing model can generate a corresponding plurality of first SQL query statements based on the target prompt word, and finally determine the target SQL query statement. The user does not need to manually write complex query statements, which reduces the user's operation complexity and improves the query efficiency. Furthermore, by determining the target SQL statement from the plurality of first SQL query statements, the accuracy and credibility of the query can be further improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a system and method for generating SQL query statements. Background Art

[0002] In the field of modern information technology, database interaction has always been a crucial issue. Traditional database queries often require users to have certain knowledge of database languages ​​and to manually construct complex query statements, which poses a certain threshold for non-professionals and increases the complexity of user operations. At the same time, due to the complexity of the database structure and the diversity of query statements, it is easy to have erroneous or incomplete query results, reducing the efficiency, accuracy and credibility of the query. Summary of the invention

[0003] The purpose of the present invention is to provide a system for generating SQL query statements to reduce the operation complexity of users and improve the efficiency, accuracy and credibility of queries.

[0004] The invention provides a system for generating SQL query statements, the system comprising: a database interaction module, a prompt word construction module, a language model interaction module, and a language processing model which are sequentially connected in communication; the prompt word construction module is also connected in communication with a tuning rule module; the tuning rule module pre-stores a first tuning rule; the database interaction module pre-stores database information and a target index;

[0005] The database interaction module is used to receive the natural language query statement sent by the user, and send the natural language query statement, database information and target index to the prompt word construction module;

[0006] The prompt word construction module is used to load the first tuning rule from the tuning rule module, construct the target prompt word corresponding to the natural language query sentence based on the first tuning rule, database information and target index, and send the target prompt word to the language model interaction module, so as to send the target prompt word to the language processing model through the language model interaction module;

[0007] The language processing model is used to generate multiple first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine the target SQL query statement from the multiple first SQL query statements.

[0008] Furthermore, the database interaction module is respectively communicated with the first database and the verification selection module; the language processing model is used to generate multiple first SQL query statements corresponding to the natural language query statement based on the target prompt word, and send each first SQL query statement to the language model interaction module; the language model interaction module is used to send each first SQL query statement to the first database through the database interaction module and run it, obtain the running results corresponding to each first SQL query statement, and send each running result to the verification selection module; the verification selection module is used to determine the target SQL query statement from the multiple first SQL query statements according to each running result.

[0009] Furthermore, each running result includes: query results and performance data of multiple preset performance indicators; the verification selection module is used to detect whether the query results in each running result are consistent. If each query result is consistent, the performance data of the same performance indicator in each running result is normalized to obtain the normalized data of each performance indicator in each running result; for each running result, according to the preset weight coefficient, the normalized data of each preset performance indicator in the running result is weighted calculated to obtain the performance score of the first SQL query statement corresponding to the running result; the first SQL query statement with the highest performance score is determined as the target SQL query statement.

[0010] Furthermore, the verification and selection module is used to send a prompt word resending instruction to the prompt word construction module if it detects that at least two query results are inconsistent; the prompt word construction module is used to repeat the step of sending the target prompt word to the language model interaction module according to the prompt word resending instruction, so as to send the target prompt word to the language processing model through the language model interaction module.

[0011] Furthermore, the database interaction module is used to receive database login information, establish a connection with the first database according to the database login information, obtain database information from the first database, and send the database information to the prompt word construction module; the prompt word construction module is used to load the index tuning rule from the tuning rule module, construct the first prompt word according to the database information and the index tuning rule, and send the first prompt word to the language model interaction module, so as to send the first prompt word to the language processing model through the language model interaction module; the language processing model is used to generate an index creation statement based on the first prompt word, and send the index creation statement to the language model interaction module; the language model interaction module is used to send the index creation statement to the first database through the database interaction module and run it to create a target index.

[0012] Furthermore, the prompt word construction module includes a prompt word template, in which a plurality of preset prompt words are pre-configured; the prompt word construction module is used to load the first tuning rule from the tuning rule module, and construct a target prompt word corresponding to the natural language query statement based on the plurality of preset prompt words, the first tuning rule, the database information and the target index.

[0013] Furthermore, the plurality of preset performance indicators include at least two of the following: query time, CPU resource usage, throughput per second, number of disk reads, and amount of data read and written per second.

[0014] Furthermore, the database login information includes: database IP address, port number, user name and password.

[0015] Furthermore, the database information includes: the structure of the data table stored in the first database.

[0016] The present invention provides a method for generating an SQL query statement, the method comprising: a database interaction module receives a natural language query statement sent by a user, and sends the natural language query statement, database information and a target index to a prompt word construction module; the prompt word construction module loads a first tuning rule from a tuning rule module, constructs a target prompt word corresponding to the natural language query statement based on the first tuning rule, database information and target index, and sends the target prompt word to a language model interaction module, so that the target prompt word is sent to a language processing model through the language model interaction module; the language processing model generates multiple first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine the target SQL query statement from the multiple first SQL query statements.

[0017] The SQL query statement generation system and method provided by the present invention can enable a user to send a natural language query statement to a database interaction module, and then construct a corresponding target prompt word through a prompt word construction module. The language processing model can generate a corresponding plurality of first SQL query statements based on the target prompt word, and finally determine the target SQL query statement. The user does not need to manually write complex query statements, which reduces the user's operation complexity and improves the query efficiency. Furthermore, determining the target SQL statement from the plurality of first SQL query statements can further improve the accuracy and credibility of the query. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the specific implementation methods of the present invention or the technical solutions in the prior art, the drawings required for use in the specific implementation methods or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some implementation methods of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0019] Figure 1 A schematic diagram of a system for generating SQL query statements provided by an embodiment of the present invention;

[0020] Figure 2 A flowchart of a method for generating a SQL query statement provided by an embodiment of the present invention;

[0021] Figure 3 A schematic diagram of a system for generating SQL query statements provided by an embodiment of the present invention;

[0022] Figure 4 A flow chart of performance comparison provided by an embodiment of the present invention;

[0023] Figure 5 A schematic diagram of a system for generating SQL query statements provided by an embodiment of the present invention;

[0024] Figure 6 A flowchart of initialization provided by an embodiment of the present invention;

[0025] Figure 7 A schematic diagram of an index provided by an embodiment of the present invention;

[0026] Figure 8 A schematic diagram of a data table in a database provided by an embodiment of the present invention;

[0027] Fig. 9 A flowchart of a method for generating a SQL query statement provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0028] The technical solution of the present invention will be clearly and completely described below in conjunction with the embodiments. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0029] In the field of software development, especially for applications based on the Mybatis framework, the generation and performance tuning of SQL (Structured Query Language) statements have always been key issues. Traditional methods usually require developers to manually write SQL statements and then tune them to ensure that performance reaches the optimal level. However, this method increases the complexity of user operations, reduces the ease of use and popularity of the database, and reduces the efficiency, accuracy and credibility of queries. Based on this, an embodiment of the present invention provides a system and method for generating SQL query statements, which can be applied to applications that need to generate SQL query statements.

[0030] To facilitate understanding of this embodiment, firstly, a system for generating SQL query statements disclosed in an embodiment of the present invention is introduced. Figure 1 As shown, the system includes: a database interaction module 10, a prompt word construction module 11, a language model interaction module 12, and a language processing model 13 which are sequentially connected in communication; the prompt word construction module 11 is also connected in communication with a tuning rule module 14; the tuning rule module 14 pre-stores a first tuning rule; the database interaction module 10 pre-stores database information and a target index; the above-mentioned first tuning rule can be understood as a rule that the language processing model 13 needs to comply with when generating and tuning SQL query statements. The first tuning rule can adopt the default stored tuning rule, or the user can add the required rule by himself, which can be selected according to actual needs. For example, the rule can be "try not to use in and not in", "efficient paging", etc.; for example: "select id from t where num in(1,2,3)" can be optimized to "select id from t where num between 1 and 3"; "select id,name,age from userlimit 10000, 20;" can be optimized to "select id,name,age from user id>10000 limit 20;”, etc., by setting the first tuning rule, it is helpful to improve the query efficiency of SQL query statements. The above database information may include the structure of the data table in the database, etc.; the above target index can be understood as a separate, physical structure for sorting the values ​​of one or more columns in the data table in the database, and the target index can be used to quickly access specific information in the data table.

[0031] The database interaction module 10 can also be called a prompt construction module, which is used to receive a natural language query statement sent by a user, and send the natural language query statement, database information and target index to the prompt word construction module 11; the natural language query statement can be understood as a natural language expression input by the user; Figure 2The flowchart of a method for generating an SQL query statement is shown. In actual implementation, the user can input a natural language query statement to the database interaction module 10, such as "Please help me find out which CPUs the host with the IP address 172.17.10.74 has". After receiving the natural language query statement, the database interaction module 10 can send the natural language query statement, database information and target index to the prompt word construction module 11. In one embodiment, the user can also make additional inputs to the natural language query statement, such as "Please only output SQL, no other text is required", to limit the output of the language processing model 13. In this case, the additional input content usually needs to be sent to the prompt word construction module 11.

[0032] The prompt word construction module 11 is used to load the first tuning rule from the tuning rule module 14, construct the target prompt word corresponding to the natural language query statement based on the first tuning rule, database information and target index, and send the target prompt word to the language model interaction module 12, so as to send the target prompt word to the language processing model 13 through the language model interaction module 12; the prompt word construction module 11 refers to designing and constructing appropriate target prompt words for natural language processing tasks based on the language processing model 13 to guide the language processing model 13 to generate the expected output. The core idea of ​​this prompt engineering is to guide the language processing model 13 to produce the expected output on a specific task through the carefully designed target prompt words. It basically includes the following four steps: Task definition: First, it is necessary to clearly define the input and output of the task, as well as the goal of the task, which helps to determine the text content that the language processing model 13 should generate, as well as the design direction of the prompt; prompt design: Designing appropriate prompt words is the key to the prompt word construction module 11. The prompt words should be concise, targeted, and able to guide the language processing model 13 to generate the expected output; the characteristics, context and user needs of the task need to be considered during the design process. Sample data: Collect and prepare sample data related to the task to verify and adjust the effect of the prompt words. The sample data should cover various situations and contexts of the task to ensure the universality and applicability of the prompt words. Evaluation and adjustment: Through experiments and evaluation, continuously adjust and optimize the prompt words to improve the quality and efficiency of the results generated by the language processing model13, including evaluating the accuracy, fluency and consistency of the generated text with the task objectives.

[0033] In actual implementation, after receiving the natural language query statement, database information and target index, the prompt word construction module 11 can load the first tuning rule from the tuning rule module 14, and construct the target prompt word based on the first tuning rule and the various data information received, and send it to the language model interaction module 12. The language model interaction module 12 then sends the received target prompt word to the language processing model 13.

[0034] The language processing model 13 is used to generate a plurality of first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine the target SQL query statement from the plurality of first SQL query statements.

[0035] The above-mentioned language processing model 13 may specifically be an LLM (Large Language Model), which is a natural language processing model 13 based on deep learning and has powerful text understanding and generation capabilities. When processing natural language tasks, LLM can accept text input and generate text output with semantic rationality and contextual logic.

[0036] In the pre-training phase, LLM usually uses unsupervised learning methods and self-supervised learning through a large-scale text corpus. In this process, LLM tries to predict the next word or text in a text sequence to learn the structure, grammar, and semantic information of the text. In this way, LLM can learn rich language knowledge and form a deep understanding of the text.

[0037] In the application stage, LLM can be used for various natural language processing tasks, such as text generation, text classification, named entity recognition, language translation, etc. Through fine-tuning or task-specific training, LLM can generate text output that meets the requirements according to specific application scenarios.

[0038] The emergence and development of LLM has greatly promoted the development of the field of natural language processing. It has made breakthrough progress in machine translation, intelligent question and answer, text generation, etc., and has been widely used in various artificial intelligence applications, providing people with a more intelligent and convenient language interaction experience. With the development and popularization of LLM, such as OpenAI's GPT series of models, people have begun to realize that LLM can bring new possibilities for database interaction. LLM has powerful language understanding and generation capabilities. It can understand natural language input and generate text output that conforms to semantic logic. Based on this, researchers began to explore how to apply LLM to database interaction to simplify user operation processes, improve query efficiency, and ensure the accuracy of results.

[0039] SQL query statements are a database query and programming language used to access data and query, update, and manage relational database systems. In this embodiment, SQL query statements can be SQL query statements in MyBatis, which is an open source persistence layer framework that simplifies the process of operating relational databases in Java applications. In MyBatis, the writing and execution of SQL query statements is one of the core functions, which allows developers to define and execute SQL query statements using simple XML or annotations. The following are some features and usages of SQL query statements in MyBatis:

[0040] XML mapping file: MyBatis uses XML files to describe the mapping relationship between SQL query statements and Java objects. In the XML file, you can define SQL query statements, parameter mapping, result mapping and other information.

[0041] Dynamic SQL: MyBatis provides a powerful dynamic SQL function that can dynamically generate SQL statements based on conditions. <if> 、 <choose> 、 <when> 、 <otherwise>Tags such as , can be used to splice different SQL fragments according to different conditions.

[0042] Parameter binding: MyBatis supports using named parameters or placeholders in SQL query statements and binding them to properties or methods in Java objects. Parameter binding can be done through <parameter>Tags or #{}, ${} placeholders.

[0043] Result Mapping: MyBatis allows developers to map query results into Java objects. <resultmap>The tag or resultType attribute defines the result mapping rules.

[0044] Batch operations: MyBatis provides support for batch insertion, updating, and deletion, and can perform multiple operations in one database connection, thereby improving database operation efficiency.

[0045] Annotation method: In addition to XML mapping files, MyBatis also supports the use of annotations to describe SQL query statements. By adding annotations such as @Select, @Insert, @Update, and @Delete to Java methods, you can directly write SQL query statements in the code.

[0046] Dynamic parameters: MyBatis also supports dynamic parameters, that is, different parameter values ​​can be passed according to different conditions. You can use <if>Tags or OGNL expressions to dynamically set parameter values.

[0047] In general, the SQL query statements in MyBatis are flexible and easy to use. You can write static or dynamic SQL queries, insert, update, and delete operations according to actual needs. At the same time, the query results are mapped to Java objects through result mapping, which simplifies data persistence operations.

[0048] In this embodiment, the language processing model 13 may generate a plurality of first SQL query statements according to the received target prompt word, so as to select a target SQL query statement with the best performance from the plurality of first SQL query statements.

[0049] In the above-mentioned SQL query statement generation system, the user can send a natural language query statement to the database interaction module 10, and then construct the corresponding target prompt word through the prompt word construction module 11. The language processing model 13 can generate corresponding multiple first SQL query statements based on the target prompt word, and finally determine the target SQL query statement. There is no need for the user to manually write complex query statements, which reduces the user's operation complexity and improves the query efficiency. In addition, determining the target SQL statement from multiple first SQL query statements can further improve the accuracy and credibility of the query.

[0050] Further, such as Figure 3 A schematic diagram of a system for generating SQL query statements is shown in FIG. Figure 4 A performance comparison flowchart is shown, combined with Figure 2 It can be seen that the database interaction module 10 is respectively communicated with the first database 16 and the verification selection module 15; the language processing model 13 is used to generate multiple first SQL query statements corresponding to the natural language query statement based on the target prompt word, and send each first SQL query statement to the language model interaction module 12; the language model interaction module 12 is used to send each first SQL query statement to the first database 16 through the database interaction module 10 and run it, obtain the running results corresponding to each first SQL query statement, and send each running result to the verification selection module 15; the verification selection module 15 is used to determine the target SQL query statement from the multiple first SQL query statements according to each running result.

[0051] In a specific implementation, the language processing model 13 can send multiple first SQL query statements generated based on the target prompt word to the language model interaction module 12, and the language model interaction module 12 can then send them to the database interaction module 10. The database interaction module 10 can then send the received multiple first SQL query statements to the connected first database 16, so as to use each first SQL query statement to query in the first database 16 to obtain the running results corresponding to each first SQL query statement. In order to further verify the quality of each first SQL query statement, the database interaction module 10 can pass each obtained running result to the verification selection module 15. The verification selection module 15 can verify the quality of each first SQL query statement based on each received running result to determine the target SQL query statement from the multiple first SQL query statements. Preferably, the first SQL query statement with the best quality can be determined as the target SQL query statement.

[0052] Further, each operation result includes: query results and performance data of multiple preset performance indicators; the query result can be understood as the result obtained by querying the first database 16 using the first SQL query statement, such as the number of result data, etc.; the above-mentioned multiple preset performance indicators usually include at least two of the following: query time, CPU resource usage, throughput per second, disk read times, and data read and written per second. Among them, query time can be understood as the average time required to execute an SQL query statement. Shorter processing time means that the query is more efficient and the user can get the result faster. This is particularly important for real-time systems or high-concurrency environments; CPU resource usage represents the percentage of CPU resources occupied during the execution of SQL query statements. Lower CPU resource usage indicates that the query uses system resources more efficiently, which helps to maintain overall system performance in a multi-tasking environment. Throughput per second refers to the number of requests that can be processed per second. Higher throughput means that the query can process a large amount of data more quickly, which is suitable for application scenarios that need to process large data sets. Disk read times refer to the number of times data is read from the disk during the execution of the SQL query statement. Fewer disk read times indicate that the query is more effective in utilizing indexes and caches, thereby reducing I / O operations and improving query speed. The amount of data read and written per second indicates the amount of data read and written per second. This indicator reflects the I / O efficiency of the query. A higher read and write speed means faster data transmission, thereby accelerating the overall execution process of the query. Of course, other performance indicators can be selected according to actual needs, such as the number of times data is read from the buffer, the total number of rows processed by cumulative execution, logical reads and writes per second, etc.; through multiple preset performance indicators, the stress test of the first database 16 can be implemented.

[0053] The verification selection module 15 is used to detect whether the query results in each operation result are consistent. If each query result is consistent, the performance data of the same performance indicator in each operation result is normalized to obtain the normalized data of each performance indicator in each operation result; for each operation result, according to the preset weight coefficient, the normalized data of each preset performance indicator in the operation result is weightedly calculated to obtain the performance score of the first SQL query statement corresponding to the operation result; the first SQL query statement with the highest performance score is determined as the target SQL query statement.

[0054] In actual implementation, the query results in the running results of each first SQL query statement may be the same or different. The verification selection module 15 can first detect whether the query results in each running result are consistent. For example, the natural language query statement is "Can you give me the name, salary and department name of the employees in the Development department, and the records whose salary is greater than 60,000?" There may be the same SQL query statement in multiple first SQL query statements. The query results of the same SQL query statement are usually the same. In order to reduce the computational complexity, the same first SQL query statement can be classified into one type. The running results of the first SQL query statement of this type can adopt the average value of the multiple same SQL query statements contained therein to compare with the running results of the first SQL query statements of other types; for example, there are ten SQL query statements, corresponding to three types, that is, corresponding to three first SQL query statements, and the number of the three types of first SQL query statements is three times, one time, and six times respectively; if the query results are consistent, such as the results obtained by running each first SQL query statement in the first database 16 are consistent, and are all 351,956 records, then the performance data of each preset performance indicator can be compared. Performance data of one type of multiple preset performance indicators as shown in Table 1.

[0055] Table 1

[0056]

[0057] In actual implementation, in order to facilitate calculation and comparison, it is usually necessary to standardize the performance data of the same performance indicator in each running result, that is, normalization. Standardization is a method of mapping the original data to a certain data range, and standardizing the above indicator results is convenient for the subsequent calculation of performance scores. For example, for the data in Table 1, the average query time, CPU resource usage, disk read times, and the amount of data read and written per second are negative indicators, and the throughput per second is a positive indicator. They can be standardized according to the following formula, where the negative indicator is calculated according to the second formula and the positive indicator is calculated according to the first formula, to obtain the normalized data of multiple preset performance indicators as shown in Table 2:

[0058] (1)

[0059] (2)

[0060] Table 2

[0061]

[0062] The above preset weight coefficients can be set according to actual needs. For example, the weight coefficient for the average query time is set to w1=0.30, the weight coefficient for the CPU resource usage is set to w2=0.20, the weight coefficient for the throughput per second is set to w3=0.20, the weight coefficient for the number of disk reads is set to w4=0.15, and the weight coefficient for the amount of data read and written per second is set to w5=0.15. The performance score of each SQL query statement can be obtained by weighted calculation of the normalized data of each preset performance indicator of each SQL query statement according to the preset weight coefficient. The performance score of the first SQL query statement is 0.9, the performance score of the second SQL query statement is 0.04, and the performance score of the third SQL query statement is 0.34649. By comparison, the performance score of the first SQL query statement is the highest, indicating that the performance of the first SQL query statement is the best. The first SQL query statement can be selected as the target SQL query statement for development.

[0063] Further, the verification and selection module 15 is used to send a prompt word resending instruction to the prompt word construction module 11 if it detects that at least two query results are inconsistent; the prompt word construction module 11 is used to repeat the step of sending the target prompt word to the language model interaction module 12 according to the prompt word resending instruction, so as to send the target prompt word to the language processing model 13 through the language model interaction module 12. In actual implementation, if the verification and selection module 15 is used to send a prompt word resending instruction to the prompt word construction module 11 if it detects that at least two query results are inconsistent, that is, there are different query results, so as to make it resend the target prompt word to the language model interaction module 12.

[0064] Furthermore, the database interaction module 10 is used to receive database login information, establish a connection with the first database 16 according to the database login information, obtain database information from the first database 16, and send the database information to the prompt word construction module 11; the prompt word construction module 11 is used to load the index tuning rule from the tuning rule module 14, construct the first prompt word according to the database information and the index tuning rule, and send the first prompt word to the language model interaction module 12, so as to send the first prompt word to the language processing model 13 through the language model interaction module 12; the language processing model 13 is used to generate an index creation statement based on the first prompt word, and send the index creation statement to the language model interaction module 12; the language model interaction module 12 is used to send the index creation statement to the first database 16 through the database interaction module 10 and run it to create a target index.

[0065] The database information includes: the structure of the data table stored in the first database 16. The first prompt word can be used to prompt the generation of an index creation statement; the index creation statement can be used to create a target index; in actual implementation, before sending the user's natural language query statement to the database interaction module 10, it is usually necessary to initialize the language processing model 13 first. For details, see Figure 5 A schematic diagram of a SQL query statement generation system shown in FIG. Figure 6 In the flowchart of an initialization shown in the figure, the user can send database login information to the database interaction module 10, and the database login information includes: database IP address, port number, user name and password. The database interaction module 10 can establish a connection with the first database 16 through the port corresponding to the port number according to the database IP address, and then obtain the above database information from the first database 16, and send it to the prompt word construction module 11. The prompt word construction module 11 can construct the corresponding first prompt word according to the database information and the loaded index tuning rules, and send it to the language processing model 13 through the language model interaction module 12. The language processing model 13 can generate an index creation statement according to the first prompt word, and send the index creation statement to the first database 16 through the language model interaction module 12 and the database interaction module 10 in turn, and execute the index creation statement to create the target index. Figure 7 A schematic diagram of an index is shown, including information such as index name, columns, tables, and index type.

[0066] In the above initialization process, the database information is mainly obtained to make the language processing model 13 understand it. The database information and the connection code of the corresponding database are needed. The database information can be obtained by using "show create table" and stored in the database interaction module 10. After obtaining the database information of the database, the first prompt word is constructed according to the database information and the loaded index tuning rules, and sent to the language processing model 13 to obtain the index creation statement. After the language processing model 13 returns OK, it can be considered that the initialization of the language processing model 13 is completed; the index creation statement is sent to the first database 16 through the language model interaction model and the database interaction module 10 in turn. If there are multiple index creation statements, each generated index creation statement can be executed in turn to obtain the target index. At this point, the initialization of the first database 16 is completed, and then the generation and verification of the tuning SQL query statement will be performed on this basis. The creation of the index also belongs to the tuning part. Running a specific SQL query statement on the first database 16 after the index is created can also play a role in improving performance. Similar to obtaining the data structure, it is also an effective way to help the large model be generated on a specific database and avoid errors caused by the generalization generation of the large model.

[0067] Furthermore, the prompt word construction module 11 includes a prompt word template, in which a plurality of preset prompt words are pre-configured; the prompt word construction module 11 is used to load the first tuning rule from the tuning rule module 14, and construct the target prompt word corresponding to the natural language query statement based on the plurality of preset prompt words, the first tuning rule, the database information and the target index. In actual implementation, the prompt word construction module 11 is usually composed of a number of pre-written prompt word templates, and the prompt word templates contain some preset prompt words in advance, and these preset prompt words are usually verified. Of course, the user can also adjust the preset prompt words according to actual needs. The prompt word construction module 11 can control the generated content and format of the language processing model 13 with the preset prompt words, so as to facilitate the use of other modules.

[0068] Combination Figure 2 , Figure 3 and Figure 4 It can be seen that the main program of this system is mainly composed of five modules, including language model interaction module 12, database interaction module 10, verification selection module 15, prompt word construction module 11 and tuning rule module 14. Database information and natural language query statements for search need to be provided. Some modules are further explained below.

[0069] The language model interaction module 12 is responsible for sending and receiving information with the language processing model 13, receiving the constructed prompt words, and sending them to the language processing model 13 to wait for a response. You can use the API (Application Programming Interface) interface provided by various language models, or you can use your own trained language model and then use the common model framework for interaction.

[0070] The database interaction module 10 is responsible for directly interacting with the first database 16, such as Python's pymysql or Java's mybatis or Gorm of Golang. It accepts the database IP, port number, user name, password and other information provided by the user to connect to the first database 16. It also needs to obtain the database information of the first database 16, collect the SQL query statements generated by the language processing model 13 for search, and the search results will be compared by the verification selection module 15 for performance comparison and correctness detection.

[0071] The verification selection module 15 is responsible for performing multiple checks on multiple first SQL query statements, using several groups of results for detection, first to confirm whether the query results are correct, and secondly to select a target SQL query statement with the best performance index and return it to the user.

[0072] The tuning rule module 14 is responsible for adding tuning rules. By adding tuning rules, the criteria to be followed when generating SQL query statements are specified. Usually, such rules are also the criteria followed by developers during development, which is beneficial to improving the query efficiency of SQL query statements.

[0073] The system mainly exists as a middleware between the language processing model 13 and the first database 16. It can be used to read database content, collect database information, and form target prompt words according to the template to control the output of the language processing model 13. At the same time, it can generate the required first SQL query statement multiple times according to the optimization requirements specified by itself, and then compare the performance parameters multiple times through the actual operation of the first SQL query statement, so as to select the optimal target SQL query statement for development. In this process, the system is mainly used to coordinate the relationship between the real first database 16 and the language processing model 13, so as to simplify the development process, improve development efficiency, and optimize the system operation efficiency.

[0074] The above SQL query generation system can be applied to a test database of millions of employees, such as Figure 8 The diagram is a schematic diagram of a data table in a database, which includes multiple data tables. The first row in each data table is the name of the table, and the other rows are the fields of the table. The connecting lines between the data tables represent the association relationship between different data tables.

[0075] The generation system of the above-mentioned SQL query statement realizes the intelligent generation and performance tuning of SQL query statements under the Mybatis framework by combining the language processing model 13. Through the steps of initializing database connection, obtaining database information, defining tuning rules, creating indexes, constructing prompt words, etc., the intelligent generation and performance tuning of SQL query statements under the Mybatis framework are realized. The language processing model 13 can generate SQL query statements suitable for Mybatis and meet the performance requirements of a specific database according to the natural language query statements and database information provided by the user. In order to ensure that the generated SQL query statements reach the optimal performance when executed, the technology needs to generate SQL query statements multiple times, and execute them in sequence, record the performance data and query results of each query, so as to select the best target SQL query statement to solve the user problem. This performance tuning method can ensure that the generated target SQL query statement reaches the optimal performance when executed, and improves the response speed and stability of the system.

[0076] Through this technology, users can directly generate Mybatis SQL query statements required in software development through spoken language, without having to manually write complex query statements. This intuitive and convenient operation method greatly improves development efficiency. At the same time, because the generated SQL statements have undergone multiple performance tunings, they can ensure a high level of performance when executed under the Mybatis framework, effectively saving the manpower and time costs of SQL tuning, allowing developers to focus more on the implementation of business logic.

[0077] This technology can generate SQL query statements suitable for Mybatis based on the natural language provided by the user, and record and analyze performance parameters and results by generating and executing SQL query statements multiple times to ensure that the generated target SQL query statements have optimal execution performance under the Mybatis framework.

[0078] The SQL statement generation and tuning technology based on Mybatis adopts the large model technology (i.e., the language processing model 13) to ensure that the generated target SQL query statement has superior performance when executed in the Mybatis environment, and can be dynamically adjusted according to the actual execution situation to optimize the performance. The tuning rules stored in the tuning rule module 14 can be flexibly defined according to the characteristics of the Mybatis framework and database information to meet the performance requirements in different application scenarios.

[0079] By using this technology, developers can greatly improve the efficiency of SQL statement generation in software development, and because the generated SQL statements have been tuned for multiple times, they can achieve a high level of performance when executed under the Mybatis framework. Therefore, this technology effectively saves the manpower and time costs of SQL tuning and improves the efficiency and quality of software development.

[0080] Based on the big model technology, this solution can intelligently generate SQL query statements suitable for Mybatis based on the natural language provided by the user. This intelligent generation method can reduce the difficulty of writing SQL query statements, reduce the possibility of errors, and improve the quality of the code. This technical solution allows developers to simply describe the required query requirements without having to deeply understand the writing and performance tuning skills of SQL query statements. This lowers the technical threshold for development and enables more users to participate in system development.

[0081] The embodiment of the present invention discloses a method for generating a SQL query statement, such as Fig. 9 As shown, the method comprises the following steps:

[0082] Step S902, the database interaction module receives the natural language query statement sent by the user, and sends the natural language query statement, database information and target index to the prompt word construction module;

[0083] Step S904: the prompt word construction module loads the first tuning rule from the tuning rule module, constructs a target prompt word corresponding to the natural language query sentence based on the first tuning rule, database information and target index, and sends the target prompt word to the language model interaction module, so that the target prompt word is sent to the language processing model through the language model interaction module;

[0084] Step S906: The language processing model generates a plurality of first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine a target SQL query statement from the plurality of first SQL query statements.

[0085] In the above-mentioned method for generating SQL query statements, the user can send a natural language query statement to the database interaction module, and then construct the corresponding target prompt word through the prompt word construction module. The language processing model can generate corresponding multiple first SQL query statements based on the target prompt word, and finally determine the target SQL query statement. There is no need for the user to manually write complex query statements, which reduces the user's operation complexity and improves the query efficiency. In addition, determining the target SQL statement from multiple first SQL query statements can further improve the accuracy and credibility of the query.

[0086] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.< / if> < / resultmap> < / parameter> < / otherwise> < / when> < / choose> < / if>

Claims

1. A system for generating SQL query statements, characterized in that: The system comprises: a database interaction module, a prompt word construction module, a language model interaction module, and a language processing model which are sequentially connected in communication; the prompt word construction module is also connected in communication with a tuning rule module; the tuning rule module stores a first tuning rule in advance; the database interaction module stores database information and a target index in advance; The database interaction module is used to receive a natural language query statement sent by a user, and send the natural language query statement, the database information and the target index to the prompt word construction module; The prompt word construction module is used to load the first tuning rule from the tuning rule module, construct a target prompt word corresponding to the natural language query sentence based on the first tuning rule, the database information and the target index, and send the target prompt word to the language model interaction module, so as to send the target prompt word to the language processing model through the language model interaction module; The language processing model is used to generate a plurality of first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine a target SQL query statement from the plurality of first SQL query statements; The target index pre-stored in the database interaction module is created in the following manner: The database interaction module receives database login information, establishes a connection with a first database according to the database login information, obtains the database information from the first database, and sends the database information to the prompt word construction module; The prompt word construction module loads the index tuning rule from the tuning rule module, constructs a first prompt word according to the database information and the index tuning rule, and sends the first prompt word to the language model interaction module, so as to send the first prompt word to the language processing model through the language model interaction module; The language processing model generates an index creation statement based on the first prompt word, and sends the index creation statement to the language model interaction module; wherein the first prompt word is used to prompt the generation of the index creation statement; and the index creation statement is used to create a target index; The language model interaction module sends the index creation statement to the first database through the database interaction module and runs it to create a target index.

2. The system according to claim 1, characterized in that The database interaction module is respectively connected to the first database and the verification selection module for communication; The language processing model is used to generate a plurality of first SQL query statements corresponding to the natural language query statement based on the target prompt word, and send each of the first SQL query statements to the language model interaction module; The language model interaction module is used to send each of the first SQL query statements to the first database through the database interaction module and run the statement, obtain the running results corresponding to each of the first SQL query statements, and send each of the running results to the verification selection module; The verification selection module is used to determine a target SQL query statement from multiple first SQL query statements according to each of the running results.

3. The system according to claim 2, characterized in that Each of the operation results includes: query results and performance data of multiple preset performance indicators; The verification selection module is used to detect whether the query results in each of the operation results are consistent. If each of the query results is consistent, the performance data of the same performance indicator in each of the operation results is normalized to obtain the normalized data of each performance indicator in each of the operation results; For each of the operation results, weighted calculation is performed on the normalized data of each preset performance indicator in the operation result according to a preset weight coefficient to obtain a performance score of the first SQL query statement corresponding to the operation result; The first SQL query statement with the highest performance score is determined as the target SQL query statement.

4. The system according to claim 2, characterized in that The verification selection module is used to send a prompt word resending instruction to the prompt word construction module if it is detected that at least two query results are inconsistent; The prompt word construction module is used to repeatedly execute the step of sending the target prompt word to the language model interaction module according to the prompt word resending instruction, so as to send the target prompt word to the language processing model through the language model interaction module.

5. The system according to claim 1, characterized in that The prompt word construction module includes a prompt word template, and the prompt word template is pre-configured with a plurality of preset prompt words; The prompt word construction module is used to load the first tuning rule from the tuning rule module, and construct a target prompt word corresponding to the natural language query statement based on a plurality of the preset prompt words, the first tuning rule, the database information and the target index.

6. The system according to claim 3, characterized in that The plurality of preset performance indicators include at least two of the following: query time, CPU resource usage, throughput per second, number of disk reads, and amount of data read and written per second.

7. The system according to claim 1, characterized in that The database login information includes: database IP address, port number, user name and password.

8. The system according to claim 2, characterized in that The database information includes: the structure of the data table stored in the first database.

9. A method for generating a SQL query statement, characterized in that: The method comprises: The database interaction module receives the natural language query statement sent by the user, and sends the natural language query statement, database information and target index to the prompt word construction module; The prompt word construction module loads a first tuning rule from the tuning rule module, constructs a target prompt word corresponding to the natural language query sentence based on the first tuning rule, the database information and the target index, and sends the target prompt word to the language model interaction module, so that the target prompt word is sent to the language processing model through the language model interaction module; The language processing model generates a plurality of first SQL query statements corresponding to the natural language query statement based on the target prompt word, so as to determine a target SQL query statement from the plurality of first SQL query statements; The target index is created in the following way: The database interaction module receives database login information, establishes a connection with a first database according to the database login information, obtains the database information from the first database, and sends the database information to the prompt word construction module; The prompt word construction module loads the index tuning rule from the tuning rule module, constructs a first prompt word according to the database information and the index tuning rule, and sends the first prompt word to the language model interaction module, so as to send the first prompt word to the language processing model through the language model interaction module; The language processing model generates an index creation statement based on the first prompt word, and sends the index creation statement to the language model interaction module; wherein the first prompt word is used to prompt the generation of the index creation statement; The index creation statement is used to create a target index; The language model interaction module sends the index creation statement to the first database through the database interaction module and runs it to create a target index.