CN115408391A

CN115408391A - Database table changing method, device, equipment and storage medium

Info

Publication number: CN115408391A
Application number: CN202211021950.3A
Authority: CN
Inventors: 高晨峰
Original assignee: Jingdong Technology Information Technology Co Ltd
Current assignee: Jingdong Technology Information Technology Co Ltd
Priority date: 2022-08-24
Filing date: 2022-08-24
Publication date: 2022-11-29
Also published as: WO2024041022A1

Abstract

The embodiment of the invention discloses a method, a device, equipment and a storage medium for changing a database table. The method comprises the following steps: the method comprises the steps of obtaining a target database to be changed and a target change statement, analyzing the target change statement, determining the name of a table to be changed, creating a current intermediate table with the same structure as the table to be changed, storing and changing table data of the current intermediate table according to the target change statement and the table data in the table to be changed, recording a first data change log generated by the table to be changed in a data processing time period of the current intermediate table, changing the data of the current intermediate table, recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table, and locking and switching the table to be changed to obtain the changed target table if the second data change log determines that preset table switching conditions are met currently.

Description

Database table changing method, device, equipment and storage medium

Technical Field

The embodiment of the invention relates to computer technology, in particular to a method, a device, equipment and a storage medium for changing a database table.

Background

With the development of computer technology, database tables often need to be changed, such as changing the table structure or updating table data.

At present, when a database table is changed, a table locking process needs to be performed on the database table first, and the database table needs to be changed after the table is locked.

However, in the process of implementing the present invention, the inventor finds that at least the following problems exist in the prior art:

when the data volume in the database table is large, for example, in the tens of millions of levels, the database table needs to be changed for a long time, so that the table locking time is long, services related to the database table are unavailable for a long time, and the user experience is reduced.

Disclosure of Invention

The embodiment of the invention provides a method, a device, equipment and a storage medium for changing a database table, which are used for greatly reducing the table locking time for changing the database table and improving the user experience.

In a first aspect, an embodiment of the present invention provides a method for changing a database table, including:

acquiring a target database to be changed and a target change statement corresponding to the target database;

analyzing the target change statement, determining the name of the table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed;

based on a target change type corresponding to a target change statement, performing table data storage and change processing on the current intermediate table according to the target change statement and table data in the table to be changed, and recording a first data change log generated by the table to be changed in a data processing time period of the current intermediate table;

performing data change on the current intermediate form based on the target change type and the first data change log, and recording a second data change log generated by the form to be changed in the data change time period of the current intermediate form;

and if the preset table switching condition is currently met based on the second data change log, locking and switching the table to be changed based on the target change type, the second data change log and the current intermediate table to obtain the changed target table.

In a second aspect, an embodiment of the present invention further provides an apparatus for changing a database table, including:

the target database acquisition module is used for acquiring a target database to be changed and a target change statement corresponding to the target database;

the target change statement analysis module is used for analyzing the target change statement, determining the name of the table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed;

the first change processing module is used for storing and changing table data of the current intermediate table according to the target change type corresponding to the target change statement and the table data in the table to be changed and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table;

the second change processing module is used for carrying out data change on the current intermediate table based on the target change type and the first data change log, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table;

and the target table acquisition module is used for locking and switching the table to be changed based on the target change type, the second data change log and the current intermediate table to acquire the changed target table if the current preset table switching condition is met based on the second data change log.

In a third aspect, an embodiment of the present invention further provides an electronic device, where the electronic device includes:

one or more processors;

a memory for storing one or more programs;

when executed by the one or more processors, cause the one or more processors to implement a database table change method as provided by any embodiment of the invention.

In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the database table changing method provided in any embodiment of the present invention.

The embodiment of the invention has the following advantages or beneficial effects:

the target database to be changed and the target change statement are obtained, the target change statement is analyzed, and the intermediate table with the same structure as the table to be changed is created based on the obtained name of the table to be changed, so that the intermediate table is used for replacing the table to be changed to perform change processing, and the table to be changed does not need to be locked when the change is started. And according to the target change type corresponding to the target change statement and the table data in the table to be changed, performing table data storage and change processing on the current intermediate table, and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table. And carrying out data change on the current intermediate table according to the first data change log, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table, so that data synchronization between the intermediate table and the table to be changed can be realized by using the data change log in the process of changing the intermediate table. If the second data change log determines that the preset table switching condition is met currently, the data difference between the current intermediate table and the table to be changed is small, at the moment, the operation of locking and switching the table to be changed can be completed in a short time, and the changed target table is obtained, so that the table locking in the whole changing process is not needed, the table locking time of the database table changing is greatly reduced, and the user experience is improved.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the embodiments or the description of the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings can be obtained by those skilled in the art without creative efforts.

FIG. 1 is a flow chart of a database table changing method according to an embodiment of the present invention;

FIG. 2 is a flow chart of another method for changing a database table according to an embodiment of the present invention;

FIG. 3 is a flow chart of another method for database table change according to an embodiment of the present invention;

fig. 4 is a schematic structural diagram of a database table changing apparatus according to an embodiment of the present invention;

fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.

Fig. 1 is a flowchart of a database table changing method according to an embodiment of the present invention, and this embodiment is applicable to a case of changing a database table with a large data size. The method can be executed by a database table changing device, which can be realized by software and/or hardware and is integrated in the electronic equipment. As shown in fig. 1, the method specifically includes the following steps:

s110, obtaining a target database to be changed and a target change statement corresponding to the target database.

The target database may refer to a database where a data table that needs to be changed is located. The target database may be configured based on business needs. The target change statement may refer to a change modification instruction for a database table. The target change statement may be a Structured Query Language (SQL) statement. The target change statement may be set based on the table name of the table to be changed, the change type, and the corresponding change content.

Specifically, the target database to be changed and the target change statement corresponding to the target database may be obtained according to a change task created by a user. For example, a user may create a change task based on business requirements in an operation interface, where the change task may include target database link information and target change statements configured by the user. One or more change tasks created by a user are stored in a change task table, when the change tasks are executed, one change task can be taken out from the change task table at regular intervals for processing, and the state of the change task is modified to be in execution until all the change tasks are processed. The corresponding target database can be determined based on the target database link information in the currently taken target change task, and the target change statement corresponding to the target database can be obtained.

Illustratively, S110 may include: acquiring a target change task to be processed currently from a change task queue, wherein the change task queue is used for storing a plurality of change tasks created by a user; and determining a target database to be changed based on the target database link information in the target change task, and acquiring a target change statement corresponding to the target database in the target change task.

The target change task may refer to a change task that needs to be executed currently. For example, the target change task may be a change task to the database table a in the database a, or may be a change task to the database table B in the database B. The target database link information may refer to link information for linking the target database addresses so that the target database that needs to be changed may be obtained based on the target database link information. For example, the target database link information may be: xxxx mysql: localhost:1111example.

Specifically, a plurality of change tasks created by the user may be sequentially stored in the change task queue according to the sequence of the creation time. By using the change task queue, the next change task can be taken out from the queue immediately after the current change task is executed, and the next change task does not need to be executed after a fixed time interval, so that the delay can be reduced, and the reliability can be improved. And acquiring a target change task needing to be processed currently from the change task queue, determining a target database needing to be changed based on target database link information in the target change task, and acquiring a target change statement corresponding to the target database from the target change task created by the user.

And S120, analyzing the target change statement, determining the name of the table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed.

The table to be changed may refer to a database table in the target database, where a change operation needs to be performed. The name of the table to be changed is unique in the database, and the table to be changed which is unique and corresponding can be inquired according to the name of the table to be changed. The intermediate table may refer to a temporary table created to have the same data structure as the table to be changed. The current intermediate table may refer to an intermediate table obtained at the current time. The table data and/or table structure in the current intermediate table is dynamically changed. For example, the current intermediate table obtained at creation time is an empty table having the same data structure as the table to be changed.

Specifically, the target change statement may include a name of a database table that needs to be changed, and the target change statement is analyzed to determine the name of the table to be changed. The corresponding table to be changed can be obtained according to the name of the table to be changed, and a current intermediate table with the same structure as the table to be changed can be created, wherein the current intermediate table is an empty table. For example, after the target change statement SQL is analyzed, the obtained table name to be changed is t1, the specific structure of the table t1 to be changed can be obtained according to the table name t1 to be changed, and a current intermediate table t1_ tmp with the same structure as the current intermediate table t1_ tmp can be created according to the structure of the table t1 to be changed, where the table name of the intermediate table is different from the table name to be updated.

And S130, based on the target change type corresponding to the target change statement, performing table data storage and change processing on the current intermediate table according to the target change statement and the table data in the table to be changed, and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table.

The target change type may refer to a type of changing the table to be changed. The target change types may include a table structure change alter table, a table data change update from based on a reference table, and the like. The table structure change can be adding, modifying and deleting operations of fields or indexes in the table to be changed. The table data change based on the reference table may be updating field data in the reference table to corresponding field data in the table to be referenced. For example, the target change statement for the table data change type based on the reference table may be: update from a, B set a2= B2where a.a1= b.b1, the statement indicates that: when the a1 field value in the table A to be changed is equal to the B1 field value in the reference table B, the a2 field value in the table A to be changed is updated to the B2 field value in the reference table B. The data change log may refer to a binary log file binlog recorded when a user performs a change operation on a to-be-changed table. The first data change log may refer to a data change log binlog recorded when a user operates the table to be changed in a change processing time period (i.e., a first change time period) of the current intermediate table according to the target change statement.

Specifically, the target change type corresponding to the table to be changed can be determined according to the target change statement created by the user. Based on the target change type and the table data in the table to be changed, data storage and change operation are performed on the created current intermediate table, and meanwhile, a first data change log corresponding to the change operation performed on the table to be changed by the user during the data storage and change operation performed on the current intermediate table (namely, a first change time period) is recorded.

It should be noted that, during the process of performing a change operation on the current intermediate table according to the target change statement, the user may continue to perform a change operation on the table to be changed, so that normal operation of a service related to the table to be changed is not affected in the first change time period, and at the same time, a first data change log of the change operation performed on the table to be changed by the user in the first change time period is recorded.

And S140, carrying out data change on the current intermediate table based on the target change type and the first data change log, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table.

The second data change log may be a data change log binlog recorded when a user operates the table to be changed in a change processing time period (i.e., a second change time period) of the current intermediate table according to the first data change log.

It is noted that the current intermediate table refers to an intermediate table whose data size is empty in S130, and the current intermediate table refers to an intermediate table which is changed according to the target change statement and stored according to the table data to be updated in S140. The current intermediate table in S130 and the current intermediate table in S140 are intermediate tables with the same name and different stages, that is, the current intermediate table in S140 is the intermediate table after the change operation is performed in S130.

Specifically, based on the target change type and the first data change log recorded in the first change time period, the data change operation is continuously performed on the current intermediate table, so that the table data of the table to be changed, which is changed in the first change time period, is synchronized into the current intermediate table, and meanwhile, a second data change log, which is operated by the user on the table to be changed during the change of the data of the current intermediate table according to the first data change log (namely, in the second change time period), is recorded.

Similarly, in the process of executing the change operation on the current intermediate table according to the first data change log, the user can continue to perform the change operation on the table to be changed, so that the normal operation of the service related to the table to be changed is not influenced in the second change time period, and meanwhile, the second data change log in which the user performs the change operation on the table to be changed in the second change time period is recorded.

S150, if the preset table switching condition is determined to be met currently based on the second data change log, locking switching is conducted on the table to be changed based on the target change type, the second data change log and the current intermediate table, and the changed target table is obtained.

The preset table switching condition may be preset, and the intermediate table may be replaced with a condition of the table to be changed. For example, the preset table switching condition may refer to that a data difference between the intermediate table and the table to be updated is smaller than a preset threshold. The target table may refer to a final database table obtained after the table to be changed is changed. The current intermediate table in S150 is the intermediate table obtained after the change operation is performed in S140.

Specifically, the present embodiment may determine whether the intermediate table and the table to be changed are close to be small by detecting whether the preset table switching condition is currently met based on the second data change log. If the second data change log meets the preset table switching condition, the data difference between the current intermediate table and the table to be changed is small, and at the moment, based on the target change type, the second data change log and the current intermediate table, the locking switching operation of the table to be updated can be completed in a short time, and the changed target table is obtained, so that table locking in the whole change process is not needed, the table locking time of database table change is greatly reduced, and the user experience is improved.

Exemplarily, S150 may include: determining a total number of data change statements in the second data change log; and if the total quantity is less than or equal to the preset quantity threshold value, determining that the preset table switching condition is currently met.

The data change statement may refer to an SQL statement when a user performs a change operation on a to-be-changed table. The second data change log may include all data change statements corresponding to all change operations performed on the to-be-changed table by the user in the second change time period. The more the data change statements are, the more the change operation times of the table to be changed in the second change time period is, and the larger the data amount required to be synchronized between the intermediate table and the table to be changed is. The preset number threshold may be a maximum value of data difference between the intermediate table and the table to be changed, which is set in advance based on the service requirement.

Specifically, after the second data change log is obtained, the total number of data change statements in the second data change log may be counted, and if the total number of data change statements in the second data change log is less than or equal to a preset number threshold, it indicates that the data difference between the current intermediate table and the table to be changed is small, and at this time, it may be determined that the preset table switching condition is currently satisfied, and the table to be changed may be switched in a locking manner.

Illustratively, the method further comprises: and if the current condition of switching the preset table is determined to be not met based on the second data change log, continuing to change the data of the current intermediate table based on the target change type and the second data change log, and recording a next data change log generated by the table to be changed in the data change time period of the current intermediate table until the next data change log determines that the condition of switching the preset table is met currently.

The next data change log may refer to a data change log operated by the user on the to-be-changed table in the next change time period. For example, in a change processing time period of the current intermediate table based on the second data change log (that is, the next change time period is the third change time period), the next data change log recorded when the user operates the to-be-changed table is the third data change log, and similarly, there may be a fourth data change log, a fifth data change log, and so on.

Specifically, if the second data change log does not satisfy the preset table switching condition, for example, the total number of data change statements in the second data change log is greater than a preset number threshold, the second data change log continues to perform data change again on the current intermediate table obtained by the previous change according to the target change type and the second data change log, and simultaneously records a third data change log in a data change period of the current intermediate table, that is, a third data change log in which the user performs a change operation on the table to be changed in a third change time period, and detects whether the preset table switching condition is currently satisfied or not based on the third data change log, if the third data change log is not satisfied, the third data change log continues to perform data change and simultaneously records a fourth data change log, and if the fourth data change log does not satisfy the preset table switching condition, the data change continues to perform data change and simultaneously records the fifth data change log, and so on until the preset table switching condition is satisfied. And locking and switching the table to be changed based on the data change log meeting the preset table switching condition to obtain the changed target table.

It should be noted that, in this embodiment, the change operation on the database table may be performed in any time period, and it is not necessary to wait until the service is down and then change and modify the database table. In addition, compared with the case of directly executing the change statement in the table to be changed, the present embodiment can perform interruption at any time by executing the change statement in the intermediate table, and does not affect the normal operation of the service related to the table to be changed.

According to the technical scheme of the embodiment of the invention, the target database to be changed and the target change statement are obtained, the target change statement is analyzed, and the intermediate table with the same structure as the table to be changed is created based on the obtained name of the table to be changed, so that the current intermediate table is used for replacing the table to be changed to perform change processing, and the table to be changed does not need to be locked when the change is started. And according to the target change type corresponding to the target change statement and the table data in the table to be changed, performing table data storage and change processing on the current intermediate table, and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table. And carrying out data change on the current intermediate table according to the first data change log, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table, so that data synchronization between the intermediate table and the table to be changed can be realized by using the data change log in the process of changing the intermediate table. If the second data change log is used for determining that the preset table switching condition is met currently, the fact that the data difference between the current intermediate table and the table to be changed is small is indicated, at the moment, the operation of locking and switching the table to be changed can be completed in a short time, the changed target table is obtained, and therefore table locking in the whole changing process is not needed, the table locking time of changing the database table is greatly reduced, and user experience is improved.

On the basis of the above embodiment, S150 may include: renaming the table to be changed to a preset table name; and performing data change and renaming operation on the current intermediate table based on the target change type, the second data change log and the name of the table to be changed to obtain the changed target table.

The preset list name is different from the list name to be changed. The preset table name may refer to other names different from the table name to be changed, which are set in advance for the table to be changed, and may be any other names different from all table names in the target database. For example, when the table name to be changed is t1, the preset table name may be t1_ bak.

In this embodiment, the specific operation of locking and switching the to-be-changed table may be to rename the name of the to-be-changed table to a preset table name, perform data change and renaming operations on the current intermediate table according to the target change type, the second data change log, and the to-be-changed table name, and determine the intermediate table after the data change and renaming is completed as the changed target table.

Exemplarily, the table name t1 to be updated is renamed to a preset table name t1_ bak, data change is performed on the current intermediate table t1_ tmp according to the target change type, the second data change log and the table name t1 to be changed, and the current intermediate table name t1_ tmp is renamed to the table name t1 to be changed, so as to obtain the target table t1 after the table to be updated is updated.

It is noted that the table to be changed t1_ bak renamed to the preset table name may be temporarily stored, so that when the target table t1 fails to operate, recovery may be performed based on the table to be changed t1_ bak. If the target table t1 does not have a fault after running for a preset time, the temporarily stored table t1_ bak to be changed can be deleted, so as to reduce data caching.

It should be noted that, in this embodiment, the phase of renaming the name of the table to be changed to the preset table name may be understood as locking the table to be changed, and the phase of determining the renamed current intermediate table as the target table may be understood as unlocking the table to be changed, so that the table to be changed is locked only after the preset switching condition is met, and the table locking time when the database table is changed is greatly reduced.

Exemplarily, performing data change and renaming operations on the current intermediate table based on the target change type, the second data change log and the name of the table to be changed, and obtaining the changed target table may include: performing data change on the current intermediate table based on the target change type and the second data change log, renaming the changed current intermediate table to be the name of the table to be changed, and obtaining a changed target table; or renaming the current intermediate table to be the name of the table to be changed, and performing data change on the renamed current intermediate table based on the target change type and the second data change log to obtain the changed target table.

Specifically, when the data change and the renaming operation are performed on the current intermediate table to obtain the changed target table, the data change may be performed on the current intermediate table based on the target change type and the second data change log, and then the changed current intermediate table is renamed to the name of the table to be changed to obtain the changed target table. Or, the current intermediate table may be renamed to the name of the table to be changed, and then the renamed current intermediate table is subjected to data change based on the target change type and the second data change log, so as to obtain the changed target table. In this embodiment, the order of the data change operation and the renaming operation of the current intermediate table is not limited.

Fig. 2 is a flowchart of another database table changing method according to an embodiment of the present invention, where on the basis of the foregoing embodiments, when a target change type corresponding to a target change statement is a table structure change, the present embodiment further optimizes a step "storing and changing table data in a current intermediate table according to the target change statement and table data in the table to be changed based on the target change type corresponding to the target change statement", and a step "changing data in the current intermediate table based on the target change type and the first data change log". Wherein explanations of the same or corresponding terms as those of the above embodiments are omitted.

Referring to fig. 2, another database table changing method provided in this embodiment specifically includes the following steps:

s210, obtaining a target database to be changed and a target change statement corresponding to the target database.

Specifically, the target change statement acquired in this embodiment is: and a statement of the table structure change type, such as an alter table statement, so that the structure of the table to be changed in the target database is changed based on the target change statement.

S220, analyzing the target change statement, determining the name of the table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed.

And S230, executing the intermediate table change statement corresponding to the target change statement in the current intermediate table, and storing the table data in the table to be changed into the executed current intermediate table.

The intermediate table change statement may refer to a statement that changes the intermediate table, and may be determined based on the target change statement. For example, the change object for the target change statement created by the user is the table to be changed, so that the change object in the target change statement can be modified into the current intermediate table, that is, the name of the table to be changed in the target change statement is updated to the name of the intermediate table, and the intermediate table change statement is obtained.

Specifically, for the table structure change type, the table structure change may be performed on the current intermediate table based on the intermediate table change statement, that is, the intermediate table change statement is executed in the current intermediate table to obtain the current intermediate table with the changed table structure, and then the table data in the table to be changed is synchronously stored in the current intermediate table with the changed table structure. For example, the manner of storing the table data in the table to be changed into the executed current intermediate table may include a normal paging-based manner and an ID segmentation-based manner. In this embodiment, the table data in the table to be changed may be stored in the executed current intermediate table based on an ID segmentation manner, so that the table data storage time may be shortened, for example, 1 ten thousand pieces of data may be stored in the current intermediate table by extracting 100 pieces of data each time based on the ID of the table data. If the table to be changed is not subjected to ID segmentation, the table data in the table to be changed can be stored into the current intermediate table after execution in a common paging mode.

S240, recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table.

It should be noted that, the table structure change is performed on the current intermediate table according to the intermediate table change statement and the table structure change type, and the first data change log generated by the table to be changed in the data processing time period of the current intermediate table is recorded, the two steps are performed simultaneously, the order is not distinguished, that is, step S230 and step S240 may be performed simultaneously.

And S250, executing the intermediate table change statement corresponding to the data change statement in the first data change log in the current intermediate table.

The intermediate table change statement corresponding to the data change statement may refer to a statement for changing the intermediate table, and may be determined based on the data change statement. For example, the change object for the data change statement in the first data change log is the table to be changed, so that the change object in the data change statement can be modified into the intermediate table, that is, the name of the table to be changed in the data change statement is updated to the name of the intermediate table, and the intermediate table change statement corresponding to the data change statement is obtained.

Specifically, for the table structure change type, the intermediate table change statement corresponding to the data change statement in the first data change log may be executed directly in the current intermediate table obtained after S230, so as to synchronize the change operation performed by the user on the table to be changed in the first change time period to the current intermediate table, thereby performing a synchronous change on the data in the current intermediate table.

And S260, recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table.

It should be noted that, the intermediate table change statement corresponding to the data change statement in the first data change log is executed in the current intermediate table, and the second data change log generated by the table to be changed in the data change time period of the current intermediate table is recorded, and the two steps are executed simultaneously without distinguishing the sequence, that is, step S250 and step S260 may be executed simultaneously.

S270, if the condition that the current intermediate table meets the switching condition of the preset table is determined based on the second data change log, the table to be changed is renamed to be the name of the preset table, data change and renaming operations are carried out on the current intermediate table based on the second data change log and the name of the table to be changed, and the changed target table is obtained.

Specifically, for the table structure change type, performing data change and renaming operations on the current intermediate table based on the second data change log and the name of the table to be changed to obtain a changed target table may include:

executing an intermediate table change statement corresponding to the data change statement in the second data change log in the current intermediate table, renaming the current intermediate table after the statement is executed to the name of the table to be changed, and obtaining a changed target table;

or, renaming the current intermediate table to the name of the table to be changed, and executing an intermediate table change statement corresponding to the data change statement in the second data change log in the renamed current intermediate table to obtain a target table after the execution statement.

According to the technical scheme of the embodiment of the invention, when the target change type corresponding to the target change statement is the table structure change, the intermediate table change statement corresponding to the target change statement is executed in the current intermediate table, the table data in the table to be changed is stored in the executed current intermediate table, and the intermediate table change statement corresponding to the data change statement in the first data change log is executed in the current intermediate table according to the recorded first data change log, so that the table structure change and data synchronization can be realized by using the intermediate table and the data change log, the table locking time of the database table change is greatly shortened, and the user experience is improved.

Fig. 3 is a flowchart of another database table changing method according to an embodiment of the present invention, where on the basis of the foregoing embodiments, when a target change type corresponding to a target change statement is a table data change based on a reference table, the present embodiment further optimizes "storing and changing table data of a current intermediate table according to the target change statement and table data in the table to be changed based on the target change statement" and "changing data of the current intermediate table based on the target change type and the first data change log". Wherein explanations of the same or corresponding terms as those of the above embodiments are omitted.

Referring to fig. 3, another method for changing a database table provided in this embodiment specifically includes the following steps:

s310, obtaining a target database to be changed and a target change statement corresponding to the target database.

Specifically, the target change statement acquired in this embodiment is: and changing a statement of the type, such as an update from statement, based on the table data of the reference table, so as to subsequently change the table data to be changed in the target database based on the target change statement.

And S320, analyzing the target change statement, determining the name of the table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed.

S330, storing the table data in the table to be changed to the current intermediate table, and executing an intermediate table change statement corresponding to the target change statement in the current intermediate table based on the reference table in the target database.

Specifically, for the table data change type based on the reference table, the table data in the table to be changed may be stored in the current intermediate table synchronously, and then the intermediate table change statement is executed in the current intermediate table after synchronous storage based on the reference table in the target database, so as to obtain the current intermediate table after the table data change. For example, the manner of storing the table data in the table to be changed into the current intermediate table may include a normal paging-based manner and an ID segmentation-based manner. In this embodiment, the table data in the table to be changed may be stored in the current intermediate table based on an ID segmentation manner, which may shorten the table data storage time, for example, there are 1 ten thousand pieces of data, and 100 pieces of data may be extracted and stored in the current intermediate table based on the ID of the table data each time. If the table to be changed is not subjected to ID segmentation, the table data in the table to be changed can be stored into the current intermediate table based on a common paging mode.

S340, recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table.

It should be noted that, the intermediate table change statement corresponding to the target change statement is executed in the current intermediate table, and the first data change log generated by the table to be changed in the data processing time period of the current intermediate table is recorded, the two steps are executed simultaneously, the order is not distinguished, that is, step S330 and step S340 may be executed simultaneously.

S350, executing an intermediate table change statement corresponding to the data change statement in the first data change log in the current intermediate table, and executing an intermediate table change statement corresponding to the target change statement again in the executed current intermediate table based on the reference table in the target database.

The intermediate table change statement corresponding to the data change statement may refer to a statement for changing the intermediate table, and may be determined based on the data change statement. For example, the change object to which the data change statement in the first data change log is directed is a table to be changed, so that the change object in the data change statement can be modified into an intermediate table, that is, the name of the table to be changed in the data change statement is updated to the name of the intermediate table, and thus the intermediate table change statement corresponding to the data change statement is obtained.

Specifically, for the table data change type based on the reference table, an intermediate table change statement corresponding to the data change statement in the first data change log may be executed in the current intermediate table obtained after step S330, so as to synchronize the change operation performed by the user on the table to be changed to the current intermediate table within the first change time period, thereby performing the synchronous change on the data in the current intermediate table. And after the synchronous data change is finished, executing the intermediate table change statement corresponding to the target change statement again in the obtained current intermediate table based on the reference table in the target database so as to carry out the re-table data change in the current intermediate table after the synchronous data change is finished, thereby ensuring the accuracy of the table data change in the whole data change process.

And S360, recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table.

It should be noted that, the middle table change statement corresponding to the target change statement is executed again in the executed current middle table, and the second data change log generated by the table to be changed in the data change time period of the current middle table is recorded, the two steps are executed simultaneously, the sequence is not distinguished, that is, step S350 and step S360 may be executed simultaneously.

And S370, if it is determined that the preset table switching condition is currently met based on the second data change log, renaming the table to be changed to a preset table name, and performing data change and renaming operations on the current intermediate table based on the second data change log and the table name to be changed to obtain a changed target table.

Specifically, for the table data change type based on the reference table, performing data change and renaming operations on the current intermediate table based on the second data change log and the name of the table to be changed, and obtaining the changed target table may include:

executing an intermediate table change statement corresponding to the data change statement in the second data change log in the current intermediate table, executing the intermediate table change statement corresponding to the target change statement again in the executed current intermediate table based on a reference table in the target database, renaming the current intermediate table after executing the statement to be the name of the table to be changed, and obtaining the changed target table;

or renaming the current intermediate table to be the name of the table to be changed, executing an intermediate table change statement corresponding to the data change statement in the second data change log in the renamed current intermediate table, and executing the intermediate table change statement corresponding to the target change statement again in the executed current intermediate table based on the reference table in the target database to obtain the target table after the statement is executed again.

According to the technical scheme of the embodiment of the invention, when the target change type corresponding to the target change statement is the table data change based on the reference table, the table data in the table to be changed is stored in the current intermediate table, the intermediate table change statement corresponding to the target change statement is executed in the current intermediate table based on the reference table in the target database, the intermediate table change statement corresponding to the data change statement in the first data change log is executed in the current intermediate table according to the recorded first data change log, and the intermediate table change statement corresponding to the target change statement is executed again in the current intermediate table after the execution based on the reference table in the target database, so that the table data change and the database table synchronization based on the reference table can be realized by using the intermediate table and the data change log, the table locking time of the data table change is greatly shortened, and the user experience is improved.

The following is an embodiment of the apparatus for changing a database table according to an embodiment of the present invention, which belongs to the same inventive concept as the database table changing methods according to the above embodiments, and reference may be made to the above embodiment of the method for changing a database table for details that are not described in detail in the embodiment of the apparatus for changing a database table.

Fig. 4 is a schematic structural diagram of a database table changing apparatus according to an embodiment of the present invention, and this embodiment is applicable to a case of changing a database table with a large data size. As shown in fig. 4, the apparatus specifically includes: a target database acquisition module 410, a target change statement parsing module 420, a first change processing module 430, a second change processing module 440, and a target table acquisition module 450.

The target database obtaining module 410 is configured to obtain a target database to be changed and a target change statement corresponding to the target database;

a target change statement parsing module 420, configured to parse the target change statement, determine a name of a table to be changed corresponding to the table to be changed in the target database, and create a current intermediate table having a structure identical to that of the table to be changed based on the name of the table to be changed;

the first change processing module 430 is configured to, based on a target change type corresponding to the target change statement, perform table data storage and change processing on the current intermediate table according to the target change statement and the table data in the table to be changed, and record a first data change log generated by the table to be changed in a data processing time period of the current intermediate table;

the second change processing module 440 is configured to perform data change on the current intermediate table based on the target change type and the first data change log, and record a second data change log generated by the table to be changed in a data change time period of the current intermediate table;

and a target table obtaining module 450, configured to, if it is determined that a preset table switching condition is currently met based on the second data change log, perform locking switching on the table to be changed based on the target change type, the second data change log, and the current intermediate table, and obtain a changed target table.

According to the technical scheme of the embodiment of the invention, the target database to be changed and the target change statement are obtained, the target change statement is analyzed, and the intermediate table with the same structure as the table to be changed is created based on the obtained name of the table to be changed, so that the current intermediate table is used for replacing the table to be changed to perform change processing, and the table to be changed does not need to be locked when the change is started. And according to the target change type corresponding to the target change statement and the table data in the table to be changed, performing table data storage and change processing on the current intermediate table, and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table. And performing data change on the current intermediate table according to the first data change log, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table, so that data synchronization between the intermediate table and the table to be changed can be realized by using the data change log in the process of performing change processing on the intermediate table. If the second data change log is used for determining that the preset table switching condition is met currently, the fact that the data difference between the current intermediate table and the table to be changed is small is indicated, at the moment, the operation of locking and switching the table to be changed can be completed in a short time, the changed target table is obtained, and therefore table locking in the whole changing process is not needed, the table locking time of changing the database table is greatly reduced, and user experience is improved.

Optionally, the target database obtaining module 410 is specifically configured to: acquiring a target change task to be processed currently from a change task queue, wherein the change task queue is used for storing a plurality of change tasks created by a user;

and determining a target database to be changed based on the target database link information in the target change task, and acquiring a target change statement corresponding to the target database in the target change task.

Optionally, the first change processing module 430 is specifically configured to: if the target change type corresponding to the target change statement is a table structure change, executing an intermediate table change statement corresponding to the target change statement in a current intermediate table, and storing table data in the table to be changed into the executed current intermediate table;

and if the target change type corresponding to the target change statement is table data change based on a reference table, storing the table data in the table to be changed to a current intermediate table, and executing the intermediate table change statement corresponding to the target change statement in the current intermediate table based on the reference table in the target database.

Optionally, the first change processing module 440 is specifically configured to: if the target change type is a table structure change, executing an intermediate table change statement corresponding to a data change statement in the first data change log in a current intermediate table;

and if the target change type is table data change based on a reference table, executing an intermediate table change statement corresponding to the data change statement in the first data change log in the current intermediate table, and executing an intermediate table change statement corresponding to the target change statement again in the executed current intermediate table based on the reference table in the target database.

Optionally, the target table obtaining module 450 may include:

a preset table switching unit, configured to determine a total number of data change statements in the second data change log; and if the total number is less than or equal to a preset number threshold, determining that the switching condition of a preset table is currently met.

Optionally, the target table obtaining module 450 may further include:

the table renaming unit to be changed is used for renaming the table to be changed to a preset table name, and the preset table name is different from the table name to be changed;

and the target table acquisition unit is used for carrying out data change and renaming operation on the current intermediate table based on the target change type, the second data change log and the name of the table to be changed to obtain the changed target table.

Optionally, the target table obtaining unit may be specifically configured to:

based on the target change type and the second data change log, carrying out data change on the current intermediate table, renaming the changed current intermediate table to be the name of the table to be changed, and obtaining a changed target table; or,

and renaming the current intermediate table to be the name of the table to be changed, and performing data change on the renamed current intermediate table based on the target change type and the second data change log to obtain a changed target table.

Optionally, the database table changing apparatus may further include:

and the to-be-changed table change termination module is used for continuing to perform data change on the current intermediate table based on the target change type and the second data change log if the to-be-changed table does not meet the preset table switching condition currently based on the second data change log, and recording a next data change log generated by the to-be-changed table in the data change time period of the current intermediate table until the to-be-changed table meets the preset table switching condition currently based on the next data change log.

The database table changing device provided by the embodiment of the invention can execute the database table changing method provided by any embodiment of the invention, and has the corresponding functional module and the beneficial effect of executing the database table changing method.

It should be noted that, in the embodiment of the database table changing apparatus, each included unit and module are only divided according to functional logic, but are not limited to the above division, as long as the corresponding function can be realized; in addition, specific names of the functional units are only for convenience of distinguishing from each other, and are not used for limiting the protection scope of the present invention.

Fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present invention. FIG. 5 illustrates a block diagram of an exemplary electronic device 12 suitable for use in implementing embodiments of the present invention. The electronic device 12 shown in fig. 5 is only an example and should not bring any limitation to the function and the scope of use of the embodiment of the present invention.

As shown in FIG. 5, electronic device 12 is embodied in the form of a general purpose computing device. The components of electronic device 12 may include, but are not limited to: one or more processors or processing units 16, a system memory 28, and a bus 18 that couples various system components including the system memory 28 and the processing unit 16.

Bus 18 represents one or more of any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures. By way of example, such architectures include, but are not limited to, industry Standard Architecture (ISA) bus, micro-channel architecture (MAC) bus, enhanced ISA bus, video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnect (PCI) bus.

Electronic device 12 typically includes a variety of computer system readable media. Such media may be any available media that is accessible by electronic device 12 and includes both volatile and nonvolatile media, removable and non-removable media.

The system memory 28 may include computer system readable media in the form of volatile memory, such as Random Access Memory (RAM) 30 and/or cache memory 32. The electronic device 12 may further include other removable/non-removable, volatile/nonvolatile computer system storage media. By way of example only, storage system 34 may be used to read from and write to non-removable, nonvolatile magnetic media (not shown in FIG. 5 and commonly referred to as a "hard drive"). Although not shown in FIG. 5, a magnetic disk drive for reading from and writing to a removable, nonvolatile magnetic disk (e.g., a "floppy disk") and an optical disk drive for reading from or writing to a removable, nonvolatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 by one or more data media interfaces. System memory 28 may include at least one program product having a set (e.g., at least one) of program modules that are configured to carry out the functions of embodiments of the invention.

A program/utility 40 having a set (at least one) of program modules 42 may be stored, for example, in system memory 28, such program modules 42 including, but not limited to, an operating system, one or more application programs, other program modules, and program data, each of which examples or some combination thereof may comprise an implementation of a network environment. Program modules 42 generally carry out the functions and/or methodologies of the described embodiments of the invention.

Electronic device 12 may also communicate with one or more external devices 14 (e.g., keyboard, pointing device, display 24, etc.), with one or more devices that enable a user to interact with electronic device 12, and/or with any devices (e.g., network card, modem, etc.) that enable electronic device 12 to communicate with one or more other computing devices. Such communication may be through an input/output (I/O) interface 22. Also, the electronic device 12 may communicate with one or more networks (e.g., a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network, such as the Internet) via the network adapter 20. As shown in FIG. 5, the network adapter 20 communicates with the other modules of the electronic device 12 via the bus 18. It should be understood that although not shown in the figures, other hardware and/or software modules may be used in conjunction with electronic device 12, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems, among others.

The processing unit 16 executes various functional applications and data processing by running programs stored in the system memory 28, for example, implementing steps of a database table changing method provided by the embodiment of the present invention, the method including:

based on the target change type and the first data change log, carrying out data change on the current intermediate table, and recording a second data change log generated by the table to be changed in the data change time period of the current intermediate table;

Of course, those skilled in the art can understand that the processor can also implement the technical solution of the database table changing method provided by any embodiment of the present invention.

The present embodiment provides a computer-readable storage medium, on which a computer program is stored, which when executed by a processor implements the steps of a database table changing method provided in any embodiment of the present invention, the method including:

and if the preset table switching condition is determined to be met currently based on the second data change log, locking and switching the table to be changed based on the target change type, the second data change log and the current intermediate table to obtain the changed target table.

Computer storage media for embodiments of the invention may employ any combination of one or more computer-readable media. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. The computer-readable storage medium may be, for example but not limited to: an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: an electrical connection having one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.

Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.

Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, smalltalk, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, 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 the connection may be made to an external computer (for example, through the Internet using an Internet service provider).

It will be understood by those skilled in the art that the modules or steps of the invention described above may be implemented by a general purpose computing device, they may be centralized on a single computing device or distributed across a network of computing devices, and optionally they may be implemented by program code executable by a computing device, such that it may be stored in a memory device and executed by a computing device, or it may be separately fabricated into various integrated circuit modules, or it may be fabricated by fabricating a plurality of modules or steps thereof into a single integrated circuit module. Thus, the present invention is not limited to any specific combination of hardware and software.

It is to be noted that the foregoing description is only exemplary of the invention and that the principles of the technology may be employed. Those skilled in the art will appreciate that the present invention is not limited to the particular embodiments described herein, and that various obvious changes, rearrangements and substitutions will now be apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A method for changing a database table, comprising:

analyzing the target change statement, determining a name of a table to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the name of the table to be changed;

based on a target change type corresponding to the target change statement, performing table data storage and change processing on the current intermediate table according to the target change statement and the table data in the table to be changed, and recording a first data change log generated by the table to be changed in a data processing time period of the current intermediate table;

and if the current preset table switching condition is determined to be met based on the second data change log, locking and switching the table to be changed based on the target change type, the second data change log and the current intermediate table to obtain a changed target table.

2. The method according to claim 1, wherein the obtaining of the target database to be changed and the target change statement corresponding to the target database includes:

acquiring a current target change task to be processed from a change task queue, wherein the change task queue is used for storing a plurality of change tasks created by a user;

3. The method according to claim 1, wherein the storing and changing the table data of the current intermediate table according to the target change statement and the table data in the table to be changed based on the target change type corresponding to the target change statement comprises:

if the target change type corresponding to the target change statement is a table structure change, executing an intermediate table change statement corresponding to the target change statement in a current intermediate table, and storing table data in the table to be changed into the executed current intermediate table;

4. The method of claim 1, wherein the making data changes to a current intermediate table based on the target change type and the first data change log comprises:

if the target change type is a table structure change, executing an intermediate table change statement corresponding to a data change statement in the first data change log in a current intermediate table;

5. The method of claim 1, wherein determining that a preset table switching condition is currently satisfied based on the second data change log comprises:

determining a total number of data change statements in the second data change log;

and if the total number is less than or equal to a preset number threshold, determining that the switching condition of a preset table is currently met.

6. The method according to claim 1, wherein the performing lock switching on the table to be changed based on the target change type, the second data change log and the current intermediate table to obtain the changed target table includes:

renaming the table to be changed to a preset table name, wherein the preset table name is different from the table name to be changed;

and performing data change and renaming operation on the current intermediate table based on the target change type, the second data change log and the name of the table to be changed to obtain a changed target table.

7. The method of claim 6, wherein the performing data change and renaming operations on the current intermediate table based on the target change type, the second data change log, and the name of the table to be changed to obtain the changed target table comprises:

renaming the current intermediate table as the name of the table to be changed, and performing data change on the renamed current intermediate table based on the target change type and the second data change log to obtain a changed target table.

8. The method according to any one of claims 1-7, further comprising:

and if the current condition of switching the preset table is determined to be not met based on the second data change log, continuing to change the data of the current intermediate table based on the target change type and the second data change log, and recording a next data change log generated by the table to be changed in the data change time period of the current intermediate table until the current condition of switching the preset table is determined to be met based on the next data change log.

9. An apparatus for changing a database table, comprising:

the target change statement analysis module is used for analyzing the target change statement, determining a table name to be changed corresponding to the table to be changed in the target database, and creating a current intermediate table with the same structure as the table to be changed based on the table name to be changed;

the first change processing module is used for storing and changing the table data of the current intermediate table according to the target change statement and the table data in the table to be changed and recording a first data change log generated by the table to be changed in the data processing time period of the current intermediate table based on the target change type corresponding to the target change statement;

and the target table acquisition module is used for locking and switching the table to be changed based on the target change type, the second data change log and the current intermediate table to acquire a changed target table if the second data change log determines that the preset table switching condition is met currently.

10. An electronic device, characterized in that the electronic device comprises:

one or more processors;

a memory for storing one or more programs;

when executed by the one or more processors, cause the one or more processors to implement the database table altering method of any of claims 1-8.

11. A computer-readable storage medium, on which a computer program is stored, which program, when being executed by a processor, is characterized by carrying out a database table changing method according to any one of claims 1 to 8.