Database table management method, device, equipment and storage medium

Abstract

The present application relates to the technical field of database management, and in particular to a database table management method, apparatus, device and storage medium, wherein the method comprises: obtaining an execution SQL statement based on a definition object corresponding to a bean definition of a database source table; obtaining a control SQL statement based on the execution SQL statement, creating a runtime SQL queue based on the control SQL statement and the database source table; determining the execution type of the runtime SQL queue based on the data volume and data volume threshold of the database source table, the execution type including slow execution and fast execution; creating a shadow data table based on the database source table; obtaining a shadow table operation log based on the execution type, the runtime SQL queue and the shadow data table, synchronizing the database source table based on the shadow table operation log, and obtaining a target source table. The present application facilitates avoiding the problem of business interruption in a business system.

Description

Technical Field

[0001] The present application relates to the technical field of database management, and in particular to a database table management method, device, equipment and storage medium. Background Art

[0002] In order to manage data, the business system is generally configured with a corresponding database. There are database tables in the database. When the business system is upgrading its functions, it often performs operations including adding, deleting, modifying and querying the database tables. However, such operations will cause the database tables to be locked, which will cause business interruption of the business system.

[0003] To solve the above-mentioned business interruption problem, currently, database table operations related to function upgrades are generally performed during business off-peak hours (such as early morning). At this time, even if the data table is locked due to the operation, there will generally be no business interruption because the business system has almost no business to process at this time.

[0004] However, if the business system itself needs to perform business during off-peak hours, or if the business system occasionally needs to perform business during off-peak hours, the business system will still have the problem of business interruption. Summary of the invention

[0005] In order to avoid business interruption in a business system, the present application provides a database table management method, device, equipment and storage medium.

[0006] In a first aspect, the present application provides a database table management method, comprising:

[0007] Get the execution SQL statement based on the definition object corresponding to the bean definition of the database source table;

[0008] Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table;

[0009] Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, wherein the execution type includes slow execution and fast execution; create a shadow data table based on the database source table;

[0010] A shadow table operation log is obtained based on the execution type, the runtime SQL queue, and the shadow data table, and the database source table is synchronized based on the shadow table operation log to obtain a target source table.

[0011] In a second aspect, the present application provides a database table management device, comprising:

[0012] The statement acquisition module is used to obtain the execution SQL statement based on the definition object corresponding to the bean definition of the database source table;

[0013] A queue generation module, used to obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table;

[0014] A data table creation module, used to determine the execution type of the runtime SQL queue based on the data volume and data volume threshold of the database source table, wherein the execution type includes slow execution and fast execution; and to create a shadow data table based on the database source table;

[0015] The target table generation module is used to obtain a shadow table operation log based on the execution type, the runtime SQL queue and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0016] In a third aspect, the present application provides a computer device, the computer device comprising a memory and a processor, the memory storing a computer program, and the processor implementing the steps in the above method when executing the computer program.

[0017] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the steps in the above method when executed by a processor.

[0018] In a fifth aspect, the present application further provides a computer program product, wherein the computer program product comprises a computer program, and when the computer program is executed by a processor, the steps in any of the above method embodiments are implemented.

[0019] The above-mentioned database table management method, device, equipment and storage medium obtain an execution SQL statement through a definition object corresponding to the bean definition based on the database source table; obtain a control SQL statement based on the execution SQL statement, create a runtime SQL queue based on the control SQL statement and the database source table; determine the execution type of the runtime SQL queue based on the data volume and data volume threshold of the database source table, and the execution type includes slow execution and fast execution; create a shadow data table based on the database source table; obtain a shadow table operation log based on the execution type, the runtime SQL queue and the shadow data table, synchronize the database source table based on the shadow table operation log, and obtain the target source table. Through the above implementation, when the business system is upgraded during the business off-peak period, even if the user operates the database source table when executing the business, the control SQL statement corresponding to the operation will first operate the shadow table corresponding to the database source table, and then the database source table can be updated according to the operation log corresponding to the operation to obtain the target source table, which can easily avoid the problem of business interruption in the business system.

[0020] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present application, nor is it intended to limit the scope of the present application. Other features of the present application will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0022] Figure 1 A flowchart of a database table management method provided in an embodiment of the present application;

[0023] Figure 2 A schematic diagram of the structure of a database table management device provided in an embodiment of the present application;

[0024] Figure 3 A schematic diagram of the structure of a computer device provided in an embodiment of the present application;

[0025] Figure 4 This is a diagram of the internal structure of a computer-readable storage medium provided in an embodiment of the present application. DETAILED DESCRIPTION

[0026] In order to make the purpose, technical solution and advantages of the present disclosure more clear, the present disclosure is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present disclosure and are not used to limit the present disclosure.

[0027] It should be noted that the terms "first", "second", etc. in the specification and claims of this article and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of this article described here can be implemented in an order other than those illustrated or described here. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, device, product or equipment that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or equipment.

[0028] In this article, the term "and / or" is only a description of the association relationship between related objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in this article generally indicates that the related objects before and after are in an "or" relationship.

[0029] Embodiment 1

[0030] Figure 1 A flowchart of a database table management method provided in Example 1 of the present application, refer to Figure 1 The method may be performed by a device for performing the method, and the device may be implemented by software and / or hardware. The method includes:

[0031] S110. Obtain an execution-state SQL statement based on a definition object corresponding to the bean definition of a database source table.

[0032] Among them, the business system will generate a large amount of data in the process of business processing. In order to facilitate the management of the generated data, the business system is configured with a corresponding database, which contains a database table for storing data, and the database table is recorded as a database source table; wherein, the business system includes case management systems, banking and financial systems, school teaching systems, etc.; the business system sometimes needs to upgrade its functions according to actual business needs, and the function upgrade is generally selected during the business off-peak period, such as the early morning period; during the function upgrade of the business system, the user may still use the business system to execute the corresponding business, and the user will call some programs to operate the database source table in the process of executing the business. Among them, a Bean pre-processor based on the Spring framework is constructed in the business system. The Bean pre-processor is used to monitor the bean definition of the database source table. The bean is Spring using the IoC (Inversion of Control) container to create, manage and configure objects. In this embodiment, the bean is also the database source table; in this embodiment, the bean definition includes one or more of XML configuration, Java annotations and Java configuration classes.

[0033] Among them, the SQL statement used by the current program to operate on the database source table can be further determined through the bean definition of the database source table, and this SQL statement is recorded as an execution-state SQL statement.

[0034] S120: Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table.

[0035] Among them, some SQL statements in the execution state SQL statements have not yet performed corresponding operations on the database source table, some are in the process of executing the database source table, and some have completed the corresponding operations on the database source table, that is, the SQL statements in the execution state SQL statements have different states; when the business system is upgraded, in order to prevent the execution state SQL statements from continuing to operate on the database source table, thereby preventing the database source table from generating table lock operations, it is necessary to control some SQL statements in the execution state SQL statements according to the states of the SQL statements in the execution state SQL statements, and record the SQL statements to be controlled as control SQL statements.

[0036] Among them, the control SQL statement is used to operate the database source table. Different control SQL statements have different timings for operating the database source table. The control SQL statements can be sorted according to the timing to form an SQL statement queue, and the SQL statement queue is recorded as a runtime SQL queue.

[0037] S130: Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution; and create a shadow data table based on the database source table.

[0038] The runtime SQL queue is subsequently used to process the data in the database source table. If the amount of data in the database source table is too large, it will take a long time for the runtime SQL queue to completely process the data in the database source table. In this case, the execution type of the runtime SQL queue can be defined as slow execution. Otherwise, the execution type of the runtime SQL queue can be defined as fast execution. Whether the amount of data in the database source table is too large can be determined by comparing the amount of data in the database source table with the data amount threshold.

[0039] Herein, based on the database source table, a new database table having the same table structure and the same data as the database source table can be created, and the new database table is recorded as a shadow data table.

[0040] S140: Obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0041] Among them, in the process of functional upgrade of the business system, in order to prevent the database source table from being locked due to operations on the database source table, in the process of functional upgrade of the business system, when the database source table needs to be operated, the shadow data table with the same table structure and data as the database source table can be operated first; in this embodiment, the time for the runtime SQL queue to operate the shadow data table is determined according to the execution type, and the shadow data table is operated when the time comes; when the shadow data table is operated, the operation behavior needs to be recorded to obtain a corresponding operation log, and the operation log is recorded as a shadow table operation log.

[0042] It should be noted that the shadow table operation log records the operation time and content of the SQL queue on the shadow data table during runtime. After the business system completes the functional upgrade, the database source table can be synchronized through the shadow table operation log, so that the database source table after the synchronization operation and the shadow data table after the operation have the same data, and the database source table after the synchronization operation is recorded as the target source table.

[0043] It should be noted that, in this embodiment, an execution-state SQL statement is obtained by a definition object corresponding to a bean definition based on a database source table; a control SQL statement is obtained based on the execution-state SQL statement, and a runtime SQL queue is created based on the control SQL statement and the database source table; the execution type of the runtime SQL queue is determined based on the data volume and data volume threshold of the database source table, and the execution type includes slow execution and fast execution; a shadow data table is created based on the database source table; a shadow table operation log is obtained based on the execution type, the runtime SQL queue and the shadow data table, and the database source table is synchronized based on the shadow table operation log to obtain a target source table. Through the above implementation, when the business system is upgraded during the business off-peak period, even if the user operates the database source table when executing the business, the control SQL statement corresponding to the operation will first operate on the shadow table corresponding to the database source table, and the database source table can be subsequently updated according to the operation log corresponding to the operation to obtain the target source table, which can easily avoid the problem of business interruption in the business system.

[0044] Embodiment 2

[0045] A database table management method provided in the second embodiment of the present application optimizes the "obtaining an executable SQL statement based on a definition object corresponding to a bean definition of a database source table" in the first embodiment; it should be noted that for the parts not described in detail in this embodiment, reference may be made to the descriptions of other embodiments, and the method includes:

[0046] S211. Monitor the bean definition of the database source table to obtain a definition object, and obtain a database connection method based on the definition object.

[0047] Among them, a Bean preprocessor based on the Spring framework is built in the business system. The Bean preprocessor is used to monitor the bean definition of the database source table. The Bean preprocessor can load the monitored bean definition. By loading the bean definition, the definition object corresponding to the bean definition can be obtained. The definition object is an additional description of the bean definition. The definition object contains a database connection method, which is used to establish a data connection between the program and the database; the database connection method can be extracted from the definition object.

[0048] S212: Determine the method annotation added to the database connection method, and obtain a bean instance based on the method annotation and the database connection method.

[0049] Among them, in order to facilitate the subsequent acquisition of the execution SQL statement through the database connection method, the database connection method needs to be annotated first, and the added annotation is recorded as a method annotation. In this embodiment, the method annotation includes a method tag and a tag name. For example, the method tag is @Bean, and the tag name is getUpdateConn.

[0050] Furthermore, by adding a corresponding method annotation to the database connection method, the database connection method can be upgraded, so that the database method is upgraded to a bean instance corresponding to the bean (ie, the database source table) under the Spring framework.

[0051] S213. Perform bytecode enhancement on the bean instance to obtain an enhanced instance, and determine an executable SQL statement based on the enhanced instance.

[0052] Among them, bytecode enhancement means adding additional functions to the bean instance without modifying the code of the bean instance to improve the maintainability and scalability of the code, and the new bean instance obtained after bytecode enhancement of the bean instance is recorded as an enhanced instance; the enhanced instance is connected with an SQL statement for operating the database source table, and the SQL statement connected to the enhanced instance for operating the database source table is recorded as an execution-state SQL statement.

[0053] S220: Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table.

[0054] S230: Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution; and create a shadow data table based on the database source table.

[0055] S240: Obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0056] Embodiment 3

[0057] A database table management method is provided in Embodiment 3 of the present application. The method optimizes the "obtaining a control SQL statement based on the execution SQL statement, and creating a runtime SQL queue based on the control SQL statement and the database source table" in Embodiment 1; it should be noted that for the part not described in detail in this embodiment, reference can be made to the description of other embodiments. The method includes:

[0058] S310. Obtain an execution-state SQL statement based on a definition object corresponding to the bean definition of a database source table.

[0059] S321. In response to the execution-state SQL statement being a preset type SQL statement, or in response to the execution-state SQL statement generating a running flag during execution, use the execution-state SQL statement as a control SQL statement.

[0060] Among them, the preset SQL statements include DDL (Data Definition Language) and DML (Data Manipulation Language); DDL is mainly used to define and manage various objects in the database, such as the structure of databases, tables, views, indexes, etc. Through DDL statements, these database objects can be created, modified, and deleted; DML is used to operate on the data in the database, including inserting, updating, deleting, and querying data.

[0061] Exemplarily, DDL includes CREATE statements (used to create a database or table), ALTER statements (used to modify the structure of database objects, such as adding columns, changing column data types, deleting tables, and deleting databases), and TRUNCATE (used to quickly delete all data in a table but retain the table structure).

[0062] Exemplarily, DML includes INSERT statements (used to insert new data records into a table), UPDATE statements (used to modify existing data records in a table), DELETE statements (used to delete data records from a table), and SELECT statements (used to query data from a table).

[0063] It should be noted that DDL mainly operates on the structure of database objects, while DML mainly operates on the data in the database. In addition, DDL statements are usually automatically committed and cannot be rolled back after execution; while DML statements can be included in transactions and support rollback operations.

[0064] It should also be noted that the preset type SQL statements are currently all SQL statements that are being compiled, that is, the preset type SQL statements are not currently performing corresponding operations on the database source table.

[0065] Among them, at the current moment, some execution-state SQL statements may have started operations on the database source table, but have not yet returned the corresponding operation results. In this case, this execution-state SQL statement will generate a running flag during the execution process. The running flag is used to indicate that the corresponding execution-state SQL statement is currently in the process of executing the corresponding operation; exemplarily, the running flag is "Waiting for meta data lock".

[0066] It should be noted that, during the function upgrade of the business system, if the execution SQL statement operates on the database source table, it will cause the database source table to be locked, which will affect the normal operation of the business. Therefore, it is necessary to determine whether the execution SQL statement is a preset type SQL statement at the current moment (a moment before the business system is upgraded), or determine whether the execution SQL statement generates a running flag during the execution process; if it is determined that the execution SQL statement is a preset type SQL statement, or it is determined that the execution SQL statement generates a running flag during the execution process, it means that this type of execution SQL statement may cause the database source table to be locked next, so this type of execution SQL statement currently needs to be controlled to stop it from continuing to operate on the database source table; if the execution SQL statement is a preset type SQL statement, or it is determined that the execution SQL statement generates a running flag during the execution process, the execution SQL statement is controlled and hijacked by the preset dynamic controller, and the execution SQL statement is used as a controlled SQL statement.

[0067] S322: Determine the operation sequence of the control SQL statement operating the database source table, and obtain a runtime SQL queue based on the operation sequence and the control SQL statement.

[0068] Among them, the control SQL statement is used to operate the database source table, and the control SQL statement is preset with a corresponding operation sequence. For example, the operation sequence of one control SQL statement to operate the database source table is the first, and the operation sequence of another control SQL statement to operate the database source table is the second, .... Through the one-to-one corresponding operation sequence of each control SQL statement, each control SQL statement is arranged in sequence to obtain a queue composed of each control SQL statement, and the queue is recorded as the runtime SQL queue.

[0069] S330: Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution; and create a shadow data table based on the database source table.

[0070] S340: Obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0071] Embodiment 4

[0072] A database table management method provided in a fourth embodiment of the present application optimizes the "creating a shadow data table based on the database source table" in the first embodiment; it should be noted that for the parts not described in detail in this embodiment, reference may be made to the descriptions of other embodiments, and the method includes:

[0073] S410: Obtain an execution-state SQL statement based on a definition object corresponding to the bean definition of a database source table.

[0074] S420: Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table.

[0075] S431. Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution.

[0076] S432: Extract the table structure of the database source table, and create a first shadow empty table based on the table structure.

[0077] In this embodiment, the table structure of the database source table includes columns (fields), primary keys, foreign keys, and constraints; wherein the columns include definitions and data types, and the definitions represent that each column in the table represents a specific attribute or data item, and the data types include integer type, string type, date and time type, etc.; the primary key is a combination of one or more columns in the table, and its value can uniquely identify each row of records in the table. For example, in the "employee information table", "employee number" can usually be used as the primary key because each employee's number is unique; the foreign key is one or more columns in the table, which references the primary key of another table. The foreign key is used to establish an association relationship between tables. For example, in the "order table", there may be a "customer number" column, which references the "customer number" primary key in the "customer information table", and the order can be associated with the corresponding customer through this foreign key; the constraints are used to limit the value range and rules of the data in the table to ensure the integrity and accuracy of the data. Common constraints include: non-null constraint (NOT NULL): stipulates that the value of a column cannot be null. For example, the "employee name" column is usually set to a non-null constraint, because each employee should have a name; unique constraint (UNIQUE): ensure that the combined value of a column or multiple columns is unique in the table. For example, the "employee email" column can be set with a unique constraint to ensure that each employee's email address is not repeated; check constraint (CHECK): specify that the value of a column must meet a specific condition. For example, the "employee age" column can be set with a check constraint, requiring the age value to be between 18 and 60.

[0078] It should be noted that by determining the table structure of a table, a new empty table can be created based on the table structure. Therefore, after determining the table structure of the database source table, a new empty table can be created based on the table structure, and the new empty table created based on the table structure of the database source table is recorded as the first shadow empty table.

[0079] S433: Obtain a second shadow empty table based on the control SQL statement and the first shadow empty table, and lock the database source table.

[0080] Among them, although the first shadow empty table is created according to the table structure of the database source table, it cannot be guaranteed that the first shadow empty table has the same table structure as the database source table; to ensure that the first shadow empty table has the same table structure as the database source table, it is necessary to process the first shadow empty table through control SQL statements, that is, by updating the table structure of the first shadow empty table, so that the first shadow empty table and the database source table have the same table structure; and the new empty table obtained after the table structure of the first shadow empty table is updated is recorded as the second shadow empty table.

[0081] It should be noted that in order to ensure the normal progress of the business during the functional upgrade of the business system, the operation on the database source table can be replaced by operating a table with the same table structure and data as the database source table. To this end, on the basis of making the second shadow empty table have the same table structure as the database source table, all the data in the database source table can also be backed up to the second shadow empty table. For this purpose, the database source table needs to be locked first.

[0082] S434: Obtain a shadow data table based on the database source table and the second shadow empty table.

[0083] In order to obtain a table with the same table structure and data as the database source table, all data in the database source table must be backed up to the second shadow empty table, and the new table obtained by backing up all data in the database source table in the second shadow empty table is recorded as a shadow data table.

[0084] S440: Obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0085] Embodiment 5

[0086] A database table management method provided in Embodiment 5 of the present application optimizes the "obtaining a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table" in Embodiment 1; it should be noted that for the parts not described in detail in this embodiment, reference may be made to the descriptions of other embodiments, and the method includes:

[0087] S510: Obtain an execution-state SQL statement based on a definition object corresponding to the bean definition of a database source table.

[0088] S520: Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table.

[0089] S530: Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution; and create a shadow data table based on the database source table.

[0090] S541. In response to the execution type being fast execution and the statement type of the SQL statement in the runtime SQL queue being a preset statement type, the table name of the database source table in the SQL statement in the runtime SQL queue is replaced with the table name of the shadow data table to obtain a target SQL queue.

[0091] Among them, in this embodiment, the data volume threshold is set to 1 million bytes, and the data volume of the data in the database source table is obtained. If the data volume is greater than the data volume threshold, it is considered that it will take a long time for the runtime SQL queue to complete all operations. At this time, the execution type of the runtime SQL queue is defined as slow execution. Otherwise, the execution type of the runtime SQL queue is defined as fast execution.

[0092] The SQL statements in the runtime SQL queue have different statement types. Exemplarily, the statement types include query statements, insert statements, edit statements, and delete statements, etc.; wherein, the preset statement types include insert statements, edit statements, and delete statements.

[0093] It should be noted that when the statement type of the SQL statement in the runtime SQL queue is a query statement, this type of SQL statement will not change the table structure and data of the database source table when operating the database source table. Therefore, the database source table can be operated; however, when the statement type of the SQL statement in the runtime SQL queue is one of an insert statement, an edit statement, and a delete statement, this type of SQL statement will change the table structure or data of the database source table when operating the database source table. Therefore, in order to prevent the database source table from being locked due to changes in the table structure or data of the database source table, for this purpose, based on the generation of the shadow data table, the table name of the database source table in the SQL statement in the runtime SQL queue can be replaced with the table name of the shadow data table, and the new runtime SQL queue obtained after the table name is replaced is recorded as the target SQL queue.

[0094] S542: Obtain a shadow table operation log based on the target SQL queue and the shadow data table.

[0095] The shadow data table is operated in sequence through the SQL statements in the target SQL queue. Corresponding log records are generated during the operation process, and the log records are recorded as shadow table operation logs.

[0096] S543: Synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0097] Embodiment 6

[0098] A database table management method provided in Example 6 of the present application optimizes the "obtaining an executable SQL statement based on a definition object corresponding to a bean definition of a database source table" in Example 5; it should be noted that for the parts not described in detail in this embodiment, reference may be made to the descriptions of other embodiments, and the method includes:

[0099] S610: Obtain an execution-state SQL statement based on a definition object corresponding to the bean definition of a database source table.

[0100] S620: Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table.

[0101] S630: Determine the execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, where the execution type includes slow execution and fast execution; and create a shadow data table based on the database source table.

[0102] S641. In response to the execution type being fast execution and the statement type of the SQL statement in the runtime SQL queue being a preset statement type, the table name of the database source table in the SQL statement in the runtime SQL queue is replaced with the table name of the shadow data table to obtain a target SQL queue.

[0103] S642: Obtain a shadow table operation log based on the target SQL queue and the shadow data table.

[0104] S643: In response to the execution type being slow execution, execute the runtime SQL queue to obtain an execution log.

[0105] Among them, when the execution type is slow execution, in order to ensure operation efficiency, it is necessary to continue to execute the SQL statements in the runtime SQL queue and record the corresponding execution logs during execution.

[0106] S644. In response to the number of SQL statements in the execution log or the runtime SQL queue exceeding a preset number threshold, and there are SQL statements in a preset language in the execution log, a shadow table operation log is obtained based on the runtime SQL queue and the shadow data table.

[0107] Among them, the execution log will record the SQL statements in the runtime SQL queue participating in the operation; in this embodiment, the preset quantity threshold is 100; the preset language SQL statements in this embodiment include editing statements and modifying statements; if it is determined that the number of SQL statements in the execution log or the runtime SQL queue exceeds the preset quantity threshold, and there are preset language SQL statements in the execution log, it means that the execution of the SQL statements in the runtime SQL queue at this time will cause the database source table to lock the table. At this time, it is necessary to operate the shadow data table in sequence through the SQL statements in the runtime SQL queue, and record the corresponding logs during the operation, which are recorded as shadow table operation logs.

[0108] S645. Synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0109] It should be understood that, although the various steps in the flowcharts involved in the above-mentioned embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above-mentioned embodiments can include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0110] Embodiment 7

[0111] Based on the same inventive concept, this embodiment also provides a database table management device for implementing the database table management method involved above. The implementation solution provided by the device to solve the problem is similar to the implementation solution recorded in the above method, so the specific limitations in one or more database table management device embodiments provided below can refer to the limitations of the database table management method above, and will not be repeated here.

[0112] In this embodiment, Figure 2 As shown, a database table management device is provided, comprising:

[0113] The statement acquisition module is used to obtain the execution SQL statement based on the definition object corresponding to the bean definition of the database source table;

[0114] A queue generation module, used to obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table;

[0115] A data table creation module, used to determine the execution type of the runtime SQL queue based on the data volume and data volume threshold of the database source table, wherein the execution type includes slow execution and fast execution; and to create a shadow data table based on the database source table;

[0116] The target table generation module is used to obtain a shadow table operation log based on the execution type, the runtime SQL queue and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table.

[0117] Each module in the above-mentioned database table management device can be implemented in whole or in part by software, hardware or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute the operations corresponding to each module above.

[0118] It should be noted that, in this embodiment, an execution-state SQL statement is obtained by a definition object corresponding to a bean definition based on a database source table; a control SQL statement is obtained based on the execution-state SQL statement, and a runtime SQL queue is created based on the control SQL statement and the database source table; the execution type of the runtime SQL queue is determined based on the data volume and data volume threshold of the database source table, and the execution type includes slow execution and fast execution; a shadow data table is created based on the database source table; a shadow table operation log is obtained based on the execution type, the runtime SQL queue and the shadow data table, and the database source table is synchronized based on the shadow table operation log to obtain a target source table. Through the above implementation, when the business system is upgraded during the business off-peak period, even if the user operates the database source table when executing the business, the control SQL statement corresponding to the operation will first operate on the shadow table corresponding to the database source table, and the database source table can be subsequently updated according to the operation log corresponding to the operation to obtain the target source table, which can easily avoid the problem of business interruption in the business system.

[0119] In one embodiment, in terms of obtaining an execution SQL statement based on a definition object corresponding to a bean definition of a database source table, the statement acquisition module is specifically used to:

[0120] Monitor the bean definition of the database source table to obtain a definition object, and obtain a database connection method based on the definition object;

[0121] Determine the method annotation added to the database connection method, and obtain a bean instance based on the method annotation and the database connection method;

[0122] Bytecode enhancement is performed on the bean instance to obtain an enhanced instance, and an executable SQL statement is determined based on the enhanced instance.

[0123] In one embodiment, in terms of obtaining a control SQL statement based on the execution SQL statement, and creating a runtime SQL queue based on the control SQL statement and the database source table, the queue generation module is specifically used to:

[0124] In response to the execution-state SQL statement being a preset type SQL statement, or in response to the execution-state SQL statement generating a running flag during execution, using the execution-state SQL statement as a control SQL statement;

[0125] An operation sequence of the control SQL statement operating the database source table is determined, and a runtime SQL queue is obtained based on the operation sequence and the control SQL statement.

[0126] In one embodiment, in terms of creating a shadow data table based on the database source table, the data table creation module is specifically used to:

[0127] Extracting the table structure of the database source table, and creating a first shadow empty table based on the table structure;

[0128] Obtaining a second shadow empty table based on the control SQL statement and the first shadow empty table, and locking the database source table;

[0129] A shadow data table is obtained based on the database source table and the second shadow empty table.

[0130] In one embodiment, in obtaining the shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, the target table generation module is specifically used to:

[0131] In response to the execution type being fast execution and the statement type of the SQL statement in the runtime SQL queue being a preset statement type, replacing the table name of the database source table in the SQL statement in the runtime SQL queue with the table name of the shadow data table to obtain a target SQL queue;

[0132] A shadow table operation log is obtained based on the target SQL queue and the shadow data table.

[0133] In one embodiment, in terms of obtaining the shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, the target table generation module is further configured to:

[0134] In response to the execution type being slow execution, executing the runtime SQL queue to obtain an execution log;

[0135] In response to the number of SQL statements in the execution log or the runtime SQL queue exceeding a preset number threshold, and there are SQL statements in a preset language in the execution log, a shadow table operation log is obtained based on the runtime SQL queue and the shadow data table.

[0136] Embodiment 8

[0137] In this embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 3As shown. The computer device includes a processor, a memory and a network interface connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a database table management method is implemented.

[0138] Those skilled in the art will understand that Figure 3 The structure shown in the figure is only a block diagram of a part of the structure related to the scheme of the present disclosure, and does not constitute a limitation on the computer device to which the scheme of the present disclosure is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0139] Embodiment 9

[0140] In this embodiment, a computer readable storage medium is provided. Figure 4 As shown, a computer program is stored thereon, and when the computer program is executed by a processor, the steps in the above-mentioned method embodiments are implemented.

[0141] Embodiment 10

[0142] In this embodiment, a computer program product is provided, including a computer program. When the computer program is executed by a processor, the steps in the above method embodiments are implemented.

[0143] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this disclosure are all information and data authorized by the user or fully authorized by all parties.

[0144] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided by the present disclosure can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided by the present disclosure may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided by the present disclosure may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic unit, a data processing logic unit based on quantum computing, etc., but are not limited to this.

[0145] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0146] The above-described embodiments only express several implementation methods of the present disclosure, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present invention. It should be pointed out that, for a person of ordinary skill in the art, several modifications and improvements can be made without departing from the concept of the present disclosure, and these all belong to the protection scope of the present disclosure. Therefore, the protection scope of the present disclosure shall be subject to the attached claims.

Claims

1. A database table management method, characterized in that: include: Get the execution SQL statement based on the definition object corresponding to the bean definition of the database source table; Obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table; Determining an execution type of the runtime SQL queue based on the data volume and the data volume threshold of the database source table, wherein the execution type includes slow execution and fast execution; Create a shadow data table based on the database source table; Obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table; The definition object corresponding to the bean definition based on the database source table obtains the execution state SQL statement, including: Monitor the bean definition of the database source table to obtain a definition object, and obtain a database connection method based on the definition object; Determine the method annotation added to the database connection method, and obtain a bean instance based on the method annotation and the database connection method; Bytecode enhancement is performed on the bean instance to obtain an enhanced instance, and an executable SQL statement is determined based on the enhanced instance.

2. The method according to claim 1, characterized in that The obtaining of a control SQL statement based on the execution SQL statement, and creating a runtime SQL queue based on the control SQL statement and the database source table, includes: In response to the execution-state SQL statement being a preset type SQL statement, or in response to the execution-state SQL statement generating a running flag during execution, using the execution-state SQL statement as a control SQL statement; An operation sequence of the control SQL statement operating the database source table is determined, and a runtime SQL queue is obtained based on the operation sequence and the control SQL statement.

3. The method according to claim 1, characterized in that The creating a shadow data table based on the database source table includes: Extracting the table structure of the database source table, and creating a first shadow empty table based on the table structure; Obtaining a second shadow empty table based on the control SQL statement and the first shadow empty table, and locking the database source table; A shadow data table is obtained based on the database source table and the second shadow empty table.

4. The method according to claim 1, characterized in that: The obtaining of the shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table includes: In response to the execution type being fast execution and the statement type of the SQL statement in the runtime SQL queue being a preset statement type, replacing the table name of the database source table in the SQL statement in the runtime SQL queue with the table name of the shadow data table to obtain a target SQL queue; A shadow table operation log is obtained based on the target SQL queue and the shadow data table.

5. The method according to claim 4, characterized in that Also includes: In response to the execution type being slow execution, executing the runtime SQL queue to obtain an execution log; In response to the number of SQL statements in the execution log or the runtime SQL queue exceeding a preset number threshold, and there are SQL statements in a preset language in the execution log, a shadow table operation log is obtained based on the runtime SQL queue and the shadow data table.

6. A database table management device, characterized in that: The device comprises: The statement acquisition module is used to obtain the execution SQL statement based on the definition object corresponding to the bean definition of the database source table; A queue generation module, used to obtain a control SQL statement based on the execution SQL statement, and create a runtime SQL queue based on the control SQL statement and the database source table; A data table creation module, used to determine the execution type of the runtime SQL queue based on the data volume and data volume threshold of the database source table, wherein the execution type includes slow execution and fast execution; and to create a shadow data table based on the database source table; A target table generation module, configured to obtain a shadow table operation log based on the execution type, the runtime SQL queue, and the shadow data table, and synchronize the database source table based on the shadow table operation log to obtain a target source table; The definition object corresponding to the bean definition based on the database source table obtains the execution state SQL statement, including: Monitor the bean definition of the database source table to obtain a definition object, and obtain a database connection method based on the definition object; Determine the method annotation added to the database connection method, and obtain a bean instance based on the method annotation and the database connection method; Bytecode enhancement is performed on the bean instance to obtain an enhanced instance, and an executable SQL statement is determined based on the enhanced instance.

7. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 5 are implemented.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

9. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

Images