Intelligent comparative analysis method and system for consistency of multi-source database table structures

Abstract

The present invention provides an intelligent comparison and analysis method for consistency of multi-source database table structures, which comprises the following steps: constructing a background interface for visual detection configuration, configuring multi-source database table mapping information and each source database table information to be detected through the background interface; after the user successfully sets the multi-source database table information to be detected, starting the detection, executing SQL, and checking whether the table exists; executing SQL, obtaining field information of the multi-source table; executing SQL, obtaining auto-increment fields of the multi-source table; performing customized conversion processing according to preset conversion rules; horizontally comparing field attribute values, and outputting comparison results; executing SQL, obtaining constraint information of the multi-source table; horizontally comparing table constraint information, and outputting comparison results; obtaining index information of the multi-source table; horizontally comparing table index information, and outputting comparison results; the present invention particularly performs innovative conversion processing for specific incompatible items between the two, so as to ensure accurate correspondence and consistent matching of table structures between different source databases.

Description

Technical Field

[0001] The present invention relates to the field of computer information technology, and in particular to a method and system for intelligent comparison and analysis of consistency of multi-source database table structures. Background Art

[0002] With the continuous expansion of enterprise business scale and the rapid growth of data volume, traditional database architecture design has gradually exposed its limitations and is unable to adapt to the current explosive changes in business needs. Faced with this challenge, enterprises urgently need to consider a comprehensive upgrade and optimization of the existing database architecture, which may even involve a complete change in database type, such as migrating the original SQL Server-based database system to the MySQL platform to ensure seamless migration of data table structures and massive business data. However, while existing database conversion tools on the market can achieve basic data structure migration to a certain extent, they often lack processing in key details such as index settings, constraint definition, and default value settings. In simple business application scenarios, such tools may be able to meet basic migration needs, but when dealing with highly sensitive and complex business logic, any minor migration omissions may lead to serious business failures.

[0003] To this end, the present invention takes a unique approach, creatively providing a comprehensive and sophisticated solution. This solution focuses on the in-depth comparison and integration of table structures across multiple database sources. This includes not only precise mapping of the table structures themselves, but also comprehensive comparison and synchronization of indexing strategies, consistent processing of constraint rules, precise migration of default settings, and the rational conversion of special agreed values, along with effective ignoring mechanisms during the migration process. This multi-layered intelligent analysis and operational approach includes: Summary of the Invention

[0004] To overcome the above problems, the purpose of the present invention is to provide an intelligent comparative analysis method for the consistency of multi-source database table structures, ensuring the relative consistency of table structures during cross-database migration in various complex business scenarios, thereby effectively guaranteeing the smooth transition and efficient operation of the business system.

[0005] The present invention is implemented by the following scheme: a multi-source database table structure consistency intelligent comparison and analysis method, the method comprising the following steps:

[0006] Step S1: Build a background interface for visual detection configuration, and configure multi-source database table mapping information and each source database table information to be detected through the background interface;

[0007] Step S2: After completing the configuration, perform a test and execute SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database, respectively, and obtain detailed field information of all tables to be tested in different source database libraries, thereby forming a SQL Server source database table field data set A1 and a MySQL source database table field data set A2;

[0008] Step S3: Obtain the index information of the table to be detected in the two databases, and generate the D1 data set corresponding to the SQL Server library and the D2 data set corresponding to the MySQL library;

[0009] Step S4: Filter out common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, perform in-depth comparisons on each of them, and output the comparison results to evaluate the consistency of index information of the table to be tested in the two databases.

[0010] Step S5: Perform customized conversion processing on the data set A1 according to the preset conversion rules; the conversion rules obtain the converted field information set A3;

[0011] Step S6: Perform a comprehensive horizontal comparison of the converted field information set A3 with the data set A2. The comparison dimensions include: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. Detailed comparison results are generated to determine the consistency of the field information in the two databases for the table to be tested.

[0012] Step S7: Execute the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library;

[0013] Step S8: Compare the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained;

[0014] Step S9: Summarize the comparison results obtained in step S4, step S6 and step S8 and feed them back to the background interface in real time so that the user can understand the test results in a timely manner.

[0015] Furthermore, the multi-source database table mapping information includes connection parameters of each source database and a list of table names that need to be compared and analyzed.

[0016] Furthermore, step S2 is specifically as follows: executing SQL statements to retrieve all table information from the SQL Server source database and the Mysql source database of the multi-source database respectively to confirm whether the table to be detected exists in multiple source databases. When it is confirmed that the table exists in all databases, continuing to execute SQL commands to obtain field detailed information of all tables to be detected in different source database libraries respectively, forming a SQL Server source database table field data set A1 and a MySQL source database table field data set A2; the field detailed information includes: field name, field type, field length, default value, whether to allow null, precision, scale and character set content.

[0017] Furthermore, the conversion rules in step S5 include: conversion of field types, conversion of field default values, character set conversion, and index addition;

[0018] The field type conversion includes: smalldatetime type is converted to datetime type; nvarchar and nchar types are converted to varchar type; ntext type is converted to text type; numeric type is determined according to the precision, if the precision is 0, it is converted to bigint type, otherwise it is converted to decimal type;

[0019] The conversion of the field default value includes: the default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP; newid() is converted to uuid();

[0020] The character set conversion includes: converting Chinese_PRC_CI_AS to utf8mb4_general_ci;

[0021] The index addition includes: if the field contains an auto-increment attribute, a normal index type is added; in SQL Server, if a field contains an auto-increment attribute, it can be a non-primary key or a non-index, but in MySQL, if a field contains an auto-increment attribute, it must be a primary key or have an index. Therefore, due to the difference, if there is a field in this situation in the table to be tested, a normal index needs to be added to the field.

[0022] The present invention also provides a multi-source database table structure consistency intelligent comparison and analysis system, the system comprising: a table structure verification module, a field data acquisition module, an index information acquisition module, an index information comparison module, a field conversion processing module, a field comparison module, a constraint information acquisition module, a constraint information comparison module, and a comparison result summary module;

[0023] The table structure verification module is constructed to construct a background interface for visual detection configuration, and multi-source database table mapping information and each source database table information to be detected are configured through the background interface;

[0024] After the configuration is completed, the field data acquisition module performs detection and executes SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database respectively, and obtains the field detailed information of all tables to be detected in different source database libraries, thereby forming the SQL Server source database table field data set A1 and the MySQL source database table field data set A2;

[0025] The index information acquisition module obtains the index information of the table to be detected in the two databases, and generates a D1 data set corresponding to the SQL Server library and a D2 data set corresponding to the MySQL library;

[0026] The index information comparison module selects common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, performs in-depth comparisons on each of them, and outputs the comparison results, thereby evaluating the consistency of the index information of the table to be tested in the two databases;

[0027] The field conversion processing module performs customized conversion processing on the data set A1 according to the preset conversion rules; the conversion rules obtain the converted field information set A3;

[0028] The field comparison module performs a comprehensive horizontal comparison of the converted field information set A3 with the data set A2, covering the following dimensions: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. It produces detailed comparison results, based on which the consistency of the field information of the table to be tested in the two databases is determined;

[0029] The constraint information acquisition module executes the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library;

[0030] The constraint information comparison module compares the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version factor. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained;

[0031] The comparison result summary module summarizes the comparison results obtained by the index information comparison module, the field comparison module and the constraint information comparison module and feeds them back to the background interface in real time, so that users can understand the detection results in a timely manner.

[0032] Furthermore, the multi-source database table mapping information includes connection parameters of each source database and a list of table names that need to be compared and analyzed.

[0033] Furthermore, the field data acquisition module specifically executes SQL statements to retrieve all table information from the SQL Server source database and the Mysql source database of the multi-source database respectively to confirm whether the table to be detected exists in multiple source databases. When it is confirmed that the table exists in all databases, continue to execute SQL commands to obtain field detailed information of all tables to be detected in different source database libraries respectively, forming a SQL Server source database table field data set A1 and a MySQL source database table field data set A2; the field detailed information includes: field name, field type, field length, default value, whether to allow null, precision, scale and character set content.

[0034] Furthermore, the conversion rules in step S5 include: conversion of field types, conversion of field default values, character set conversion, and index addition;

[0035] The field type conversion includes: smalldatetime type is converted to datetime type; nvarchar and nchar types are converted to varchar type; ntext type is converted to text type; numeric type is determined according to the precision, if the precision is 0, it is converted to bigint type, otherwise it is converted to decimal type;

[0036] The conversion of the field default value includes: the default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP; newid() is converted to uuid();

[0037] The character set conversion includes: converting Chinese_PRC_CI_AS to utf8mb4_general_ci;

[0038] The index addition includes: if the field contains an auto-increment attribute, a normal index type is added; in SQL Server, if a field contains an auto-increment attribute, it can be a non-primary key or a non-index, but in MySQL, if a field contains an auto-increment attribute, it must be a primary key or have an index. Therefore, due to the difference, if there is a field in this situation in the table to be tested, a normal index needs to be added to the field.

[0039] The beneficial effects of the present invention are as follows: It focuses on the in-depth comparison and integration of table structures between multi-source databases, including not only the precise mapping of the table structures themselves, but also multi-level intelligent analysis and operation methods such as detailed comparison and synchronization of index strategies, consistent processing of constraint rules, precise migration of default settings, and reasonable conversion of special agreed values ​​and effective ignoring mechanisms during the migration process. It can effectively identify and resolve various migration sequelae. Through this innovative detection mechanism, it can not only ensure the consistency of table structures during large-scale migrations, but also greatly improve the quality and efficiency of data migration, thereby effectively supporting enterprises to steadily promote business development on the MySQL platform. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 It is a schematic flow chart of the method of the present invention.

[0041] Figure 2 It is a schematic diagram of the background interface of the visual detection configuration according to an embodiment of the present invention.

[0042] Figure 3 1 is a schematic diagram of the detection results of an embodiment of the present invention.

[0043] Figure 4 It is a system principle block diagram of the present invention. DETAILED DESCRIPTION

[0044] The present invention will be further described below with reference to the accompanying drawings.

[0045] See also Figure 1 As shown, the present invention provides a multi-source database table structure consistency intelligent comparison and analysis method, the method comprising the following steps:

[0046] Step S1: Build a background interface for visual detection configuration, and configure multi-source database table mapping information and each source database table information to be detected through the background interface;

[0047] Step S2: After completing the configuration, a test is performed. The multi-source database includes various types of databases. In the present invention, a SQL Server source database and a MySQL source database are used. An SQL statement is executed to retrieve all table information from the SQL Server source database and the MySQL source database, respectively, and detailed field information of all tables to be tested in different source database libraries is obtained, thereby forming a SQL Server source database table field data set A1 and a MySQL source database table field data set A2.

[0048] Step S3: Obtain the index information of the table to be detected in the two databases, and generate the D1 data set corresponding to the SQL Server library and the D2 data set corresponding to the MySQL library;

[0049] Step S4: Filter out common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, perform in-depth comparisons on each of them, and output the comparison results to evaluate the consistency of index information of the table to be tested in the two databases.

[0050] Step S5: Perform customized conversion processing on the data set A1 according to the preset conversion rules; the conversion rules obtain the converted field information set A3;

[0051] Step S6: Perform a comprehensive horizontal comparison of the converted field information set A3 with the data set A2. The comparison dimensions include: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. Detailed comparison results are generated to determine the consistency of the field information in the two databases for the table to be tested.

[0052] Step S7: Execute the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library;

[0053] Step S8: Compare the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained;

[0054] Step S9: Summarize the comparison results obtained in step S4, step S6 and step S8 and feed them back to the background interface in real time so that the user can understand the test results in a timely manner.

[0055] The present invention will be further described below with reference to a specific embodiment:

[0056] An intelligent comparative analysis method for consistency of multi-source database table structures.

[0057] Step 1: Build the background interface of the table structure verification tool, such as Figure 2 As shown, the backend interface is mainly used to configure and manage the multi-source database table mapping information to be detected, including the connection parameters of each source database and the list of table names that need to be compared and analyzed.

[0058] Step 2: After the user completes the configuration of the database table to be tested in the backend interface, the test process is started. This time, the two source tables are selected as SQL Server database and MySQL database. The specific test logic is further verified according to the following steps 3 to 11.

[0059] Step 3: First, use SQL query instructions to retrieve all table information from the SQL Server and MySQL libraries to confirm whether the table to be tested exists in multiple source libraries. The question marks (?) and %s in SQL represent parameter placeholders.

[0060] The SQL Server query SQL command is as follows, the parameter is the database name:

[0061] SELECT table_name as table_name FROM information_schema.TABLES wheretable_catalog=?

[0062] The MySQL query SQL is as follows, and the parameter is the database name:

[0063] SELECT table_name as table_name FROM information_schema.TABLES wheretable_schema=?

[0064] Step 4: Once you've confirmed that the tables exist in all repositories, continue executing SQL commands to obtain detailed field information for these tables in different source repositories. This creates basic field data sets A1 (representing the SQL Server source database table field information set) and A2 (representing the MySQL source database table field information set). These data sets include field name, field type, field length, default value, whether nulls are allowed, precision, scale, and character set.

[0065] The SQL Server query SQL command is as follows, the parameters are the table name and database name:

[0066]

[0067]

[0068] The MySQL query SQL is as follows, with the parameters being the table name and database name:

[0069]

[0070] Step 5: Execute the SQL command again to obtain the index information of the table to be tested in the two databases and generate the D1 and D2 data sets.

[0071] The SQL Server query SQL command is as follows, and the parameter is the specific table name:

[0072] SELECT c.name as column_name,i.name as key_name,i.is_unique,i.is_primary_key as is_primary

[0073] FROM

[0074] sys.schemas s

[0075] JOIN

[0076] sys.tables t ON s.schema_id=t.schema_id

[0077] JOIN

[0078] sys.indexes i ON t.object_id=i.object_id

[0079] JOIN

[0080] sys.index_columns ic ON i.object_id=ic.object_id AND i.index_id=ic.index_id

[0081] JOIN

[0082] sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id WHERE

[0083] t.name = ?;

[0084] The MySQL query SQL is as follows, and the parameter is the specific table name:

[0085] SHOW INDEX FROM %s;

[0086] Step 6: Filter out common indexes, unique indexes, and combined indexes from D1 and D2, perform in-depth comparisons on each of them, and output the comparison results to evaluate the consistency of index information of the table to be tested in the two databases.

[0087] Step 7: Perform customized conversion on A1 data according to the preset conversion rules. The conversion rules include:

[0088] (1) Field type conversion

[0089] Convert the smalldatetime type to the datetime type;

[0090] nvarchar and nchar types are converted to varchar type;

[0091] ntext type is converted to text type;

[0092] The numeric type is determined by the precision. If the precision is 0, it is converted to the bigint type, otherwise it is converted to the decimal type.

[0093] (2) Conversion of field default values

[0094] The default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP;

[0095] newid() to uuid()

[0096] (3) Character set conversion

[0097] Chinese_PRC_CI_AS converted to utf8mb4_general_ci

[0098] (4) Index addition

[0099] If a field contains an auto-increment attribute, add a regular index. In SQL Server, fields with auto-increment attributes can be non-primary keys or non-indexed, but in MySQL, fields with auto-increment attributes must be primary keys or have an index. Due to this discrepancy, if such a field exists in the table being tested, add a regular index to it.

[0100] The above are the existing preset rules. You can also add conversion rules based on actual business needs.

[0101] Through the above conversion rules, the converted field information set A3 is finally generated;

[0102] Step 8: Perform a comprehensive horizontal comparison between the converted A3 and the original A2. Comparison dimensions include the number of fields, field names, field types, field lengths, default values, non-null attributes, precision, scale, character sets, and auto-increment attributes. Detailed comparison results are generated to determine the consistency of field information in the two databases for the table under test.

[0103] Step 9: Execute the SQL command again to collect the constraint information of the table to be tested in the two databases to form the C1 and C2 data sets.

[0104] The SQL Server query SQL command is as follows, and the parameter is the table name:

[0105] SELECT name as check_name,definition as check_definition FROMsys.check_constraints WHERE parent_object_id=OBJECT_ID(?);

[0106] Mysql query SQL is as follows, the parameters are database name and table name:

[0107]

[0108]

[0109] Step 10: Compare the constraints of C1 and C2, including the number of constraints and constraint expressions. Considering the MySQL database version, this step is skipped for versions earlier than 8.0, as constraints are fully supported in MySQL 8.0 and higher. After the comparison is complete, the consistency of the constraints in the two databases for the table under test is determined.

[0110] Step 11: Finally, the comparison results obtained in steps 6, 8, and 10 are summarized and fed back to the backend page of the table structure verification tool in real time, so that users can understand the test results in a timely manner and make necessary adjustments in the production environment accordingly; Figure 3 shown.

[0111] In summary, through this series of rigorous steps, the present invention effectively ensures high consistency of multiple source databases in key aspects such as table structure design, table index settings, and table constraint rules. Make preliminary preparations for table data migration.

[0112] See also Figure 4 As shown, the present invention also provides a multi-source database table structure consistency intelligent comparison and analysis system, the system includes: a table structure verification module, a field data acquisition module, an index information acquisition module, an index information comparison module, a field conversion processing module, a field comparison module, a constraint information acquisition module, a constraint information comparison module, and a comparison result summary module;

[0113] The table structure verification module is constructed to construct a background interface for visual detection configuration, and multi-source database table mapping information and each source database table information to be detected are configured through the background interface;

[0114] After the configuration is completed, the field data acquisition module performs detection and executes SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database respectively, and obtains the field detailed information of all tables to be detected in different source database libraries, thereby forming the SQL Server source database table field data set A1 and the MySQL source database table field data set A2;

[0115] The index information acquisition module obtains the index information of the table to be detected in the two databases, and generates a D1 data set corresponding to the SQL Server library and a D2 data set corresponding to the MySQL library;

[0116] The index information comparison module selects common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, performs in-depth comparisons on each of them, and outputs the comparison results, thereby evaluating the consistency of the index information of the table to be tested in the two databases;

[0117] The field conversion processing module performs customized conversion processing on the data set A1 according to the preset conversion rules; the conversion rules obtain the converted field information set A3;

[0118] The field comparison module performs a comprehensive horizontal comparison of the converted field information set A3 with the data set A2, covering the following dimensions: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. It produces detailed comparison results, based on which the consistency of the field information of the table to be tested in the two databases is determined;

[0119] The constraint information acquisition module executes the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library;

[0120] The constraint information comparison module compares the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version factor. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained;

[0121] The comparison result summary module summarizes the comparison results obtained by the index information comparison module, the field comparison module and the constraint information comparison module and feeds them back to the background interface in real time, so that users can understand the detection results in a timely manner.

[0122] The multi-source database table mapping information includes connection parameters of each source database and a list of table names that need to be compared and analyzed.

[0123] Among them, the field data acquisition module specifically executes SQL statements to retrieve all table information from the SQL Server source database and the Mysql source database of the multi-source database respectively to confirm whether the table to be detected exists in multiple source databases. When it is confirmed that the table exists in all databases, the SQL command is continued to be executed to obtain the field detailed information of all tables to be detected in different source database libraries respectively, forming the SQL Server source database table field data set A1 and the MySQL source database table field data set A2; the field detailed information includes: field name, field type, field length, default value, whether to allow null, precision, scale and character set content.

[0124] The conversion rules in step S5 include: conversion of field types, conversion of field default values, character set conversion, and index addition;

[0125] The field type conversion includes: smalldatetime type is converted to datetime type; nvarchar and nchar types are converted to varchar type; ntext type is converted to text type; numeric type is determined according to the precision, if the precision is 0, it is converted to bigint type, otherwise it is converted to decimal type;

[0126] The conversion of the field default value includes: the default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP; newid() is converted to uuid();

[0127] The character set conversion includes: converting Chinese_PRC_CI_AS to utf8mb4_general_ci;

[0128] The index addition includes: if the field contains an auto-increment attribute, a normal index type is added; in SQL Server, if a field contains an auto-increment attribute, it can be a non-primary key or a non-index, but in MySQL, if a field contains an auto-increment attribute, it must be a primary key or have an index. Therefore, due to the difference, if there is a field in this situation in the table to be tested, a normal index needs to be added to the field.

[0129] The application scenario of this invention is as follows: In a large-scale data migration project for NetDragon Mall, this invention focuses on solving the problem of efficient migration from SQL Server to MySQL database systems. As the enterprise's business scale continues to expand and the total amount of data grows exponentially, the existing SQL Server database architecture has exposed its limitations in meeting rapidly iterative business needs, and an upgrade to a solution more suitable for the current highly dynamic business environment is urgently needed. Therefore, a strategic transition plan was developed to gradually migrate the massive amount of data in the SQL Server database to the MySQL platform.

[0130] In the initial stages, we successfully migrated thousands of SQL Server tables to MySQL using a professional third-party migration tool. However, when deployed in complex and diverse business scenarios, we encountered a series of subtle yet significant technical challenges. These included data integrity issues caused by incomplete migration of table constraints, inconsistent default values ​​for some table fields, omission of joint indexes relied on by some tables after migration, and character set conversion issues that did not conform to pre-defined specifications. Given the sheer number of tables, which numbered in the thousands or even tens of thousands, manually comparing the design differences of each table became extremely difficult and time-consuming.

[0131] It is against this backdrop that this invention was born, aiming to develop an automated, refined table structure verification method that effectively identifies and resolves the various migration complications mentioned above. This innovative verification mechanism not only ensures table structure consistency during large-scale migrations but also significantly improves the quality and efficiency of data migration, thereby effectively supporting enterprises in steadily advancing their business development on the MySQL platform.

[0132] The above description is only a preferred embodiment of the present invention. All equivalent changes and modifications made according to the scope of the patent application of the present invention should fall within the scope of the present invention.

Claims

1. A method for intelligent comparison and analysis of the consistency of multi-source database table structures, characterized by: The method comprises the following steps: Step S1: Build a background interface for visual detection configuration, and configure multi-source database table mapping information and each source database table information to be detected through the background interface; Step S2: After completing the configuration, perform a test and execute SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database, respectively, and obtain detailed field information of all tables to be tested in different source database libraries, thereby forming a SQL Server source database table field data set A1 and a MySQL source database table field data set A2; Step S3: Obtain the index information of the table to be detected in the two databases, and generate the D1 data set corresponding to the SQL Server library and the D2 data set corresponding to the MySQL library; Step S4: Filter out common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, perform in-depth comparisons on each of them, and output the comparison results to evaluate the consistency of index information of the table to be tested in the two databases. Step S5: Perform customized conversion processing on the data set A1 according to the preset conversion rules; obtain the converted field information set A3 according to the conversion rules; Step S6: Perform a comprehensive horizontal comparison of the converted field information set A3 with the data set A2. The comparison dimensions include: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. Detailed comparison results are generated to determine the consistency of the field information in the two databases for the table to be tested. Step S7: Execute the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library; Step S8: Compare the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained; Step S9: Summarize the comparison results obtained in step S4, step S6 and step S8 and feed them back to the backend interface in real time so that the user can understand the test results in a timely manner; The conversion rules in step S5 include: conversion of field types, conversion of field default values, character set conversion, and index addition; The field type conversion includes: smalldatetime type is converted to datetime type; nvarchar and nchar types are converted to varchar type; ntext type is converted to text type; numeric type is determined based on precision, if the precision is 0, it is converted to bigint type, otherwise it is converted to decimal type; The conversion of the default value of the field includes: the default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP; newid() is converted to uuid(); The character set conversion includes: converting Chinese_PRC_CI_AS to utf8mb4_general_ci; The index addition includes: if the field contains an auto-increment attribute, a normal index type is added; in SQL Server, if a field contains an auto-increment attribute, it can be a non-primary key or a non-index, but in MySQL, if a field contains an auto-increment attribute, it must be a primary key or have an index. Therefore, due to the difference, if there is a field in this situation in the table to be tested, a normal index needs to be added to the field.

2. The method for intelligent comparison and analysis of the consistency of multi-source database table structures according to claim 1, characterized in that: The multi-source database table mapping information includes connection parameters of each source database and a list of table names that need to be compared and analyzed.

3. The method for intelligent comparison and analysis of the consistency of multi-source database table structures according to claim 1, characterized in that: Step S2 specifically includes: executing SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database of the multi-source database, respectively, to confirm whether the table to be detected exists in multiple source databases; when it is confirmed that the table exists in all databases, continuing to execute SQL commands to obtain detailed field information of all tables to be detected in different source databases, respectively, to form a SQL Server source database table field data set A1 and a MySQL source database table field data set A2; The field details include: field name, field type, field length, default value, whether to allow null, precision, scale, and character set content.

4. An intelligent comparison and analysis system for consistency of multi-source database table structures, characterized by: The system includes: a table structure verification module, a field data acquisition module, an index information acquisition module, an index information comparison module, a field conversion processing module, a field comparison module, a constraint information acquisition module, a constraint information comparison module, and a comparison result summary module; The table structure verification module is constructed to construct a background interface for visual detection configuration, and multi-source database table mapping information and each source database table information to be detected are configured through the background interface; After the configuration is completed, the field data acquisition module performs detection and executes SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database respectively, and obtains the field detailed information of all tables to be detected in different source database libraries, thereby forming the SQL Server source database table field data set A1 and the MySQL source database table field data set A2; The index information acquisition module obtains the index information of the table to be detected in the two databases, and generates a D1 data set corresponding to the SQL Server library and a D2 data set corresponding to the MySQL library; The index information comparison module selects common indexes, unique indexes, and combined indexes from the D1 and D2 data sets, performs in-depth comparisons on each of them, and outputs the comparison results, thereby evaluating the consistency of the index information of the table to be tested in the two databases; The field conversion processing module performs customized conversion processing on the data set A1 according to the preset conversion rules; and obtains the converted field information set A3 according to the conversion rules; The field comparison module performs a comprehensive horizontal comparison of the converted field information set A3 with the data set A2, covering the following dimensions: number of fields, field name, field type, field length, default value, non-null attribute, precision, scale, character set, and auto-increment attribute. It produces detailed comparison results, based on which the consistency of the field information of the table to be tested in the two databases is determined; The constraint information acquisition module executes the SQL command again to collect the constraint information of the table to be detected in the two databases to form a C1 data set corresponding to the SQL Server library and a C2 data set corresponding to the MySQL library; The constraint information comparison module compares the constraint information of the C1 dataset and the C2 dataset, including the number of constraints and constraint expressions, while taking into account the MySQL database version factor. After the comparison is completed, the consistency of the constraint information of the table to be tested in the two databases is obtained; The comparison result summary module summarizes the comparison results obtained by the index information comparison module, the field comparison module and the constraint information comparison module and feeds them back to the background interface in real time, so that users can understand the detection results in a timely manner; The conversion rules in step S5 include: conversion of field types, conversion of field default values, character set conversion, and index addition; The field type conversion includes: smalldatetime type is converted to datetime type; nvarchar and nchar types are converted to varchar type; ntext type is converted to text type; numeric type is determined based on precision, if the precision is 0, it is converted to bigint type, otherwise it is converted to decimal type; The conversion of the default value of the field includes: the default value of the time type field is converted from getdate() to CURRENT_TIMESTAMP; newid() is converted to uuid(); The character set conversion includes: converting Chinese_PRC_CI_AS to utf8mb4_general_ci; The index addition includes: if the field contains an auto-increment attribute, a normal index type is added; in SQL Server, if a field contains an auto-increment attribute, it can be a non-primary key or a non-index, but in MySQL, if a field contains an auto-increment attribute, it must be a primary key or have an index. Therefore, due to the difference, if there is a field in this situation in the table to be tested, a normal index needs to be added to the field.

5. The intelligent comparison and analysis system for consistency of multi-source database table structures according to claim 4, characterized in that: The multi-source database table mapping information includes connection parameters of each source database and a list of table names that need to be compared and analyzed.

6. The intelligent comparison and analysis system for consistency of multi-source database table structures according to claim 4, characterized in that: The field data acquisition module specifically executes SQL statements to retrieve all table information from the SQL Server source database and the MySQL source database of the multi-source database to confirm whether the table to be detected exists in multiple source databases. When it is confirmed that the table exists in all databases, the SQL command is continued to be executed to obtain the field detailed information of all the tables to be detected in different source databases to form the SQL Server source database table field data set A1 and the MySQL source database table field data set A2; The field details include: field name, field type, field length, default value, whether to allow null, precision, scale, and character set content.

Images