🔥 会员专享文生文数据集

SQL结构解析转换器

👁️ 297 次查看

📅 Oct 18, 2025

💡 核心价值： 本提示词模板专门用于将SQL数据定义语言(DDL)语句转换为结构化的JSON格式，帮助用户轻松解析和理解数据库架构定义。该工具具备智能语法识别能力，能够准确提取表名、字段定义、约束条件等关键信息，并以标准化的JSON结构输出。通过自动化的转换过程，显著提升数据库文档生成、架构分析和系统集成的效率，支持主流的SQL方言包括MySQL、PostgreSQL、Oracle等。转换结果保持原始DDL语句的完整语义，确保数据类型、主外键关系、索引定义等核心要素的准确映射，为数据可视化工具和自动化工作流提供标准化的数据输入。

终身会员免费复制

🎯 可自定义参数（3个）

SQL语句

需要转换的SQL DDL语句，如CREATE TABLE语句

转换配置

转换配置选项，如是否包含注释、索引详情等

输出要求

输出格式特殊要求，如缩进格式、字段排序等

🎨 效果示例

转换结果概览

原始DDL语句摘要
- CREATE TABLE app_user (...)
- ALTER TABLE app_user ADD COLUMN last_login TIMESTAMP
- CREATE TABLE user_profile (...)
- CREATE INDEX idx_app_user_email ON app_user(email)
解析状态标识
- 成功

JSON数据结构

{
  "tables": [
    {
      "table_name": "app_user",
      "columns": [
        {
          "name": "id",
          "type": "BIGINT",
          "constraints": ["PRIMARY KEY"]
        },
        {
          "name": "username",
          "type": "VARCHAR(32)",
          "constraints": ["NOT NULL"]
        },
        {
          "name": "email",
          "type": "VARCHAR(64)",
          "constraints": []
        },
        {
          "name": "created_at",
          "type": "TIMESTAMP",
          "constraints": ["DEFAULT CURRENT_TIMESTAMP"]
        },
        {
          "name": "last_login",
          "type": "TIMESTAMP",
          "constraints": []
        }
      ],
      "constraints": {
        "primary_key": "PRIMARY KEY (id)",
        "foreign_keys": [],
        "indexes": [
          {
            "name": "idx_app_user_email",
            "columns": ["email"]
          }
        ]
      }
    },
    {
      "table_name": "user_profile",
      "columns": [
        {
          "name": "user_id",
          "type": "BIGINT",
          "constraints": ["PRIMARY KEY"]
        },
        {
          "name": "nickname",
          "type": "VARCHAR(32)",
          "constraints": []
        },
        {
          "name": "bio",
          "type": "VARCHAR(128)",
          "constraints": []
        }
      ],
      "constraints": {
        "primary_key": "PRIMARY KEY (user_id)",
        "foreign_keys": [
          {
            "name": "fk_user_profile_user",
            "columns": ["user_id"],
            "references": {
              "table": "app_user",
              "columns": ["id"]
            }
          }
        ],
        "indexes": []
      }
    }
  ]
}

解析详情

成功解析的组件统计
- 表: 2
- 列: app_user(5), user_profile(3)
- 主键: 2
- 外键: 1
- 索引: 1
- 变更: 1
需要注意的特殊语法说明
- DEFAULT CURRENT_TIMESTAMP
- 列级PRIMARY KEY
- 命名外键约束: fk_user_profile_user
- ALTER TABLE ADD COLUMN
变更摘要（比较维度: 列, 约束）
- app_user: + 列 last_login TIMESTAMP

转换结果概览

原始DDL语句摘要：3个表：user_account, orders, order_item；包含主键、外键、检查约束与默认值
解析状态标识：成功

JSON数据结构

{
  "tables": [
    {
      "table_name": "user_account",
      "columns": [
        {
          "name": "user_id",
          "type": "bigint",
          "constraints": ["primary key", "auto_increment"]
        },
        {
          "name": "user_name",
          "type": "varchar(32)",
          "constraints": ["not null"]
        },
        {
          "name": "created_at",
          "type": "timestamp",
          "constraints": ["not null"]
        }
      ],
      "constraints": {
        "primary_key": "pk_user_account(user_id)",
        "foreign_keys": [],
        "checks": [],
        "indexes": []
      }
    },
    {
      "table_name": "orders",
      "columns": [
        {
          "name": "order_id",
          "type": "bigint",
          "constraints": ["primary key", "auto_increment"]
        },
        {
          "name": "user_id",
          "type": "bigint",
          "constraints": ["not null"]
        },
        {
          "name": "status",
          "type": "char(1)",
          "constraints": ["not null", "default 'N'"]
        },
        {
          "name": "total_amount",
          "type": "numeric(10,2)",
          "constraints": ["not null"]
        },
        {
          "name": "created_at",
          "type": "timestamp",
          "constraints": ["not null"]
        }
      ],
      "constraints": {
        "primary_key": "pk_orders(order_id)",
        "foreign_keys": [
          "fk_orders_user(user_id) REFERENCES user_account(user_id)"
        ],
        "checks": [
          "chk_status (status IN ('N','P','C'))"
        ],
        "indexes": []
      }
    },
    {
      "table_name": "order_item",
      "columns": [
        {
          "name": "item_id",
          "type": "bigint",
          "constraints": ["primary key", "auto_increment"]
        },
        {
          "name": "order_id",
          "type": "bigint",
          "constraints": ["not null"]
        },
        {
          "name": "sku_code",
          "type": "varchar(32)",
          "constraints": ["not null"]
        },
        {
          "name": "qty",
          "type": "integer",
          "constraints": ["not null"]
        },
        {
          "name": "price",
          "type": "numeric(10,2)",
          "constraints": ["not null"]
        }
      ],
      "constraints": {
        "primary_key": "pk_order_item(item_id)",
        "foreign_keys": [
          "fk_item_order(order_id) REFERENCES orders(order_id)"
        ],
        "checks": [],
        "indexes": []
      }
    }
  ],
  "relationship_graph": [
    {
      "name": "fk_orders_user",
      "from": "orders.user_id",
      "to": "user_account.user_id"
    },
    {
      "name": "fk_item_order",
      "from": "order_item.order_id",
      "to": "orders.order_id"
    }
  ]
}

解析详情

成功解析的组件统计：表(3)，列(user_account:3, orders:5, order_item:5)，主键(3)，外键(2)，检查约束(1)，默认值(1)，索引(0)
需要注意的特殊语法说明：
- BIGSERIAL已标准化为bigint并以auto_increment标识
- 保留显式命名的外键与检查约束
- 命名已规范为snake_case

转换结果概览

原始DDL语句摘要: CREATE TABLE: 2; CREATE INDEX: 1
解析状态标识: 成功; 方言: MySQL

JSON数据结构

{
  "tables": [
    {
      "table_name": "fact_order_daily",
      "comment": "每日订单指标",
      "columns": [
        {
          "name": "date_key",
          "type": "INT",
          "constraints": [
            "NOT NULL"
          ],
          "comment": "日期键"
        },
        {
          "name": "gmv",
          "type": "DECIMAL(12,2)",
          "constraints": [
            "NOT NULL"
          ],
          "comment": "交易额"
        },
        {
          "name": "order_cnt",
          "type": "INT",
          "constraints": [
            "NOT NULL"
          ],
          "comment": "订单数"
        }
      ],
      "constraints": {
        "primary_key": "date_key",
        "foreign_keys": [
          {
            "name": "fk_fact_date",
            "columns": [
              "date_key"
            ],
            "references": {
              "table": "dim_date",
              "columns": [
                "date_key"
              ]
            }
          }
        ],
        "indexes": [
          "idx_fact_date"
        ]
      }
    },
    {
      "table_name": "dim_date",
      "columns": [
        {
          "name": "date_key",
          "type": "INT",
          "constraints": [
            "PRIMARY KEY"
          ],
          "comment": "日期键"
        },
        {
          "name": "date_value",
          "type": "DATE",
          "constraints": [
            "NOT NULL"
          ],
          "comment": "日期值"
        },
        {
          "name": "month",
          "type": "INT",
          "constraints": [
            "NOT NULL"
          ],
          "comment": "月份"
        }
      ],
      "constraints": {
        "primary_key": "date_key",
        "foreign_keys": [],
        "indexes": []
      }
    }
  ]
}