A database adaptation method and device, electronic equipment and storage medium

Abstract

Embodiments of the present application disclose a database adaptation method and device, electronic equipment and storage medium, which belong to the technical field of database technology and artificial intelligence cross technology, wherein the method comprises: capturing the original SQL statement sent by the application and standardizing by extending the MyBatis plug-in function; using Redis cache to query whether the converted target SQL statement exists; if the cache is not hit, the original SQL statement is converted into the syntax format supported by the target database by combining the pre-trained database syntax mapping rule through the well-designed large model of Prompt; the converted SQL statement is submitted to the target database for execution, and the result is stored in the cache. The present application solves the problem that the developer needs to manually adapt the SQL code in the prior art under different database environments, which is labor-intensive, error-prone and affects the system compatibility and stability, realizes "one development, multi-library adaptation", and provides strong support for flexible deployment and long-term stable operation of digital systems.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database technology and artificial intelligence, and particularly relates to a database adaptation method and device, electronic equipment and storage medium. BACKGROUND

[0002] With the rapid development of information technology and the deepening of enterprise digital transformation, the diversity and heterogeneity of databases as the core components of data storage and processing are increasingly prominent.

[0003] Especially under the background of information technology application innovation (XinChuang), database localization replacement has become an important trend, and enterprises are facing the urgent need to migrate from traditional databases (such as MySQL, Oracle) to domestic or new databases (such as Dream, RenDa JinCang, Huawei cloud database, etc.).

[0004] However, different databases have significant differences in SQL syntax, function characteristics, stored procedures, etc. For example, MySQL uses LIMIT for paging, while Oracle relies on ROWNUM. These differences lead to the need for a large number of modifications and adaptations of SQL code during application migration. Traditional manual adaptation methods not only have a large workload and long cycle, but also easily introduce compatibility BUGs due to human oversight, affecting system stable operation.

[0005] In addition, traditional database migration and adaptation technology is not up to the task when faced with complex and variable database environments. They often rely on manual writing and modification of SQL scripts, lack of automation and intelligent support, and are difficult to cope with large-scale, high-frequency database migration requirements.

[0006] Therefore, there is an urgent need for a database adaptation method that can dynamically adapt to different databases, automatically handle SQL differences, improve migration efficiency and system stability. SUMMARY

[0007] The embodiments of the present application provide a database adaptation method to solve the problem that the prior art requires a large number of manual modifications and adaptations of SQL code, which has a large workload, a long cycle, is prone to errors, affects system stable operation, and lacks automation and intelligent support. The technical solution is as follows:

[0008] According to one aspect of the present application, a database adaptation method, the method comprising: obtaining an original SQL statement and preprocessing to generate a standardized SQL statement, generating an MD5 hash value based on the standardized SQL statement, querying whether a target SQL statement corresponding to the MD5 hash value exists in the Redis cache; if the cache is not hit, obtaining the configuration information of the original SQL statement, generating a target SQL statement according to the configuration information by calling a pre-trained SQL conversion model through a Prompt project; verifying the syntax of the target SQL statement through an explain execution plan, and if the verification is passed, executing the target SQL statement and obtaining a running result, if the running is successful, storing the target SQL statement and the MD5 hash value into the Redis cache; if the running fails, generating a target SQL statement again according to a set number of times, if the running still fails after reaching the set number of times, manually correcting the SQL statement that fails to execute to obtain a correct SQL statement, and storing the correct SQL statement and the corresponding MD5 hash value.

[0009] In one embodiment, obtaining an original SQL statement and preprocessing to generate a standardized SQL statement, generating an MD5 hash value based on the standardized SQL statement, and querying whether a target SQL statement corresponding to the MD5 hash value exists in the Redis cache are achieved by the following steps: capturing an original SQL text without bound parameters from a MyBatis interceptor, preprocessing the original SQL text to generate a standardized SQL statement through a Java replaceAll method; performing MD5 hash operation on the preprocessed standardized SQL statement to generate a unique MD5 hash value, obtaining the name of a target database from database connection parameters; using the MD5 hash value and the target database name as a key-value pair to query whether a corresponding target SQL statement exists in the Redis cache, and if it exists, directly using the target SQL statement.

[0010] In one embodiment, if the cache is not hit, obtaining the configuration information of the original SQL statement, and generating a target SQL statement according to the configuration information by calling a pre-trained SQL conversion model through a Prompt project are achieved by the following steps: when the corresponding target SQL statement in the Redis cache is not hit, preparing input parameters through a Prompt project; the input parameters include a standardized original SQL statement, a source database type, a target database type, and a pre-trained database syntax mapping rule example; inputting the input parameters into a pre-trained SQL conversion model; the SQL conversion model adopts a Transformer architecture containing an encoder and a decoder; the SQL conversion model is used to convert an original SQL statement into a syntax format supported by a target database according to a pre-trained database syntax mapping rule.

[0011] In one of the embodiments, the encoder part of the SQL conversion model comprises a plurality of self-attention mechanism modules, a multi-head attention mechanism, and a feedforward neural network; the decoder of the SQL conversion model adopts an attention mechanism-based architecture; and the encoder and the decoder both comprise a normalization layer.

[0012] In one of the embodiments, the training process of the SQL conversion model is implemented by the following steps: obtaining a training data set and performing cleaning, conversion, and normalization processing; the training data set comprises table structure information of a relational database, SQL statement data, and database operation log data; randomly initializing parameters of the SQL conversion model in a supervised learning manner, dividing the preprocessed training data set into a plurality of batches for multi-round iterative training, calculating an output through forward propagation and comparing it with a target output during the training process to calculate a cross-entropy loss function; using a back propagation algorithm to calculate the gradient of the cross-entropy loss function on the model parameters, and updating the parameters of the SQL conversion model through an optimization algorithm to minimize the loss function until the cross-entropy loss function converges or a preset training round number is reached; and the optimization algorithm comprises a stochastic gradient descent and an Adam optimization algorithm.

[0013] In one of the embodiments, the correct SQL statement is obtained by manually correcting the SQL statement that fails to execute, and the correct SQL statement and the corresponding MD5 hash value are stored by the following steps: using intelligent pre-analysis technology to preliminarily scan the SQL statement that fails to execute and mark obvious errors, popping up a detailed error analysis window to explain the error causes and provide editing guidance for the marked areas, and using a real-time verification mechanism to instantaneously feed back the verification results after manual correction; obtaining the correct SQL statement after the verification results pass, comprehensively verifying the correct SQL statement and encapsulating to obtain a correction case, using a machine learning algorithm to intelligently classify the correction case and construct a multi-dimensional index, adding it to a knowledge base, and using a feedback mechanism to collect actual effect data to evaluate the quality and effectiveness of the case.

[0014] In one of the embodiments, the correct SQL statement is obtained by manually correcting the SQL statement that fails to execute, and the correct SQL statement and the corresponding MD5 hash value are stored by the following steps: performing a final comprehensive verification on the correct SQL statement; the comprehensive verification comprises syntax correctness, semantic consistency, and compatibility with the database, and if the verification passes, the correct SQL statement is marked as valid; otherwise, the commit operation is prevented and a prompt for correction is given; combining the MD5 hash value of the original SQL statement and the target database name as the key in a key-value pair, and constructing the key-value pair by taking the correct SQL statement as the value in the key-value pair, and using the HSET command of Redis to set the key-value pair in the Redis cache.

[0015] According to an aspect of the present application, a database adaptation device, the device comprising: an original SQL processing module for obtaining an original SQL statement and preprocessing to generate a standardized SQL statement, generating an MD5 hash value based on the standardized SQL statement, and querying whether a target SQL statement corresponding to the MD5 hash value exists in a Redis cache; an SQL statement conversion module for, if the cache is not hit, obtaining configuration information of the original SQL statement, and generating a target SQL statement according to the configuration information by calling a pre-trained SQL conversion model through a Prompt project; an SQL execution and cache module for pre-verifying the syntax of the target SQL statement through an explain execution plan, executing the target SQL statement and obtaining a running result after the verification is passed, and storing the target SQL statement and MD5 hash value into the Redis cache if the running is successful; and a manual correction and update module for, if the running fails, regenerating the target SQL statement according to a set number of times, and if the running still fails after the set number of times is reached, manually correcting the SQL statement that fails to execute to obtain a correct SQL statement, and storing the correct SQL statement and the corresponding MD5 hash value.

[0016] According to an aspect of the present application, an electronic device comprising at least one processor and at least one memory, wherein the memory has computer readable instructions stored thereon; the computer readable instructions are executed by one or more processors, so that the electronic device implements the database adaptation method as described above.

[0017] According to an aspect of the present application, a storage medium having computer readable instructions stored thereon, the computer readable instructions being executed by one or more processors to implement the database adaptation method as described above.

[0018] The technical scheme provided by the present application has the beneficial effects that:

[0019] In the technical solution, the original SQL statement is captured by a MyBatis interceptor and pretreated to generate a standardized SQL statement, and then an MD5 hash value is generated to query a Redis cache. If the cache is not hit, a pre-trained SQL conversion model is called by a Prompt project, and a target SQL statement is automatically generated in combination with a source database type, a target database type and a syntax mapping rule. The target SQL syntax is pre-verified by an explain execution plan, and if the verification is passed, the target SQL statement is executed and a result is obtained. If the execution is successful, the result is stored in the Redis cache. If the execution fails, the target SQL statement is regenerated for a set number of times, and if the regeneration still fails, manual correction is performed, a correct SQL statement is obtained and stored in the cache. In the manual correction process, an intelligent pre-analysis technology is used to mark errors, detailed analysis and editing guidance are provided, and a verification result is fed back in real time. Finally, the correct SQL statement and the MD5 hash value are stored in the Redis, forming a closed loop. The method realizes dynamic adaptation of the database, improves migration efficiency and system stability, and effectively solves the problems of large manual adaptation workload and errors in the prior art. BRIEF DESCRIPTION OF DRAWINGS

[0020] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the description of the embodiments of the present application will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0021] Figure 1 is a flowchart of a database adaptation method according to an example embodiment;

[0022] Figure 2 is a flowchart of a database adaptation method according to an example embodiment;

[0023] Figure 3 is a block diagram of a database adaptation device according to an example embodiment;

[0024] Figure 4 is a hardware structure diagram of an electronic device according to an example embodiment;

[0025] Figure 5 is a block diagram of an electronic device according to an example embodiment. DETAILED DESCRIPTION

[0026] The embodiments of the present application will be described in detail below, and examples of the embodiments are shown in the drawings, wherein the same or similar reference numerals represent the same or similar elements or elements having the same or similar functions throughout. The embodiments described below by referring to the drawings are exemplary and are only used to explain the present application, and cannot be interpreted as a limitation on the present application.

[0027] As will be understood by one skilled in the art, the singular forms "a," "an," and "the" include plural referents unless the context clearly dictates otherwise. It should be further understood that the terms "comprise," "comprises," "comprising," "include," "includes," and / or "including" when used in this specification, specify the presence of stated features, integers, steps, operations, elements, and / or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and / or groups thereof. As will be understood by one skilled in the art, the term "connected" or "coupled to" as used herein refers to any connection or coupling, either direct or indirect, between otherwise-individual elements, and can encompass wired and / or wireless connections or couplings. As used herein, the term "and / or" comprises any and all combination of one or more of the associated listed items.

[0028] The application provides a database adaptation method, which realizes dynamic automatic adaptation of original SQL to target database syntax and efficient cache reuse by integrating an AI large model and Redis cache technology, solves the problems of large workload, long cycle, error-prone, poor system compatibility and stability of traditional manual adaptation, and is suitable for a database adaptation device, which can be an electronic device. The database adaptation method in the embodiment of the application can be applied to various scenarios, such as zero-sample deep modeling.

[0029] Please refer to Figure 1 The embodiment of the application provides a database adaptation method, which is suitable for an electronic device.

[0030] In the following method embodiment, in order to facilitate description, the execution subject of each step of the method is taken as an example for description, but this does not constitute a specific limitation.

[0031] As Figure 1 indicated, the method can include the following steps:

[0032] In step 110, an original SQL statement is obtained and preprocessed to generate a standardized SQL statement, an MD5 hash value is generated based on the standardized SQL statement, and it is queried whether a target SQL statement corresponding to the MD5 hash value exists in Redis cache.

[0033] In one possible implementation, the original SQL text without bound parameters is captured from a MyBatis interceptor, the original SQL text is preprocessed by a replaceAll method of Java to generate a standardized SQL statement, and MD5 hash operation is performed on the preprocessed standardized SQL statement to generate a unique MD5 hash value.

[0034] Further, the name of the target database is obtained from the database connection parameters, and the variable MD5 hash value and the target database name are used as a key-value pair to query whether the corresponding target SQL statement exists in the Redis cache, and if it exists, the target SQL statement is directly used.

[0035] Specifically, the MyBatis interceptor is used to capture the original SQL text without bound parameters before SQL precompilation, preprocess the captured original SQL to remove irrelevant characters such as spaces and carriage returns, generate a standardized SQL statement, and perform MD5 hash operation on the standardized SQL statement to generate a unique hash value as the key for subsequent cache query.

[0036] Specifically, in the SQL statement capturing stage, the Interceptor interface of MyBatis is configured to intercept the StatementHandler.prepare method to ensure that the pure original SQL text is obtained before SQL precompilation. In the preprocessing stage, redundant characters in the SQL are removed to ensure that SQL statements of different formats can be uniformly processed. The generation of MD5 hash value provides an efficient key-value pair mechanism for subsequent cache query.

[0037] In the above process, the embodiments of the application ensure the accuracy and consistency of subsequent processing by accurately capturing and standardizing the original SQL statement. The introduction of MD5 hash value provides an efficient and unique key-value pair for cache query, improves the query efficiency, and effectively reduces the processing errors caused by SQL format differences.

[0038] Step 120, if the cache is not hit, the configuration information of the original SQL statement is obtained, and the pre-trained SQL conversion model is called through the Prompt project to generate the target SQL statement according to the configuration information.

[0039] In one possible implementation, when the corresponding target SQL statement is not hit in the Redis cache, the input parameters are prepared through the Prompt project, and the input parameters are input into the pre-trained SQL conversion model.

[0040] The input parameters include the standardized original SQL statement, the source database type, the target database type, and the pre-trained database syntax mapping rule example, etc., which are not limited here.

[0041] The SQL conversion model adopts a Transformer architecture including an encoder and a decoder; the SQL conversion model is used to convert the original SQL statement into a syntax format supported by the target database according to the pre-trained database syntax mapping rule.

[0042] The encoder part of the SQL conversion model comprises a plurality of self-attention mechanism modules, a multi-head attention mechanism, and a feedforward neural network.

[0043] In one possible implementation, the training process of the SQL conversion model comprises: obtaining a training data set and performing cleaning, conversion, and normalization processing, randomly initializing the parameters of the SQL conversion model in a supervised learning manner, dividing the preprocessed training data set into a plurality of batches for multi-round iterative training, calculating the output through forward propagation and comparing it with the target output during the training process, and calculating the cross-entropy loss function.

[0044] Further, the gradient of the model parameters is calculated using the back propagation algorithm to calculate the cross-entropy loss function, and the parameters of the SQL conversion model are updated through an optimization algorithm to minimize the loss function until the cross-entropy loss function converges or reaches a preset training round number.

[0045] The training data set comprises table structure information, SQL statement data, and database operation log data of a relational database, and the like; and the optimization algorithm comprises a stochastic gradient descent, an Adam optimization algorithm, and the like, which are not limited herein.

[0046] Specifically, the MD5 hash value and the target database name are used as a key-value pair to query whether the corresponding target SQL statement exists in the Redis cache. If the cache is not hit, the pre-trained SQL conversion model is called through the Prompt engineering to generate the target SQL statement according to the configuration information.

[0047] Specifically, in the cache query stage, the high-performance key-value pair feature of Redis is used to quickly retrieve whether the adapted target SQL statement exists. If it is not hit, the AI model conversion stage is entered. The input parameters are designed through the Prompt engineering to guide the AI model to generate a SQL statement that conforms to the target database syntax.

[0048] In the above process, the cache query mechanism is used to significantly reduce the time-consuming of repeated processing of the same SQL statement and improve the system response speed. The introduction of the AI model realizes the automatic conversion of the SQL statement, avoids the tediousness and errors of manual adaptation, and improves the conversion accuracy and efficiency.

[0049] In step 130, the syntax of the target SQL statement is pre-verified through the explain execution plan, and the target SQL statement is executed and the running result is obtained after the verification is passed. If the running is successful, the target SQL statement and the MD5 hash value are stored in the Redis cache.

[0050] Specifically, the target SQL statement generated by the AI model is pre-verified for syntax correctness by explain execution plan. After verification, the target SQL statement is executed and the execution result is obtained. If the execution is successful, the target SQL statement and the corresponding MD5 hash value are stored in the Redis cache.

[0051] Wherein, before SQL execution, the target SQL statement is pre-verified for syntax by explain execution plan to ensure the executability of the SQL statement in the target database. After verification, the SQL statement is executed formally and the result is obtained. If the execution is successful, the target SQL statement and its MD5 hash value are stored in the Redis cache for subsequent query.

[0052] In the above process, the pre-verification mechanism of the embodiment of the application effectively avoids execution failure caused by SQL syntax errors, improving system stability. The cache mechanism after successful execution further improves system query efficiency and reduces the time-consuming of repeated processing of the same SQL statement.

[0053] Step 140, if the running fails, the target SQL statement is regenerated according to the set number of times, and if the running still fails after reaching the set number of times, the SQL statement with execution failure is manually corrected to obtain the correct SQL statement, and the correct SQL statement and the corresponding MD5 hash value are stored.

[0054] In one possible implementation, the intelligent pre-analysis technology is used to preliminarily scan the SQL statement with execution failure and mark obvious errors, a detailed error analysis window is popped up for the marked area to explain the error reason and provide editing guidance, and after manual correction, a real-time verification mechanism is used to feedback the verification result.

[0055] Further, the correct SQL statement is obtained after the verification result is passed, the correct SQL statement is comprehensively verified and encapsulated to obtain a correction case, the correction case is intelligently classified by using a machine learning algorithm and a multi-dimensional index is constructed, the correction case is added to a knowledge base and actual effect data is collected by using a feedback mechanism to evaluate the case quality and effectiveness.

[0056] Further, the correct SQL statement is comprehensively verified for the last time, if the verification is passed, the correct SQL statement is marked as valid, otherwise the submission operation is prevented and a correction is prompted, the combination of the MD5 hash value of the original SQL statement and the target database name is used as the key in the key-value pair, and the correct SQL statement is used as the value in the key-value pair to construct a key-value pair, and the HSET command of Redis is used to set the key-value pair to the Redis cache.

[0057] Wherein, the comprehensive verification includes syntax correctness, semantic consistency, compatibility with the database, etc., which are not limited here.

[0058] Specifically, the SQL statement that fails to execute is manually corrected, the correct SQL statement is obtained, the correction process is encapsulated into a case, intelligent classification and index construction are performed, the correct SQL statement and the corresponding MD5 hash value are stored in the Redis cache, and the knowledge base of the AI model is updated.

[0059] Specifically, for the SQL statement that fails to execute, manual correction is performed through the user interface, the system provides functions such as preliminary problem annotation, error detailed analysis, and editing guidance to help users quickly locate and correct errors. After the correction is completed, the correction process is encapsulated into a case, machine learning algorithms are used for intelligent classification and index construction, so that the AI model can be referenced in subsequent processing. Finally, the correct SQL statement and the corresponding MD5 hash value are stored in the Redis cache, and the knowledge base of the AI model is updated to improve the learning efficiency and accuracy of the model.

[0060] In the above process, the embodiment of the application effectively solves the errors that may occur when the AI model processes complex SQL statements through the manual correction mechanism, improving the adaptability of the system. The encapsulation of the correction case and the updating of the knowledge base further improve the learning efficiency and accuracy of the AI model, providing strong support for subsequent processing.

[0061] Through the above process, the application integrates AI large models and Redis cache technology to achieve dynamic automatic adaptation of original SQL to target database syntax and efficient cache reuse. The method includes four main steps: SQL statement capture and standardization, cache query and AI model conversion, SQL execution and result processing, manual correction and cache updating. Each step is designed with careful technical features to ensure accuracy, efficiency, and stability. Overall, the method provides strong technical support for enterprise database migration and adaptation, effectively solving the problems of large workload, long cycle, and error-prone of traditional manual adaptation methods.

[0062] In an exemplary embodiment, the process of the database adaptation method of the application in the e-commerce order query system is demonstrated.

[0063] As shown in Figure 2 , it can include the following steps:

[0064] Step S1, SQL statement standardization.

[0065] Specifically, in the e-commerce order query system, after the application receives the user's request to query the order, the original SQL text is generated, such as querying the order information of a certain user in a specific time period, for example: "SELECT * FROM orders WHEREuser_id = [user_id] AND order_time BETWEEN [start_time] AND [end_time]". The interceptor captures the original SQL text without binding parameters through the MyBatis interceptor mechanism, and uses the replaceAll method of Java to remove irrelevant characters such as spaces and carriage returns, to generate a standardized SQL statement.

[0066] Further, the standardized SQL statement is subjected to MD5 hash operation to generate a unique MD5 hash value, and the name of the target database is obtained from the database connection parameters.

[0067] In the above process, the embodiments of the present application ensure the accuracy and uniqueness of SQL processing by standardizing the SQL statement and generating a unique hash value, providing a basis for subsequent cache query and conversion.

[0068] Step S2, cache query.

[0069] Specifically, the interceptor uses the generated MD5 hash value and the target database name as a key-value pair to query whether the corresponding target SQL statement exists in the Redis cache service. Redis cache can quickly respond to query requests due to its high-performance key-value pair storage characteristics.

[0070] In the above process, the embodiments of the present application aim to reduce repeated SQL conversion and database query operations, and improve system response speed and performance through the cache query mechanism.

[0071] Step S3, large model converts SQL.

[0072] Specifically, if the cache is not hit, the interceptor prepares input parameters through the Prompt engineering, including the standardized original SQL statement, the source database type (such as MySQL), the target database type (such as Oracle), and the pre-trained database syntax mapping rule example.

[0073] Further, these input parameters are input into the pre-trained SQL conversion large model, which adopts a Transformer architecture containing an encoder and a decoder, and converts the original SQL statement into a syntax format supported by the target database according to the pre-trained database syntax mapping rule.

[0074] The SQL conversion large model adopts a Transformer architecture including an encoder and a decoder.

[0075] The self-attention mechanism module can capture semantic information and structural features of input data from different angles, the multi-head attention mechanism works in parallel through multiple "heads", further enhancing the extraction ability of data features, and the feedforward neural network further processes and converts the features output by the attention mechanism.

[0076] The decoder adopts an attention mechanism-based architecture, interacts with the output of the encoder through the attention mechanism, focuses on the encoded information related to the current generation task, and generates accurate adaptive results. At the same time, the encoder and the decoder both contain normalization layers to normalize the input of each layer, accelerate the model training process and reduce the problems of gradient vanishing and gradient explosion.

[0077] Specifically, in the model training phase, first, a training data set is obtained, which covers various types and sizes of database-related data, including table structure information of relational databases (such as MySQL, Oracle, SQL Server, etc.), at least 1000 different table structure samples are collected for each type of database; a large number of SQL statement data, covering simple queries, complex join queries, subqueries and other syntax structures, with a quantity of more than 100,000; and database operation log data, with a record number of more than 500,000.

[0078] Further, after cleaning, converting and normalizing these data, the model parameters are randomly initialized in a supervised learning manner. The preprocessed training data is divided into multiple batches for multiple rounds of iterative training. In each training batch, the database structure data, SQL statement data and corresponding target output are input into the model, the output is calculated through forward propagation and compared with the target output, and the cross-entropy loss function is calculated.

[0079] Further, the loss function is calculated using the backpropagation algorithm to calculate the gradient of the model parameters, and the model parameters are updated through an optimization algorithm (such as stochastic gradient descent, Adam optimization algorithm) to minimize the loss function until the model loss function converges to a smaller value or reaches the preset training number of rounds.

[0080] Further, during the model training process, accuracy, recall rate, F1 value and other indicators are used to evaluate the performance of the model. The accuracy measures the proportion of the converted SQL statements that are correct according to the target database syntax; the recall rate reflects the proportion of the SQL statements that can be correctly converted among all the SQL statements that need to be correctly converted; and the F1 value is a comprehensive indicator considering accuracy and recall rate. Through these evaluation indicators, the performance of the model can be comprehensively understood, and the model can be adjusted and optimized according to the evaluation results.

[0081] Among them, when the cache is not hit, the interceptor prepares the input parameters through the Prompt project. The Prompt design follows a clear logic, first identifying the source database type and the target database type, and obtaining relevant information from the system configuration. Then input the standardized original SQL statement, while providing pre-trained database syntax mapping rule examples.

[0082] Among them, the Prompt clearly proposes conversion requirements, including: syntax adaptation, ensuring that the converted SQL conforms to the syntax specifications of the target database; function replacement, converting functions specific to the source database into equivalent or approximate functions of the target database; data type mapping, adjusting field types according to the data type system of the target database; preserving business logic, without changing the query logic, filtering conditions and calculation rules of the original SQL; special syntax processing, processing syntax structures specific to the source database; annotation, adding annotations to key modifications involved in the conversion. The output requirement is to output only the converted SQL statement.

[0083] Further, input these carefully designed input parameters into the pre-trained SQL conversion large model, and the large model converts the original SQL statement into a syntax format supported by the target database according to the pre-trained database syntax mapping rules.

[0084] In the above process, the embodiment of the application realizes the automatic adaptation of SQL statements between different database syntaxes through the intelligent conversion capability of the large model, combined with reasonable model architecture, scientific training process, effective evaluation indicators and carefully designed Prompt, avoiding the tediousness and errors of manually writing and modifying SQL scripts.

[0085] Step S4, return the converted SQL.

[0086] Specifically, the large model returns the converted target SQL statement to the interceptor.

[0087] In the above process, the embodiment of the application ensures that the converted SQL statement can be returned to the system in time for subsequent processing through the interaction between the large model and the interceptor.

[0088] Step S5, cache the converted SQL.

[0089] Specifically, the interceptor sends the converted target SQL statement to the cache service, and the cache service stores the converted SQL statement and the corresponding MD5 hash value.

[0090] Further, if a subsequent SQL query request with the same MD5 hash value is received again, the converted SQL statement can be directly obtained from the cache, improving system response speed.

[0091] In the above process, the embodiment of the application further optimizes system performance by caching the converted SQL statement, reducing the overhead of repeated conversion.

[0092] Step S6: Query the database using the converted SQL.

[0093] Specifically, the interceptor sends the converted target SQL statement to the target database for query operation to obtain the order information required by the user.

[0094] Further, the target database executes the SQL statement and returns the query result to the application, which displays the order information to the user. In the above process, the embodiment of the application realizes the cross-database order query function by using the converted SQL statement to query the target database, improving the compatibility and flexibility of the system.

[0095] Through the above process, the embodiment of the application starts from SQL statement standardization to ensure accuracy; improves system response speed through cache query; uses a large model with a specific architecture, scientific training and evaluation, combined with a carefully designed Prompt to realize intelligent conversion of SQL; returns and caches the converted SQL to optimize system performance; and finally uses the converted SQL to query the target database to realize the cross-database order query function. This method not only improves the response speed and performance of the system, but also avoids the tediousness and errors of manual adaptation, providing comprehensive, efficient and reliable technical support for cross-database applications such as e-commerce order query.

[0096] The following is an embodiment of the device of the application, which can be used to execute the database adaptation method involved in the application. For details not disclosed in the device embodiment of the application, please refer to the method embodiment of the database adaptation method involved in the application.

[0097] Please refer to Figure 3 In the embodiment of the application, a database adaptation device 800 is provided.

[0098] The database adaptation device 800 includes but is not limited to: an original SQL processing module 810, an SQL statement conversion module 830, an SQL execution and cache module 850, and a manual correction and update module 870.

[0099] The original SQL processing module 810 is configured to obtain an original SQL statement, preprocess the original SQL statement to generate a standardized SQL statement, generate an MD5 hash value based on the standardized SQL statement, and query whether a target SQL statement corresponding to the MD5 hash value exists in the Redis cache.

[0100] The SQL statement conversion module 830 is configured to, if the cache is not hit, obtain configuration information of the original SQL statement, and generate a target SQL statement based on the configuration information by calling a pre-trained SQL conversion model through a Prompt project.

[0101] The SQL execution and cache module 850 is configured to verify the syntax of the target SQL statement by an explain execution plan, execute the target SQL statement and obtain a running result after the verification is passed, and store the target SQL statement and an MD5 hash value in the Redis cache if the running is successful.

[0102] The manual correction and update module 870 is configured to, if the running fails, regenerate the target SQL statement according to a set number of times, and if the running still fails after the set number of times, manually correct an SQL statement that fails to execute to obtain a correct SQL statement and store the correct SQL statement and a corresponding MD5 hash value.

[0103] It should be noted that, in the database adaptation provided by the above embodiment, only the division of the above functional modules is exemplarily described, and in actual application, the above functions can be completed by different functional modules according to needs, that is, the internal structure of the database adaptation apparatus is divided into different functional modules to complete all or part of the above-described functions.

[0104] In addition, the database adaptation apparatus and the database adaptation method provided by the above embodiment belong to the same concept, and the specific operation manner of each module has been described in detail in the method embodiment, which will not be described here.

[0105] Figure 4 According to an exemplary embodiment, a structure of an electronic device is shown.

[0106] It should be noted that the electronic device is only an example adapted to the present application, and cannot be considered as providing any limitation on the use range of the present application. The electronic device also cannot be interpreted as needing to depend on or must have Figure 4 One or more components in the exemplary electronic device 2000 shown.

[0107] The hardware structure of the electronic device 2000 can have great differences due to different configurations or performances, such as Figure 4As shown, the electronic device 2000 comprises a power supply 210, an interface 230, at least one memory 250, and at least one central processing unit (CPU) 270.

[0108] Specifically, the power supply 210 is configured to provide working voltage for each hardware device on the electronic device 2000.

[0109] The interface 230 comprises at least one wired or wireless network interface 231 configured to interact with external devices. Of course, in the remaining examples of the present application, the interface 230 can further comprise at least one serial-parallel conversion interface 233, at least one input-output interface 235, at least one USB interface 237, and the like, as long as they are applicable to the present application. Figure 4 As shown, no specific limitation is made herein.

[0110] The memory 250, as a carrier of resource storage, can be a read-only memory, a random access memory, a magnetic disk, or an optical disk, etc. The resources stored thereon include an operating system 251, an application program 253, and data 255, etc. The storage mode can be temporary storage or permanent storage.

[0111] The operating system 251 is configured to manage and control each hardware device and the application program 253 on the electronic device 2000, so as to realize the operation and processing of the central processing unit 270 on the mass data 255 in the memory 250. The operating system 251 can be Windows Server™, Mac OS X™, Unix™, Linux™, FreeBSD™, etc.

[0112] The application program 253 is computer readable instructions for completing at least one specific work based on the operating system 251. The application program 253 can comprise at least one module (not shown), and each module can contain computer readable instructions for the electronic device 2000. For example, the database adaptation apparatus can be regarded as an application program 253 deployed on the electronic device 2000. Figure 4

[0113] The data 255 can be signal information, etc., and is stored in the memory 250.

[0114] The central processing unit 270 can comprise one or more than one processor, and is configured to communicate with the memory 250 through at least one communication bus, so as to read the computer readable instructions stored in the memory 250, and further realize the operation and processing of the mass data 255 in the memory 250. For example, the database adaptation method is completed in the form of reading a series of computer readable instructions stored in the memory 250 by the central processing unit 270.

[0115] ​Moreover, the present application can also be implemented by hardware circuit or by a combination of hardware circuit and software, and therefore, the implementation of the present application is not limited to any specific hardware circuit, software and combination of both.

[0116] Referring to Figure 5 In an embodiment of the present application, an electronic device 4000 is provided, which can include a desktop computer, a notebook computer, a server, etc. with sensor identification capability.

[0117] In Figure 5 The electronic device 4000 includes at least one processor 4001 and at least one memory 4003.

[0118] The data interaction between the processor 4001 and the memory 4003 can be realized through at least one communication bus 4002. The communication bus 4002 can include a channel for transmitting data between the processor 4001 and the memory 4003. The communication bus 4002 can be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. The communication bus 4002 can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 5 In the figure, only one thick line is used to represent the communication bus 4002, but it does not mean that there is only one bus or only one type of bus.

[0119] Optionally, the electronic device 4000 can further include a transceiver 4004, which can be used for data interaction, such as data sending and / or data receiving, etc. between the electronic device and other electronic devices. It should be noted that the transceiver 4004 is not limited to one in actual application, and the structure of the electronic device 4000 does not constitute a limitation on the embodiments of the present application.

[0120] The processor 4001 can be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array) or other programmable logic device, transistor logic device, hardware component, or any combination thereof. The processor 4001 can implement or execute various exemplary logical blocks, modules, and circuits described in conjunction with the present disclosure. The processor 4001 can also be a combination implementing a computing function, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, and the like.

[0121] The memory 4003 can be a ROM (Read Only Memory) or other type of static storage device that can store static information and instructions, a RAM (Random Access Memory) or other type of dynamic storage device that can store information and instructions, an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory) or other optical disk storage, a magneto-optical disk storage, a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store desired program instructions in the form of instructions or data structures and that can be accessed by the electronic device 4000, but is not limited thereto.

[0122] The memory 4003 stores computer-readable instructions, which can be read by the processor 4001 through the communication bus 4002.

[0123] The computer-readable instructions are executed by the one or more processors 4001 to implement the database adaptation method in the above embodiments.

[0124] In addition, the present embodiment provides a storage medium, which stores computer-readable instructions, and the computer-readable instructions are executed by one or more processors to implement the database adaptation method as described above.

[0125] The embodiment of the present application provides a computer program product, the computer program product comprises computer readable instructions, the computer readable instructions are stored in a storage medium, one or more processors of an electronic device read the computer readable instructions from the storage medium, load and execute the computer readable instructions, so that the electronic device implements the database adaptation method as described above.

[0126] Compared with the related art, the present application has the following beneficial effects:

[0127] 1. The present application can develop and adapt multiple databases at one time, compatible with heterogeneous database environment; by integrating artificial intelligence big model, real-time analysis and conversion of original SQL statements issued by application, automatic conversion of original SQL statements into syntax format supported by target database, avoiding tedious work of manual modification and adaptation of SQL code by developers for different databases.

[0128] 2. The present application has the effect of improving system compatibility and stability; through the standardized conversion of AI model, the errors that may occur in manual adaptation are avoided, the accurate execution of SQL statements in different database environments is ensured, and the compatibility and stability of the system are improved.

[0129] 3. The present application can reduce labor cost and adaptation period; developers do not need to adapt multiple databases in the development stage, and also save the tedious work of manual modification of SQL during deployment, which significantly reduces the labor cost and adaptation period.

[0130] 4. The present application has the effect of improving system response speed and performance; through the Redis caching mechanism, the SQL files converted to the target database are stored in the cache, reducing the time-consuming of processing SQL and increasing the performance of system querying database.

[0131] It should be understood that although each step in the flowchart of the accompanying drawings is displayed in sequence according to the direction of the arrow, these steps are not necessarily executed in sequence according to the direction of the arrow. Unless otherwise specified herein, the execution of these steps is not strictly limited in sequence, and they can be executed in other sequences. Moreover, at least part of the steps in the flowchart of the accompanying drawings can include multiple sub-steps or multiple stages, which are not necessarily executed at the same time, but can be executed at different times, and the execution sequence is not necessarily sequential, but can be executed in rotation or alternation with at least part of other steps or sub-steps or stages of other steps.

[0132] The above only describes some embodiments of the present application, and it should be pointed out that for ordinary skilled in the art, without departing from the principles of the present application, a number of improvements and refinements can be made, and these improvements and refinements should be regarded as the protection scope of the present application.

Claims

1. A database adaptation method, characterized in that, The method includes: Capture raw SQL text without bound parameters from MyBatis interceptors, and preprocess the raw SQL text using Java's replaceAll method to generate standardized SQL statements; Perform an MD5 hash operation on the preprocessed standardized SQL statement to generate a unique MD5 hash value, and obtain the name of the target database from the database connection parameters; Using the MD5 hash value and the target database name as key-value pairs, query the Redis cache to see if the corresponding target SQL statement already exists. If it does, use the target SQL statement directly. When the corresponding target SQL statement is not found in the Redis cache, the input parameters are prepared through the Prompt project. The input parameters include the standardized original SQL statement, the source database type, the target database type, and pre-trained database syntax mapping rule examples. The input parameters are input into a pre-trained SQL transformation model; the SQL transformation model adopts a Transformer architecture that includes an encoder and a decoder; the SQL transformation model is used to convert the original SQL statement into a syntax format supported by the target database according to the pre-trained database syntax mapping rules. The syntax of the target SQL statement is pre-verified by the execution plan using explain. If the verification is successful, the target SQL statement is executed and the running result is obtained. If the execution is successful, the target SQL statement and the MD5 hash value are stored in the Redis cache. If the execution fails, the target SQL statement will be regenerated a set number of times. If the execution still fails after the set number of times, the failed SQL statement will be manually corrected to obtain the correct SQL statement, and the correct SQL statement and its corresponding MD5 hash value will be stored. The step of manually correcting failed SQL statements to obtain correct SQL statements and storing the correct SQL statements and their corresponding MD5 hash values ​​includes: The system uses intelligent pre-analysis technology to initially scan for SQL statements that fail to execute and marks obvious errors. For the marked areas, a detailed error analysis window pops up to explain the reasons for the errors and provides editing guidance. After manual correction, a real-time verification mechanism is used to provide immediate feedback on the verification results. After the verification result is passed, the correct SQL statement is obtained. The correct SQL statement is fully verified and encapsulated to obtain the correction case. The correction case is intelligently classified and a multi-dimensional index is constructed using machine learning algorithms. It is added to the knowledge base and a feedback mechanism is used to collect actual effect data to evaluate the quality and effectiveness of the case.

2. The database adaptation method as described in claim 1, characterized in that, The encoder of the SQL conversion model includes multiple self-attention mechanism modules, a multi-head attention mechanism, and a feedforward neural network; the decoder of the SQL conversion model adopts an attention mechanism-based architecture; both the encoder and the decoder include a normalization layer.

3. The database adaptation method as described in claim 1, characterized in that, The training process of the SQL conversion model includes: Obtain the training dataset and perform cleaning, transformation, and normalization processing; the training dataset includes table structure information of the relational database, SQL statement data, and database operation log data; The parameters of the SQL conversion model are randomly initialized using supervised learning. The preprocessed training dataset is divided into multiple batches for multiple rounds of iterative training. During the training process, the output is calculated through forward propagation and compared with the target output to calculate the cross-entropy loss function. The gradient of the cross-entropy loss function with respect to the model parameters is calculated using the backpropagation algorithm, and the parameters of the SQL transformation model are updated using an optimization algorithm to minimize the loss function until the cross-entropy loss function converges or reaches a preset number of training rounds; the optimization algorithm includes stochastic gradient descent and Adam optimization algorithm.

4. The database adaptation method as described in claim 1, characterized in that, The step of manually correcting failed SQL statements to obtain correct SQL statements and storing the correct SQL statements and their corresponding MD5 hash values ​​also includes: Perform a final full verification on the correct SQL statement; the full verification includes syntax correctness, semantic consistency, and database compatibility. If the verification passes, the correct SQL statement is marked as valid; otherwise, the submission operation is blocked and a correction is prompted. The combination of the MD5 hash value of the original SQL statement and the target database name is used as the key in the key-value pair, and the correct SQL statement is used as the value in the key-value pair. The key-value pair is then set into the Redis cache using the Redis HSET command.

5. A database adaptation device, characterized in that, The apparatus implements the database adaptation method of claim 1, and the apparatus comprises: The raw SQL processing module is used to obtain the raw SQL statement and preprocess it to generate a standardized SQL statement, generate an MD5 hash value based on the standardized SQL statement, and query whether the Redis cache has a target SQL statement corresponding to the MD5 hash value. The SQL statement conversion module is used to obtain the configuration information of the original SQL statement if the cache is not hit, and to generate the target SQL statement by calling the pre-trained SQL conversion model through the Prompt project based on the configuration information. The SQL execution and caching module is used to pre-verify the syntax of the target SQL statement through the explain execution plan. After the verification is successful, the target SQL statement is executed and the running result is obtained. If the running is successful, the target SQL statement and MD5 hash value are stored in the Redis cache. The manual correction and update module is used to regenerate the target SQL statement a set number of times if the execution fails. If the execution still fails after the set number of times, the failed SQL statement is manually corrected to obtain the correct SQL statement, and the correct SQL statement and its corresponding MD5 hash value are stored.

6. An electronic device, characterized in that, include: At least one processor and at least one memory, wherein, The memory stores computer-readable instructions; The computer-readable instructions are executed by one or more of the processors, causing the electronic device to implement the database adaptation method as described in any one of claims 1 to 4.

7. A storage medium having computer-readable instructions stored thereon, characterized in that, The computer-readable instructions are executed by one or more processors to implement the database adaptation method as described in any one of claims 1 to 4.

Images