CN118820203A - Data modification method, device, equipment and medium applied to database - Google Patents

Abstract

The present invention provides a data modification method, device, equipment and medium applied to a database, which are used to solve the technical problems in the related art that the rollback operation of database data modification is time-consuming and the uncertainty of data after rollback is high. The method comprises: obtaining a modification statement to be executed for the database; querying and backing up the initial data corresponding to the data modification object in the database according to the data modification object in the modification statement to be executed, and obtaining the backup data; generating a rollback statement corresponding to the modification statement to be executed according to the backup data; executing the data modification operation corresponding to the modification statement to be executed; when the modification rollback condition of the modification statement to be executed is triggered, executing the data rollback operation corresponding to the rollback statement; and performing data audit on the rollback data corresponding to the data rollback operation based on the backup data.

Description

Data modification method, device, equipment and medium applied to database

Technical Field

The present invention relates to the field of data processing technology, and in particular to a data modification method, device, equipment and medium applied to a database.

Background Art

In the daily operation and maintenance of business support systems, when multiple batches of database data are modified, SQL (Structured Query Language) statements are mainly written manually and submitted to the database for execution, or submitted to a data modification tool with data verification function for execution.

Existing data modification tools can check each SQL statement in the modification plan one by one, and simply verify whether the syntax of each statement is correct. In scenarios involving the execution and modification of a large number of SQL statements, multiple SQL statements are often packaged together for modification. If an abnormal interruption occurs during the modification process due to network, host, or database failure, or when the user needs to roll back the previously modified data, it is necessary to partially or fully restore the data that has been modified. However, in the existing technical solutions, when the data modification of the database is executed abnormally, manual intervention is required, and the more SQL statements in the modification plan, the more complicated the data modification rollback plan will be, resulting in a long time-consuming data modification rollback operation and high uncertainty in the data after the rollback.

Summary of the invention

The present invention provides a data modification method, device, equipment and medium applied to a database, so as to solve the technical problems in the related art that the rollback operation of database data modification is time-consuming and the uncertainty of the data after the rollback is high.

In a first aspect, an embodiment of the present invention provides a data modification method applied to a database, the method comprising:

Get the modification statements to be executed on the database;

According to the data modification object in the modification statement to be executed, the initial data corresponding to the data modification object is queried and backed up in the database to obtain the backup data;

Generate a rollback statement corresponding to the modification statement to be executed based on the backup data;

Execute the data modification operation corresponding to the modification statement to be executed;

When the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed;

Perform data audit on the rollback data corresponding to the data rollback operation based on the backup data.

In a possible implementation, in the method provided by the embodiment of the present invention, querying and backing up the initial data corresponding to the data modification object in the database according to the data modification object in the modification statement to be executed to obtain the backup data includes:

Identify and extract key information about data modification in the modification statement to be executed;

The initial data corresponding to the key information is queried and backed up in the database according to the key information to obtain the backup data.

In a possible implementation, in the method provided by the embodiment of the present invention, identifying and extracting key information about data modification in the modification statement to be executed includes:

Clean the modified statements to be executed and remove the comments in them.

Match the modification statement to be executed with the regular expressions corresponding to each statement pattern, and determine the statement pattern of the modification statement to be executed according to the matching result;

Extract key information about data modification in the modification statement to be executed according to the extraction rules corresponding to the sentence pattern of the modification statement to be executed.

In a possible implementation, in the method provided by an embodiment of the present invention, the key information includes the target modification library table of the data modification object, the library table alias, the where condition clause, and the modification field.

In a possible implementation manner, in the method provided by the embodiment of the present invention, querying and backing up initial data corresponding to the key information in a database according to the key information to obtain backup data includes:

Extract the basic information of the target modified library table in the key information;

Define the variables of the data modification object according to the information of the data modification object in the key information and the basic information of the library table, and generate a temporary backup table;

The initial values corresponding to the variables in the temporary backup table are searched in the database, and the variables in the temporary backup table are assigned values according to the initial values to obtain the backup data.

In a possible implementation, in the method provided by the embodiment of the present invention, generating a rollback statement corresponding to the modification statement to be executed according to the backup data includes:

A rollback statement corresponding to the modification statement to be executed is generated according to the preset rollback statement rule expression and the value of the variable in the temporary backup table.

In a possible implementation, in the method provided in the embodiment of the present invention, data auditing is performed on the rollback data corresponding to the data rollback operation based on the backup data, including:

Execute the preset rollback check statement based on the backup data to perform data audit on the rollback data corresponding to the data rollback operation.

In a second aspect, an embodiment of the present invention provides a data modification device applied to a database, comprising:

An acquisition unit, used for acquiring modification statements to be executed on the database;

An extraction unit is used to query and back up the initial data corresponding to the data modification object in the database according to the data modification object in the modification statement to be executed, so as to obtain the backup data;

A rollback statement generating unit, used to generate a rollback statement corresponding to the modification statement to be executed according to the backup data;

A modification unit, used to execute the data modification operation corresponding to the modification statement to be executed;

A rollback unit, used to execute a data rollback operation corresponding to a rollback statement when a modification rollback condition of a modification statement to be executed is triggered;

The audit unit is used to perform data audit on the rollback data corresponding to the data rollback operation based on the backup data.

In a possible implementation manner, in the device provided by the embodiment of the present invention, the extraction unit is specifically used for:

Identify and extract key information about data modification in the modification statement to be executed;

The initial data corresponding to the key information is queried and backed up in the database according to the key information to obtain the backup data.

In a possible implementation manner, in the device provided by the embodiment of the present invention, the extraction unit is specifically used for:

Clean the modified statements to be executed and remove the comments in them.

Match the modification statement to be executed with the regular expressions corresponding to each statement pattern, and determine the statement pattern of the modification statement to be executed according to the matching result;

Extract key information about data modification in the modification statement to be executed according to the extraction rules corresponding to the sentence pattern of the modification statement to be executed.

In a possible implementation, in the device provided by the embodiment of the present invention, the key information includes the target modification library table of the data modification object, the library table alias, the where condition clause, and the modification field.

In a possible implementation manner, in the device provided by the embodiment of the present invention, the extraction unit is specifically used for:

Extract the basic information of the target modified library table in the key information;

Define the variables of the data modification object according to the information of the data modification object in the key information and the basic information of the library table, and generate a temporary backup table;

The initial values corresponding to the variables in the temporary backup table are searched in the database, and the variables in the temporary backup table are assigned values according to the initial values to obtain the backup data.

In a possible implementation manner, in the device provided by the embodiment of the present invention, the fallback statement generating unit is specifically used for:

A rollback statement corresponding to the modification statement to be executed is generated according to the preset rollback statement rule expression and the value of the variable in the temporary backup table.

In a possible implementation, in the device provided by the embodiment of the present invention, the audit unit is specifically used for:

Execute the preset rollback check statement based on the backup data to perform data audit on the rollback data corresponding to the data rollback operation.

In a third aspect, an embodiment of the present invention provides an electronic device, comprising: at least one processor, at least one memory, and computer program instructions stored in the memory, which, when executed by the processor, implement the method provided in the first aspect of the embodiment of the present invention.

In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium having computer program instructions stored thereon, which, when executed by a processor, implements the method provided in the first aspect of the embodiment of the present invention.

In an embodiment of the present invention, by obtaining a modification statement to be executed for a database, querying and backing up the initial data corresponding to the data modification object in the modification statement to be executed in the database according to the data modification object in the modification statement to be executed, obtaining backup data, and then generating a rollback statement corresponding to the modification statement to be executed according to the backup data, and then executing the data modification operation corresponding to the modification statement to be executed. When the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed, and the rollback data corresponding to the data rollback operation is audited based on the backup data. Compared with the related art, a corresponding data backup mechanism and a data modification rollback mechanism are provided in the database data modification scheme, and the data modification rollback mechanism is implemented by setting the rollback statement corresponding to the modification statement, so that the data modification rollback of the modification statement can be completed by executing the rollback statement, which simplifies the rollback scheme, reduces manual intervention and time-consuming rollback operations, and in addition, by auditing and comparing the backup data with the rolled-back data, the correctness of the rolled-back data is improved.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG1 is a schematic flow chart of a method for modifying data in a database according to an embodiment of the present invention;

FIG2 is a schematic diagram of the sentence structure of update, delete, and insert statements provided in an embodiment of the present invention;

FIG3 is a schematic diagram of a where clause acquisition process according to an embodiment of the present invention;

FIG4 is a schematic diagram of the structure of a data modification device applied to a database provided by an embodiment of the present invention;

FIG5 is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention.

DETAILED DESCRIPTION

In order to make the purpose, technical solution and advantages of the present invention clearer, the present invention will be further described in detail below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

The following are explanations of some of the terms that appear in the text:

1. In the embodiments of the present invention, the term "and/or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and/or B may represent: A exists alone, A and B exist at the same time, and B exists alone. The character "/" generally indicates that the associated objects are in an "or" relationship.

During the heating operation of current conventional variable-frequency air conditioners, the internal fan speed continues to run at the set speed. If the user sets the wind speed higher, when the outdoor heat exchanger is frosted or the indoor ambient temperature is close to the set temperature, not only will the air outlet temperature continue to decrease, but the frost speed of the outdoor heat exchanger will also be accelerated, seriously affecting the user's thermal comfort experience.

The data modification method, device, equipment and medium applied to a database provided by the present invention are described in more detail below in conjunction with the accompanying drawings and embodiments.

An embodiment of the present invention provides a data modification method applied to a database, as shown in FIG1 , comprising:

Step S101, obtaining a modification statement to be executed for the database.

In specific implementation, each modification statement can be read in sequence from a data source (such as user input, modification statement file, other software tools or scripts, remote host call, etc.), and then these modification statements to be executed are saved in a modification statement variable array.

Step S102, querying and backing up the initial data corresponding to the data modification object in the database according to the data modification object in the modification statement to be executed, to obtain backup data.

The data modification object is the object used for modification operations in the modification statement when the modification statement is executed. The initial data corresponding to the data modification object is the data to be modified by the modification statement in the database. The initial data corresponding to the data modification object is backed up so that the modified data can be rolled back to the state before the modification.

In one possible implementation, initial data corresponding to the data modification objects in the modification statement to be executed is queried in the database and backed up to obtain backup data, including: identifying and extracting key information about the data modification in the modification statement to be executed; and initial data corresponding to the key information is queried in the database and backed up to obtain backup data based on the key information.

Each modification statement is read sequentially from the modification statement variable data group for processing, and is pushed to the corresponding sub-process for processing according to the modification statement type (update, delete, insert). By identifying and interpreting the modification statement to be executed, key information such as the modified target library table, library table alias, where condition clause, and modified field in the modification statement is extracted.

In one possible implementation, key information about data modification in the modification statement to be executed is identified and extracted, including: sentence cleaning of the modification statement to be executed to remove the comment part in the modification statement to be executed; matching the modification statement to be executed with the regular expression corresponding to each statement pattern, and determining the statement pattern of the modification statement to be executed according to the matching result; extracting the key information about data modification in the modification statement to be executed according to the extraction rule corresponding to the statement pattern of the modification statement to be executed.

In the key information identification and extraction sub-process, the statements will be refined first, the comments will be removed, and according to the sentence structure characteristics of the modification statement, the identification method of the keyword position such as set, from, where, values, select, etc. will be used, and the processing method of special characters such as quotation marks and brackets will be combined to extract the key information in the modification statement such as the target modification table, table alias, where condition clause, modification field, etc.

Exemplarily, taking the sentence structure of an update modification statement, a delete deletion statement, and an insert addition statement as an example, the sentence structure of update, delete, and insert statements is shown in FIG. 2 .

To remove comments, you can split the modification statement into several substrings using single quotes as delimiters and store them in an array. Replace the characters between "/*" and "*/" in array elements with even-numbered array subscripts with spaces, and then reassemble the array elements into a new modification statement. In other words, replace the comments that are not in single quotes with spaces.

When extracting the key information of the modification statement, the modification statement to be executed can be matched with the regular expressions corresponding to each statement pattern. The programming language used includes but is not limited to perl and python. If the match fails, it is considered a non-standard modification statement and the system exits. If the match succeeds, the key information is extracted successfully and the matching information is saved. The regular expression matching used here is not case-sensitive. Taking pythone language as an example, the key information identification and extraction process of update, delete, and insert statements is described below.

Update statement key information identification and extraction: After the modification statement to be executed successfully matches the rule expression corresponding to the update statement, the first group of the modification statement to be executed is the target modification library table, the second group is the library table alias if it exists, and the third group is the substring after the set keyword. Then, the modification field is extracted through the set common modification sentence "set [library table alias.] field = " and "set ([library table alias.] field 1, [library table alias.] field 2 ...) = ". When obtaining the where condition substring from the set substring, introduce word temporary variables, single quote counter variables, and bracket counter variables, and process the characters of the set substring one by one. The word temporary variable is used to store English letters, numbers, and underscore characters. When the current character is an English letter, number, or underscore, the character is appended after the value of the temporary word variable. If the current character is not an English letter, number, or underscore, the temporary word variable is set to empty. If the current character is a single quote, the single quote counter variable is incremented by one. If the current character is a left bracket, the bracket counter variable is incremented by one. If it is a right bracket, the bracket counter variable is decremented by one. If the current character is not one of the above characters, the bracket counter variable value is 0, and the single quote counter variable value is an even number, the temporary word variable is judged to be a where string. If the temporary word variable is a where string, the remaining unprocessed string of the set substring is the where condition clause. If it is not a where string, the temporary word variable is set to empty and the processing continues. If no where string is matched at the end, the where condition clause in this modification statement does not exist. The where clause acquisition process is shown in Figure 3.

Identify and extract key information of delete statements: After the modification statement to be executed successfully matches the rule expression corresponding to the delete statement, the first group of the modification statement to be executed is the target modification library table, the second group is the library table alias if it exists, and the third group is the where condition clause if it exists and matches the beginning of the where string.

Identify and extract key information of insert statements: After the modification statement to be executed successfully matches the rule expression corresponding to the insert statement, the first group of the modification statement to be executed is the target modification library table, the second group is the library table alias if it exists, and the third group is the values or select clause if it exists and matches the beginning of the values or select string.

After the statements are cleaned and comments are removed, the disclosed embodiment can quickly identify key information such as modified library tables, where conditions, and modified fields in the statements through regular expressions corresponding to each statement pattern, thereby improving the processing efficiency of extracting key information.

In one possible implementation, initial data corresponding to the key information is queried and backed up in a database according to the key information to obtain backup data, including: extracting basic information of a target modified library table in the key information; defining variables of the data modification object according to information of the data modification object in the key information and basic information of the library table to generate a temporary backup table; searching for initial values corresponding to the variables in the database according to the variables in the temporary backup table, and assigning values to the variables in the temporary backup table according to the initial values to obtain backup data.

In the specific implementation, extract the non-hidden fields, primary keys, default values and other basic information of the target modified table in the key information. Define variables based on the information of the data modification object and the basic information of the table in the key information, and then bind the variables to the rule expression of the backup statement in the statement generation rule. Substitute the value of the variable into the rule expression of the backup statement to generate the backup statement.

The backup statement generation rule involves multiple execution statements, including creating a temporary backup table, adding a rowidbak column to the temporary backup table, assigning default values to all columns with default values in the temporary backup table (there are as many assignment statements as there are fields with default values), and backing up modified, deleted, and newly added data to the temporary backup table. The rowidbak column in the temporary backup table is used to save the rowid hidden column of the corresponding data in the target modification library table. For update modification statements and delete deletion statements, this column is brought over when backing up data from the target modification library table to the temporary backup table. The value of the rowidbak column in the temporary backup table is one-to-one corresponding to the rowid hidden column value of the target modification library table, see the backup statement rule expression column in "List 2". For insert addition statements, the temporary backup table will not be assigned a value after adding a rowidbak column. After the insert statement is executed to add data to the target modification library table, the rowid hidden column value of the newly added data in the target modification library table is updated to the corresponding rowidbak column in the temporary backup table.

Step S103: Generate a rollback statement corresponding to the modification statement to be executed according to the backup data.

A rollback statement corresponding to the modification statement to be executed is generated according to the preset rollback statement rule expression and the value of the variable in the temporary backup table.

In specific implementation, the variables of the data modification objects defined in the temporary backup table are bound to the rule expressions of the rollback statements in the statement generation rules, and the rollback statements can be generated by substituting the values of the variables into the rule expressions of the rollback statements. The rollback statements use the fields in the temporary backup table to quickly and accurately restore the data to the state before the modification.

Step S104, executing the data modification operation corresponding to the modification statement to be executed.

According to the key information of the modification statement to be executed, the modification original table can be created. Combined with the information obtained in the previous steps, a modification statement information instance object containing information such as the modification original table, temporary backup table, backup statement, and rollback statement can be created and added to the modification statement information instance object task queue. The modification statement information instance object task queue adopts a list data structure, and each node element adopts a class or object design.

The instance object data items are obtained in sequence from the modification statement information instance object task queue, and the update modification sub-process, delete deletion sub-process or insert new sub-process are jumped to according to the modification statement type. At the same time, the modification information is displayed for the operator to check and confirm, and the modification log is recorded for future verification. The data backup table management is performed until all the tasks in the instance object task queue are successfully executed. The data backup table management is mainly based on the operator's choice to continue to retain the backup data or clean up the backup data. If a task fails to execute, is abnormally interrupted, or the operator chooses to interrupt, the operator can choose to exit or perform a data rollback operation.

Determine the sqltype modification statement type variable of the instance object. If the value is update or delete, jump to sub-process StepA. If the value is insert, jump to sub-process StepB.

StepA sub-process:

Get the statements for creating a temporary backup table, adding a rowidbak column to the temporary backup table, assigning a default value to the temporary backup table, and modifying or deleting the target modification database table from the modification statement information instance object, and execute them in this order. After successful execution, modify the execution status flag in the modification statement information instance object to 1 "executed", and continue to obtain task processing from the modification statement information instance object task queue until all executions are completed. If the execution fails, call the data rollback and rollback data audit module to roll back the data.

StepB sub-process:

Get the statements for creating a temporary backup table, assigning default values to fields in the temporary backup table, adding data to the temporary backup table, adding rowidbak columns to the temporary backup table, and adding data to the target modification table from the modification statement information instance object, and execute them in this order.

Get the target modification table from the modification statement information instance object to obtain the table primary key, then substitute the primary key, temporary backup table, and target modification table into the "rowidbak assignment statement r1" statement and execute it, and update the target modification table rowid field value to the temporary backup table rowidbak field. If the target modification table does not have a table primary key, obtain all the table fields and the temporary backup table and target modification table into the "rowidbak assignment statement r2" statement and execute it, and update the target modification table rowid field value to the temporary backup table rowidbak field.

rowidbak assignment statement r1: update temporary backup table variable backup_table a set

a.rowidbak=(select b.rowid from target modification library table variable source_table b where a.primary key field 1=b.primary key field 2and a.primary key field 1=b.primary key field 2……)

ROWIDBAK assignment statement R2: update temporary backup table variable backup_table a set

a.rowidbak＝(select b.rowid from target modification library table variable source_table b where((a.field1is null and b.field1is null)or(a.field1is not null and b.field1is notnull and a.field1＝b.field1))and((a.field2is null and b.field2is null)or(a.field2is not null and b.field2is not null and a.field2＝b.field2))……)

After successful execution, the execution status mark in the modification statement information instance object is updated to be executed, and task processing continues to be obtained from the modification statement information instance object task queue until all executions are completed. If the execution is abnormal, the data rollback and rollback data audit module are called to perform data rollback.

Step S105, when the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed.

In specific implementation, the modification rollback conditions include data modification execution failure or manual intervention triggering. For example, the rollback is triggered by software application interface operation or by other software application calls).

Displays the information of the statements that have been modified, including statement sorting number, statement, number of modified records, etc. The operator enters the rollback number from the software application interface to confirm which step to roll back to, and sets the input time limit. If the time limit is exceeded, the default number is 1 and all modifications are rolled back.

Starting from the end of the modification statement information instance object task queue, read the modification statement information instance objects whose execution status is marked as executed and whose rollback sequence number is greater than or equal to the input rollback sequence number in reverse order, execute the rollback statement in the instance object, and then perform the data consistency audit and comparison processing in step 4 until all qualified modification statement information instance objects are rolled back and the system exits. For example, there are 10 modification statement information instance object tasks with sequence numbers 1 to 10, and when sequence number 7 fails or the execution is manually interrupted and needs to be rolled back to sequence number 2, the modification statement information instance object tasks that need to be rolled back are sequence numbers 6, 5, 4, 3, and 2.

Step S106: Performing a data audit on the rollback data corresponding to the data rollback operation based on the backup data.

After the rollback statement is executed, a data consistency audit and comparison is performed to audit and compare the backup data with the data after rollback to ensure that the data after rollback is consistent with the data before the modification statement is executed.

In one possible implementation, performing a data audit on the rollback data corresponding to the data rollback operation based on the backup data includes: executing a preset rollback check statement based on the backup data to perform a data audit on the rollback data corresponding to the data rollback operation.

Get the target modified table from the modification statement information instance object to obtain the table primary key and all table field information. If the modification statement type sqltype value is update or delete, and the target modified table has a primary key, substitute the primary key, field, backup temporary table, and target modified table into the "rollback check statement h1" statement. If the target modified table does not have a primary key, substitute the field, backup temporary table, and target modified table into the "rollback check statement h2" statement and then execute. After execution, if the record queried is 0, it means that the data has been restored to the original state. If the record is not 0, it means that the data has not been restored to the original state, and manual confirmation is required to continue the rollback operation or exit the system. If the modification statement type sqltype value is insert, and the target modification table has a primary key, the primary key, field, backup temporary table, and target modification table are substituted into the "rollback check statement h3" statement. If the target modification table does not have a primary key, the field, backup temporary table, and target modification table are substituted into the "rollback check statement h4" statement and then executed. After execution, if the record queried is 0, it means that the data has been restored to the original state. If the record is not 0, it means that the data has not been restored to the original state, and manual confirmation is required whether to continue the rollback operation or exit the system.

Rollback check statement h1: select a. field 1, a. field 2, a. field 3... from backup temporary table variable backup_table a minus select a. field 1, a. field 2, a. field 3... from target modification library table variable source_table a where exists (select 1 from backup temporary table variable backup_table b where a. primary key field 1 = b. primary key field 2 and a. primary key field 1 = b. primary key field 2...)

Rollback check statement h2: select a.field1, a.field2, a.field3…from backup temporary table variable backup_table a minus select a.field1, a.field2, a.field3…from target modification library table variable source_table a where(select b.rowid from target modification library table variable source_table bwhere((a.field1is null and b.field1is null)or(a.field1is not null and b.field1is notnull and a.field1＝b.field1))and((a.field2is null and b.field2is null)or(a.field2is not null and b.field2is not null and a.field2＝b.field2))…)

Rollback check statement h3: select a. field 1, a. field 2, a. field 3... from target modification library table variable source_table a where exists (select 1 from backup temporary table variable backup_table b where a. primary key field 1 = b. primary key field 2 and a. primary key field 1 = b. primary key field 2...)

Fallback check statement h4: select a.field1, a.field2, a.field3…from target modification library table variable source_table a where(select b.rowid from target modification library table variable source_table bwhere((a.field1is null and b.field1is null)or(a.field1is not null and b.field1is notnull and a.field1＝b.field1))and((a.field2is null and b.field2is null)or(a.field2is not null and b.field2is not null and a.field2＝b.field2))…)

As shown in FIG. 4 , based on the same inventive concept as the data modification method applied to a database, the present invention further provides a data modification device applied to a database, comprising:

The acquisition unit 401 is used to acquire the modification statement to be executed for the database;

An extraction unit 402 is used to query and back up the initial data corresponding to the data modification object in the database according to the data modification object in the modification statement to be executed, and obtain backup data;

A rollback statement generating unit 403, used to generate a rollback statement corresponding to the modification statement to be executed according to the backup data;

Modification unit 404, used to execute the data modification operation corresponding to the modification statement to be executed;

The rollback unit 405 is used to execute the data rollback operation corresponding to the rollback statement when the modification rollback condition of the modification statement to be executed is triggered;

The audit unit 406 is used to perform data audit on the rollback data corresponding to the data rollback operation based on the backup data.

In a possible implementation, in the device provided by the embodiment of the present invention, the extraction unit 402 is specifically used for:

Identify and extract key information about data modification in the modification statement to be executed;

The initial data corresponding to the key information is queried and backed up in the database according to the key information to obtain the backup data.

In a possible implementation, in the device provided by the embodiment of the present invention, the extraction unit 402 is specifically used for:

Clean the modified statements to be executed and remove the comments in them.

Match the modification statement to be executed with the regular expressions corresponding to each statement pattern, and determine the statement pattern of the modification statement to be executed according to the matching result;

Extract key information about data modification in the modification statement to be executed according to the extraction rules corresponding to the sentence pattern of the modification statement to be executed.

In a possible implementation, in the device provided by the embodiment of the present invention, the key information includes the target modification library table of the data modification object, the library table alias, the where condition clause, and the modification field.

In a possible implementation, in the device provided by the embodiment of the present invention, the extraction unit 402 is specifically used for:

Extract the basic information of the target modified library table in the key information;

Define the variables of the data modification object according to the information of the data modification object in the key information and the basic information of the library table, and generate a temporary backup table;

The initial values corresponding to the variables in the temporary backup table are searched in the database, and the variables in the temporary backup table are assigned values according to the initial values to obtain the backup data.

In a possible implementation, in the device provided by the embodiment of the present invention, the fallback statement generating unit 403 is specifically used for:

A rollback statement corresponding to the modification statement to be executed is generated according to the preset rollback statement rule expression and the value of the variable in the temporary backup table.

In a possible implementation, in the device provided by the embodiment of the present invention, the audit unit 406 is specifically used for:

Execute the preset rollback check statement based on the backup data to perform data audit on the rollback data corresponding to the data rollback operation.

In addition, the data modification method and device applied to the database according to the embodiment of the present invention can be implemented by an electronic device. Fig. 5 shows a schematic diagram of the hardware structure of an electronic device provided by an embodiment of the present invention.

5, which shows a schematic diagram of the structure of an electronic device 500 suitable for implementing the embodiments of the present disclosure. The electronic device shown in FIG5 is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

As shown in FIG5 , the electronic device 500 may include a processing device (e.g., a central processing unit, a graphics processing unit, etc.) 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage device 508 into a random access memory (RAM) 503 to implement the data modification method applied to a database according to the embodiment described in the present disclosure. In the RAM 503, various programs and data required for the operation of the electronic device 500 are also stored. The processing device 501, the ROM 502, and the RAM 503 are connected to each other via a bus 504. An input/output (I/O) interface 505 is also connected to the bus 504.

Typically, the following devices may be connected to the I/O interface 505: input devices 506 including, for example, a touch screen, a touchpad, a keyboard, a mouse, a camera, a microphone, an accelerometer, a gyroscope, etc.; output devices 507 including, for example, a liquid crystal display (LCD), a speaker, a vibrator, etc.; storage devices 508 including, for example, a magnetic tape, a hard disk, etc.; and communication devices 509. The communication devices 509 may allow the electronic device 500 to communicate with other devices wirelessly or by wire to exchange data. Although FIG. 5 shows an electronic device 500 with various devices, it should be understood that it is not required to implement or have all the devices shown. More or fewer devices may be implemented or have alternatively.

In particular, according to an embodiment of the present disclosure, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a non-transitory computer-readable medium, and the computer program contains a program code for executing the method shown in the flowchart, thereby implementing the data modification method applied to the database as described above. In such an embodiment, the computer program can be downloaded and installed from the network through the communication device 509, or installed from the storage device 508, or installed from the ROM 502. When the computer program is executed by the processing device 501, the above-mentioned functions defined in the method of the embodiment of the present disclosure are executed.

It should be noted that the computer-readable medium disclosed above may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in combination with an instruction execution system, device or device. In the present disclosure, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, in which a computer-readable program code is carried. This propagated data signal may take a variety of forms, including but not limited to an electromagnetic signal, an optical signal, or any suitable combination of the above. The computer readable signal medium may also be any computer readable medium other than a computer readable storage medium, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer readable medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (radio frequency), etc., or any suitable combination of the above.

In some embodiments, the client and the server may communicate using any currently known or future developed network protocol such as HTTP (HyperText Transfer Protocol), and may be interconnected with any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network ("LAN"), a wide area network ("WAN"), an internet (e.g., the Internet), and a peer-to-peer network (e.g., an ad hoc peer-to-peer network), as well as any currently known or future developed network.

The computer-readable medium may be included in the electronic device, or may exist independently without being incorporated into the electronic device.

The computer-readable medium carries one or more programs. When the one or more programs are executed by the electronic device, the electronic device:

Get the modification statements to be executed on the database;

According to the data modification object in the modification statement to be executed, the initial data corresponding to the data modification object is queried and backed up in the database to obtain the backup data;

Generate a rollback statement corresponding to the modification statement to be executed based on the backup data;

Execute the data modification operation corresponding to the modification statement to be executed;

When the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed;

Perform data audit on the rollback data corresponding to the data rollback operation based on the backup data.

Optionally, when the above one or more programs are executed by the electronic device, the electronic device may also execute other steps described in the above embodiments.

Computer program code for performing the operations of the present disclosure may be written in one or more programming languages or a combination thereof, including, but not limited to, object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., through the Internet using an Internet service provider).

The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present disclosure. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some implementations as replacements, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and/or flow chart, and the combination of the square boxes in the block diagram and/or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

The units involved in the embodiments described in the present disclosure may be implemented by software or hardware, wherein the name of a unit does not, in some cases, limit the unit itself.

The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), and the like.

In the context of the present disclosure, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

In an embodiment of the present invention, by obtaining a modification statement to be executed for a database, querying and backing up the initial data corresponding to the data modification object in the modification statement to be executed in the database according to the data modification object in the modification statement to be executed, obtaining backup data, and then generating a rollback statement corresponding to the modification statement to be executed according to the backup data, and then executing the data modification operation corresponding to the modification statement to be executed. When the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed, and the rollback data corresponding to the data rollback operation is audited based on the backup data. Compared with the related art, a corresponding data backup mechanism and a data modification rollback mechanism are provided in the database data modification scheme, and the data modification rollback mechanism is implemented by setting the rollback statement corresponding to the modification statement, so that the data modification rollback of the modification statement can be completed by executing the rollback statement, which simplifies the rollback scheme, reduces manual intervention and time-consuming rollback operations, and in addition, by auditing and comparing the backup data with the rolled-back data, the correctness of the rolled-back data is improved.

Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

The present invention is described with reference to the flowchart and/or block diagram of the method, device (system), and computer program product according to the embodiment of the present invention. It should be understood that each process and/or box in the flowchart and/or block diagram, as well as the combination of the process and/or box in the flowchart and/or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for implementing the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including an instruction device that implements the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

These computer program instructions may also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, whereby the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more processes in the flowchart and/or one or more boxes in the block diagram.

Although the preferred embodiments of the present invention have been described, those skilled in the art may make additional changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the present invention.

Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention is also intended to include these modifications and variations.

Claims (10)

1. A data modification method applied to a database, characterized by comprising: Get the modification statements to be executed on the database; According to the data modification object in the modification statement to be executed, query and back up the initial data corresponding to the data modification object in the database to obtain backup data; Generate a rollback statement corresponding to the modification statement to be executed according to the backup data; Execute the data modification operation corresponding to the modification statement to be executed; When the modification rollback condition of the modification statement to be executed is triggered, the data rollback operation corresponding to the rollback statement is executed; A data audit is performed on the rollback data corresponding to the data rollback operation based on the backup data. 2. The method according to claim 1 is characterized in that, according to the data modification object in the modification statement to be executed, querying and backing up the initial data corresponding to the data modification object in the database to obtain the backup data comprises: Identify and extract key information about data modification in the modification statement to be executed; The initial data corresponding to the key information is searched and backed up in the database according to the key information to obtain the backup data. 3. The method according to claim 2, characterized in that the step of identifying and extracting key information about data modification in the modification statement to be executed comprises: Perform sentence cleaning on the modification statement to be executed, and remove the comment part in the modification statement to be executed; Matching the modification statement to be executed with the regular expressions corresponding to each statement pattern, and determining the statement pattern of the modification statement to be executed according to the matching result; The key information about the data modification in the modification statement to be executed is extracted according to the extraction rule corresponding to the sentence pattern of the modification statement to be executed. 4. The method according to claim 2 is characterized in that the key information includes the target modification library table, library table alias, where condition clause and modification field of the data modification object. 5. The method according to claim 4, characterized in that the step of querying and backing up the initial data corresponding to the key information in the database according to the key information to obtain the backup data comprises: Extracting the basic information of the target modified library table from the key information; Define the variables of the data modification object according to the information of the data modification object in the key information and the basic information of the library table, and generate a temporary backup table; The initial values corresponding to the variables are searched in the database according to the variables in the temporary backup table, and the variables in the temporary backup table are assigned values according to the initial values to obtain the backup data. 6. The method according to claim 5, characterized in that generating a rollback statement corresponding to the modification statement to be executed according to the backup data comprises: A rollback statement corresponding to the modification statement to be executed is generated according to a preset rollback statement rule expression and the value of the variable in the temporary backup table. 7. The method according to claim 1, characterized in that the step of performing data audit on the rollback data corresponding to the data rollback operation based on the backup data comprises: A preset rollback check statement is executed based on the backup data to perform a data audit on the rollback data corresponding to the data rollback operation. 8. A data modification device applied to a database, characterized by comprising: An acquisition unit, used for acquiring modification statements to be executed on the database; An extraction unit, configured to query and back up the initial data corresponding to the data modification object in the to-be-executed modification statement in the database, and obtain backup data; A rollback statement generating unit, configured to generate a rollback statement corresponding to the modification statement to be executed according to the backup data; A modification unit, used to execute the data modification operation corresponding to the modification statement to be executed; A rollback unit, configured to execute a data rollback operation corresponding to the rollback statement when a modification rollback condition of the modification statement to be executed is triggered; An audit unit is used to perform data audit on the rollback data corresponding to the data rollback operation based on the backup data. 9. An electronic device, characterized in that it comprises: at least one processor, at least one memory, and computer program instructions stored in the memory, and when the computer program instructions are executed by the processor, the method according to any one of claims 1 to 7 is implemented. 10. A computer-readable storage medium having computer program instructions stored thereon, wherein when the computer program instructions are executed by a processor, the method according to any one of claims 1 to 7 is implemented.