MySQLStructuredComparison

数据库结构化比对，通过对数据库生成的结构化SQL文件比对，生成同步数据库的DDL语句，包含表和新增字段（Database structured comparison generates DDL statements for synchronized databases, including tables and new fields, by comparing the structured SQL files generated by the database）

预设使用脚本原因：现有两个系统的数据库，旧系统的数据库老旧，希望同步新系统的数据库结构到旧系统，并且不影响旧系统对原库的使用.

脚本工作原理，在脚本当前目录，读取source_schema.sql和target_schema.sql，例如source_schema.sql为新系统导出数据库结构文件，target_schema.sql为旧系统导出数据库结构文件，脚本会以source_schema.sql结构为基准，比对target_schema.sql中不存在的表格，和相同表格下缺少的字段，在当前目录生成DDL文件(目前忽略了同字段不同类型或字段的判断,为了泛用化使用文件比对，需要的可以自行增加判断和使用connector的形式获取结构化信息).

执行命令：python diff_dateBases.py.

使用脚本环境需要python3,文件UTF-8.

(Reason for using the preset script:

There are two existing systems' databases, the old system's database is old, and we hope to synchronize the database structure of the new system to the old system without affecting the old system's use of the original database

The working principle of scripts is to read the source in the current directory of the script_ Schema.sql and target_ Schema.sql, such as source_ Schema.sql exports the database structure file for the new system, target_ Schema.sql exports the database structure file for the old system, and the script will start with source_ Using the schema.sql structure as a benchmark, compare the target_ Generate a DDL file in the current directory for tables that do not exist in schema.SQL and fields that are missing under the same table (currently ignoring the judgment of different types or fields in the same field. In order to use file comparison for generalization, it is necessary to add judgment and use the form of connectors to obtain structured information)

Execute command: Python diff_ DateBases. py

Using the script environment requires Python 3 and UTF-8 as the file)

github:github.com/QinJun-IOC/…
希望对您有用的可以给一个小小的Star，或使用有什么建议或问题可以在下方留言。

python:

import os
import re

def read_schema_file(filename):
    with open(filename, 'r', encoding='utf-8') as file:
        return file.read()

def compare_schemas(source_schema, target_schema):
    source_tables = re.findall(r"CREATE TABLE `(\w+)`", source_schema)
    target_tables = re.findall(r"CREATE TABLE `(\w+)`", target_schema)
    
    new_tables = set(source_tables) - set(target_tables)
    column_changes = {}

    for table_name in set(source_tables).intersection(target_tables):
        source_table_definition = re.search(rf"CREATE TABLE `{table_name}`(.*?);", source_schema, re.DOTALL).group(1)
        target_table_definition = re.search(rf"CREATE TABLE `{table_name}`(.*?);", target_schema, re.DOTALL).group(1)

        source_columns = re.findall(r"`(\w+)`\s+.*?(?:,\n|$)", source_table_definition)
        target_columns = re.findall(r"`(\w+)`\s+.*?(?:,\n|$)", target_table_definition)

        columns_diff = []
        new_columns = []

        for source_column in source_columns:
            if source_column not in target_columns:
                columns_diff.append(source_column)
                new_columns.append(re.search(rf"`{source_column}`\s+.*?(,\n|$)", source_table_definition).group(0))

        if columns_diff:
            column_changes[table_name] = new_columns

    return new_tables, column_changes


def generate_ddl(new_tables, column_changes, source_schema):
    ddl_changes = []

    for table_name in new_tables:
        create_table_definition = re.search(rf'CREATE TABLE `{table_name}`(.*?);', source_schema, re.DOTALL).group(1)
        ddl_changes.append(f"CREATE TABLE `{table_name}` ({create_table_definition});")

    for table_name, columns in column_changes.items():
        for column_definition in columns:
            ddl_changes.append(f"ALTER TABLE `{table_name}` ADD COLUMN {column_definition.strip()};")

    return "\n\n".join(ddl_changes)

def main():
    script_directory = os.path.dirname(os.path.abspath(__file__))
    source_schema_text = read_schema_file(os.path.join(script_directory, "source_schema.sql"))
    target_schema_text = read_schema_file(os.path.join(script_directory, "target_schema.sql"))

    new_tables, column_changes = compare_schemas(source_schema_text, target_schema_text)
    ddl_changes = generate_ddl(new_tables, column_changes, source_schema_text)

    output_file_path = os.path.join(script_directory, "ddl_changes.sql")
    with open(output_file_path, 'w', encoding='utf-8') as output_file:
        output_file.write(ddl_changes)

    print(f"DDL changes saved to: {output_file_path}")

if __name__ == "__main__":
    main()

MySQL结构化文件比对脚本，自动生成DDL语句

MySQLStructuredComparison