跳到主要内容

MySQL数据库Schema读取工具

简介​

MySQL Schema读取工具是一个专门用于读取MySQL数据库表结构信息的工具。它能够自动扫描数据库中的所有表，提取完整的结构信息，包括字段定义、索引、约束、外键关系等，并以结构化的数组格式返回，便于后续处理和分析。

核心功能​

🔍 完整的表结构分析​

  • 读取所有表的字段信息（名称、类型、长度、默认值等）
  • 识别主键、外键和唯一键
  • 获取索引信息和约束详情
  • 支持视图和触发器信息读取

🎯 灵活的过滤选项​

  • 指定要读取的特定表
  • 排除不需要的系统表或临时表
  • 可选择包含或排除视图
  • 按需读取索引和约束信息

⚡ 高性能设计​

  • 内置连接池管理
  • 批量查询优化
  • 支持并发读取
  • 智能缓存机制

配置参数​

数据库连接配置​

参数类型默认值必填说明
hoststring'localhost'✅MySQL服务器地址
portnumber3306❌MySQL服务器端口
userstring'root'✅数据库用户名
passwordstring''✅数据库密码
databasestring-✅目标数据库名

读取选项配置​

参数类型默认值说明
includeTablesarray[]指定要读取的表名列表，空数组表示读取所有表
excludeTablesarray[]要排除的表名列表
includeViewsbooleanfalse是否包含数据库视图
includeIndexesbooleantrue是否读取索引信息
includeConstraintsbooleantrue是否读取约束信息
includeTriggersInfobooleanfalse是否读取触发器信息

连接池配置​

参数类型默认值说明
connectionLimitnumber10连接池最大连接数
acquireTimeoutnumber60000获取连接的超时时间（毫秒）
timeoutnumber60000查询超时时间（毫秒）

使用方法​

基础用法​

最简单的使用方式，读取指定数据库的所有表结构：

{
  "id": "read-schema",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "database": "your_database"
  }
}

读取指定表​

只读取特定的几个表：

{
  "id": "read-user-tables",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "your_database",
    "includeTables": ["users", "user_profiles", "user_permissions"]
  }
}

排除系统表​

排除日志表和临时表：

{
  "id": "read-business-tables",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "your_database",
    "excludeTables": ["logs", "temp_data", "cache_table"]
  }
}

完整配置示例​

包含所有可用选项的完整配置：

{
  "id": "comprehensive-schema-read",
  "type": "mysql-schema",
  "config": {
    "host": "192.168.1.100",
    "port": 3306,
    "user": "schema_reader",
    "password": "secure_password",
    "database": "production_db",
    "includeTables": ["users", "orders", "products", "categories"],
    "excludeTables": ["temp_logs", "session_data"],
    "includeViews": true,
    "includeIndexes": true,
    "includeConstraints": true,
    "includeTriggersInfo": false,
    "connectionLimit": 5,
    "acquireTimeout": 30000,
    "timeout": 30000
  }
}

输出格式​

成功响应结构​

{
  "success": true,
  "database": "your_database",
  "host": "localhost",
  "schema": [
    {
      "tableName": "users",
      "tableType": "BASE TABLE",
      "engine": "InnoDB",
      "tableComment": "用户信息表",
      "columns": [
        {
          "columnName": "id",
          "dataType": "int",
          "columnType": "int(11)",
          "isNullable": false,
          "defaultValue": null,
          "isAutoIncrement": true,
          "comment": "用户ID",
          "maxLength": null,
          "precision": 10,
          "scale": 0,
          "position": 1
        },
        {
          "columnName": "username",
          "dataType": "varchar",
          "columnType": "varchar(50)",
          "isNullable": false,
          "defaultValue": null,
          "isAutoIncrement": false,
          "comment": "用户名",
          "maxLength": 50,
          "precision": null,
          "scale": null,
          "position": 2
        }
      ],
      "primaryKeys": ["id"],
      "foreignKeys": [
        {
          "constraintName": "FK_user_role",
          "columnName": "role_id",
          "referencedTable": "roles",
          "referencedColumn": "id"
        }
      ],
      "indexes": [
        {
          "indexName": "PRIMARY",
          "columns": [
            {
              "columnName": "id",
              "position": 1
            }
          ],
          "isUnique": true,
          "indexType": "BTREE"
        },
        {
          "indexName": "idx_username",
          "columns": [
            {
              "columnName": "username",
              "position": 1
            }
          ],
          "isUnique": true,
          "indexType": "BTREE"
        }
      ],
      "constraints": [
        {
          "constraintName": "PRIMARY",
          "constraintType": "PRIMARY KEY"
        }
      ],
      "triggers": []
    }
  ],
  "metadata": {
    "totalTables": 1,
    "readAt": "2024-01-15T10:30:00.000Z",
    "executionTime": 1250,
    "config": {
      "includeViews": false,
      "includeIndexes": true,
      "includeConstraints": true,
      "includeTriggersInfo": false
    }
  }
}

字段说明​

表信息 (Table Info)​

  • tableName: 表名
  • tableType: 表类型（BASE TABLE、VIEW等）
  • engine: 存储引擎（InnoDB、MyISAM等）
  • tableComment: 表注释

列信息 (Column Info)​

  • columnName: 列名
  • dataType: 数据类型（int、varchar等）
  • columnType: 完整的列类型定义
  • isNullable: 是否允许NULL值
  • defaultValue: 默认值
  • isAutoIncrement: 是否自增
  • comment: 列注释
  • maxLength: 最大长度（字符串类型）
  • precision: 数值精度
  • scale: 数值标度
  • position: 列在表中的位置

索引信息 (Index Info)​

  • indexName: 索引名称
  • columns: 索引包含的列
  • isUnique: 是否唯一索引
  • indexType: 索引类型（BTREE、HASH等）

外键信息 (Foreign Key Info)​

  • constraintName: 约束名称
  • columnName: 外键列名
  • referencedTable: 被引用的表
  • referencedColumn: 被引用的列

错误响应结构​

{
  "success": false,
  "error": "连接数据库失败: Access denied for user 'root'@'localhost'",
  "database": "your_database",
  "host": "localhost",
  "readAt": "2024-01-15T10:30:00.000Z",
  "executionTime": 1000
}

应用场景​

1. 数据库文档生成​

自动生成数据库文档和数据字典：

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "production_db",
        "includeIndexes": true,
        "includeConstraints": true
      }
    },
    {
      "id": "generate-docs",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let markdown = '# 数据库结构文档\\n\\n';
          inputs.schema.forEach(table => {
            markdown += \`## \${table.tableName} - \${table.tableComment || '无注释'}\\n\\n\`;
            markdown += '| 字段名 | 类型 | 必填 | 默认值 | 注释 |\\n';
            markdown += '|--------|------|------|--------|------|\\n';
            table.columns.forEach(col => {
              markdown += \`| \${col.columnName} | \${col.columnType} | \${col.isNullable ? '否' : '是'} | \${col.defaultValue || '-'} | \${col.comment || '-'} |\\n\`;
            });
            markdown += '\\n';
          });
          return { markdown };
        `
      }
    }
  ]
}

2. 数据库迁移和同步​

比较不同环境的数据库结构差异：

{
  "workflow": [
    {
      "id": "read-prod-schema",
      "type": "mysql-schema",
      "config": {
        "host": "prod-db.example.com",
        "user": "readonly",
        "password": "{{prod_password}}",
        "database": "production"
      }
    },
    {
      "id": "read-dev-schema",
      "type": "mysql-schema",
      "config": {
        "host": "dev-db.example.com",
        "user": "dev",
        "password": "{{dev_password}}",
        "database": "development"
      }
    },
    {
      "id": "compare-schemas",
      "type": "js-executor",
      "inputs": {
        "prodSchema": "{{read-prod-schema.schema}}",
        "devSchema": "{{read-dev-schema.schema}}",
        "code": `
          const prodTables = new Set(inputs.prodSchema.map(t => t.tableName));
          const devTables = new Set(inputs.devSchema.map(t => t.tableName));
          
          return {
            missingInDev: [...prodTables].filter(t => !devTables.has(t)),
            missingInProd: [...devTables].filter(t => !prodTables.has(t)),
            commonTables: [...prodTables].filter(t => devTables.has(t))
          };
        `
      }
    }
  ]
}

3. 代码自动生成​

基于数据库结构生成模型类或API代码：

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db",
        "includeTables": ["users", "orders", "products"]
      }
    },
    {
      "id": "generate-models",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let models = {};
          inputs.schema.forEach(table => {
            const className = table.tableName.charAt(0).toUpperCase() + table.tableName.slice(1);
            let classCode = \`class \${className} {\\n\`;
            
            table.columns.forEach(col => {
              const jsType = col.dataType.includes('int') ? 'number' : 
                           col.dataType.includes('varchar') ? 'string' : 'any';
              classCode += \`  \${col.columnName}: \${jsType}; // \${col.comment || ''}\\n\`;
            });
            
            classCode += '}\\n';
            models[table.tableName] = classCode;
          });
          
          return { models };
        `
      }
    }
  ]
}

4. 数据库健康检查​

检查数据库设计规范和最佳实践：

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db",
        "includeIndexes": true
      }
    },
    {
      "id": "health-check",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let issues = [];
          
          inputs.schema.forEach(table => {
            // 检查是否有主键
            if (table.primaryKeys.length === 0) {
              issues.push(\`表 \${table.tableName} 没有主键\`);
            }
            
            // 检查是否有注释
            if (!table.tableComment) {
              issues.push(\`表 \${table.tableName} 没有注释\`);
            }
            
            // 检查列注释
            table.columns.forEach(col => {
              if (!col.comment && col.columnName !== 'id') {
                issues.push(\`表 \${table.tableName} 的列 \${col.columnName} 没有注释\`);
              }
            });
            
            // 检查索引
            const nonPrimaryIndexes = table.indexes.filter(idx => idx.indexName !== 'PRIMARY');
            if (nonPrimaryIndexes.length === 0 && table.columns.length > 3) {
              issues.push(\`表 \${table.tableName} 可能需要添加索引\`);
            }
          });
          
          return { issues, totalIssues: issues.length };
        `
      }
    }
  ]
}

安全配置​

数据库用户权限​

为了安全起见，建议创建专门的只读用户：

-- 创建专用的schema读取用户
CREATE USER 'schema_reader'@'%' IDENTIFIED BY 'secure_password';

-- 授予必要的权限
GRANT SELECT ON information_schema.* TO 'schema_reader'@'%';
GRANT SHOW VIEW ON your_database.* TO 'schema_reader'@'%';

-- 应用权限更改
FLUSH PRIVILEGES;

连接安全​

生产环境建议启用SSL连接：

{
  "config": {
    "host": "secure-db.example.com",
    "port": 3306,
    "user": "schema_reader",
    "password": "{{DB_PASSWORD}}",
    "database": "production",
    "ssl": {
      "rejectUnauthorized": true
    }
  }
}

性能优化​

1. 连接池配置​

根据并发需求调整连接池参数：

{
  "config": {
    "connectionLimit": 5,
    "acquireTimeout": 30000,
    "timeout": 30000
  }
}

2. 选择性读取​

只读取必要的信息以提高性能：

{
  "config": {
    "includeTables": ["users", "orders"],
    "includeViews": false,
    "includeTriggersInfo": false
  }
}

3. 批量处理​

对于大型数据库，考虑分批读取：

{
  "workflow": [
    {
      "id": "read-user-tables",
      "type": "mysql-schema",
      "config": {
        "includeTables": ["users", "user_profiles", "user_sessions"]
      }
    },
    {
      "id": "read-order-tables",
      "type": "mysql-schema",
      "config": {
        "includeTables": ["orders", "order_items", "payments"]
      }
    }
  ]
}

故障排除​

常见错误及解决方案​

错误信息可能原因解决方案
Access denied for user用户名或密码错误检查数据库凭据
Unknown database数据库不存在确认数据库名称正确
Connection timeout网络连接问题检查网络连接和防火墙设置
Table doesn't exist指定的表不存在检查includeTables中的表名
Too many connections连接数超限减少connectionLimit或增加MySQL的max_connections

调试技巧​

  1. 启用详细日志：

    {
      "config": {
        "debug": true
      }
    }

  2. 测试连接：

    mysql -h localhost -u root -p your_database

  3. 检查权限：

    SHOW GRANTS FOR 'your_user'@'%';

最佳实践​

1. 环境变量管理​

使用环境变量存储敏感信息：

{
  "config": {
    "host": "{{DB_HOST}}",
    "user": "{{DB_USER}}",
    "password": "{{DB_PASSWORD}}",
    "database": "{{DB_NAME}}"
  }
}

2. 错误处理​

在工作流中添加错误处理：

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db"
      },
      "onError": {
        "continue": true,
        "defaultValue": {
          "success": false,
          "schema": [],
          "error": "Failed to read schema"
        }
      }
    }
  ]
}

3. 结果缓存​

对于大型数据库，考虑缓存结果：

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db"
      },
      "cache": {
        "ttl": 3600,
        "key": "mysql-schema-{{database}}"
      }
    }
  ]
}

相关资源​