SQL statement comparison method, device, terminal device and storage medium

Abstract

The present application is applicable to the field of artificial intelligence technology, and provides a method, device, terminal device and storage medium for comparing SQL statements. The present application first converts two SQL statements to be compared into class objects, through which the attributes of each field in the SQL statement can be identified, so as to find out each field contained therein that can be interchangeably sorted; then, the sorting is adjusted in the same way for the fields contained in the class object that can be interchangeably sorted; then, the two class objects after the field sorting is adjusted are converted back into SQL statements, and text matching is performed on the converted SQL statements to determine whether the two SQL statements to be compared are consistent. Since the sorting methods of the interchangeable sorting fields contained in the converted SQL statements are the same, the problem of misidentification of inconsistent SQL statements due to different sorting methods of the interchangeable sorting fields can be avoided, thereby improving the accuracy of SQL statement comparison.

Description

Technical Field

[0001] The present application relates to the field of artificial intelligence technology and provides a method, apparatus, terminal device and storage medium for comparing SQL statements. Background Art

[0002] SQL (Structured Query Language) is a database query and programming language, mainly used to access data and query, update and manage relational database systems. In some application scenarios, it is necessary to compare whether the execution results of two SQL statements are consistent. The commonly used SQL statement comparison method is text matching, that is, comparing whether the texts of two SQL statements are completely consistent. If the texts are completely consistent, it is determined that the execution results of the two SQL statements are consistent.

[0003] However, the execution results of two SQL statements with inconsistent texts (for example, only the sorting methods of some interchangeable fields are different) may be the same, and using text matching to compare these two SQL statements will result in inconsistent comparison results. Therefore, the existing SQL statement comparison method has the problem of low accuracy. Summary of the invention

[0004] In view of this, the present application proposes a SQL statement comparison method, apparatus, terminal device and storage medium, which can improve the accuracy of SQL statement comparison.

[0005] In a first aspect, an embodiment of the present application provides a method for comparing SQL statements, including:

[0006] Obtain the source SQL statement and target SQL statement to be compared;

[0007] Convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object;

[0008] Adjusting the order of the exchangeable order fields contained in the class objects of the first class objects and the second class objects in the same manner to obtain updated first class objects and updated second class objects, wherein the exchangeable order fields refer to fields that have no effect on the execution result of the SQL statement after exchanging the order of the fields in the SQL statement;

[0009] Converting the updated first-category object into a first SQL statement, and converting the updated second-category object into a second SQL statement;

[0010] Performing text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result;

[0011] Determine whether the source SQL statement is consistent with the target SQL statement according to the text matching result.

[0012] The embodiment of the present application first converts the two SQL statements to be compared into class objects, through which the attributes of each field in the SQL statement can be identified, so as to find out the fields contained therein that can be exchanged and sorted; then, the fields contained in the class objects that can be exchanged and sorted are adjusted in the same way, for example, for the exchangeable and sortable fields =? and? = in the two class objects, they can be uniformly sorted according to? =; then, the two class objects after the field sorting is adjusted are converted back into SQL statements, and text matching is performed on the converted SQL statements to determine whether the two SQL statements to be compared are consistent. In the above process, the sorting methods of the exchangeable and sorted fields contained in the converted SQL statements are the same, so the problem of misidentification of inconsistent SQL statements due to different sorting methods of the exchangeable and sorted fields can be avoided, thereby improving the accuracy of SQL statement comparison.

[0013] In one embodiment of the present application, after converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the following may also be included:

[0014] Determine whether the implementation class of the first category object is the same as the implementation class of the second category object;

[0015] If the implementation class of the first category of objects is different from the implementation class of the second category of objects, determining that the source SQL statement is inconsistent with the target SQL statement;

[0016] If the implementation class of the first class object is the same as the implementation class of the second class object, the step of adjusting the sorting of the exchangeable sorting fields contained in the class objects in the same way for the first class object and the second class object and subsequent steps are performed.

[0017] In one embodiment of the present application, adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner may include:

[0018] Detecting a first target expression included in the first type of object, and detecting a second target expression included in the second type of object, wherein the first target expression and the second target expression are expressions containing front and rear two parts of fields, and swapping the order of the front and rear two parts of fields has no effect on the execution result;

[0019] The sorting of the two parts of the fields contained in the first target expression and the second target expression is adjusted in the same order.

[0020] In another embodiment of the present application, adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner may include:

[0021] Detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects;

[0022] The column name fields and table name fields included in the first category of objects, and the column name fields and table name fields included in the second category of objects are adjusted in the same sorting manner.

[0023] In one embodiment of the present application, after converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the following may also be included:

[0024] Detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects;

[0025] The aliases in the column name field and the table name field included in the first type of objects are deleted, and the aliases in the column name field and the table name field included in the second type of objects are deleted.

[0026] In one embodiment of the present application, after performing text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result, the following may also be included:

[0027] If the text matching results are inconsistent, obtaining text differences between the first SQL statement and the second SQL statement;

[0028] Each of the text difference points contained in the first SQL statement is marked according to the corresponding type.

[0029] In one embodiment of the present application, converting the source SQL statement into a first type of object and converting the target SQL statement into a second type of object may include:

[0030] The source SQL statement is converted into a first Statement object and the target SQL statement is converted into a second Statement object using a JSqlParser tool.

[0031] In a second aspect, an embodiment of the present application provides a SQL statement comparison device, including:

[0032] An SQL statement acquisition module is used to acquire source SQL statements and target SQL statements to be compared;

[0033] An SQL statement conversion module, used to convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object;

[0034] A field sorting adjustment module, configured to adjust the sorting of the exchangeable sorting fields contained in the first category object and the second category object in the same manner to obtain updated first category objects and updated second category objects, wherein the exchangeable sorting fields refer to fields that have no effect on the execution result of the SQL statement after the sorting thereof in the SQL statement is exchanged;

[0035] a class object conversion module, configured to convert the updated first class object into a first SQL statement, and convert the updated second class object into a second SQL statement;

[0036] A text matching module, used to perform text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result;

[0037] The SQL statement comparison module is used to determine whether the source SQL statement is consistent with the target SQL statement according to the text matching result.

[0038] In a third aspect, an embodiment of the present application provides a terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the SQL statement comparison method proposed in the first aspect of the embodiment of the present application is implemented.

[0039] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the SQL statement comparison method proposed in the first aspect of the embodiment of the present application is implemented.

[0040] In a fifth aspect, an embodiment of the present application provides a computer program product. When the computer program product is run on a terminal device, the terminal device executes the SQL statement comparison method proposed in the first aspect of the embodiment of the present application.

[0041] The beneficial effects that can be achieved in the second to fifth aspects mentioned above can be referred to the relevant description of the first aspect mentioned above. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0043] Figure 1 It is a flowchart of an embodiment of a method for comparing SQL statements provided in an embodiment of the present application;

[0044] Figure 2 It is a schematic diagram of the operating principle of a SQL statement comparison method provided in an embodiment of the present application;

[0045] Figure 3 It is a structural diagram of an embodiment of a SQL statement comparison device provided in an embodiment of the present application;

[0046] Figure 4 It is a schematic diagram of a terminal device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0047] In the following description, for the purpose of illustration rather than limitation, specific details such as specific system structures, technologies, etc. are proposed, so as to thoroughly understand the embodiments of the present application. However, it should be clear to those skilled in the art that the present application can also be implemented in other embodiments without these specific details. In other cases, the detailed description of well-known systems, devices, circuits and methods is omitted to prevent unnecessary details from hindering the description of the present application. In addition, in the description of the present application specification and the attached claims, the terms "first", "second", "third" etc. are only used to distinguish the description, and cannot be interpreted as indicating or suggesting relative importance.

[0048] At present, when performing SQL statement comparison, a text matching method is usually adopted, that is, comparing whether the texts of two SQL statements are completely consistent. If the texts are completely consistent, it is determined that the execution results of the two SQL statements are consistent. However, the execution results obtained by two SQL statements with inconsistent texts may also be the same, and using the text matching method to compare the two SQL statements will obtain a comparison result that the SQL statements are inconsistent. It can be seen that the accuracy of this method is low. In view of this, the present application proposes a SQL statement comparison method that can improve the accuracy of SQL statement comparison.

[0049] The embodiments of the present application can acquire and process relevant data based on artificial intelligence technology. Among them, artificial intelligence (AI) is the theory, method, technology and application system that uses digital computers or machines controlled by digital computers to simulate, extend and expand human intelligence, perceive the environment, acquire knowledge and use knowledge to obtain the best results.

[0050] AI basic technologies generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing technology, operation / interaction systems, mechatronics, etc. AI software technologies mainly include computer vision technology, robotics technology, biometrics technology, speech processing technology, natural language processing technology, and machine learning / deep learning.

[0051] It should be understood that the execution subject of the SQL statement comparison method provided in the embodiment of the present application can be a mobile phone, a tablet computer, a wearable device, a vehicle-mounted device, an augmented reality (AR) / virtual reality (VR) device, a laptop, an ultra-mobile personal computer (UMPC), a netbook, a personal digital assistant (PDA), a large-screen TV and other terminal devices or servers, and the embodiment of the present application does not impose any restrictions on the specific types of the terminal device and the server. The server here can be an independent server, or it can be a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDN), and big data and artificial intelligence platforms.

[0052] See also Figure 1 In the embodiments of the present application, a first embodiment of a method for comparing SQL statements includes:

[0053] 101. Obtain a source SQL statement and a target SQL statement to be compared;

[0054] First, two SQL statements to be compared are obtained, namely the source SQL statement and the target SQL statement, that is, it is necessary to determine whether the source SQL statement and the target SQL statement are consistent. It should be noted that the consistency here does not mean that the texts of the two SQL statements are completely consistent, but whether the execution results of the two SQL statements are consistent.

[0055] For example, a source SQL statement is:

[0056] select PROFIT_ID,ACCT_ASSET_ACCT,EXT_ASSET_ACCT,FUND_COMPANY_CODE,FUND_CODE,TOTAL_AMOUNT,TOTAL_UNITS,CURRENCY,USER_INCOME,CAPITAL_INCOME,NO_INCO ME_UNITS,HIS_TOTAL_IC_AMOUNT,INCOME_DATE,CREATE_TIME,UPDATE_TIME from T_MFBS_NF_PROFIT t where ACCT_ASSET_ACCT=? AND FUND_CODE=? AND INCOME_DATE>=? order by INCOME_DATE desc

[0057] A target SQL statement is:

[0058] select ID,ACCT_ASSET_ACCT,HISTORY_PROFIT,INCOME_DATE,CREATE_TIME,UPDATE_TIME,MEMOFROM T_MFBS_NF_HISTORY_PROFIT where ACCT_ASSET_ACCT=?

[0059] In the following steps, the above two SQL examples will be used for illustration.

[0060] 102. Convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object;

[0061] After obtaining the two SQL statements, convert them both into class objects. Class objects refer to instantiations of classes. In actual operations, SQL statements can be converted into Statement objects. Statement represents an abstract operation on the database, such as Select, Create, Drop, Insert, Delete, etc., which are all Statement implementation classes. After converting the SQL statement into a class object, you can obtain the properties of each field contained in the SQL statement, so as to distinguish which fields are exchangeable and sortable fields, which fields are deletable fields, and the functions and effects of each field, etc.

[0062] In an implementation of the embodiment of the present application, converting the source SQL statement into a first type of object and converting the target SQL statement into a second type of object may include:

[0063] The source SQL statement is converted into a first Statement object and the target SQL statement is converted into a second Statement object using a JSqlParser tool.

[0064] The JSqlParser tool is mainly used to parse and assemble SQL statements. The JSqlParser tool can convert both SQL statements into corresponding Statement objects. For the specific use of the JSqlParser tool, reference can be made to the prior art.

[0065] In an implementation of the embodiment of the present application, after converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the method may further include:

[0066] (1) determining whether the implementation class of the first category object and the implementation class of the second category object are the same;

[0067] (2) if the implementation class of the first category of objects is different from the implementation class of the second category of objects, determining that the source SQL statement and the target SQL statement are inconsistent;

[0068] (3) If the implementation class of the first class object is the same as the implementation class of the second class object, performing a step of adjusting the sorting of the exchangeable sorting fields contained in the class objects in the same manner for the first class object and the second class object and subsequent steps.

[0069] Before executing step 103, first check whether the implementation classes of the two Statement objects are the same. If they are different, it can be determined that the two SQL statements are definitely inconsistent, and the categories of the two SQL statements can be output at this time. If they are the same, continue to execute subsequent steps 103-106 to further determine whether the two SQL statements are consistent. For example, for the two SQL statements listed in 101, the implementation class of their Statement objects is "select", so it is necessary to execute subsequent steps to further determine whether the two SQL statements are consistent. On the contrary, if the implementation class of one of the Statement objects is "delete", it indicates that the types of the two SQL statements are different, one is a selection operation and the other is a deletion operation. The execution results of the two are definitely different, so it can be determined that the two SQL statements are inconsistent, and the categories of the two SQL statements can be indicated.

[0070] In an implementation of the embodiment of the present application, after converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the method may further include:

[0071] (1) detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects;

[0072] (2) Deleting the aliases in the column name field and the table name field contained in the first category of objects, and deleting the aliases in the column name field and the table name field contained in the second category of objects.

[0073] Some SQL statements contain aliases in the column name field and table name field. The aliases will not affect the execution results of the SQL statements (whether there is an alias or not is for the same data column or data table), but they can have an impact on the text matching process of the SQL statements. Therefore, after converting the two SQL statements into two class objects, the aliases in the column name field and table name field contained in the two class objects can be deleted to eliminate the impact of the aliases in the text matching process. In the specific operation, if there is an alias in the column name field, the alias is set to a space; and if there is an alias in the table name field, on the basis of setting the alias to a space, some fields that reference the alias are also set to spaces. For example, for the source SQL statements listed in step 101, in this step, the alias "t" needs to be removed from the table name field "T_MFBS_NF_PROFITt" in the source SQL.

[0074] 103. Adjust the sorting of the exchangeable sorting fields contained in the first category objects and the second category objects in the same manner to obtain updated first category objects and updated second category objects;

[0075] After converting the two SQL statements into two class objects, the class objects can be used to identify which fields in the SQL statements are exchangeable sort fields, so the order of the exchangeable sort fields contained in the two class objects can be adjusted in the same way to obtain two updated class objects. Here, exchangeable sort fields refer to fields that have no effect on the execution results of the SQL statements after exchanging their order in the SQL statements. For example, =? and ? = are exchangeable sort fields, and each parallel column name field or table name field also belongs to exchangeable sort fields.

[0076] In addition, when adjusting the sorting of exchangeable sorting fields, it is only necessary to limit the two class objects to the same sorting method, without limiting the specific sorting rules. For example, you can use dictionary order (A, B, C...), reverse dictionary order (Z, Y, X...), or any other set sorting rules.

[0077] In an implementation of the embodiment of the present application, adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner may include:

[0078] (1) detecting a first target expression contained in the first type of object and detecting a second target expression contained in the second type of object, wherein the first target expression and the second target expression are expressions containing two parts of fields, and swapping the order of the two parts of fields has no effect on the execution result;

[0079] (2) Adjust the sorting of the two fields contained in the first target expression and the second target expression in the same order.

[0080] For example, =? and ? = belong to the target expression. Exchanging the two parts of the field = and ? will not affect the execution result. At this time, the target expressions contained in the two class objects are adjusted to the same order, that is, both are adjusted to =? or both are adjusted to ? =. For the partial fields "ACCT_ASSET_ACCT=? AND FUND_CODE=? AND INCOME_DATE>=?" in the source SQL statement listed in step 101, after the adjustment of the order, it will become "?=ACCT_ASSET_ACCT AND?=FUND_CODE AND INCOME_DATE>=?".

[0081] In an implementation of the embodiment of the present application, adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner may include:

[0082] (1) detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects;

[0083] (2) The column name field and the table name field included in the first category of objects, and the column name field and the table name field included in the second category of objects are adjusted in the same sorting manner.

[0084] For example, the sorting of the column name fields and table name fields contained in the two class objects may be adjusted in a lexicographical order (A, B, C...). For the column name fields and table name fields (collectively referred to as a column set) contained in the source SQL statement listed in step 101, the sorting is adjusted in lexicographical order as follows:

[0085] ACCT_ASSET_ACCT,CAPITAL_INCOME,CREATE_TIME,EXT_ASSET_ACCT,FUND_COMPANY_PRCOFITDE,FUND_CORDE,HICURRENCY,HIDS_TOTAL_IC_AMOUNT,INCOME_DATE,NO_INCOME_UNITS,PROFIT_ID,TOTAL_AMOUNT,TOTAL_UNITS,UPDATE_TIME,USER_INCOME

[0086] The column name field and table name field included in the target SQL statement listed in step 101 are adjusted and sorted in lexicographical order as follows:

[0087] ACCT_ASSET_ACCT,CREATE_TIME,HISTORY_PROFIT,ID,INCOME_DATE,MEMO,UPDATE_TIME

[0088] In another implementation of the embodiment of the present application, adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner may include:

[0089] (1) detecting the table name field contained in the first category of objects and the sub-table name field corresponding to each table name field, and detecting the table name field contained in the second category of objects and the sub-table name field corresponding to each table name field;

[0090] (2) The following operations are performed for both the first category of objects and the second category of objects: first, the sub-table name fields corresponding to each table name field are adjusted in the same sorting manner, and then the sorting of each table name field is adjusted in the same sorting manner.

[0091] For example, if the source SQL statement contains "left join subtable X on expression A" and the target SQL statement contains "left join subtable Y on expression B", subtable X can contain multiple different subtable name fields, and subtable Y can also contain multiple different subtable name fields. During the operation, first sort the subtable name fields contained in subtable X and subtable Y in the same sorting method (for example, dictionary order). Then, take the sorted expressions A and B to form a clause set and perform set sorting.

[0092] 104. Convert the updated first-category object into a first SQL statement, and convert the updated second-category object into a second SQL statement;

[0093] Next, the two class objects after adjusting the field sorting method are converted back into SQL statements to obtain a first SQL statement corresponding to the source SQL statement and a second SQL statement corresponding to the target SQL statement. During the operation, various existing class object conversion SQL statement tools can be used. This step is the inverse operation of step 102.

[0094] For example, for the two actual SQL statements in step 101, the corresponding first SQL statement and second SQL statement are as follows:

[0095] The first SQL statement: SELECT ACCT_ASSET_ACCT,CAPITAL_INCOME,CREATE_TIME,EXT_ASSET_ACCT,FUND_COMPANY_PRCOFITDE,FUND_CORDE,HICURRENCY,HIDS_TOTAL_IC_AMOUNT,INCOME_DATE,NO_INCOME_UNITS,PROFIT_ID,TOTAL_AMOUNT,TOTAL_UNITS,UPDATE_TIME,USER_INCOME FROM T_MFBS_NF_HISTORY_PROFIT t WHERE? =ACCT_ASSET_ACCT AND? =FUND_CODE AND INCOME_DATE>=? ORDER BY INCOME_DATE DESC

[0096] The second SQL statement: SELECT ACCT_ASSET_ACCT,CREATE_TIME,HISTORY_PROFIT,ID,INCOME_DATE,MEMO,UPDATE_TIME FROM T_MFBS_NF_HISTORY_PROFIT WHERE? =ACCT_ASSET_ACCT

[0097] 105. Perform text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result;

[0098] Next, text matching is performed on the two converted SQL statements to obtain text matching results. Here, the equal comparison method of strings can be used to determine whether the texts of the two SQL statements are consistent, and obtain text matching results of consistent or inconsistent texts. In this process, since the sorting methods of the exchangeable sorting fields contained in the converted SQL statements are the same, the problem of misidentification of inconsistent SQL statements due to different sorting methods of the exchangeable sorting fields can be avoided.

[0099] In an implementation of the embodiment of the present application, after performing text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result, the following may also be included:

[0100] (1) If the text matching results are inconsistent, obtaining text differences between the first SQL statement and the second SQL statement;

[0101] (2) Each of the text distinction points contained in the first SQL statement is marked according to the corresponding type.

[0102] If the text matching results are inconsistent, it means that there are text differences between the first SQL statement and the second SQL statement. Different types of text differences can be marked in different ways. For example, the extra part can be marked in green, the less part can be marked in red and underlined, and the indifferent part has no special display. The way to mark the difference points can be to approach the first text difference point that appears from the starting point of the first SQL statement backwards, and then approach the last text difference point from the end of the first SQL statement forward, and then recursively split the smallest text difference point, and save the state (there are three types of state, insert, delete, equal) and the text object in the form of a linked list in text order.

[0103] 106. Determine whether the source SQL statement and the target SQL statement are consistent according to the text matching result.

[0104] Finally, it can be determined whether the source SQL statement and the target SQL statement are consistent, that is, whether their execution results are the same, based on the text matching result. Specifically, if the text matching results of the first SQL statement and the second SQL statement are consistent, it can be determined that the source SQL statement and the target SQL statement are consistent, and the terminal system can output a judgment result that there is no difference in the SQL statements. If the text matching results of the first SQL statement and the second SQL statement are inconsistent, it can be determined that the source SQL statement and the target SQL statement are inconsistent, and the terminal system can output a judgment result that there is a difference in the SQL statements.

[0105] The embodiment of the present application first converts the two SQL statements to be compared into class objects, through which the attributes of each field in the SQL statement can be identified, so as to find out the fields contained therein that can be exchanged and sorted; then, the fields contained in the class objects that can be exchanged and sorted are adjusted in the same way, for example, for the exchangeable and sortable fields =? and? = in the two class objects, they can be uniformly sorted according to? =; then, the two class objects after the field sorting is adjusted are converted back into SQL statements, and text matching is performed on the converted SQL statements to determine whether the two SQL statements to be compared are consistent. In the above process, the sorting methods of the exchangeable and sorted fields contained in the converted SQL statements are the same, so the problem of misidentification of inconsistent SQL statements due to different sorting methods of the exchangeable and sorted fields can be avoided, thereby improving the accuracy of SQL statement comparison.

[0106] like Figure 2 FIG. 1 is a schematic diagram showing the operating principle of the SQL statement comparison method proposed in this application. Figure 2 In the method, firstly, the two SQL statements to be compared are converted into class objects, and it is determined whether the implementation classes of the two class objects are the same; then, if the implementation classes of the two class objects are different, the result of inconsistent SQL statements can be output; if the implementation classes of the two class objects are the same, the sorting of the exchangeable sorting fields contained in the two class objects is adjusted in the same way to obtain two updated class objects; then, the two updated class objects are converted back into SQL statements, and then text matching is performed on the converted SQL statements; finally, according to the result of the text matching, a judgment is made, and the result of consistent SQL statements or inconsistent SQL statements is output. In addition, when the result of inconsistent SQL statements is output, the difference points of the two SQL statements can also be displayed at the same time.

[0107] In summary, the present application proposes a SQL statement comparison method, which can ignore interference factors that have no effect on the execution result and improve the accuracy of SQL statement comparison by rearranging the exchangeable sort fields compared to a simple text comparison method.

[0108] It should be understood that the size of the serial numbers of the steps in the above embodiments does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0109] Corresponding to the comparison method of the SQL statements described in the above embodiment, Figure 3 A structural block diagram of a SQL statement comparison device provided in an embodiment of the present application is shown. For ease of explanation, only the parts related to the embodiment of the present application are shown.

[0110] Reference Figure 3 , the device comprises:

[0111] SQL statement acquisition module 301, used to acquire source SQL statements and target SQL statements to be compared;

[0112] An SQL statement conversion module 302, used to convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object;

[0113] A field sorting adjustment module 303 is used to adjust the sorting of the exchangeable sorting fields contained in the first category object and the second category object in the same way to obtain updated first category objects and updated second category objects, wherein the exchangeable sorting fields refer to fields that have no effect on the execution result of the SQL statement after exchanging their sorting in the SQL statement;

[0114] A class object conversion module 304, configured to convert the updated first class object into a first SQL statement, and convert the updated second class object into a second SQL statement;

[0115] A text matching module 305 is used to perform text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result;

[0116] The SQL statement comparison module 306 is used to determine whether the source SQL statement is consistent with the target SQL statement according to the text matching result.

[0117] In an implementation of the embodiment of the present application, the SQL statement comparison device may further include:

[0118] An implementation class detection module, used to determine whether the implementation class of the first category object is the same as the implementation class of the second category object;

[0119] A comparison and determination module, configured to determine that the source SQL statement and the target SQL statement are inconsistent if the implementation class of the first category object and the implementation class of the second category object are different;

[0120] A comparison execution module is used to execute the step of adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same way and subsequent steps if the implementation class of the first class objects and the implementation class of the second class objects are the same.

[0121] In an implementation of the embodiment of the present application, the field sorting adjustment module may include:

[0122] a target expression detection unit, used for detecting a first target expression contained in the first type of object, and detecting a second target expression contained in the second type of object, wherein the first target expression and the second target expression are expressions containing front and rear two parts of fields, and swapping the order of the front and rear two parts of fields has no effect on the execution result;

[0123] The expression field sorting adjustment unit is used to adjust the sorting of the two parts of the fields contained in the first target expression and the second target expression in the same order.

[0124] In another implementation of the embodiment of the present application, the field sorting adjustment module may include:

[0125] a name field detection unit, used to detect the column name field and the table name field included in the first category of objects, and to detect the column name field and the table name field included in the second category of objects;

[0126] The name field sorting adjustment unit is used to adjust the sorting of the column name fields and table name fields included in the first category of objects, and the column name fields and table name fields included in the second category of objects in the same sorting manner.

[0127] In an implementation of the embodiment of the present application, the SQL statement comparison device may further include:

[0128] A name field detection module, used to detect the column name field and the table name field included in the first category of objects, and to detect the column name field and the table name field included in the second category of objects;

[0129] The alias deletion module is used to delete the aliases in the column name field and the table name field contained in the first category of objects, and to delete the aliases in the column name field and the table name field contained in the second category of objects.

[0130] In an implementation of the embodiment of the present application, the SQL statement comparison device may further include:

[0131] A text difference point acquisition module, used for acquiring text difference points between the first SQL statement and the second SQL statement if the text matching results are inconsistent;

[0132] The text distinguishing point marking module is used to mark each of the text distinguishing points contained in the first SQL statement according to the corresponding type.

[0133] In one implementation of the embodiment of the present application, the SQL statement conversion module may include:

[0134] The SQL statement conversion unit is used to convert the source SQL statement into a first Statement object and convert the target SQL statement into a second Statement object using a JSqlParser tool.

[0135] The present application also provides a computer-readable storage medium storing computer-readable instructions. When the computer-readable instructions are executed by a processor, the following is achieved: Figure 1 Represents a comparison method for any SQL statement.

[0136] The present application also provides a computer program product, which, when executed on a server, enables the server to execute the following Figure 1 Represents a comparison method for any SQL statement.

[0137] Figure 4 Schematic diagram of a terminal device provided by an embodiment of the present application. Figure 4 As shown, the terminal device 4 of this embodiment includes: a processor 40, a memory 41, and a computer-readable instruction 42 stored in the memory 41 and executable on the processor 40. When the processor 40 executes the computer-readable instruction 42, the steps in the above-mentioned SQL statement comparison method embodiment are implemented, for example Figure 1 Alternatively, when the processor 40 executes the computer-readable instructions 42, the functions of each module / unit in the above-mentioned device embodiments are realized, for example, Figure 3 Functions of modules 301 to 306 are shown.

[0138] Exemplarily, the computer-readable instruction 42 may be divided into one or more modules / units, which are stored in the memory 41 and executed by the processor 40 to complete the present application. The one or more modules / units may be a series of computer-readable instruction segments capable of completing a specific function, which are used to describe the execution process of the computer-readable instruction 42 in the terminal device 4.

[0139] The terminal device 4 may be a computing device such as a smart phone, a notebook, a PDA, or a cloud terminal device. The terminal device 4 may include, but is not limited to, a processor 40 and a memory 41. Those skilled in the art will appreciate that Figure 4 It is only an example of the terminal device 4 and does not constitute a limitation on the terminal device 4. It may include more or fewer components than shown in the figure, or a combination of certain components, or different components. For example, the terminal device 4 may also include input and output devices, network access devices, buses, etc.

[0140] The processor 40 may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or the processor may be any conventional processor, etc.

[0141] The memory 41 may be an internal storage unit of the terminal device 4, such as a hard disk or memory of the terminal device 4. The memory 41 may also be an external storage device of the terminal device 4, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the terminal device 4. Further, the memory 41 may also include both an internal storage unit and an external storage device of the terminal device 4. The memory 41 is used to store the computer-readable instructions and other programs and data required by the terminal device. The memory 41 may also be used to temporarily store data that has been output or is to be output.

[0142] It should be noted that the information interaction, execution process, etc. between the above-mentioned devices / units are based on the same concept as the method embodiment of the present application. Their specific functions and technical effects can be found in the method embodiment part and will not be repeated here.

[0143] The technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example for illustration. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated in a processing unit, or each unit can exist physically separately, or two or more units can be integrated in one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned system can refer to the corresponding process in the aforementioned method embodiment, which will not be repeated here.

[0144] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present application implements all or part of the processes in the above-mentioned embodiment method, which can be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a computer-readable storage medium, and the computer program can implement the steps of the above-mentioned various method embodiments when executed by the processor. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may at least include: any entity or device that can carry the computer program code to the camera device / terminal device, recording medium, computer memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), electrical carrier signal, telecommunication signal and software distribution medium. For example, a USB flash drive, a mobile hard disk, a disk or an optical disk.

[0145] In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described or recorded in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0146] The embodiments described above are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, a person skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A method for comparing SQL statements, characterized in that: include: Obtain the source SQL statement and target SQL statement to be compared; Convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object; Adjusting the order of the exchangeable order fields contained in the class objects of the first class objects and the second class objects in the same manner to obtain updated first class objects and updated second class objects, wherein the exchangeable order fields refer to fields that have no effect on the execution result of the SQL statement after exchanging the order of the fields in the SQL statement; Converting the updated first-category object into a first SQL statement, and converting the updated second-category object into a second SQL statement; Performing text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result; Determine whether the source SQL statement is consistent with the target SQL statement according to the text matching result.

2. The method according to claim 1, characterized in that After converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the method further includes: Determine whether the implementation class of the first category object is the same as the implementation class of the second category object; If the implementation class of the first category of objects is different from the implementation class of the second category of objects, determining that the source SQL statement is inconsistent with the target SQL statement; If the implementation class of the first class object is the same as the implementation class of the second class object, the step of adjusting the sorting of the exchangeable sorting fields contained in the class objects in the same way for the first class object and the second class object and subsequent steps are performed.

3. The method according to claim 1, characterized in that The step of adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner includes: Detecting a first target expression included in the first type of object, and detecting a second target expression included in the second type of object, wherein the first target expression and the second target expression are expressions containing front and rear two parts of fields, and swapping the order of the front and rear two parts of fields has no effect on the execution result; The sorting of the two parts of the fields contained in the first target expression and the second target expression is adjusted in the same order.

4. The method according to claim 1, characterized in that The step of adjusting the sorting of the exchangeable sorting fields contained in the class objects of the first class objects and the second class objects in the same manner includes: Detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects; The column name fields and table name fields included in the first category of objects, and the column name fields and table name fields included in the second category of objects are adjusted in the same sorting manner.

5. The method according to claim 1, characterized in that After converting the source SQL statement into the first type of object and converting the target SQL statement into the second type of object, the method further includes: Detecting the column name field and the table name field included in the first category of objects, and detecting the column name field and the table name field included in the second category of objects; The aliases in the column name field and the table name field included in the first type of objects are deleted, and the aliases in the column name field and the table name field included in the second type of objects are deleted.

6. The method according to claim 1, characterized in that After performing text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result, the method further includes: If the text matching results are inconsistent, obtaining text differences between the first SQL statement and the second SQL statement; Each of the text difference points contained in the first SQL statement is marked according to the corresponding type.

7. The method according to any one of claims 1 to 6, characterized in that The converting the source SQL statement into a first type of object, and converting the target SQL statement into a second type of object, comprises: The source SQL statement is converted into a first Statement object and the target SQL statement is converted into a second Statement object using a JSqlParser tool.

8. A device for comparing SQL statements, characterized in that: include: An SQL statement acquisition module is used to acquire source SQL statements and target SQL statements to be compared; An SQL statement conversion module, used to convert the source SQL statement into a first type of object, and convert the target SQL statement into a second type of object; A field sorting adjustment module, configured to adjust the sorting of the exchangeable sorting fields contained in the first category object and the second category object in the same manner to obtain updated first category objects and updated second category objects, wherein the exchangeable sorting fields refer to fields that have no effect on the execution result of the SQL statement after the sorting thereof in the SQL statement is exchanged; a class object conversion module, configured to convert the updated first class object into a first SQL statement, and convert the updated second class object into a second SQL statement; A text matching module, used to perform text matching processing on the first SQL statement and the second SQL statement to obtain a text matching result; The SQL statement comparison module is used to determine whether the source SQL statement is consistent with the target SQL statement according to the text matching result.

9. A terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the SQL statement comparison method according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the SQL statement comparison method according to any one of claims 1 to 7 is implemented.

Images