CN115033592A - SQL statement processing method, device, equipment and storage medium based on database - Google Patents
SQL statement processing method, device, equipment and storage medium based on database Download PDFInfo
- Publication number
- CN115033592A CN115033592A CN202210688587.4A CN202210688587A CN115033592A CN 115033592 A CN115033592 A CN 115033592A CN 202210688587 A CN202210688587 A CN 202210688587A CN 115033592 A CN115033592 A CN 115033592A
- Authority
- CN
- China
- Prior art keywords
- statement
- source
- target
- function
- sql statement
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2448—Query languages for particular applications; for extensibility, e.g. user defined types
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/289—Object oriented databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application provides a method, a device, equipment and a storage medium for SQL statement processing based on a database, and belongs to the technical field of data processing. The method comprises the following steps: acquiring a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement; performing type analysis on the source SQL statement, and determining the source database type of the source SQL statement; determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating a source SQL statement according to the target keyword to obtain a first SQL statement; and determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain the target SQL statement. According to the method and the device, the source SQL statement is converted into the target SQL statement, the processing difficulty of the SQL statement can be reduced, the development efficiency is improved, and statement errors are avoided.
Description
Technical Field
The present application relates to, but not limited to, the field of data processing technologies, and in particular, to a method, an apparatus, a device, and a storage medium for processing an SQL statement based on a database.
Background
When different types of database systems are used, corresponding SQL statements are required to access data and to query, update, and manage the database systems.
At present, in order to reduce the development difficulty, a developer usually uses a part of SQL statements of an old project when developing a new project, and if the database type of the new project is different from the database type of the old project, because SQL syntax of different database types is different, when the developer is unfamiliar with the SQL syntax corresponding to the new project or the old project, the processing difficulty of the SQL statements is large, so that the development efficiency is low, and statement errors are easy to occur.
Disclosure of Invention
The following is a summary of the subject matter described in detail herein. This summary is not intended to limit the scope of the claims.
The embodiment of the application provides a method, a device, equipment and a storage medium for processing SQL sentences based on a database, which can reduce the processing difficulty of the SQL sentences, thereby improving the development efficiency and avoiding the occurrence of sentence errors.
In order to achieve the above object, a first aspect of the embodiments of the present application provides a method for processing an SQL statement based on a database, where the method includes: acquiring a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement; performing type analysis on the source SQL statement to determine a source database type of the source SQL statement; determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating the source SQL statement according to the target keyword to obtain a first SQL statement; and determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement.
In some embodiments, the determining, based on the source database type, the target database type, and a preset function statement association table, a target function statement according to the source function statement, and updating the first SQL statement according to the target function statement to obtain a target SQL statement includes: removing the target keyword from the first SQL statement to obtain a second SQL statement; determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the second SQL statement according to the target function statement to obtain a third SQL statement; and obtaining a target SQL statement according to the first SQL statement and the third SQL statement.
In some embodiments, before the step of determining a target function statement according to the source function statement based on the source database type, the target database type, and a preset function statement association table, and updating the second SQL statement according to the target function statement to obtain a third SQL statement, the method further includes: name extraction processing is carried out on preset database data, and a database object name is determined; screening out a target object from objects of the second SQL statement based on the name of the database object, wherein the target object corresponds to the name of the database object; removing the target object from the second SQL statement.
In some embodiments, the step of creating the keyword association table comprises: acquiring a plurality of keyword sets, wherein the database types corresponding to any two keyword sets are different, and the keyword sets comprise a plurality of keywords to be associated; labeling the keywords to be associated, and determining keyword significance labels of the keywords to be associated; based on the database type, performing association processing on each keyword to be associated and the corresponding keyword meaning label to determine a first mapping relation; and creating a keyword association table according to the first mapping relation and the plurality of keyword sets.
In some embodiments, the creating of the function statement association table includes: acquiring a plurality of function statement sets, wherein the types of databases corresponding to any two function statement sets are different, and the function statement sets comprise a plurality of function statements to be associated; labeling the function statement to be associated, and determining a function statement function tag of the function statement to be associated; based on the database type, performing association processing on each function statement to be associated and the corresponding function statement function tag to determine a second mapping relation; and creating a function statement association table according to the second mapping relation and the plurality of function statement sets.
In some embodiments, after the step of creating a function statement association table according to the second mapping relationship and the plurality of function statement sets, the method further includes: labeling the function statement to be associated, and determining annotation information of the function statement to be associated; and updating the function statement association table according to the annotation information.
In some embodiments, the method further comprises: acquiring query information; performing semantic analysis on the query information, and determining target function information corresponding to the function statement function tag; and determining prompt information according to the type of the target database, the function statement association table and the target function information, and determining the prompt information, wherein the prompt information is used for displaying on a preset display interface.
In order to achieve the above object, a second aspect of the embodiments of the present application provides a database-based SQL statement processing apparatus, where the apparatus includes: the system comprises an acquisition unit, a processing unit and a processing unit, wherein the acquisition unit is used for acquiring a source SQL statement and a target database type, and the source SQL statement comprises a source keyword and a source function statement; the analysis unit is used for carrying out type analysis on the source SQL statement and determining the source database type of the source SQL statement; a keyword updating unit, configured to determine a target keyword according to the source keyword based on the source database type, the target database type, and a preset keyword association table, and update the source SQL statement according to the target keyword to obtain a first SQL statement; and the function statement updating unit is used for determining a target function statement according to the source function statement and updating the first SQL statement according to the target function statement to obtain the target SQL statement based on the source database type, the target database type and a preset function statement association table.
In order to achieve the above object, a third aspect of the embodiments of the present application provides an electronic device, which includes a memory, a processor, a program stored on the memory and executable on the processor, and a data bus for implementing connection communication between the processor and the memory, where the program, when executed by the processor, implements the database-based SQL statement processing method according to the first aspect.
In order to achieve the above object, a fourth aspect of the embodiments of the present application proposes a storage medium, which is a computer-readable storage medium for computer-readable storage, and the storage medium stores one or more programs, where the one or more programs are executable by one or more processors to implement the method for processing SQL statements based on a database according to the first aspect.
The embodiment of the application provides a method, a device, equipment and a storage medium for SQL statement processing based on a database, and the method comprises the following steps: acquiring a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement; performing type analysis on the source SQL statement to determine a source database type of the source SQL statement; determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating the source SQL statement according to the target keyword to obtain a first SQL statement; and determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement. According to the scheme provided by the embodiment of the application, the type of the source database is determined by performing type analysis on the source SQL sentence, then the target keyword corresponding to the source keyword is determined by the keyword association table for the target database type, the source SQL sentence is updated to obtain the first SQL sentence, then the target function sentence corresponding to the source function sentence is determined by the function sentence association table for the target database type, and then the first SQL sentence is updated to obtain the target SQL sentence, so that the source SQL sentence is converted into the target SQL sentence, the processing difficulty of the SQL sentence can be reduced, the development efficiency is improved, and the sentence error is avoided.
Additional features and advantages of the application will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by the practice of the application. The objectives and other advantages of the application may be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.
Drawings
The accompanying drawings are included to provide a further understanding of the claimed subject matter and are incorporated in and constitute a part of this specification, illustrate embodiments of the subject matter and together with the description serve to explain the principles of the subject matter and not to limit the subject matter.
FIG. 1 is a flow chart of a database-based SQL statement processing method according to an embodiment of the present application;
FIG. 2 is a flow chart of obtaining a target SQL statement according to another embodiment of the present application;
FIG. 3 is a flow chart of a method for removing a target object according to another embodiment of the present application;
FIG. 4 is a flowchart of creating a keyword association table according to another embodiment of the present application;
FIG. 5 is a flowchart of creating a function statement association table according to another embodiment of the present application;
FIG. 6 is a flow chart of updating an association table of function statements according to another embodiment of the present application;
FIG. 7 is a flow chart of determining a toast according to another embodiment of the present application;
fig. 8 is a schematic structural diagram of an SQL statement processing apparatus based on a database according to another embodiment of the present application;
fig. 9 is a schematic hardware structure diagram of an electronic device according to another embodiment of the present application.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of and not restrictive on the broad application.
In the description of the present application, the meaning of a plurality is one or more, the meaning of a plurality is two or more, and greater than, less than, more than, etc. are understood as excluding the present number, and greater than, less than, etc. are understood as including the present number.
It is noted that while functional block divisions are provided in device diagrams and logical sequences are shown in flowcharts, in some cases, steps shown or described may be performed in sequences other than block divisions within devices or flowcharts. The terms "first," "second," and the like in the description, in the claims, or in the drawings described above, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order.
First, several terms referred to in the present application are resolved:
a database is an organized, sharable, uniformly managed collection of large amounts of data that is stored long term within a computer.
Structured Query Language (SQL), a database Query and programming Language, is used to access data and to Query, update, and manage relational database systems.
Oracle Database, called Oracle for short, is a relational Database management system of Oracle corporation, has good system portability, convenient use and strong function, is suitable for various large, medium and small microcomputer environments, and has the advantages of high efficiency, good reliability and high throughput.
PostgreSQL is an object-relational database management system of free software with very complete features. PostgreSQL supports most SQL standards and offers many other modern features such as complex queries, foreign keys, triggers, views, transaction integrity, multi-version concurrency control, etc. Likewise, PostgreSQL may also be extended in many ways, such as by adding new data types, functions, operators, aggregation functions, indexing methods, procedural languages, and the like. In addition, because of the flexibility of the license, PostgreSQL can be used, modified and distributed by anyone for free for any purpose.
At present, in order to reduce the development difficulty, a developer usually uses a part of SQL statements of an old project when developing a new project, and if the database type of the new project is different from the database type of the old project, because SQL syntax of different database types is different, when the developer is unfamiliar with the SQL syntax corresponding to the new project or the old project, the processing difficulty of the SQL statements is large, so that the development efficiency is low, and statement errors are easy to occur.
Aiming at the problems that SQL statements are difficult to process, so that development efficiency is low, and statement errors are easy to occur, the application provides a database-based SQL statement processing method, a database-based SQL statement processing device, database-based SQL statement processing equipment and a database-based SQL statement processing storage medium, wherein the method comprises the following steps: acquiring a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement; performing type analysis on the source SQL statement, and determining the source database type of the source SQL statement; determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating a source SQL statement according to the target keyword to obtain a first SQL statement; and determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain the target SQL statement. According to the scheme provided by the embodiment of the application, the type of the source database is determined by performing type analysis on the source SQL sentence, then the target keyword corresponding to the source keyword is determined by the keyword association table for the target database type, the source SQL sentence is updated to obtain the first SQL sentence, then the target function sentence corresponding to the source function sentence is determined by the function sentence association table for the target database type, and then the first SQL sentence is updated to obtain the target SQL sentence, so that the source SQL sentence is converted into the target SQL sentence, the processing difficulty of the SQL sentence can be reduced, the development efficiency is improved, and the sentence error is avoided.
The method, the apparatus, the device and the storage medium for processing the SQL statements based on the database provided in the embodiments of the present application are specifically described in the following embodiments, and first, the method for processing the SQL statements based on the database in the embodiments of the present application is described.
The embodiment of the application provides a SQL statement processing method based on a database, and relates to the technical field of data processing. The SQL statement processing method based on the database can be applied to a terminal, a server side and software running in the terminal or the server side. In some embodiments, the terminal may be a smartphone, tablet, laptop, desktop computer, or the like; the server side can be configured into an independent physical server, a server cluster or a distributed system formed by a plurality of physical servers, and cloud servers for providing basic cloud computing services such as cloud service, a cloud database, cloud computing, cloud functions, cloud storage, network service, cloud communication, middleware service, domain name service, security service, CDN (content delivery network) and big data and artificial intelligence platforms; the software may be an application or the like that implements the database-based SQL statement processing method, but is not limited to the above form.
The application is operational with numerous general purpose or special purpose computing system environments or configurations. For example: personal computers, server computers, hand-held or portable devices, tablet-type devices, multiprocessor systems, microprocessor-based systems, set top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments that include any of the above systems or devices, and the like. The application may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The application may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.
In each embodiment of the present application, when data related to the user identity or characteristic, such as user information, user behavior data, user history data, and user location information, is processed, permission or consent of the user is obtained, and the data collection, use, and processing comply with relevant laws and regulations and standards of relevant countries and regions. In addition, when the embodiment of the present application needs to acquire sensitive personal information of a user, individual permission or individual consent of the user is obtained through a pop-up window or a jump to a confirmation page, and after the individual permission or individual consent of the user is definitely obtained, necessary user-related data for enabling the embodiment of the present application to operate normally is acquired.
The embodiments of the present application will be further explained with reference to the drawings.
As shown in fig. 1, fig. 1 is a flowchart of a method for processing SQL statements based on a database according to an embodiment of the present application. The SQL statement processing method based on the database comprises the following steps of:
step S110, obtaining a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement;
step S120, performing type analysis on the source SQL statement, and determining the source database type of the source SQL statement;
step S130, based on the source database type, the target database type and a preset keyword association table, determining a target keyword according to the source keyword, and updating the source SQL statement according to the target keyword to obtain a first SQL statement;
step S140, based on the source database type, the target database type, and the preset function statement association table, determining a target function statement according to the source function statement, and updating the first SQL statement according to the target function statement to obtain the target SQL statement.
It can be understood that the source keyword and the target keyword are predefined identifiers having special meanings, each keyword has special meanings in the source database type and the target database type, each special meaning corresponds to a source keyword and a target keyword, the source keyword and the target keyword can be the same or different, and the keyword association table is used for determining the mapping relationship between the source keyword and the target keyword, so that the target keyword is determined through the source keyword and the keyword association table, and the source SQL statement is updated to obtain the first SQL statement; the source function statement and the target function statement respectively comprise a function name part and a parameter part, each function statement is used for realizing a unique function in a source database type and a target database type, each unique function respectively corresponds to one source function statement and one target function statement, and the source function statement and the target function statement can be the same or different; the function statements are identical, namely the function name parts are identical, and the parameter parts are also identical in structure; different function statements mean different function name parts or different structures of parameter parts; the function statement association table is used for determining the mapping relation between the source function statement and the target function statement, so that the target function statement is determined through the source function statement and the function statement association table, when the function statements are different, the function name part of the source function statement needs to be updated, or the structure of the parameter part of the source function statement needs to be updated, so that the source function statement is updated into the target function statement, and then the first SQL statement is updated to obtain the target SQL statement; based on the method, the type of the source database is determined by performing type analysis on the source SQL sentence, then the target keyword corresponding to the source keyword is determined through the keyword association table aiming at the target database type, the source SQL sentence is further updated to obtain the first SQL sentence, then the target function sentence corresponding to the source function sentence is determined through the function sentence association table aiming at the target database type, and then the first SQL sentence is updated to obtain the target SQL sentence, so that the conversion of the source SQL sentence into the target SQL sentence is realized, the processing difficulty of the SQL sentence can be reduced, the development efficiency is improved, and the sentence errors are avoided.
It should be noted that, for each combination of the source database type and the target database type, a keyword association table and a function statement association table are determined.
It should be noted that the method for determining the keyword and the function statement corresponding to each database type belongs to the technologies known to those skilled in the art, and is not described herein again.
In particular practice, when the database type is Oracle, the keywords include, but are not limited to: SELECT, FROM, LEFT JOIN … ON, RIGHT JOIN … ON, WHERE, GROUP BY, ORDER BY, and UPDATE; functional statements include, but are not limited to: and in the NVL (A, B) statement, when A is a null value, the value is assigned as B.
Referring to fig. 2, in an embodiment, step S140 in the embodiment shown in fig. 1 includes, but is not limited to, the following steps:
step S210, removing the target keyword from the first SQL statement to obtain a second SQL statement;
step S220, determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating a second SQL statement according to the target function statement to obtain a third SQL statement;
and step S230, obtaining a target SQL statement according to the first SQL statement and the third SQL statement.
It can be understood that the first SQL statement includes a target keyword, the target keyword in the first SQL statement is removed to obtain a second SQL statement, then the update pair quadrant of the target function statement is determined as the second SQL statement, when the second SQL statement is updated, because the target keyword has already been removed by the second SQL statement, an object to be analyzed in the second SQL statement can be reduced, and the update efficiency of the second SQL statement is improved.
In a specific implementation, when the source database type is Oracle and the target database type is PostgreSQL, and the source SQL is as follows:
SELECT NVL(t.is_msg,'X1')FORM work_task_detail td LEFT JOIN work_task_table t ON td.task_id＝t.task_id WHERE td.task_index＝'X2'；
in source SQL, the source key includes: SELECT, FORM, LEFT JOIN … ON, WHERE;
the target keywords corresponding to the source keywords include: SELECT, FORM, LEFT JOIN … ON, WHERE;
updating the source SQL based on the target keywords to obtain a first SQL statement, and then removing the target keywords in the first SQL statement to obtain a second SQL statement, wherein the second SQL statement comprises the following steps:
NVL(t.is_msg,'X1')work_task_detail td work_task_table t td.task_id＝t.task_id td.task_index＝'X2'；
in the second SQL statement, the source function statement includes: NVL (t.is _ msg, 'X1');
the target function statement corresponding to the source function statement comprises: covesce (t.is _ msg, 'X1');
updating the second SQL statement based on the target function statement to obtain a third SQL statement, wherein the third SQL statement is as follows:
COALESCE(t.is_msg,'X1')work_task_detail td work_task_table ttd.task_id＝t.task_id td.task_index＝'X2'；
and then integrating the first SQL statement and the third SQL statement, and inserting the removed target keyword into a corresponding position in the third SQL statement to obtain a target SQL statement, wherein the target SQL statement comprises the following steps:
SELECT COALESCE(t.is_msg,'X1')FORM work_task_detail td LEFT JOIN work_task_table t ON td.task_id＝t.task_id WHERE td.task_index＝'X2'；
in the source SQL statement, X1 refers to the first argument, X2 refers to the second argument, and NVL (t.is _ msg, 'X1') refers to when t.is _ msg is null, t.is _ msg is assigned as X1, and X2 can be set according to actual requirements.
In addition, referring to fig. 3, in an embodiment, before step S230 in the embodiment shown in fig. 2, the following steps are further included, but not limited to:
step S310, name extraction processing is carried out on preset database data, and a database object name is determined;
step S320, screening out a target object from objects of the second SQL statement based on the name of the database object, wherein the target object corresponds to the name of the database object;
step S330, removing the target object from the second SQL statement.
It can be understood that, a plurality of database objects are stored in the database data, the names of the corresponding database objects can be determined by performing name extraction processing on the database objects, and then the target objects are screened out by the names of the database objects, wherein the target objects refer to table names, field names and alias names in the second SQL statement; and then integrating the first SQL statement and the third SQL statement, and inserting the removed target keyword and the target object into the corresponding position in the third SQL statement to obtain the target SQL statement, so that the development efficiency can be improved.
In a specific implementation, when the source database type is Oracle and the target database type is PostgreSQL, and the source SQL is as follows:
SELECT NVL(t.is_msg,'X1')FORM work_task_detail td LEFT JOIN work_task_table t ON td.task_id＝t.task_id WHERE td.task_index＝'X2'；
in source SQL, the source key includes: SELECT, FORM, LEFT JOIN … ON, WHERE;
the target keywords corresponding to the source keywords comprise: SELECT, FORM, LEFT JOIN … ON, WHERE;
updating the source SQL based on the target keyword to obtain a first SQL statement, and then removing the target keyword in the first SQL statement to obtain a second SQL statement, wherein the second SQL statement is as follows:
NVL(t.is_msg,'X1')work_task_detail td work_task_table t td.task_id＝t.task_id td.task_index＝'X2'；
the target object screened in the second SQL statement by the database object name comprises: td, t, work _ task _ detail, work _ task _ table, td.
Then, removing the target object from the second SQL statement to obtain:
NVL(,'X1')＝＝'X2'；
in the second SQL statement for removing the target object, the source function statement includes: NVL (, 'X1');
the target function statement corresponding to the source function statement comprises: coelsece (, 'X1');
updating the second SQL statement without the target object based on the target function statement to obtain a third SQL statement, wherein the third SQL statement is as follows:
COALESCE(,'X1')＝＝'X2'；
and then integrating the first SQL statement and the third SQL statement, and inserting the removed target keyword and the target object into a corresponding position in the third SQL statement to obtain a target SQL statement, wherein the target SQL statement is as follows:
SELECT COALESCE(t.is_msg,'X1')FORM work_task_detail td LEFT JOIN work_task_table t ON td.task_id＝t.task_id WHERE td.task_index＝'X2'；
in the source SQL statement, X1 refers to the first argument, X2 refers to the second argument, and NVL (t.is _ msg, 'X1') refers to when t.is _ msg is null, t.is _ msg is assigned as X1, and X2 can be set according to actual requirements.
Referring to fig. 4, in an embodiment, the step of creating the keyword association table in the embodiment shown in fig. 1 includes, but is not limited to, the following steps:
step S410, acquiring a plurality of keyword sets, wherein the database types corresponding to any two keyword sets are different, and the keyword sets comprise a plurality of keywords to be associated;
step S420, labeling the keywords to be associated, and determining the keyword meaning labels of the keywords to be associated;
step S430, based on the database type, associating each keyword to be associated with the corresponding keyword sense label, and determining a first mapping relation;
step S440, a keyword association table is created according to the first mapping relation and the plurality of keyword sets.
It can be understood that any keyword set refers to a set of all keywords of a corresponding database type, for each keyword set, based on the special meaning of each keyword to be associated, the keyword to be associated is labeled, a keyword meaning label is determined, then, for each database type, a first mapping relation between the keyword to be associated and the keyword meaning label is determined, and a keyword association table is created.
It should be noted that the method for determining the special meaning of each keyword to be associated belongs to the technologies well known to those skilled in the art, and is not described herein in detail.
In specific practice, when the source database type is a1 and the target database type is B1, a first mapping relationship C1 can be determined, where each keyword to be associated corresponds to one sense ID in a keyword set corresponding to the source database type, and each keyword to be associated corresponds to one sense association ID in a keyword set corresponding to the target database type; when the source database type is a1 and the target database type is B2, a first mapping relationship C2 can be determined, in the keyword set corresponding to the source database type, the meaning IDs corresponding to the keywords to be associated are the same, and in the keyword set corresponding to the target database type, each keyword to be associated is made to correspond to a new meaning association ID; the keyword association table is composed of a keyword table and a first ID table, the keyword table is composed of a database type, keywords and a meaning ID, and the first ID table is composed of a meaning ID and a meaning association ID; for example, after determining the source database type, the target database type, and the source keyword, the meaningful correlation ID of the source keyword can be determined through the keyword table of the source database type, then the meaningful correlation ID is determined through the first ID table, and finally the target keyword is determined through the keyword table of the target database type.
As shown in fig. 5, in an embodiment, the step of creating the function statement association table in the embodiment shown in fig. 1 includes, but is not limited to, the following steps:
step S510, a plurality of function statement sets are obtained, wherein the types of databases corresponding to any two function statement sets are different, and each function statement set comprises a plurality of function statements to be associated;
step S520, labeling the function sentence to be associated, and determining a function sentence function label of the function sentence to be associated;
step S530, based on the database type, performing association processing on each function statement to be associated and the corresponding function statement function tag, and determining a second mapping relation;
and step S540, creating a function statement association table according to the second mapping relation and the plurality of function statement sets.
It can be understood that any function statement set refers to a collection of all function statements of a corresponding database type, and for each function statement set, based on a unique function of each function statement to be associated, the function statement to be associated is labeled, a function statement function tag is determined, then for each database type, a second mapping relationship between the function statement to be associated and the function statement function tag is determined, and a function statement association table is created.
It should be noted that the method for determining the special meaning of each function statement to be associated belongs to the technologies well known to those skilled in the art, and is not described herein again.
In specific practice, when the source database type is D1 and the target database type is E1, the second mapping relationship F1 can be determined, where each function statement to be associated corresponds to one function ID in the function statement set corresponding to the source database type, and each function statement to be associated corresponds to one function association ID in the function statement set corresponding to the target database type; when the source database type is D1 and the target database type is E2, the second mapping relationship F2 can be determined, where the function IDs corresponding to the function statements to be associated are the same in the function statement set corresponding to the source database type, and each function statement to be associated is made to correspond to a new function association ID in the function statement set corresponding to the target database type; the function statement association table is composed of a function statement table and a second ID table, the function statement table is composed of a database type, a function statement and a function ID, and the second ID table is composed of a function ID and a function association ID; for example, after the source database type, the target database type, and the source function statement are determined, the association ID of the source function statement can be determined through the function statement table of the source database type, then the functional association ID is determined through the second ID table, and finally the target function statement is determined through the function statement table of the target database type.
As shown in fig. 6, in an embodiment, after step S540 in the embodiment shown in fig. 5, the following steps are further included, but not limited to:
step S610, labeling the function statement to be associated, and determining annotation information of the function statement to be associated;
and step S620, updating the function statement association table according to the annotation information.
It is to be understood that the comments are explanations and illustrations of the code, the purpose of which is to allow a person to more easily understand the code; when the annotation is used for writing a program, a person writing the program can explain or prompt a sentence, a program segment, a function and the like, so that the readability of a program code can be improved; therefore, by determining the annotation information of the function statement to be associated and then adding the annotation information to the function statement association table, the readability of the function statement association table by the user can be increased.
It should be noted that the method for determining the annotation information of the function statement to be associated belongs to the technologies known to those skilled in the art, and is not described herein again.
In specific practice, the example sentences of the function sentences to be associated are determined, and the example sentences of the function sentences to be associated are added into the function sentence association table, so that the readability of the function sentence association table by the user is further improved.
As shown in fig. 7, in an embodiment, the method for processing SQL statements based on a database further includes, but is not limited to, the following steps:
step S710, acquiring query information;
step S720, performing semantic analysis on the query information, and determining target function information corresponding to the function label of the function statement;
and step S730, determining prompt information according to the type of the target database, the function statement association table and the target function information, wherein the prompt information is used for displaying on a preset display interface.
It can be understood that, in the development process, when a developer needs to implement a certain function, but there is no function statement related to the function in the old project, it takes a lot of time to search for related content from the internet, and it is also necessary to verify the reliability of information; obtaining query information input by a developer, then performing semantic analysis on the query information, determining description about functions in the query information, and further determining target function information; determining a corresponding function statement function label through the target function information, and then determining a function statement for prompting by using a function statement association table, wherein the function statement for prompting is an example sentence of a function statement to be associated; and then generating prompt information through the function statement for prompting, and displaying the prompt information on a display interface, so that developers can write SQL statements according to the displayed prompt information.
It should be noted that the query information input by the developer refers to a text containing target function information input by the developer on a query interface; in addition, in order to improve the development efficiency, a developer may also input a partial SQL statement, search in the function statement association table, and find a complete SQL statement corresponding to the partial SQL statement, thereby generating corresponding information for prompting.
In addition, referring to fig. 8, the present application further provides a database-based SQL statement processing apparatus 800, including:
an obtaining unit 810, configured to obtain a source SQL statement and a target database type, where the source SQL statement includes a source keyword and a source function statement;
an analysis unit 820, configured to perform type analysis on the source SQL statement, and determine a source database type of the source SQL statement;
a keyword updating unit 830, configured to determine a target keyword according to the source keyword based on the source database type, the target database type, and a preset keyword association table, and update the source SQL statement according to the target keyword to obtain a first SQL statement;
and the function statement updating unit 840 is configured to determine a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and update the first SQL statement according to the target function statement to obtain the target SQL statement.
It can be understood that the specific implementation of the SQL statement processing apparatus 800 based on the database is substantially the same as the specific implementation of the SQL statement processing method based on the database, and is not described herein again; based on the method, the type of the source SQL statement is analyzed to determine the type of the source database, then the target keyword corresponding to the source keyword is determined through the keyword association table for the type of the target database, the source SQL statement is updated to obtain the first SQL statement, then the target function statement corresponding to the source function statement is determined through the function statement association table for the type of the target database, the first SQL statement is updated to obtain the target SQL statement, the conversion of the source SQL statement into the target SQL statement is achieved, the processing difficulty of the SQL statement can be reduced, the development efficiency is improved, and statement errors are avoided.
In addition, referring to fig. 9, fig. 9 illustrates a hardware structure of an electronic device of another embodiment, the electronic device including:
the processor 901 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an Application Specific Integrated Circuit (ASIC), or one or more Integrated circuits, and is configured to execute a relevant program to implement the technical solution provided in the embodiment of the present Application;
the Memory 902 may be implemented in the form of a Read Only Memory (ROM), a static storage device, a dynamic storage device, or a Random Access Memory (RAM). The memory 902 may store an operating system and other application programs, and when the technical solution provided in the embodiments of the present specification is implemented by software or firmware, the related program codes are stored in the memory 902, and the processor 901 is used to call and execute the SQL statement processing method based on the database according to the embodiments of the present application, for example, the method steps S110 to S140 in fig. 1, the method steps S210 to S230 in fig. 2, the method steps S310 to S330 in fig. 3, the method steps S410 to S440 in fig. 4, the method steps S510 to S540 in fig. 5, the method steps S610 to S620 in fig. 6, and the method steps S710 to S730 in fig. 7 described above are executed;
an input/output interface 903 for implementing information input and output;
a communication interface 904, configured to implement communication interaction between the device and another device, where communication may be implemented in a wired manner (e.g., USB, network cable, etc.), or in a wireless manner (e.g., mobile network, WIFI, bluetooth, etc.);
a bus 905 that transfers information between various components of the device (e.g., the processor 901, the memory 902, the input/output interface 903, and the communication interface 904);
wherein the processor 901, the memory 902, the input/output interface 903 and the communication interface 904 enable a communication connection within the device with each other through a bus 905.
An embodiment of the present application further provides a storage medium, which is a computer-readable storage medium and is used for computer-readable storage, where the storage medium stores one or more programs, and the one or more programs are executable by one or more processors to implement the above-described SQL statement processing method based on a database, for example, the method steps S110 to S140 in fig. 1, the method steps S210 to S230 in fig. 2, the method steps S310 to S330 in fig. 3, the method steps S410 to S440 in fig. 4, the method steps S510 to S540 in fig. 5, the method steps S610 to S620 in fig. 6, and the method steps S710 to S730 in fig. 7 described above are performed.
The memory, which is a non-transitory computer readable storage medium, may be used to store non-transitory software programs as well as non-transitory computer executable programs. Further, the memory may include high speed random access memory, and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid state storage device. In some embodiments, the memory optionally includes memory located remotely from the processor, and these remote memories may be connected to the processor through a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The SQL statement processing method, the device, the equipment and the storage medium based on the database, provided by the embodiment of the application, are characterized in that a source SQL statement and a target database type are obtained, wherein the source SQL statement comprises a source keyword and a source function statement; performing type analysis on the source SQL statement to determine a source database type of the source SQL statement; determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating the source SQL statement according to the target keyword to obtain a first SQL statement; determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement; based on the method, the type of the source SQL statement is analyzed to determine the type of the source database, then the target keyword corresponding to the source keyword is determined through the keyword association table for the type of the target database, the source SQL statement is updated to obtain the first SQL statement, then the target function statement corresponding to the source function statement is determined through the function statement association table for the type of the target database, the first SQL statement is updated to obtain the target SQL statement, the conversion of the source SQL statement into the target SQL statement is achieved, the processing difficulty of the SQL statement can be reduced, the development efficiency is improved, and statement errors are avoided.
The embodiments described in the embodiments of the present application are for more clearly illustrating the technical solutions of the embodiments of the present application, and do not constitute a limitation to the technical solutions provided in the embodiments of the present application, and it is obvious to those skilled in the art that the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems with the evolution of technology and the emergence of new application scenarios.
It will be appreciated by those skilled in the art that the solutions shown in fig. 1 to 7 do not constitute a limitation of the embodiments of the present application, and may comprise more or less steps than those shown, or some steps may be combined, or different steps.
The above-described embodiments of the apparatus are merely illustrative, wherein the units illustrated as separate components may or may not be physically separate, i.e. may be located in one place, or may also be distributed over a plurality of network elements. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
One of ordinary skill in the art will appreciate that all or some of the steps of the methods, systems, functional modules/units in the devices disclosed above may be implemented as software, firmware, hardware, and suitable combinations thereof.
The terms "first," "second," "third," "fourth," and the like in the description of the application and the above-described figures, if any, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the application described herein are capable of operation in sequences other than those illustrated or described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
It should be understood that in the present application, "at least one" means one or more, "a plurality" means two or more. "and/or" is used to describe the association relationship of the associated object, indicating that there may be three relationships, for example, "a and/or B" may indicate: only A, only B and both A and B are present, wherein A and B may be singular or plural. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship. "at least one of the following" or similar expressions refer to any combination of these items, including any combination of single item(s) or plural items. For example, at least one (one) of a, b, or c, may represent: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", wherein a, b, c may be single or plural.
In the several embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the above-described division of units is only one type of division of logical functions, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.
The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.
In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit.
The integrated unit, if implemented in the form of a software functional unit and sold or used as a separate product, may be stored in a computer readable storage medium. Based on such understanding, the technical solutions of the present application, which are essential or part of the technical solutions contributing to the prior art, or all or part of the technical solutions, may be embodied in the form of a software product stored in a storage medium, which includes multiple instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the methods of the embodiments of the present application. And the aforementioned storage medium includes: various media capable of storing programs, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk, or an optical disk.
The preferred embodiments of the present application have been described above with reference to the accompanying drawings, and the scope of the claims of the embodiments of the present application is not limited thereto. Any modifications, equivalents and improvements that may occur to those skilled in the art without departing from the scope and spirit of the embodiments of the present application are intended to be within the scope of the claims of the embodiments of the present application.
Claims (10)
1. A SQL statement processing method based on a database is characterized by comprising the following steps:
acquiring a source SQL statement and a target database type, wherein the source SQL statement comprises a source keyword and a source function statement;
performing type analysis on the source SQL statement to determine a source database type of the source SQL statement;
determining a target keyword according to the source keyword based on the source database type, the target database type and a preset keyword association table, and updating the source SQL statement according to the target keyword to obtain a first SQL statement;
and determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement.
2. The method according to claim 1, wherein the determining a target function statement according to the source function statement based on the source database type, the target database type, and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement comprises:
removing the target keyword from the first SQL statement to obtain a second SQL statement;
determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the second SQL statement according to the target function statement to obtain a third SQL statement;
and obtaining a target SQL statement according to the first SQL statement and the third SQL statement.
3. The method according to claim 2, wherein before the step of determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the second SQL statement according to the target function statement to obtain a third SQL statement, the method further comprises:
name extraction processing is carried out on preset database data, and a database object name is determined;
screening out a target object from objects of the second SQL statement based on the name of the database object, wherein the target object corresponds to the name of the database object;
removing the target object from the second SQL statement.
4. The method of claim 1, wherein the step of creating the keyword association table comprises:
acquiring a plurality of keyword sets, wherein the database types corresponding to any two keyword sets are different, and the keyword sets comprise a plurality of keywords to be associated;
labeling the keywords to be associated, and determining keyword significance labels of the keywords to be associated;
based on the database type, performing association processing on each keyword to be associated and the corresponding keyword meaning label to determine a first mapping relation;
and creating a keyword association table according to the first mapping relation and the plurality of keyword sets.
5. The method of claim 1, wherein the step of creating the function statement association table comprises:
acquiring a plurality of function statement sets, wherein the types of databases corresponding to any two function statement sets are different, and the function statement sets comprise a plurality of function statements to be associated;
labeling the function statement to be associated, and determining a function statement function tag of the function statement to be associated;
based on the database type, performing association processing on each function statement to be associated and the corresponding function statement function tag to determine a second mapping relation;
and creating a function statement association table according to the second mapping relation and the plurality of function statement sets.
6. The method according to claim 5, wherein after the step of creating a function statement association table according to the second mapping relationship and the plurality of function statement sets, further comprising:
performing annotation processing on the function statement to be associated, and determining annotation information of the function statement to be associated;
and updating the function statement association table according to the annotation information.
7. The method of claim 5, further comprising:
acquiring query information;
performing semantic analysis on the query information, and determining target function information corresponding to the function statement function tag;
and determining prompt information according to the type of the target database, the function statement association table and the target function information, wherein the prompt information is used for displaying on a preset display interface.
8. A database-based SQL statement processing apparatus, the apparatus comprising:
the system comprises an acquisition unit, a processing unit and a processing unit, wherein the acquisition unit is used for acquiring a source SQL statement and a target database type, and the source SQL statement comprises a source keyword and a source function statement;
the analysis unit is used for carrying out type analysis on the source SQL statement and determining a source database type of the source SQL statement;
a keyword updating unit, configured to determine a target keyword according to the source keyword based on the source database type, the target database type, and a preset keyword association table, and update the source SQL statement according to the target keyword to obtain a first SQL statement;
and the function statement updating unit is used for determining a target function statement according to the source function statement based on the source database type, the target database type and a preset function statement association table, and updating the first SQL statement according to the target function statement to obtain a target SQL statement.
9. An electronic device comprising a memory, a processor, a program stored on the memory and executable on the processor, and a data bus for implementing a connection communication between the processor and the memory, the program, when executed by the processor, implementing the steps of the database-based SQL statement processing method according to any of claims 1 to 7.
10. A storage medium which is a computer-readable storage medium for computer-readable storage, characterized in that the storage medium stores one or more programs which are executable by one or more processors to implement the steps of the database-based SQL statement processing method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210688587.4A CN115033592A (en) | 2022-06-17 | 2022-06-17 | SQL statement processing method, device, equipment and storage medium based on database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210688587.4A CN115033592A (en) | 2022-06-17 | 2022-06-17 | SQL statement processing method, device, equipment and storage medium based on database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115033592A true CN115033592A (en) | 2022-09-09 |
Family
ID=83125028
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210688587.4A Pending CN115033592A (en) | 2022-06-17 | 2022-06-17 | SQL statement processing method, device, equipment and storage medium based on database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115033592A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117194389A (en) * | 2023-11-08 | 2023-12-08 | 山东省国土空间数据和遥感技术研究院(山东省海域动态监视监测中心) | SQL-based database compatibility method and device, electronic equipment and storage medium |
-
2022
- 2022-06-17 CN CN202210688587.4A patent/CN115033592A/en active Pending
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117194389A (en) * | 2023-11-08 | 2023-12-08 | 山东省国土空间数据和遥感技术研究院(山东省海域动态监视监测中心) | SQL-based database compatibility method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110472068B (en) | Big data processing method, equipment and medium based on heterogeneous distributed knowledge graph | |
| US11308031B2 (en) | Resolving in-memory foreign keys in transmitted data packets from single-parent hierarchies | |
| CN108399072B (en) | Application page updating method and device | |
| AU2017268630A1 (en) | Method, device, server and storage apparatus of reviewing SQL | |
| CN106407360B (en) | Data processing method and device | |
| CN111078776A (en) | Data table standardization method, device, equipment and storage medium | |
| CN111026753A (en) | Label storage method and device based on tree structure | |
| CN115033592A (en) | SQL statement processing method, device, equipment and storage medium based on database | |
| CN111427577A (en) | Code processing method and device and server | |
| Xu et al. | Hue: A user-adaptive parser for hybrid logs | |
| CN112000690B (en) | Method and device for analyzing structured operation statement | |
| CN113760891A (en) | Data table generation method, device, equipment and storage medium | |
| US10824803B2 (en) | System and method for logical identification of differences between spreadsheets | |
| US20200110769A1 (en) | Machine learning (ml) based expansion of a data set | |
| CN105808628A (en) | Webpage transcoding method, apparatus and system | |
| CN114791882A (en) | Method and device for solving variable naming conflict, electronic equipment and storage medium | |
| CN114064601B (en) | Storage process conversion method, device, equipment and storage medium | |
| CN110471708B (en) | Method and device for acquiring configuration items based on reusable components | |
| US11170164B2 (en) | System and method for cell comparison between spreadsheets | |
| CN111753548A (en) | Information acquisition method and device, computer storage medium and electronic equipment | |
| CN111104409A (en) | Database processing method and device, storage medium and electronic equipment | |
| CN111221846B (en) | Automatic translation method and device for SQL sentences | |
| CN116185468A (en) | Application migration method, device, equipment and storage medium | |
| CN110851456B (en) | Information processing method, electronic equipment and storage medium | |
| CN118035210A (en) | Data dictionary management method, storage medium, device and product of database |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |