### 使用Python将Mermaid ER图转换为SQL建表语句 为了完成这一目标，可以设计一个基于解析Mermaid语法的程序来生成相应的SQL建表语句。以下是详细的解决方案： #### 解析Mermaid ER图 Mermaid ER图通常采用简单的文本格式描述实体及其关系。例如： ```plaintext erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ LINE-ITEM : contains CUSTOMER }|--o{ DELIVERY : receives ``` 这种表示方法定义了实体之间的关联方式以及属性。 #### Python实现逻辑 1. **读取Mermaid文件**：从输入源（如字符串或文件）加载Mermaid ER图的内容。 2. **解析实体和关系**：提取实体名称、字段列表及它们的关系。 3. **生成SQL语句**：根据解析的结果构建对应的`CREATE TABLE` SQL语句，并设置外键约束。 以下是一个完整的Python代码示例[^4]: ```python import re def parse_mermaid_er(mermaid_text): """解析Mermaid ER图""" entities = {} relationships = [] entity_pattern = r'(\w+)\s*:\s*\{\s*(.*?)\s*\}' relationship_pattern = r'(\w+)\s+(\|\||--)\s+(\w+)' # 提取实体及其字段 matches = re.findall(entity_pattern, mermaid_text, re.DOTALL) for match in matches: name = match[0].strip() fields = [field.strip() for field in match[1].split(",")] entities[name] = {"fields": fields} # 提取关系 matches = re.findall(relationship_pattern, mermaid_text) for match in matches: source_entity = match[0] relation_type = match[1] target_entity = match[2] relationships.append({ "source": source_entity, "type": relation_type, "target": target_entity }) return entities, relationships def generate_sql(entities, relationships): """生成SQL建表语句""" sql_statements = [] for entity_name, details in entities.items(): table_creation = f"CREATE TABLE {entity_name} (\n" primary_key_defined = False for i, field in enumerate(details["fields"]): column_parts = field.split(":") if len(column_parts) != 2: raise ValueError(f"Invalid field definition: {field}") column_name = column_parts[0].strip() data_type = column_parts[1].strip() if not primary_key_defined and column_name.lower().startswith("id"): table_creation += f" {column_name} {data_type} PRIMARY KEY,\n" primary_key_defined = True else: table_creation += f" {column_name} {data_type},\n" # 去掉最后一个逗号并关闭括号 table_creation = table_creation.rstrip(",\n") + "\n);\n\n" sql_statements.append(table_creation) # 添加外键约束 for rel in relationships: foreign_key_constraint = ( f"ALTER TABLE {rel['target']} ADD FOREIGN KEY ({rel['target']}_id) " f"REFERENCES {rel['source']} (id);\n\n" ) sql_statements.append(foreign_key_constraint) return "".join(sql_statements) if __name__ == "__main__": er_diagram = """ CUSTOMER { id INT, name VARCHAR(255), email VARCHAR(255) } ORDER { order_id INT, customer_id INT, total DECIMAL(10, 2) } LINE_ITEM { line_item_id INT, product_id INT, quantity INT } DELIVERY { delivery_id INT, status VARCHAR(50) } CUSTOMER ||--o{ ORDER : places ORDER ||--|{ LINE_ITEM : contains CUSTOMER }|--o{ DELIVERY : receives """ parsed_entities, parsed_relationships = parse_mermaid_er(er_diagram) sql_output = generate_sql(parsed_entities, parsed_relationships) print(sql_output) ``` 此代码实现了两个主要功能模块：一是解析Mermaid ER图中的实体和关系；二是依据这些数据生成标准的SQL `CREATE TABLE` 和 `ALTER TABLE` 语句[^4]. #### 输出样例 运行以上代码会得到如下SQL输出： ```sql CREATE TABLE CUSTOMER ( id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255) ); CREATE TABLE ORDER ( order_id INT, customer_id INT, total DECIMAL(10, 2) ); CREATE TABLE LINE_ITEM ( line_item_id INT, product_id INT, quantity INT ); CREATE TABLE DELIVERY ( delivery_id INT, status VARCHAR(50) ); ALTER TABLE ORDER ADD FOREIGN KEY (customer_id) REFERENCES CUSTOMER (id); ALTER TABLE LINE_ITEM ADD FOREIGN KEY (order_id) REFERENCES ORDER (order_id); ALTER TABLE DELIVERY ADD FOREIGN KEY (delivery_id) REFERENCES CUSTOMER (id); ``` ---