如何获取所有列名及其类型，包括ENUM (及其可能的值)

获取数据库表列名、类型及ENUM可能值的方法

基础概念

在数据库管理和开发中，获取表的列名、数据类型以及ENUM类型的可能值是常见的需求。这对于动态生成SQL查询、构建ORM系统或进行数据库文档化非常重要。

方法汇总

1. MySQL/MariaDB

SELECT 
    COLUMN_NAME, 
    DATA_TYPE,
    COLUMN_TYPE,
    COLUMN_COMMENT
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name';

对于ENUM类型，COLUMN_TYPE字段会包含所有可能值，例如：enum('small','medium','large')

2. PostgreSQL

SELECT 
    column_name, 
    data_type,
    udt_name,
    character_maximum_length,
    is_nullable,
    column_default
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public'
    AND table_name = 'your_table_name';

对于枚举类型，需要额外查询：

SELECT 
    t.typname AS enum_name,
    e.enumlabel AS enum_value
FROM 
    pg_type t
    JOIN pg_enum e ON t.oid = e.enumtypid
    JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE 
    n.nspname = 'public';

3. SQL Server

SELECT 
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable
FROM 
    sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE 
    c.object_id = OBJECT_ID('your_table_name');

4. Oracle

SELECT 
    column_name, 
    data_type, 
    data_length, 
    data_precision, 
    data_scale, 
    nullable
FROM 
    all_tab_columns
WHERE 
    table_name = 'YOUR_TABLE_NAME';

5. SQLite

PRAGMA table_info('your_table_name');

SQLite不支持ENUM类型，但可以通过CHECK约束模拟。

编程语言实现示例

Python (MySQL)

import mysql.connector

def get_table_schema(db_name, table_name):
    conn = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database=db_name
    )
    
    cursor = conn.cursor(dictionary=True)
    
    # 获取列信息
    cursor.execute(f"""
        SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_COMMENT
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{table_name}'
    """)
    
    columns = cursor.fetchall()
    
    # 处理ENUM类型
    for col in columns:
        if 'enum' in col['COLUMN_TYPE'].lower():
            enum_values = col['COLUMN_TYPE'].split('enum(')[1].rstrip(')').replace("'", "").split(',')
            col['ENUM_VALUES'] = enum_values
    
    cursor.close()
    conn.close()
    
    return columns

# 使用示例
schema = get_table_schema('your_database', 'your_table')
for col in schema:
    print(f"Name: {col['COLUMN_NAME']}, Type: {col['DATA_TYPE']}")
    if 'ENUM_VALUES' in col:
        print(f"Possible values: {col['ENUM_VALUES']}")

Java (JDBC)

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TableSchema {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, "your_table", null);
            
            List<ColumnInfo> columnInfos = new ArrayList<>();
            
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String typeName = columns.getString("TYPE_NAME");
                String columnSize = columns.getString("COLUMN_SIZE");
                
                ColumnInfo info = new ColumnInfo(columnName, typeName, columnSize);
                
                // 处理ENUM类型
                if ("ENUM".equalsIgnoreCase(typeName)) {
                    String columnDef = columns.getString("COLUMN_DEF");
                    if (columnDef != null) {
                        String[] enumValues = columnDef.replaceAll("'", "").split(",");
                        info.setEnumValues(enumValues);
                    }
                }
                
                columnInfos.add(info);
            }
            
            // 输出结果
            for (ColumnInfo info : columnInfos) {
                System.out.println(info);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class ColumnInfo {
    private String name;
    private String type;
    private String size;
    private String[] enumValues;
    
    // 构造函数、getter和setter省略...
    
    @Override
    public String toString() {
        String result = "Column: " + name + ", Type: " + type + ", Size: " + size;
        if (enumValues != null) {
            result += ", Values: " + String.join(", ", enumValues);
        }
        return result;
    }
}