Database migration method, apparatus, device, and storage medium

Abstract

The application provides a database migration method and device, equipment and storage medium, relates to the technical field of database, can be applied to various scenes such as cloud technology, artificial intelligence, intelligent transportation and auxiliary driving, and includes the following steps: acquiring first data table information and first view information of a source database and second data table information and second view information of a target database respectively; generating a table creation statement of a first data table corresponding to the source database according to the first data table information and the first view information; generating a table creation statement of a second data table corresponding to the target database according to the second data table information and the second view information; comparing the table creation statement of the first data table and the table creation statement of the second data table by string comparison to obtain a table structure comparison result; and performing database migration based on the table structure comparison result to migrate target data in the source database to the target database, which can effectively improve the security of database migration.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database migration method, apparatus, device, and storage medium. Background Technology

[0002] Database migration is a common requirement in scenarios such as database version updates, expansion, distributed storage, or hardware upgrades. Currently, common database migration solutions are mainly divided into manual migration and fully automated migration. Manual migration involves developers manually writing SQL statements to migrate the database, resulting in low success rates and efficiency, and is prone to errors and omissions. Currently, fully automated migration solutions heavily rely on and are limited by the built-in functions of the database framework, exhibiting poor generalization capabilities. For example, in database version updates, changes to the database framework or programming language can significantly impact these fully automated migration solutions. Furthermore, due to the limitations imposed by the database framework, migration failures or incomplete data migrations are likely. Additionally, the migration process is completely opaque, making it impossible to control sensitive or dangerous operations, potentially leading to data loss.

[0003] Therefore, an improved database migration solution is needed to address the aforementioned existing problems. Summary of the Invention

[0004] This application provides a database migration method, apparatus, and storage medium, which has good generalization ability and effectively improves the operational security of data migration.

[0005] On the one hand, this application provides a database migration method, the method comprising:

[0006] The first data table information and the first view information of the source database, and the second data table information and the second view information of the target database are obtained respectively.

[0007] Generate a table creation statement for the first data table corresponding to the source database based on the first data table information and the first view information;

[0008] Generate a table creation statement for the second data table corresponding to the target database based on the second data table information and the second view information;

[0009] The table creation statements of the first data table and the table creation statements of the second data table are compared as strings to obtain the table structure comparison results;

[0010] Based on the table structure comparison results, database migration is performed to migrate the target data from the source database to the target database.

[0011] On the other hand, a database migration apparatus is provided, the apparatus comprising:

[0012] Table structure information acquisition module: used to acquire the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively;

[0013] First table creation statement generation module: used to generate a table creation statement for the first data table corresponding to the source database based on the first data table information and the first view information;

[0014] The second table creation statement generation module is used to generate a table creation statement for the second data table corresponding to the target database based on the second data table information and the second view information.

[0015] Table structure comparison module: used to compare the table creation statements of the first data table and the table creation statements of the second data table to obtain the table structure comparison results;

[0016] Database migration module: used to perform database migration based on the table structure comparison results, so as to migrate the target data in the source database to the target database.

[0017] On the other hand, a computer device is provided, the device including a processor and a memory, the memory storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the database migration method as described above.

[0018] On the other hand, a computer-readable storage medium is provided, wherein at least one instruction or at least one program is stored therein, the at least one instruction or the at least one program being loaded and executed by a processor to implement the database migration method as described above.

[0019] On the other hand, a server is provided, the server including a processor and a memory, the memory storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the database migration method as described above.

[0020] On the other hand, a computer program product or computer program is provided, which includes computer instructions that, when executed by a processor, implement the database migration method described above.

[0021] The database migration method, apparatus, device, storage medium, server, and computer program product provided in this application have the following technical effects:

[0022] The technical solution of this application obtains the first data table information and first view information of the source database, and the second data table information and second view information of the target database respectively; then it generates table creation statements for the first data table corresponding to the source database, and table creation statements for the second data table corresponding to the target database; then it compares the table creation statements of the first and second data tables to obtain the table structure comparison results, and then performs database migration based on the table structure comparison results to migrate the target data from the source database to the target database. This semi-automatic database migration reduces migration costs, improves migration efficiency, and avoids dangerous operations such as improper data deletion and modification caused by black-box operations, thus improving database security during the migration process. Furthermore, the solution of this application can be executed based on an independent third-party program, without relying on or being limited by the database framework and its own provided functions, decoupling from the database code logic and exhibiting good generalization capabilities. Attached Figure Description

[0023] To more clearly illustrate the technical solutions and advantages in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0024] Figure 1 This is a schematic diagram of an application environment provided in an embodiment of this application;

[0025] Figure 2 This is a flowchart illustrating a database migration method provided in an embodiment of this application;

[0026] Figure 3 This is a schematic diagram illustrating the principle of a database migration method provided in an embodiment of this application;

[0027] Figure 4 This is a flowchart illustrating another database migration method provided in an embodiment of this application;

[0028] Figure 5 This is a flowchart illustrating another database migration method provided in an embodiment of this application;

[0029] Figure 6 This is a schematic diagram of the framework of a database migration device provided in an embodiment of this application;

[0030] Figure 7 This is a hardware structure block diagram of an electronic device for a database migration method provided in an embodiment of this application;

[0031] Figure 8This is a schematic diagram of the structure of a blockchain system provided in an embodiment of this application. Detailed Implementation

[0032] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0033] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or server that comprises a series of steps or sub-modules is not necessarily limited to those steps or sub-modules explicitly listed, but may include other steps or sub-modules not explicitly listed or inherent to such processes, methods, products, or devices.

[0034] Please see Figure 1 , Figure 1 This is a schematic diagram of an application environment provided in an embodiment of this application, such as... Figure 1 As shown, the application environment may include at least server 01 and terminal 02. In practical applications, server 01 and terminal 02 can be directly or indirectly connected via wired or wireless communication, and this application does not impose any restrictions on this.

[0035] In this embodiment, server 01 can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms.

[0036] Specifically, cloud technology refers to a managed technology that unifies hardware, software, and network resources within a wide area network (WAN) or local area network (LAN) to achieve data computation, storage, processing, and sharing. It distributes computing tasks across a resource pool composed of numerous computers, enabling various application systems to access computing power, storage space, and information services as needed. The network providing these resources is called the "cloud." Artificial intelligence cloud services are generally also known as AIaaS (AI as a Service). This is currently a mainstream service model for artificial intelligence platforms. Specifically, AIaaS platforms break down several common AI services and provide them as independent or packaged services in the cloud. This service model is similar to opening an AI-themed marketplace: all developers can access and use one or more AI services provided by the platform through API interfaces. Some experienced developers can also use the AI ​​framework and AI infrastructure provided by the platform to deploy and maintain their own dedicated cloud AI services.

[0037] Specifically, a database can be simply viewed as an electronic filing cabinet—a place to store electronic files, where users can perform operations such as adding, querying, updating, and deleting data. A "database" is a collection of data stored together in a certain way, shared by multiple users, with minimal redundancy, and independent of application programs. A Database Management System (DBMS) is a computer software system designed to manage databases, generally possessing basic functions such as storage, retrieval, security, and backup. DBMSs can be classified according to the database model they support, such as relational or XML (Extensible Markup Language); or according to the type of computer they support, such as server clusters or mobile phones; or according to the query language used, such as SQL (Structured Query Language) or XQuery; or according to performance priorities, such as maximum scale or maximum operating speed; or other classification methods. Regardless of the classification method used, some DBMSs can cross categories, for example, supporting multiple query languages ​​simultaneously.

[0038] Specifically, the servers mentioned above may include physical devices, such as network communication submodules, processors, and memory, as well as software running on the physical devices, such as applications.

[0039] In this embodiment, terminal 02 may include physical devices such as smartphones, desktop computers, tablets, laptops, digital assistants, augmented reality (AR) / virtual reality (VR) devices, smart voice interaction devices, smart home appliances, smart wearable devices, and in-vehicle terminal devices, and may also include software running on the physical device, such as applications.

[0040] In this embodiment, server 01 runs a source database and a target database, which can run in different server clusters. Server 01 runs a database migration program independent of the source and target databases, providing data migration services. This program can automatically generate database migration instructions to trigger database migration, or it can trigger database migration in response to database migration instructions sent by terminal 02. Specifically, during the database migration process, server 01 also sends a database migration command file to terminal 02, obtains command modification information for the database migration command file from terminal 02, and then executes the database migration command file based on the command modification information to migrate the target data from the source database to the target database.

[0041] In this embodiment, terminal 02 can receive and display a database migration command file, generate corresponding command modification information in response to modifications to the database migration command file, and send it to server 01. Specifically, terminal 02 can also send database migration instructions to server 01.

[0042] Furthermore, it is understandable that Figure 1 The example shown is merely an application environment for one database migration method. This application environment may include more or fewer nodes, and this application does not impose any restrictions here.

[0043] The application environment involved in this application embodiment, or the terminal 02 and server 01 in the application environment, can be a distributed system formed by connecting clients and multiple nodes (any form of computing device accessing the network, such as servers and user terminals) through network communication. The distributed system can be a blockchain system, which can provide the aforementioned database migration service and data storage service, etc.

[0044] See Figure 8 , Figure 8This is an optional structural diagram of the distributed system 100 provided in this embodiment of the invention applied to a blockchain system. It consists of multiple nodes (any form of computing device in the network, such as servers or user terminals) and clients, forming a peer-to-peer (P2P) network. The P2P protocol is an application layer protocol running on top of the Transmission Control Protocol (TCP). In the distributed system, any machine, such as a server or terminal, can join and become a node. A node includes a hardware layer, a middleware layer, an operating system layer, and an application layer.

[0045] The following describes a database migration method based on the aforementioned application environment, applied to the server side. This embodiment can be applied to various scenarios, including but not limited to cloud technology, artificial intelligence, smart transportation, and assisted driving. Please refer to... Figure 2 , Figure 2 This is a flowchart illustrating a database migration method provided in an embodiment of this application. This specification provides method operation steps as shown in the embodiments or flowcharts, but based on conventional or non-inventive labor, more or fewer operation steps may be included. The order of steps listed in the embodiments is merely one possible execution order among many, and does not represent the only execution order. In actual system or server product execution, the method can be executed sequentially according to the embodiments or drawings, or in parallel (e.g., in a parallel processor or multi-threaded processing environment). Specifically, the method can be applied to database migration scenarios such as database release, version update, expansion, distributed storage, or hardware update, etc. Figure 2 As shown, the method may include the following steps.

[0046] S201: Obtain the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively.

[0047] Specifically, the source and target databases are deployed on different servers or server clusters. The server-side also runs a database migration program independent of the source and target databases to perform the database migration service. In this way, through a completely independent third-party program, without relying on or being limited by the database framework, structure, or operation method, and decoupled from the database code logic, the application's database can be switched at any time. Even if the database language changes, there is no need to redevelop the data migration program, demonstrating strong generalization capabilities.

[0048] Specifically, the database migration program calls the database interfaces of the source and target databases respectively to obtain the table structure information of the source and target databases. The table structure information includes data table information and view information. Specifically, the first data table information includes at least one data table of a first data table, and the first view information includes at least one view of a first data table; the second data table information includes at least one data table of a second data table, and the second view information includes at least one view of a second data table.

[0049] Specifically, the database migration program can detect the add / delete operations and running parameters of the source database to trigger the generation of data migration instructions, or it can receive data migration instructions sent by the terminal. Specifically, prior to S201, it can also receive migration configuration information for the source and target databases sent by the terminal. This migration configuration information may include, but is not limited to, the identification information and interface information of the source and target databases, as well as the storage location information of the data migration records.

[0050] S203: Generate the table creation statement for the first data table corresponding to the source database based on the information of the first data table and the information of the first view.

[0051] Specifically, the table creation statements include the creation statements required to create new tables. After obtaining the first data table information and the first view information, a table creation instruction is triggered. Based on a preset data table creation language, the corresponding table creation statements for each first data table are generated, and the table name and corresponding table creation statement for each first data table are associated and stored. These statements can also be sent to the terminal so that the terminal can display them in association, such as displaying the table creation statements after the corresponding table names, and displaying different data tables in segments.

[0052] In one possible implementation, after obtaining the table creation statements for each first data table, these statements can be stored as a table structure file. Specifically, the table creation statements for each first data table can be read as strings into a file, thereby generating the table structure file for the source database, and then stored in a specified storage location. The table structure file can be, but is not limited to, a JSON file. After generating the table structure file, it can be sent to a terminal for display.

[0053] S205: Generate the table creation statement for the second data table corresponding to the target database based on the second data table information and the second view information.

[0054] Specifically, after obtaining the information from the second data table and the second view, a table creation command is triggered. Based on a preset data table creation language, a table creation statement for each second data table is generated, and the table name and corresponding table creation statement for each second data table are associated and stored. In some cases, they can also be displayed in association, such as displaying the table creation statement after the corresponding table name, with different data tables displayed in segments.

[0055] S207: Compare the table creation statements of the first data table and the table creation statements of the second data table to obtain the table structure comparison results.

[0056] In some embodiments, the table creation statements of the first data table and the second data table can be directly compared as strings. Specifically, the first and second data tables are first matched based on their table names to obtain table matching results, including the first and second data tables with the same table name, and the first data table whose table name does not match. Then, for the first and second data tables with the same table name, their table creation statements are compared as strings to obtain the comparison results of the creation statements.

[0057] In other embodiments, the table creation statements of each second data table can be read into a file as strings, thereby generating a table structure file of the target database and storing it in a specified storage location. The table structure files of the source database and the target database are then compared string by string to obtain a table structure comparison result. Alternatively, the table structure file of the target database may not be generated; instead, the table creation statements of the second data tables are compared string by string with the table structure file of the source database while the table creation statements of the second data tables are being obtained. Accordingly, S207 may specifically include: reading the table structure file of the source database; and, on a table-by-table basis, comparing the table creation statements based on the table creation statements of the first data tables in the table structure file to obtain a table structure comparison result.

[0058] Specifically, for each first data table in the table structure file, the name of the second data table is matched against the table name of the first data table to obtain the table matching result in the table structure comparison result. If the table matching result matches a second data table corresponding to the first data table (i.e., a first data table and a second data table with the same name), the table creation statements of the matched first data table and the second data table are compared by string traversal to obtain the creation statement comparison result in the table structure comparison result. Then, based on this creation statement comparison result, the difference data migration is performed on the first data table that matches the second data table. If the table matching result does not match a second data table corresponding to the first data table (i.e., there is no second data table with the same name as the first data table), then the table data migration is performed on the first data table that does not match the second data table.

[0059] Specifically, the string traversal comparison of the table creation statements of the matched first and second data tables to obtain the table structure comparison results can include the following steps.

[0060] S301: Based on a preset string matching pattern, obtain the field fields of the table creation statements of the first data table and the second data table respectively.

[0061] S302: Based on the field name of the field, perform table mapping processing on the field field corresponding to the first data table and the field field corresponding to the second data table respectively to obtain the corresponding first table mapping result and the second table mapping result.

[0062] S303: Compare the row statements based on the mapping results of the first table and the mapping results of the second table to obtain the comparison results of the creation statements.

[0063] The preset string matching pattern can include, but is not limited to, matching based on regular expressions or filling update statements based on template languages. Specifically, based on the preset string matching pattern, all field names of table creation statements in the first and second data tables can be retrieved. These field names include the field name and the row statement. The field name can be the field in the table creation statement, and the row statement is the entire row statement containing that field. Then, table mapping is performed based on the field name, using the field name as the key and the corresponding row statement as the value, storing the corresponding field names of the first and second data tables in a hash table. Specifically, they can be stored in the same hash table, and if the same field name exists in both tables, the corresponding row statements from the first and second data tables can be associated with it; alternatively, they can be stored separately in the hash tables of the first and second data tables, using the field name as the key and the row statement as the value. Furthermore, by using hash table indexes, it can be determined whether the field fields of the first and second data tables match. For example, it can be determined whether there are newly added field fields, modified field field names, or deleted field fields in the table creation statement of the first data table. By comparing the row statements of the first and second data tables corresponding to the same hash table index, it can be determined whether there are field fields in the first data table whose field attributes have been modified.

[0064] Specifically, if a hash table comparison determines that all field names and row statements in the first and second data tables are identical, the table structure comparison result indicates that the table structures are the same, meaning that the corresponding data in the first table in the source database has not been changed. If a hash table index determines that the table creation statement in the first data table includes field names not found in the second data table, the creation statement comparison result indicates that a new field exists in the first data table, meaning that new data exists in the first table in the source database. Conversely, if the table creation statement in the second data table includes field names not found in the first data table, the creation statement comparison result indicates that a deleted field exists, meaning that some data in the first table in the source database has been deleted. If a string comparison is performed on the row statements of the first and second data tables corresponding to the same hash table index, and the two row statements are inconsistent, the creation statement comparison result indicates that a field with modified field attributes exists, meaning that the data attributes or data corresponding to that field in the source database have been modified.

[0065] S209: Perform database migration based on table structure comparison results to migrate target data from the source database to the target database.

[0066] In this embodiment, database migration is performed based on the table structure comparison results, which can achieve database migration in a semi-automatic manner, thereby reducing database migration costs and improving migration efficiency.

[0067] In practical applications, S209 can specifically include the following steps.

[0068] S2091: Generate database migration command file based on table structure comparison results.

[0069] In a specific embodiment, the database migration command file may include a table creation migration command. Accordingly, S2091 may specifically include: if the table matching result is that no second data table corresponding to the first data table is matched, determine that the table creation statement of the unmatched first data table is the first target creation statement, and then generate a table modification migration command corresponding to the first target creation statement.

[0070] In a specific embodiment, the database migration command file may also include table modification migration commands. S2091 may specifically include: if the comparison result of the creation statements shows that there are inconsistent table creation statements between the matched first data table and the second data table, the inconsistent table creation statement is determined to be the second target creation statement, and then the table modification migration command corresponding to the second target creation statement is generated. Thus, the first target creation statement and / or the second target creation statement are determined through the table matching result and the creation statement comparison result, and then the table modification migration command and / or the table modification migration command are generated to obtain the database migration command file.

[0071] Specifically, if, based on the table name, it is determined that no matching second table exists for any first data table in the table structure file of the source database, i.e., no second data table has the same name, then the first data table is determined to be a new data table, its creation statement is determined as the first target creation statement, and then the corresponding table creation migration command is generated to instruct the generation of the second data table corresponding to the first data table in the target database. If a matching second data table exists, but the creation statements of the two tables are inconsistent (e.g., the first data table has added fields, modified field names, deleted fields, or fields with modified attributes), the corresponding creation statement is identified as the second target creation statement. A table modification migration command is then generated to instruct the modification and updating of the corresponding creation statement in the second data table. For example, for added or deleted fields, the command instructs the appending of their corresponding creation statement to the creation statement of the second data table; for modified fields, the command instructs the appending of their corresponding creation statement to the creation statement of the second data table, and then deletes the corresponding creation statement in the second data table. Alternatively, the field name of the corresponding creation statement can be renamed and then appended to the creation statement of the second data table. It should be noted that the deletion process here can be marking the creation statement as deleted while retaining the creation statement and its corresponding data. If a matching second data table exists, but the comparison results of the creation statements for the two tables are consistent, it indicates that the data corresponding to the first data table has not changed. Therefore, the first data table is ignored, and its corresponding migration instruction is not generated.

[0072] S2092: Retrieve command modification information for the database migration command file.

[0073] Specifically, the database migration command file is sent to the terminal for display. The terminal receives modification operations on the migration commands in the file, generates corresponding command modification information, and sends it to the database migration program. For example, if the database migration command file contains table modification migration commands that modify field names or field attributes, it might indicate dangerous operations such as statement deletion or field name modification. Relevant personnel can verify and modify these commands before submitting the modification operations, thereby generating command modification information. In this way, visualizing the commands and operations involved in the migration process facilitates review, addition, deletion, and auditing, preventing data loss caused by dangerous operations.

[0074] S2093: Execute the database migration command file based on the command modification information to migrate the target data from the source database to the target database.

[0075] Specifically, the database migration program modifies and updates the database migration command file based on the command modification information, generating an updated database migration command file, which is then executed to complete the database migration. In some cases, the terminal can also update the database migration command file based on the command modification information and send the updated command file to the database migration program, which then directly executes the updated command file to complete the database migration.

[0076] In some embodiments, the table structure files of the source database can be periodically retrieved, and the currently retrieved table structure file can be compared with the previously retrieved table structure file to obtain the difference data between the two. Based on this difference data, a database migration instruction is triggered, and a corresponding database migration command file is generated to migrate the corresponding data in the source database and update the target database. This improves the efficiency of generating the database migration command file, thereby improving the overall database migration efficiency.

[0077] In a possible real-time manner, please refer to Figure 3 The above database migration method will be introduced using MySQL as an example.

[0078] 1. In response to the database migration command, the database migration program performs a dump operation on the source database to retrieve its table structure data. It executes the statement "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA={database_name}" to obtain all tables and views in the source database. Then, it executes "SHOW CREATE TABLE{database.table}" to obtain the table creation statements for each first table in the source database. All the table creation statements for the first tables are stored in a JSON file, resulting in the table structure file (env1.json). The database migration command can be sent from the terminal and carries database information for both the source (env1) and target (env2) databases, such as database identifiers.

[0079] 2. Using the database migration program, execute the compare command on the target database. First, similar to the aforementioned dump operation, obtain the table creation statements for each second table in the target database. Then, read the env1.json file and compare it with the table creation statements for each second table in the target database. If a first table is found to be absent in env2, retain its table creation statement and directly generate the corresponding SQL creation command statement. If a second table corresponding to the first table exists in env2, compare its table creation statements line by line. If the table creation statements are consistent, no action is taken. If the table creation statements are inconsistent, generate SQL modification command statements based on the comparison logic.

[0080] For example, the following shows a set of matching table creation statements for a first and a second data table.

[0081] ```

[0082] CREATE TABLE{new_table}(

[0083] `id`INT UNSIGNED AUTO_INCREMENT,

[0084] `title`VARCHAR(100)NOT NULL,

[0085] `author`VARCHAR(40)NOT NULL,

[0086] `create_date`DATE,

[0087] PRIMARY KEY(`id`)

[0088] )ENGINE=InnoDB DEFAULT CHARSET=utf8;

[0089] CREATE TABLE{old_table}(

[0090] `id`INT UNSIGNED AUTO_INCREMENT,

[0091] `title`VARCHAR(100)NOT NULL,

[0092] `create_date`DATE,

[0093] PRIMARY KEY(`id`)

[0094] )ENGINE=InnoDB DEFAULT CHARSET=utf8;

[0095] ```

[0096] First, use regular expressions to retrieve all fields from the two tables mentioned above and store them in a hash table. The key is the field name, such as `id`, and the value is the entire line of text, such as "INT UNSIGNED AUTO_INCREMENT". Then, the hash table index can be used to determine that the `author` field is missing in the second data table.

[0097] 3. Save the generated SQL creation command statements and SQL modification command statements as a database migration command file (env2.sql file).

[0098] For example, a portion of the contents of the env2.sql file can be as follows: the comparison table indicates that there are differences in the table creation statements of the first and second data tables; the newly created table indicates that the table (t_carbon_factor_favorites) is either newly added or deleted; and the identical table structure indicates that the table creation statements of the first and second data tables are consistent.

[0099] Comparison table: t_notify_letter

[0100] Comparison table: t_app_combo

[0101] Create a new table: t_carbon_factor_favorites

[0102] The table structure is the same, so skip: t_user_info_test

[0103] ...

[0104] The table structure is the same, skip: t_app_product_trial

[0105] 4. Perform an update operation on the target database: Execute the env2.sql file to migrate the target data and update the target database.

[0106] Specifically, in MySQL, table creation statements that update table fields begin with `ALTER`. For example, to create a table field name, the statement is `ALTER TABLE {table_name} ADD {field_name} {field_type};`. Similarly, in the table creation statement for the database, each line ends with `{field_type}` of that field. Accordingly, taking the aforementioned field lacking `author` as an example, during execution, the logic for generating the update statement is as follows: the table modification migration instruction for adding a field is used to instruct that `author`VARCHAR(40)NOT NULL` be appended to the end of the table creation statement for the second data table.

[0107] Furthermore, the generation logic for modification and deletion statements is similar to that for adding field fields, but it does not actually delete the table creation statement in the second data table. Instead, it performs deletion by modifying the field name or marking it as a field to be deleted, such as changing "author" to the format "author_del_{del_time}" to ensure data security.

[0108] In summary, the technical solution of this application can achieve database migration in a semi-automatic manner, automatically generating database migration command files to reduce database migration costs and improve migration efficiency. Furthermore, by obtaining command modification information, it avoids dangerous operations such as improper data deletion and modification caused by black-box operations, thus improving database security during the migration process. Simultaneously, the solution of this application can be executed based on an independent third-party program, independent of and unrestricted by the database framework and its own provided functions, decoupled from the database code logic, and has good generalization capabilities. In addition, the solution of this application is not limited by the operating system platform, possessing cross-platform universality and compatibility with multiple operating systems such as Mac, Linux, and Windows.

[0109] Based on all or part of the above implementation methods, the database migration instructions can also be automatically generated on the server side. For details, please refer to [link / reference needed]. Figure 4 Prior to S201, the method may also include the following steps.

[0110] S501: Obtain information on tablespace usage, database access volume, and tablespace response time of the source database.

[0111] S503: Performs weighted statistical processing on tablespace usage, database access volume, and tablespace response time information to obtain the operating performance results of the source database.

[0112] The tablespace usage is the difference between the total number of tablespaces in the source database and the remaining tablespace space, representing the amount of space currently used in the source database tablespaces. Database access volume represents the access status of the source database, which may include, but is not limited to, the number of tablespace connections and the number of input and output read / write operations. Tablespace response time information represents the response status of the source database to data access, which may include the average response time for data reads and the average response time for data storage. By detecting the above information from the source database, the data can be weighted and summed to obtain the runtime performance result. If the runtime performance result is greater than or equal to the performance threshold, it is determined that the runtime performance result meets the migration conditions, triggering the execution operation of S201. In this way, automatic database migration is achieved, and after the migration is completed, some or all of the data in the source database can be deleted.

[0113] Further, please refer to Figure 5 The method may also include the following steps.

[0114] S505: If the performance results do not meet the migration conditions, obtain the resource usage of the source database.

[0115] S507: If the amount of resources used exceeds the resource threshold, check the data access volume of the source data in the source database.

[0116] S509: Identify target source data whose access volume exceeds the access volume threshold.

[0117] S511: Migrate the target source data to the target database.

[0118] S513: Update the access path of the target source data.

[0119] Specifically, if the performance result is less than the performance threshold, it is determined that the migration conditions are not met. The resource usage of the source database is then checked. This resource usage represents the resource consumption during the source database's operation. If the resource usage exceeds the resource threshold, it indicates that the source database is being accessed too frequently. The data access volume of the source data is then checked and compared with the access volume threshold to determine if there is target source data with excessively high access frequency. If target source data is identified, it is migrated, and the corresponding access path is updated. The destination address of the updated access path is the address of the corresponding data in the target database. This reduces the access volume of the source database, thereby reducing its resource consumption and mitigating risks such as database breakdown.

[0120] In some embodiments, after determining the target source data, the table information corresponding to the target source data in the table structure data can be obtained, and a matching search can be performed in the target database based on the table information of the target source data. If information matching the table information of the target source data is found, it is determined that there is data matching the target source data. If the target source data and the matching data are consistent, the access path of the target source data is directly updated. If no information matching the table information of the target source data is found, a data migration instruction for the target source data is generated, the target source data will be generated in the target database, and then the access path will be updated.

[0121] This application embodiment also provides a database migration device 800, such as... Figure 6 As shown, Figure 6 The diagram shows a structural schematic of a database migration apparatus provided in an embodiment of this application. The apparatus may include the following modules.

[0122] Table structure information acquisition module 10: used to acquire the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively.

[0123] First table creation statement generation module 20: used to generate table creation statements for the first data table corresponding to the source database based on the first data table information and the first view information.

[0124] Second table creation statement generation module 30: used to generate table creation statements for the second data table corresponding to the target database based on the second data table information and the second view information.

[0125] Table structure comparison module 40: Used to compare the table creation statements of the first data table and the table creation statements of the second data table to obtain the table structure comparison results.

[0126] Database migration module 70: Used to perform database migration based on table structure comparison results, so as to migrate target data from the source database to the target database.

[0127] In some embodiments, the database migration module 70 includes the following units.

[0128] Migration command generation unit: used to generate database migration command files based on the table structure comparison results.

[0129] Command modification information acquisition unit: used to acquire command modification information for the database migration command file.

[0130] Migration command execution unit: used to execute the database migration command file based on the command modification information to migrate the target data in the source database to the target database.

[0131] In some embodiments, the apparatus further includes the following modules.

[0132] Table structure file generation module: After generating the table creation statement of the first data table corresponding to the source database based on the first data table information and the first view information, it stores the table creation statement of the first data table corresponding to the source database as a table structure file.

[0133] Accordingly, the table structure comparison module 40 may include the following sub-modules.

[0134] Table structure file reading submodule: Used to read the table structure file of the source database.

[0135] The statement comparison submodule is used to perform string comparisons of table creation statements based on the table creation statements of the first table in the table structure file, taking data tables as the unit, and obtain the table structure comparison results.

[0136] In some embodiments, the statement comparison submodule may include the following units.

[0137] Table matching unit: For each first data table in the table structure file, based on the table name of the first data table, the table name of the second data table is matched to obtain the table matching result in the table structure comparison result.

[0138] The table creation statement comparison unit is used to perform string traversal comparison on the table creation statements of the matched first and second data tables if the table matching result is a second data table corresponding to the first data table, and obtain the creation statement comparison result in the table structure comparison result.

[0139] In some embodiments, the database migration command file includes table creation migration commands, and the migration command file generation module 50 may include the following sub-modules.

[0140] The first creation statement determination submodule is used to determine the first target creation statement if the table matching result is that no second data table corresponding to the first data table is matched.

[0141] The first migration command generation submodule is used to generate the table creation migration command corresponding to the first target creation statement.

[0142] In some embodiments, the database migration command file includes table modification migration commands, and the migration command file generation module 50 may include the following sub-modules.

[0143] The second creation statement determination submodule is used to determine the inconsistent table creation statement as the second target creation statement if the comparison result of the creation statement shows that there is an inconsistency between the first and second data tables.

[0144] The second migration command generation submodule is used to generate the table modification migration commands corresponding to the second target creation statement.

[0145] In some embodiments, the table creation statement comparison unit may include the following sub-units.

[0146] The field retrieval sub-unit is used to retrieve the field fields of the table creation statements of the first data table and the second data table respectively, based on a preset string matching pattern. The field fields include the field field name and the row statement.

[0147] The table mapping processing subunit is used to perform table mapping processing on the field fields corresponding to the first data table and the field fields corresponding to the second data table according to the field field names of the field fields, so as to obtain the corresponding first table mapping results and second table mapping results.

[0148] Statement Comparison Subunit: Used to compare row statements based on the mapping results of the first table and the mapping results of the second table, and obtain the comparison results of creation statements.

[0149] In some embodiments, the table mapping processing subunit may be specifically used to: store the field fields corresponding to the first data table and the field fields corresponding to the second data table in a hash table, using the field field name as the key and the corresponding row statement as the value.

[0150] In some embodiments, the apparatus further includes the following modules.

[0151] Database information acquisition module: Before acquiring the first data table information and first view information of the source database, and the second data table information and second view information of the target database, the module acquires the table space usage, database access volume and table space response time information of the source database.

[0152] Weighted statistics module: Used to perform weighted statistical processing on tablespace usage, database access volume, and tablespace response time information to obtain the running performance results of the source database.

[0153] Migration condition judgment module: When the running performance results meet the migration conditions, it executes the steps of obtaining the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively.

[0154] In some embodiments, the apparatus further includes the following modules.

[0155] Resource usage acquisition module: Used to obtain the resource usage of the source database when the running performance results do not meet the migration conditions.

[0156] Data access volume detection module: Used to detect the data access volume of source data in the source database if the resource usage exceeds the resource threshold.

[0157] Target data determination module: used to determine target source data whose access volume exceeds the access volume threshold.

[0158] Target source database migration module: Used to migrate target source data to the target database.

[0159] Access path update module: Used to update the access path of the target source data.

[0160] It should be noted that the above-described device embodiments and method embodiments are based on the same implementation methods.

[0161] This application provides a database migration device. The identification device can be a terminal or a server. The database migration device includes a processor and a memory. The memory stores at least one instruction or at least one program. The at least one instruction or at least one program is loaded and executed by the processor to implement the database migration method provided in the above method embodiments.

[0162] Memory is used to store software programs and modules. The processor executes these stored software programs and modules to perform various functional applications and data processing. Memory can primarily consist of a program storage area and a data storage area. The program storage area stores the operating system, application programs required for functionality, etc.; the data storage area stores data created based on device usage, etc. Furthermore, memory can include high-speed random access memory (RAM) and non-volatile memory, such as at least one disk storage device, flash memory device, or other volatile solid-state storage device. Accordingly, memory can also include a memory controller to provide the processor with access to the memory.

[0163] The methods and embodiments provided in this application can be executed in electronic devices such as mobile terminals, computer terminals, servers, or similar computing devices. Figure 7 This is a hardware structure block diagram of an electronic device for a database migration method provided in an embodiment of this application. For example... Figure 7As shown, the electronic device 900 can vary significantly due to different configurations or performance. It may include one or more Central Processing Units (CPUs) 910 (CPUs 910 may include, but are not limited to, microprocessors such as MCUs or programmable logic devices such as FPGAs), a memory 930 for storing data, and one or more storage media 920 (e.g., one or more mass storage devices) for storing application programs 923 or data 922. The memory 930 and storage media 920 may be temporary or persistent storage. The program stored in the storage media 920 may include one or more modules, each module including a series of instruction operations on the electronic device. Furthermore, the CPU 910 may be configured to communicate with the storage media 920 and execute a series of instruction operations in the storage media 920 on the electronic device 900. The electronic device 900 may also include one or more power supplies 960, one or more wired or wireless network interfaces 950, one or more input / output interfaces 940, and / or one or more operating systems 921, such as Windows Server. TM Mac OS X TM Unix TM Linux™, FreeBSD™, etc.

[0164] The input / output interface 940 can be used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the electronic device 900. In one example, the input / output interface 940 includes a network interface controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the input / output interface 940 may be a radio frequency (RF) module for wireless communication with the Internet.

[0165] Those skilled in the art will understand that Figure 7 The structure shown is for illustrative purposes only and does not limit the structure of the electronic device described above. For example, the electronic device 900 may also include... Figure 7 The more or fewer components shown, or having the same Figure 7 The different configurations shown.

[0166] Embodiments of this application also provide a computer-readable storage medium, which can be disposed in an electronic device to store at least one instruction or at least one program related to implementing a database migration method in the method embodiments. The at least one instruction or the at least one program is loaded and executed by the processor to implement the database migration method provided in the above method embodiments.

[0167] Optionally, in this embodiment, the storage medium may be located at at least one of the multiple network servers in a computer network. Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0168] According to one aspect of this application, a computer program product or computer program is provided, comprising computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the various alternative implementations described above.

[0169] As can be seen from the embodiments of the database migration method, apparatus, device, server, terminal, storage medium, and program product provided in this application, the technical solution of this application obtains the first data table information and first view information of the source database, and the second data table information and second view information of the target database respectively; then generates table creation statements for the first data table corresponding to the source database, and table creation statements for the second data table corresponding to the target database; then compares the table creation statements of the first data table and the second data table to obtain the table structure comparison result, and then performs database migration based on the table structure comparison result to migrate the target data in the source database to the target database. This semi-automatic approach enables database migration, automatically generating database migration command files to reduce migration costs and improve migration efficiency. Furthermore, by obtaining command modification information, it avoids dangerous operations such as improper data deletion and modification caused by black-box operations, thus improving database security during the migration process. Simultaneously, the solution of this application can be executed based on an independent third-party program, without relying on or being limited by the database framework and its own provided functions, decoupling from the database code logic and exhibiting good generalization ability.

[0170] It should be noted that the order of the embodiments described above is merely for descriptive purposes and does not represent the superiority or inferiority of the embodiments. Furthermore, the above description focuses on specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims can be performed in a different order than that shown in the embodiments and still achieve the desired results. Additionally, the processes depicted in the drawings do not necessarily require a specific or sequential order to achieve the desired results. In some implementations, multitasking and parallel processing are also possible or may be advantageous.

[0171] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device, equipment, and storage medium embodiments are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0172] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by hardware, or by a program instructing the relevant hardware to implement them. The program can be stored in a computer-readable storage medium, such as a read-only memory, a disk, or an optical disk.

[0173] The above are merely preferred embodiments of this application and are not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A database migration method, characterized in that, The method includes: Obtain the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively; Generate a table creation statement for the first data table corresponding to the source database based on the first data table information and the first view information; Generate a table creation statement for the second data table corresponding to the target database based on the second data table information and the second view information; The table creation statements of the first data table and the table creation statements of the second data table are compared as strings to obtain the table structure comparison results; Based on the table structure comparison results, database migration is performed to migrate the target data from the source database to the target database; The database migration based on the table structure comparison results, to migrate the target data from the source database to the target database, includes: If the table structure comparison result is that no second data table corresponding to the first data table is matched, the table creation statement of the first data table that is not matched is determined to be the first target creation statement; Generate a table creation migration command corresponding to the first target creation statement. The table creation migration command belongs to the database migration command file and is used to instruct the generation of a second data table corresponding to the first data table in the target database.

2. The method according to claim 1, characterized in that, The database migration based on the table structure comparison results, to migrate the target data from the source database to the target database, includes: Generate a database migration command file based on the table structure comparison results; Obtain command modification information for the database migration command file; The database migration command file is executed based on the command modification information to migrate the target data from the source database to the target database.

3. The method according to claim 2, characterized in that, After generating the table creation statement for the first data table corresponding to the source database based on the first data table information and the first view information, the method further includes: Store the table creation statement of the first data table corresponding to the source database as a table structure file; The string comparison of the table creation statements for the first data table and the second data table to obtain the table structure comparison result includes: Read the table structure file of the source database; Using data tables as units, the table creation statements in the first data table of the table structure file are compared with strings to obtain the table structure comparison results.

4. The method according to claim 3, characterized in that, The step of comparing the table creation statements based on the table creation statements of the first data table in the table structure file, using data tables as units, to obtain the table structure comparison results includes: For each first data table in the table structure file, based on the table name of the first data table, string matching is performed on the table name of the second data table to obtain the table matching result in the table structure comparison result; If the table matching result matches a second data table corresponding to the first data table, the table creation statements of the matched first data table and the second data table are compared by string traversal to obtain the creation statement comparison result in the table structure comparison result.

5. The method according to claim 3, characterized in that, The database migration command file includes table modification migration commands, and the process of generating the database migration command file based on the table structure comparison results includes: If the comparison result of the creation statement is that there is an inconsistent table creation statement between the matched first data table and the second data table, the inconsistent table creation statement is determined to be the second target creation statement; Generate the table modification migration command corresponding to the second target creation statement.

6. The method according to claim 4, characterized in that, The string traversal comparison of the table creation statements in the matched first and second data tables, to obtain the table structure comparison results, includes: Based on a preset string matching pattern, the field fields of the table creation statements of the first data table and the second data table are obtained respectively. The field fields include the field field name and the row statement. Based on the domain field name, table mapping processing is performed on the domain field corresponding to the first data table and the domain field corresponding to the second data table respectively to obtain the corresponding first table mapping result and second table mapping result; Based on the mapping results of the first table and the mapping results of the second table, the row statements are compared to obtain the comparison result of the creation statement.

7. The method according to claim 6, characterized in that, The table mapping process for the field fields corresponding to the first data table and the field fields corresponding to the second data table includes: Using the field name as the key and the corresponding row statement as the value, store the field fields corresponding to the first data table and the field fields corresponding to the second data table in a hash table.

8. The method according to any one of claims 1-7, characterized in that, Before obtaining the first data table information and first view information of the source database, and the second data table information and second view information of the target database, the method further includes: Obtain information on tablespace usage, database access volume, and tablespace response time of the source database; The tablespace usage, database access volume, and tablespace response time information are weighted and statistically processed to obtain the operating performance results of the source database; If the performance results meet the migration conditions, the steps of obtaining the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database are executed respectively.

9. The method according to claim 8, characterized in that, The method further includes: If the performance results do not meet the migration conditions, obtain the resource usage of the source database; If the amount of resources used exceeds the resource threshold, the data access volume of the source data in the source database is detected. Identify the target source data whose data access volume exceeds the access volume threshold; Migrate the target source data to the target database; Update the access path of the target source data.

10. A database migration device, characterized in that, The device includes: Table structure information acquisition module: used to acquire the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively; First table creation statement generation module: used to generate a table creation statement for the first data table corresponding to the source database based on the first data table information and the first view information; The second table creation statement generation module is used to generate a table creation statement for the second data table corresponding to the target database based on the second data table information and the second view information. Table structure comparison module: used to compare the table creation statements of the first data table and the table creation statements of the second data table to obtain the table structure comparison results; Database migration module: used to perform database migration based on the table structure comparison results, so as to migrate the target data in the source database to the target database; The database migration based on the table structure comparison results, to migrate the target data from the source database to the target database, includes: If the table structure comparison result is that no second data table corresponding to the first data table is matched, the table creation statement of the first data table that is not matched is determined to be the first target creation statement; Generate a table creation migration command corresponding to the first target creation statement. The table creation migration command belongs to the database migration command file and is used to instruct the generation of a second data table corresponding to the first data table in the target database.

11. The apparatus according to claim 10, characterized in that, The database migration module includes: Migration command generation unit: used to generate database migration command files based on the table structure comparison results; Command modification information acquisition unit: used to acquire command modification information for the database migration command file; Migration command execution unit: used to execute the database migration command file based on the command modification information to migrate the target data in the source database to the target database.

12. The apparatus according to claim 11, characterized in that, The device further includes: Table structure file generation module: After generating the table creation statement of the first data table corresponding to the source database based on the first data table information and the first view information, it stores the table creation statement of the first data table corresponding to the source database as a table structure file; The table structure comparison module includes: Table structure file reading submodule: used to read the table structure file of the source database; Create Statement Comparison Submodule: Used to perform string comparison of the table creation statements based on the table creation statements of the first data table in the table structure file, on a data table basis, to obtain the table structure comparison result.

13. The apparatus according to claim 12, characterized in that, The creation statement comparison submodule includes: Table matching unit: For each first data table in the table structure file, based on the table name of the first data table, performs string matching on the table name of the second data table to obtain the table matching result in the table structure comparison result; The table creation statement comparison unit is used to perform string traversal comparison on the table creation statements of the matched first data table and the second data table if the table matching result is a second data table corresponding to the first data table, so as to obtain the creation statement comparison result in the table structure comparison result.

14. The apparatus according to claim 12, characterized in that, The database migration command file includes table modification migration commands, and the migration command file generation module includes: The second creation statement determination submodule is used to determine the inconsistent table creation statement as the second target creation statement if the comparison result of the creation statement is that there is an inconsistent table creation statement between the matched first data table and the second data table. The second migration command generation submodule is used to generate the table modification migration command corresponding to the second target creation statement.

15. The apparatus according to claim 13, characterized in that, The table creation statement comparison unit includes: The field acquisition subunit is used to acquire the field fields of the table creation statements of the first data table and the second data table respectively based on a preset string matching pattern. The field fields include the field field name and the row statement. Table mapping processing subunit: used to perform table mapping processing on the field field corresponding to the first data table and the field field corresponding to the second data table according to the field field name of the field field, respectively, to obtain the corresponding first table mapping result and second table mapping result; Statement comparison subunit: used to compare row statements based on the mapping results of the first table and the mapping results of the second table, and obtain the comparison result of the creation statement.

16. The apparatus according to claim 15, characterized in that, The table mapping processing subunit is specifically used for: Using the field name as the key and the corresponding row statement as the value, store the field fields corresponding to the first data table and the field fields corresponding to the second data table in a hash table.

17. The apparatus according to any one of claims 11-16, characterized in that, The device further includes: Database information acquisition module: used to acquire tablespace usage, database access volume and tablespace response time information of the source database before acquiring the first data table information and first view information of the source database and the second data table information and second view information of the target database respectively; Weighted statistics module: used to perform weighted statistical processing on the tablespace usage, database access volume and tablespace response time information to obtain the running performance results of the source database; Migration condition judgment module: used to execute the steps of obtaining the first data table information and the first view information of the source database, and the second data table information and the second view information of the target database respectively, when the running performance results meet the migration conditions.

18. The apparatus according to claim 17, characterized in that, The device further includes: Resource usage acquisition module: used to acquire the resource usage of the source database when the running performance results do not meet the migration conditions; Data access volume detection module: used to detect the data access volume of source data in the source database if the amount of resources occupied exceeds the resource volume threshold; Target data determination module: used to determine target source data whose data access volume exceeds the access volume threshold; Target source database migration module: used to migrate the target source data to the target database; Access path update module: used to update the access path of the target source data.

19. A computer-readable storage medium, characterized in that, The storage medium stores at least one instruction or at least one program segment, which is loaded and executed by a processor to implement the database migration method as described in any one of claims 1-9.

20. A computer device, characterized in that, The device includes a processor and a memory, the memory storing at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the database migration method as described in any one of claims 1-9.

21. A computer program product, characterized in that, The computer program product includes computer instructions that, when executed by a processor, implement the database migration method as described in any one of claims 1-9.

Images