Table structure modification method, device, equipment, and computer-readable storage medium

Abstract

The present invention discloses a table structure modification method, apparatus, device, and computer-readable storage medium. The method comprises: upon receiving a table structure modification instruction, obtaining a structured query language (SQL) file carried in the table structure modification instruction; parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language (DDL) statement in the SQL file; determining the modification type corresponding to the table structure modification instruction based on the table name of each DDL statement, and determining the corresponding modification strategy based on the modification type; processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure. The present invention can solve the problems of the existing complex table structure modification process and low DDL execution efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of financial technology (Fintech), and in particular to a table structure modification method, device, equipment and computer-readable storage medium. Background Art

[0002] With the development of computer technology, more and more technologies (big data, distributed computing, blockchain, artificial intelligence, etc.) are being applied in the financial field. The traditional financial industry is gradually transforming into financial technology (Fintech). However, due to the security and real-time requirements of the financial industry, higher requirements are also placed on technology.

[0003] With the frequent increase in Internet application functions, it is often necessary to modify the table structure, such as adding fields, modifying field properties, etc. At present, pt-online-schema-change (an open source online table structure change tool) is generally used to modify the table structure online. However, this tool can only change one table at a time. If a SQL (Structured Query Language) file involves DDL (Data Definition Language) statements for multiple tables, it is necessary to manually split it into multiple SQL files, and then call the tool multiple times to execute the DDL statements in each SQL file separately. This process is complicated. In addition, the tool also needs to execute multiple independent DDL statements for the same table separately. If a SQL file involves multiple DDL statements for the same table, it is necessary to call the tool multiple times to execute each DDL statement separately. Each time the tool is called, data must be copied repeatedly, which will reduce database performance and make the entire change take longer. Therefore, the existing table structure modification method has the problems of a complex table structure modification process and low DDL execution efficiency. Summary of the Invention

[0004] The main purpose of the present invention is to provide a table structure modification method, device, equipment and computer-readable storage medium, aiming to solve the problems of complex table structure modification process and low DDL execution efficiency.

[0005] To achieve the above object, the present invention provides a table structure modification method, which includes:

[0006] Upon receiving a table structure modification instruction, obtaining a structured query language SQL file carried in the table structure modification instruction;

[0007] Parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language (DDL) statement in the SQL file;

[0008] Determine the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine the corresponding modification strategy according to the modification type;

[0009] The DDL statement is processed based on the modification strategy, the table name and the execution command to obtain a processed DDL statement, and a preset tool is called to execute the processed DDL statement to modify the corresponding table structure.

[0010] Optionally, the step of parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language DDL statement in the SQL file includes:

[0011] A preset programming language is used to perform regular expression matching on each DDL statement in the SQL file to obtain object elements contained in each DDL statement, wherein the object elements include a table name and a corresponding execution command.

[0012] Optionally, the step of determining a modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining a corresponding modification strategy according to the modification type includes:

[0013] If the table names of the DDL statements are all different, determining that the modification type corresponding to the table structure modification instruction is the first modification type, and determining that the modification strategy is the first modification strategy;

[0014] The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include:

[0015] Based on the first modification strategy, the table name and the execution command are used to generate a corresponding first data structure in the form of a key-value pair, and a preset tool is called to perform a loop iteration on the first data structure to modify the corresponding table structure.

[0016] Optionally, the step of determining a modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining a corresponding modification strategy according to the modification type includes:

[0017] If the table names of the DDL statements are all the same, determining that the modification type corresponding to the table structure modification instruction is the second modification type, and determining that the modification strategy is the second modification strategy;

[0018] The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include:

[0019] The DDL statements are merged based on the second modification strategy, the table name, and the execution command to obtain a merged DDL statement, and a preset tool is called to execute the merged DDL statement to modify the corresponding table structure.

[0020] Optionally, the step of determining a modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining a corresponding modification strategy according to the modification type includes:

[0021] If the table names of the DDL statements are partially the same, determining that the modification type corresponding to the table structure modification instruction is the third modification type, and determining that the modification strategy is the third modification strategy;

[0022] The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include:

[0023] Merging execution commands corresponding to the same table names in the DDL statements based on the third modification strategy to obtain merged execution commands;

[0024] The different table names and corresponding execution commands, the same table names and the merged execution commands in the table names of each DDL statement are used to generate a corresponding second data structure in the form of key-value pairs, and a preset tool is called to loop and iterate the second data structure to modify the corresponding table structure.

[0025] Optionally, the table structure modification method further includes:

[0026] After the execution is completed, the output content returned by the preset tool is obtained, and the target data in the output content is extracted, wherein the target data includes a return code and return information;

[0027] The return code and the return information are encapsulated into data in a preset format, and the encapsulated target data is sent to a preset working end.

[0028] Optionally, before the step of calling a preset tool to execute the processed DDL statement to modify the corresponding table structure, the method further includes:

[0029] Calling the test parameters in the preset tool to test the processed DDL statement, and after the test is completed, detecting whether there is an identification file that passes the test in the preset directory;

[0030] If it exists, execute the following steps: call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0031] In addition, to achieve the above-mentioned purpose, the present invention further provides a table structure modification device, the table structure modification device comprising:

[0032] A file acquisition module is used to acquire the SQL file carried in the table structure modification instruction when receiving the table structure modification instruction;

[0033] A file parsing module is used to parse the SQL file to obtain the table name and corresponding execution command of each DDL statement in the SQL file;

[0034] A strategy determination module is used to determine the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine the corresponding modification strategy according to the modification type;

[0035] The statement execution module is used to process the DDL statement based on the modification strategy, the table name and the execution command to obtain a processed DDL statement, and call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0036] In addition, to achieve the above-mentioned purpose, the present invention also provides a table structure modification device, which includes: a memory, a processor, and a table structure modification program stored in the memory and executable on the processor. When the table structure modification program is executed by the processor, the steps of the table structure modification method described above are implemented.

[0037] In addition, to achieve the above-mentioned purpose, the present invention further provides a computer-readable storage medium, on which a table structure modification program is stored. When the table structure modification program is executed by a processor, the steps of the table structure modification method described above are implemented.

[0038] The present invention provides a table structure modification method, device, equipment and computer-readable storage medium. When a table structure modification instruction is received, the SQL file carried in the table structure modification instruction is obtained; the SQL file is parsed to obtain the table name and corresponding execution command of each DDL statement in the SQL file; the modification type corresponding to the table structure modification instruction is determined according to the table name of each DDL statement, and the corresponding modification strategy is determined according to the modification type, and then the DDL statement is processed based on the modification strategy, table name and execution command to obtain the processed DDL statement, and a preset tool is called to execute the processed DDL statement to modify the corresponding table structure. Through the above method, the present invention can automatically parse the SQL file, and then determine the corresponding modification strategy for the table name of each DDL statement, so as to automatically process the DDL statement based on the modification strategy, avoid manually splitting the SQL file, thereby saving labor costs. At the same time, the present invention only needs to call the tool once, compared with the prior art that requires multiple tool calls, the present invention can improve the DDL execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 A schematic diagram of the device structure of the hardware operating environment involved in the embodiment of the present invention;

[0040] Figure 2 This is a flow chart of a first embodiment of a table structure modification method according to the present invention;

[0041] Figure 3 This is a functional module diagram of the first embodiment of the table structure modification device of the present invention.

[0042] The purpose, features and advantages of the present invention will be further described with reference to the accompanying drawings and in conjunction with the embodiments. DETAILED DESCRIPTION

[0043] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0044] Reference Figure 1 , Figure 1 This is a schematic diagram of the device structure of the hardware operating environment involved in the embodiment of the present invention.

[0045] The table structure modification device in the embodiment of the present invention may be a smart phone, or a terminal device such as a PC (Personal Computer), a tablet computer, or a portable computer.

[0046] like Figure 1As shown, the table structure modification device may include: a processor 1001, such as a CPU, a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. Among them, the communication bus 1002 is used to realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), an input unit such as a keyboard (Keyboard), and the user interface 1003 may optionally include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a Wi-Fi interface). The memory 1005 may be a high-speed RAM memory, or a stable memory (non-volatile memory), such as a disk memory. The memory 1005 may optionally also be a storage device independent of the aforementioned processor 1001.

[0047] Those skilled in the art will understand that Figure 1 The table structure modification device structure shown in the figure does not constitute a limitation to the table structure modification device, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0048] like Figure 1 As shown, the memory 1005 as a computer storage medium may include an operating system, a network communication module, a user interface module, and a table structure modification program.

[0049] exist Figure 1 In the terminal shown, the network interface 1004 is mainly used to connect to the backend server and communicate data with the backend server; the user interface 1003 is mainly used to connect to the client and communicate data with the client; and the processor 1001 can be used to call the table structure modification program stored in the memory 1005 and perform the following operations:

[0050] Upon receiving a table structure modification instruction, obtaining a structured query language SQL file carried in the table structure modification instruction;

[0051] Parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language (DDL) statement in the SQL file;

[0052] Determine the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine the corresponding modification strategy according to the modification type;

[0053] The DDL statement is processed based on the modification strategy, the table name and the execution command to obtain a processed DDL statement, and a preset tool is called to execute the processed DDL statement to modify the corresponding table structure.

[0054] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0055] A preset programming language is used to perform regular expression matching on each DDL statement in the SQL file to obtain object elements contained in each DDL statement, wherein the object elements include a table name and a corresponding execution command.

[0056] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0057] If the table names of the DDL statements are all different, determining that the modification type corresponding to the table structure modification instruction is the first modification type, and determining that the modification strategy is the first modification strategy;

[0058] Based on the first modification strategy, the table name and the execution command are used to generate a corresponding first data structure in the form of a key-value pair, and a preset tool is called to perform a loop iteration on the first data structure to modify the corresponding table structure.

[0059] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0060] If the table names of the DDL statements are all the same, determining that the modification type corresponding to the table structure modification instruction is the second modification type, and determining that the modification strategy is the second modification strategy;

[0061] The DDL statements are merged based on the second modification strategy, the table name, and the execution command to obtain a merged DDL statement, and a preset tool is called to execute the merged DDL statement to modify the corresponding table structure.

[0062] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0063] If the table names of the DDL statements are partially the same, determining that the modification type corresponding to the table structure modification instruction is the third modification type, and determining that the modification strategy is the third modification strategy;

[0064] Merging execution commands corresponding to the same table names in the DDL statements based on the third modification strategy to obtain merged execution commands;

[0065] The different table names and corresponding execution commands, the same table names and the merged execution commands in the table names of each DDL statement are used to generate a corresponding second data structure in the form of key-value pairs, and a preset tool is called to loop and iterate the second data structure to modify the corresponding table structure.

[0066] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0067] After the execution is completed, the output content returned by the preset tool is obtained, and the target data in the output content is extracted, wherein the target data includes a return code and return information;

[0068] The return code and the return information are encapsulated into data in a preset format, and the encapsulated target data is sent to a preset working end.

[0069] Furthermore, the processor 1001 may call the table structure modification program stored in the memory 1005 and perform the following operations:

[0070] Calling the test parameters in the preset tool to test the processed DDL statement, and after the test is completed, detecting whether there is an identification file that passes the test in the preset directory;

[0071] If it exists, the preset tool is called to execute the processed DDL statement to modify the corresponding table structure.

[0072] Based on the above hardware structure, various embodiments of the table structure modification method of the present invention are proposed.

[0073] The invention provides a table structure modification method.

[0074] Reference Figure 2 , Figure 2 This is a flow chart of the first embodiment of the table structure modification method of the present invention.

[0075] In this embodiment, the table structure modification method includes:

[0076] Step S10: upon receiving a table structure modification instruction, obtaining a structured query language SQL file carried in the table structure modification instruction;

[0077] The table structure modification method of this embodiment is implemented by a table structure modification device, which is installed with a preset tool, which can be pt-online-schema-change (open source online table structure change tool). The device is explained using a server as an example. In this embodiment, when the staff needs to modify the table structure, they can trigger a table structure modification request through the corresponding software or App (Application), thereby displaying the corresponding interface for the user to enter the corresponding SQL (Structured Query Language) file. After the input is completed and confirmed, the table structure modification instruction can be triggered. At this time, when the server receives the table structure modification instruction, it obtains the SQL file carried in the table structure modification instruction.

[0078] Step S20, parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language DDL statement in the SQL file;

[0079] After obtaining the SQL file, the SQL file is parsed to obtain the table name and corresponding execution command for each DDL (Data Definition Language) statement in the SQL file. Specifically, step S20 includes: using a preset programming language to perform regular expression matching on each DDL statement in the SQL file to obtain the object elements contained in each DDL statement, wherein the object elements include the table name and the corresponding execution command.

[0080] When processing an SQL file, a preset programming language can be used to perform regular expression matching on each DDL statement in the SQL file to obtain the object elements contained in each DDL statement, wherein the object elements include the table name and the corresponding execution command. Optionally, the preset programming language can be Perl (Practical Extraction and Report Language, used for system-oriented tasks) programming language, Python (an object-oriented dynamically typed language) programming language, Go (Golang, a statically strongly typed, compiled, concurrent, and garbage collection programming language) programming language. Since the general DDL statement is characterized by alter table tbName add column colName int, it is divided into three parts. The first part, alter table, indicates that this is a DDL operation. The second part, tbName, indicates which table is operated (i.e., the table name). The third part is the specific operation (i.e., the execution command), such as adding or deleting fields, adding or deleting indexes, etc.

[0081] When performing regular expression matching on DDL statements, the DDL statements may be matched using a pre-set regular expression, wherein the pre-set regular expression may be:

[0082]

[0083] Finally, $1 (i.e., table name) and $2 (i.e., execution command) can be extracted through the pre-set regular expression.

[0084] After regular expression matching, the table name and the corresponding execution command in the DDL statement can be obtained. For example, for the DDL statement: alter table tbName_1add column colName_1int, the table name is tbName_1, and the corresponding execution command is add column colName_1int; for the DDL statement: alter table tbName_2add columncolName_2varchar(50), the table name is tbName_2, and the corresponding execution command is add column colName_2varchar(50).

[0085] Step S30, determining the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining the corresponding modification strategy according to the modification type;

[0086] Step S40: Process the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0087] After obtaining the table name and the corresponding execution command in the DDL statement, the modification type corresponding to the table structure modification instruction is determined according to the table name of each DDL statement, and the corresponding modification strategy is determined according to the modification type. Then, the DDL statement is processed based on the modification strategy, table name and execution command to obtain the processed DDL statement, and the preset tool is called to execute the processed DDL statement to modify the corresponding table structure. Among them, the preset tool can be selected as pt-online-schema-change. pt-online-schema-change is an open source online table structure change tool. When using this tool to modify the table structure online, it will not lock the table and will not block the read and write operations of the business. The specific process of using this tool to execute DDL statements to modify the corresponding table structure is the same as the existing technology and will not be repeated here.

[0088] Specifically, step S30 may include:

[0089] Step a1: If the table names of the DDL statements are all different, determining that the modification type corresponding to the table structure modification instruction is the first modification type, and determining that the modification strategy is the first modification strategy;

[0090] At this time, step S40 includes:

[0091] Step b1: Generate a corresponding first data structure in the form of a key-value pair using the table name and the execution command based on the first modification strategy, and call a preset tool to iterate the first data structure in a loop to modify the corresponding table structure.

[0092] In this embodiment, if the table names of the DDL statements are all different, for example, there are three table names, namely tbName_1, tbName_2, and tbName_3, then they are all different. At this time, it is determined that the modification type corresponding to the table structure modification instruction is the first modification type, and the modification strategy is determined to be the first modification strategy. Then, based on the first modification strategy, the table name and the execution command are generated in the form of a key-value pair to generate a corresponding first data structure, and the preset tool is called to loop and iterate the first data structure to modify the corresponding table structure. For example, if the obtained table name and the corresponding execution command are as follows: tbName_1, addcolumn colName_1int; tbName_2, add column colName_2int; tbName_3, add columncolName_3int; the following first data structure can be generated:

[0093] key->value

[0094] tbName_1->add column colName_1int

[0095] tbName_2->add column colName_2int

[0096] tbName_3->add column colName_3int

[0097] In the above manner, the SQL file is first parsed to obtain the table name and corresponding execution command of each DLL statement in the SQL file. When all the table names are different, that is, when a SQL file involves DDL statements of multiple tables, in this embodiment, the table name and the execution command are generated in the form of a key-value pair to form a corresponding first data structure, and a preset tool is called to iterate the first data structure in a loop, thereby eliminating the need to manually split multiple SQL files and then call the tool multiple times to execute the DDL statements in each SQL file respectively, thereby simplifying the table structure modification process, avoiding manual splitting operations, and improving the DDL execution efficiency.

[0098] Furthermore, step S30 may further include:

[0099] Step a2: If the table names of the DDL statements are all the same, determining that the modification type corresponding to the table structure modification instruction is the second modification type, and determining that the modification strategy is the second modification strategy;

[0100] At this time, step S40 includes:

[0101] Step b2: Merge the DDL statements based on the second modification strategy, the table name, and the execution command to obtain a merged DDL statement, and call a preset tool to execute the merged DDL statement to modify the corresponding table structure.

[0102] If the table names of all DDL statements are the same, for example, there are three table names, namely tbName_1, tbName_1, and tbName_1, then they are all the same. At this time, it is determined that the modification type corresponding to the table structure modification instruction is the second modification type, and the modification strategy is determined to be the second modification strategy. Then, based on the second modification strategy, the table name, and the execution command, the DDL statements are merged to obtain the merged DDL statements, and the preset tool is called to execute the merged DDL statements to modify the corresponding table structure. For example, for DDL statement 1: alter table tbName_1add column colName_1int, DDL statement 2: alter table tbName_1add column colName_2int, DDL statement 3: alter table tbName_1add column colName_3int, after merging, the merged DDL statements are: alter tabletbName_1add column colName_1int, add column colName_2int, add column colName_3int.

[0103] In the above manner, the SQL file is first parsed to obtain the table name and corresponding execution command of each DLL statement in the SQL file. When all the table names are the same, that is, when a SQL file involves multiple DDL statements for the same table, in this embodiment, the DDL statements are automatically merged to obtain a merged DDL statement, and then the preset tool is called to execute the merged DDL statement. This eliminates the need to repeatedly call the tool to execute each DDL statement separately, avoids the situation where database performance loss due to repeated copying of data, and reduces the time consumed by changes, thereby improving the efficiency of DDL execution.

[0104] Furthermore, step S30 may further include:

[0105] Step a3: If the table names of the DDL statements are partially the same, determining that the modification type corresponding to the table structure modification instruction is the third modification type, and determining that the modification strategy is the third modification strategy;

[0106] At this time, step S40 includes:

[0107] Step b3: merging execution commands corresponding to the same table names in the DDL statements based on the third modification strategy to obtain merged execution commands;

[0108] Step b4, generates a corresponding second data structure in the form of key-value pairs for the different table names and corresponding execution commands, the same table names and the merged execution commands in the table names of each DDL statement, and calls a preset tool to loop and iterate the second data structure to modify the corresponding table structure.

[0109] If the table names of the DDL statements are partially identical, for example, there are three table names, namely tbName_1, tbName_1, and tbName_2, then they are partially identical. At this time, the modification type corresponding to the table structure modification instruction is determined to be the third modification type, and the modification strategy is determined to be the third modification strategy. Then, based on the third modification strategy, the execution commands corresponding to the same table names in the table names of the DDL statements are merged to obtain the merged execution commands. Then, the different table names and corresponding execution commands in the table names of the DDL statements, the same table names and the merged execution commands are generated into a corresponding second data structure in the form of key-value pairs, and the preset tool is called to iterate the second data structure in a loop to modify the corresponding table structure. For example, if the table names and corresponding execution commands are as follows: tbName_1, add column colName_1int; tbName_1, add column colName_2int; tbName_3, add column colName_3int; the two execution commands corresponding to tbName_1 can be merged to obtain: tbName_1, add column colName_1int; addcolumn colName_2int; and the following second data structure can be generated:

[0110] key->value

[0111] tbName_1->add column colName_1int; add column colName_2int

[0112] tbName_3->add column colName_3int

[0113] In the above manner, the SQL file is first parsed to obtain the table name and corresponding execution command of each DLL statement in the SQL file. When the table name is partially the same, that is, there are both the same table and different tables in multiple DDL statements involved in an SQL file, in this embodiment, the execution commands with the same table name are first merged to obtain the merged execution command, and then the different table names and corresponding execution commands in the table names of each DDL statement, the same table name and the merged execution command are generated into a corresponding second data structure in the form of key-value pairs, and then the preset tool is called to iterate the second data structure in a loop, thereby eliminating the need to manually split the SQL file and repeatedly call the tool for execution, thereby improving the DDL execution efficiency.

[0114] In the process of modifying the table structure of a bank or other financial institution, the present invention first parses the SQL file after obtaining the SQL file to obtain the table name and corresponding execution command of each DDL statement in the SQL file; then determines the modification type and modification strategy based on the table name of each DDL statement, and then processes the DDL statement based on the modification strategy, table name and execution command to obtain the processed DDL statement, and calls a preset tool to execute the processed DDL statement to modify the corresponding table structure. Through the above method, it is achieved to determine the corresponding modification strategy for the table name of each DDL statement, and then automatically process the DDL statement, avoiding the manual splitting of the SQL file by staff, thereby saving the labor cost of banks or other financial institutions. At the same time, the present invention only needs to call the tool once, avoiding multiple tool calls, which can improve the DDL execution efficiency of banks or other financial institutions in the process of modifying the table structure.

[0115] An embodiment of the present invention provides a table structure modification method. When a table structure modification instruction is received, an SQL file carried in the table structure modification instruction is obtained; the SQL file is parsed to obtain the table name and corresponding execution command of each DDL statement in the SQL file; the modification type corresponding to the table structure modification instruction is determined according to the table name of each DDL statement, and the corresponding modification strategy is determined according to the modification type, and then the DDL statement is processed based on the modification strategy, table name and execution command to obtain the processed DDL statement, and a preset tool is called to execute the processed DDL statement to modify the corresponding table structure. Through the above method, the embodiment of the present invention can automatically parse the SQL file, and then determine the corresponding modification strategy for the table name of each DDL statement, so as to automatically process the DDL statement based on the modification strategy, thereby avoiding manual splitting of the SQL file, thereby saving labor costs. At the same time, the embodiment of the present invention only needs to call the tool once, compared with the prior art that requires multiple tool calls, the embodiment of the present invention can improve the efficiency of DDL execution.

[0116] Furthermore, in the prior art, after calling the pt-online-schema-change tool to execute the processed DDL statement, the output content returned by the tool will be received. However, the output content is not in a standardized format and is complex, so the staff cannot intuitively judge whether the execution is successful. Figure 2 The first embodiment shown provides a second embodiment of the table structure modification method of the present invention.

[0117] In this embodiment, after step S40, the table structure modification method further includes:

[0118] Step A, after execution is completed, obtain the output content returned by the preset tool and extract the target data in the output content, the target data including the return code and return information;

[0119] In this embodiment, after calling the preset tool pt-online-schema-change to execute the processed DDL statement, the output content returned by the preset tool is obtained, and then the target data in the output content is extracted. The target data includes a return code and return information. The return code and return information correspond to each other. For example, if the return code is 0, the return information corresponds to OK; if the return code is 6, the return information corresponds to Duplicate key name.

[0120] Step B: encapsulate the return code and the return information into data in a preset format, and send the encapsulated target data to a preset working end.

[0121] Then, the return code and return information are encapsulated into data in a preset format, and the encapsulated target data is sent to the preset working end. Among them, the preset format can be selected as json (JavaScript Object Notation, JS object notation) format. For example, in the above example, when the return code is 0 and the return information is OK, it can be encapsulated into {"status":0,"msg":"OK"}; when the return code is 6 and the return information is Duplicate key name, it can be encapsulated into {"status":6,"msg":"Duplicate key name"}. By encapsulating the return code and return information into a standardized output in json format, the output content can be made more readable, which makes it convenient for staff to directly obtain the return code and return information according to the encapsulated target data, and then understand the execution status (execution success or failure), and at the same time, it is convenient for staff to locate execution exceptions.

[0122] It should be noted that, in a specific embodiment, in order to facilitate staff to understand the execution status corresponding to each return code and the exception cause corresponding to the return information, a mapping relationship table including the mapping relationship between return code and execution status, and the mapping relationship between return information and exception cause can be pre-set to determine the corresponding execution status and exception cause based on the return code, return information and the pre-set mapping relationship table, and then send the encapsulated target data, execution status and exception cause to the preset work end.

[0123] Furthermore, although the pt-online-schema-change tool provides two parameters, test and execute, there is no mandatory requirement in the process that the test must be successful before the execute can be performed. If the execute is performed directly without the test, there may be a failure during the execution process, and then the process needs to be re-executed after handling the exception, resulting in a low DDL execution success rate. Figure 2 The first embodiment shown here provides a third embodiment of the table structure modification method of the present invention.

[0124] In this embodiment, before the step of "calling a preset tool to execute the processed DDL statement to modify the corresponding table structure", the table structure modification method further includes:

[0125] Step C: calling the test parameters in the preset tool to test the processed DDL statement, and after the test is completed, detecting whether there is an identification file that passes the test in the preset directory;

[0126] If it exists, execute the following steps: call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0127] In this embodiment, after the DDL statement is processed to obtain the processed DDL statement, the test parameters in the preset tool are first called to test the processed DDL statement, and then after the test is completed, it is detected whether there is an identification file that passed the test in the preset directory. If it is detected that there is an identification file that passed the test in the preset directory, it means that the test has passed. At this time, the preset tool is called to execute the processed DDL statement to modify the corresponding table structure. It should be noted that if the test passes, a passed identification file will be generated in the local preset directory. Therefore, it can be determined whether the test is successful by detecting whether there is an identification file that passed the test in the preset directory.

[0128] In this embodiment, by adding a process that must be tested, the execution is performed only when the test passes, thereby increasing the success rate of the actual execution and further improving the quality of the business change.

[0129] The present invention also provides a table structure modification device.

[0130] Reference Figure 3 , Figure 3 This is a functional module diagram of the first embodiment of the table structure modification device of the present invention.

[0131] like Figure 3 As shown, the table structure modification device includes:

[0132] The file acquisition module 10 is used to acquire the SQL file carried in the table structure modification instruction when receiving the table structure modification instruction;

[0133] A file parsing module 20 is used to parse the SQL file to obtain the table name and corresponding execution command of each DDL statement in the SQL file;

[0134] A strategy determination module 30 is configured to determine a modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine a corresponding modification strategy according to the modification type;

[0135] The statement execution module 40 is used to process the DDL statement based on the modification strategy, the table name and the execution command to obtain a processed DDL statement, and call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0136] Furthermore, the file parsing module 20 is specifically configured to:

[0137] A preset programming language is used to perform regular expression matching on each DDL statement in the SQL file to obtain object elements contained in each DDL statement, wherein the object elements include a table name and a corresponding execution command.

[0138] Furthermore, the strategy determination module 30 is specifically configured to:

[0139] If the table names of the DDL statements are all different, determining that the modification type corresponding to the table structure modification instruction is the first modification type, and determining that the modification strategy is the first modification strategy;

[0140] The statement execution module 40 is specifically used to:

[0141] Based on the first modification strategy, the table name and the execution command are used to generate a corresponding first data structure in the form of a key-value pair, and a preset tool is called to perform a loop iteration on the first data structure to modify the corresponding table structure.

[0142] Furthermore, the strategy determination module 30 is further specifically configured to:

[0143] If the table names of the DDL statements are all the same, determining that the modification type corresponding to the table structure modification instruction is the second modification type, and determining that the modification strategy is the second modification strategy;

[0144] The statement execution module 40 is further specifically configured to:

[0145] The DDL statements are merged based on the second modification strategy, the table name, and the execution command to obtain a merged DDL statement, and a preset tool is called to execute the merged DDL statement to modify the corresponding table structure.

[0146] Furthermore, the strategy determination module 30 is further specifically configured to:

[0147] If the table names of the DDL statements are partially the same, determining that the modification type corresponding to the table structure modification instruction is the third modification type, and determining that the modification strategy is the third modification strategy;

[0148] The statement execution module 40 is further specifically configured to:

[0149] Merging execution commands corresponding to the same table names in the DDL statements based on the third modification strategy to obtain merged execution commands;

[0150] The different table names and corresponding execution commands, the same table names and the merged execution commands in the table names of each DDL statement are used to generate a corresponding second data structure in the form of key-value pairs, and a preset tool is called to loop and iterate the second data structure to modify the corresponding table structure.

[0151] Furthermore, the table structure modification device further includes:

[0152] A data extraction module is used to obtain the output content returned by the preset tool after execution is completed, and extract the target data in the output content, wherein the target data includes a return code and return information;

[0153] The data encapsulation module is used to encapsulate the return code and the return information into data in a preset format, and send the encapsulated target data to a preset working end.

[0154] Furthermore, the table structure modification device further includes:

[0155] A file detection module is used to call the test parameters in the preset tool to test the processed DDL statement, and after the test is completed, detect whether there is a test-passing identification file in the preset directory;

[0156] The statement execution module 40 is further specifically configured to: if there is a passed-test identification file in a preset directory, call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

[0157] Among them, the functional implementation of each module in the above-mentioned table structure modification device corresponds to each step in the above-mentioned table structure modification method embodiment, and its functions and implementation processes are no longer detailed here.

[0158] The present invention also provides a computer-readable storage medium storing a table structure modification program. When the table structure modification program is executed by a processor, the steps of the table structure modification method described in any one of the above embodiments are implemented.

[0159] The specific embodiments of the computer-readable storage medium of the present invention are substantially the same as the embodiments of the above-mentioned table structure modification method, and are not described in detail here.

[0160] It should be noted that, in this document, the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, article, or system comprising a series of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, method, article, or system. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, article, or system comprising the element.

[0161] The serial numbers of the above embodiments of the present invention are for description only and do not represent the advantages or disadvantages of the embodiments.

[0162] Through the description of the above embodiments, those skilled in the art can clearly understand that the above embodiment methods can be implemented by means of software plus the necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better embodiment. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present invention.

[0163] The above are only preferred embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention description and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A table structure modification method, characterized in that: The table structure modification method includes: Upon receiving a table structure modification instruction, obtaining a structured query language SQL file carried in the table structure modification instruction; Parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language (DDL) statement in the SQL file; Determine the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine the corresponding modification strategy according to the modification type; Processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure; The step of determining the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining the corresponding modification strategy according to the modification type includes: If the table names of the DDL statements are all different, determining that the modification type corresponding to the table structure modification instruction is the first modification type, and determining that the modification strategy is the first modification strategy; The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include: Based on the first modification strategy, the table name and the execution command are used to generate a corresponding first data structure in the form of a key-value pair, and a preset tool is called to perform a loop iteration on the first data structure to modify the corresponding table structure.

2. The table structure modification method according to claim 1, characterized in that: The step of parsing the SQL file to obtain the table name and corresponding execution command of each database schema definition language DDL statement in the SQL file includes: A preset programming language is used to perform regular expression matching on each DDL statement in the SQL file to obtain object elements contained in each DDL statement, wherein the object elements include a table name and a corresponding execution command.

3. The table structure modification method according to claim 1, characterized in that: The step of determining the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining the corresponding modification strategy according to the modification type includes: If the table names of the DDL statements are all the same, determining that the modification type corresponding to the table structure modification instruction is the second modification type, and determining that the modification strategy is the second modification strategy; The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include: The DDL statements are merged based on the second modification strategy, the table name, and the execution command to obtain a merged DDL statement, and a preset tool is called to execute the merged DDL statement to modify the corresponding table structure.

4. The table structure modification method according to claim 1, characterized in that: The step of determining the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determining the corresponding modification strategy according to the modification type includes: If the table names of the DDL statements are partially the same, determining that the modification type corresponding to the table structure modification instruction is the third modification type, and determining that the modification strategy is the third modification strategy; The steps of processing the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and calling a preset tool to execute the processed DDL statement to modify the corresponding table structure include: Merging execution commands corresponding to the same table names in the DDL statements based on the third modification strategy to obtain merged execution commands; The different table names and corresponding execution commands, the same table names and the merged execution commands in the table names of each DDL statement are used to generate a corresponding second data structure in the form of key-value pairs, and a preset tool is called to loop and iterate the second data structure to modify the corresponding table structure.

5. The table structure modification method according to any one of claims 1 to 4, characterized in that: The table structure modification method further includes: After the execution is completed, the output content returned by the preset tool is obtained, and the target data in the output content is extracted, wherein the target data includes a return code and return information; The return code and the return information are encapsulated into data in a preset format, and the encapsulated target data is sent to a preset working end.

6. The table structure modification method according to any one of claims 1 to 4, characterized in that: Before the step of calling a preset tool to execute the processed DDL statement to modify the corresponding table structure, the method further includes: Calling the test parameters in the preset tool to test the processed DDL statement, and after the test is completed, detecting whether there is an identification file that passes the test in the preset directory; If it exists, execute the following steps: call a preset tool to execute the processed DDL statement to modify the corresponding table structure.

7. A table structure modification device, characterized in that: The table structure modification device includes: A file acquisition module is used to acquire the SQL file carried in the table structure modification instruction when receiving the table structure modification instruction; A file parsing module is used to parse the SQL file to obtain the table name and corresponding execution command of each DDL statement in the SQL file; A strategy determination module is used to determine the modification type corresponding to the table structure modification instruction according to the table name of each DDL statement, and determine the corresponding modification strategy according to the modification type; A statement execution module, configured to process the DDL statement based on the modification strategy, the table name, and the execution command to obtain a processed DDL statement, and to call a preset tool to execute the processed DDL statement to modify the corresponding table structure; The strategy determination module is specifically configured to: if the table names of the DDL statements are all different, determine that the modification type corresponding to the table structure modification instruction is the first modification type, and determine that the modification strategy is the first modification strategy; The statement execution module is specifically used to: generate a corresponding first data structure in the form of a key-value pair for the table name and the execution command based on the first modification strategy, and call a preset tool to iterate the first data structure in a loop to modify the corresponding table structure.

8. A table structure modification device, characterized in that: The table structure modification device includes: a memory, a processor, and a table structure modification program stored in the memory and executable on the processor. When the table structure modification program is executed by the processor, the steps of the table structure modification method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a table structure modification program, which, when executed by a processor, implements the steps of the table structure modification method according to any one of claims 1 to 6.

Images