Java如何获取数据库表索引并区分唯一索引与全文索引

一、区分唯一索引与全文索引 #

1. 修正唯一索引的判断逻辑 #

你原来的代码里唯一索引的判断写反了：NON_UNIQUE字段为true表示索引允许重复值（非唯一），正确的判断应该取反：

boolean isUnique = !rs.getBoolean("NON_UNIQUE");

2. 识别全文索引 #

JDBC标准的DatabaseMetaData没有统一的字段标识全文索引，得针对不同数据库做适配：

MySQL 适配方案

直接查询information_schema.STATISTICS表，通过INDEX_TYPE字段判断：

// 拿到索引名称后，执行这个查询判断是否为全文索引
String checkFulltextSql = "SELECT INDEX_TYPE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND INDEX_NAME = ? LIMIT 1";
try (PreparedStatement pstmt = conn.prepareStatement(checkFulltextSql)) {
    pstmt.setString(1, schema);
    pstmt.setString(2, tableName);
    pstmt.setString(3, indexName);
    ResultSet ftRs = pstmt.executeQuery();
    if (ftRs.next()) {
        boolean isFulltext = "FULLTEXT".equals(ftRs.getString("INDEX_TYPE"));
        // 把这个结果存下来即可
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Oracle 适配方案

Oracle的全文索引属于域索引，查询USER_INDEXES表判断：

String checkFulltextSql = "SELECT INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = ? AND INDEX_NAME = ?";
try (PreparedStatement pstmt = conn.prepareStatement(checkFulltextSql)) {
    // Oracle表名、索引名默认大写，注意转义
    pstmt.setString(1, tableName.toUpperCase());
    pstmt.setString(2, indexName.toUpperCase());
    ResultSet ftRs = pstmt.executeQuery();
    if (ftRs.next()) {
        String indexType = ftRs.getString("INDEX_TYPE");
        boolean isFulltext = indexType.contains("CONTEXT") || indexType.contains("CTXCAT");
        // 记录结果
    }
} catch (SQLException e) {
    e.printStackTrace();
}

二、生成表的完整DDL语句 #

要生成包含所有信息的DDL，有两种常用方式：

方法1：直接调用数据库内置功能（最省心） #

• MySQL：用SHOW CREATE TABLE直接获取完整DDL，里面包含所有索引（包括唯一、全文）的定义：

String showCreateSql = "SHOW CREATE TABLE " + tableName;
try (Statement stmt = conn.createStatement()) {
    ResultSet ddlRs = stmt.executeQuery(showCreateSql);
    if (ddlRs.next()) {
        String fullDdl = ddlRs.getString("Create Table");
        // fullDdl就是带所有索引、约束的完整表DDL
    }
} catch (SQLException e) {
    e.printStackTrace();
}

• Oracle：用DBMS_METADATA.GET_DDL函数获取：

String getDdlSql = "SELECT DBMS_METADATA.GET_DDL('TABLE', ?) FROM DUAL";
try (PreparedStatement pstmt = conn.prepareStatement(getDdlSql)) {
    pstmt.setString(1, tableName.toUpperCase());
    ResultSet ddlRs = pstmt.executeQuery();
    if (ddlRs.next()) {
        String fullDdl = ddlRs.getString(1);
        // 可以对fullDdl做格式整理，比如去掉多余换行
    }
} catch (SQLException e) {
    e.printStackTrace();
}

方法2：通过DatabaseMetaData拼接DDL（自定义程度高） #

如果需要自己控制DDL的格式，可以依次获取表的列、主键、索引、约束信息拼接：

// 1. 拼接表结构和列定义
StringBuilder ddlBuilder = new StringBuilder("CREATE TABLE ").append(tableName).append("(\n");
ResultSet columnsRs = metaData.getColumns(catalog, schema, tableName, null);
while (columnsRs.next()) {
    String colName = columnsRs.getString("COLUMN_NAME");
    String colType = columnsRs.getString("TYPE_NAME");
    int colSize = columnsRs.getInt("COLUMN_SIZE");
    boolean isNullable = columnsRs.getBoolean("NULLABLE");
    String defaultValue = columnsRs.getString("COLUMN_DEF");

    ddlBuilder.append("  ").append(colName).append(" ").append(colType);
    // 对需要指定长度的类型添加长度
    if (colSize > 0 && !"TEXT".equalsIgnoreCase(colType) && !"BLOB".equalsIgnoreCase(colType)) {
        ddlBuilder.append("(").append(colSize).append(")");
    }
    if (!isNullable) {
        ddlBuilder.append(" NOT NULL");
    }
    if (defaultValue != null) {
        ddlBuilder.append(" DEFAULT ").append(defaultValue);
    }
    ddlBuilder.append(",\n");
}

// 2. 拼接主键
ResultSet pkRs = metaData.getPrimaryKeys(catalog, schema, tableName);
StringBuilder pkBuilder = new StringBuilder("  PRIMARY KEY (");
boolean firstPk = true;
while (pkRs.next()) {
    if (!firstPk) {
        pkBuilder.append(", ");
    }
    pkBuilder.append(pkRs.getString("COLUMN_NAME"));
    firstPk = false;
}
pkBuilder.append(")");
ddlBuilder.append(pkBuilder).append("\n)");

// 3. 拼接索引（包含唯一、全文索引）
ResultSet indexRs = metaData.getIndexInfo(catalog, schema, tableName, false, false);
while (indexRs.next()) {
    String idxName = indexRs.getString("INDEX_NAME");
    if (idxName == null || idxName.equalsIgnoreCase("PRIMARY")) {
        continue; // 跳过主键索引
    }
    boolean unique = !indexRs.getBoolean("NON_UNIQUE");
    String colName = indexRs.getString("COLUMN_NAME");
    
    // 判断是否为全文索引（用前面的数据库适配逻辑）
    boolean isFulltext = false;
    // 这里插入前面的全文索引判断代码...
    
    StringBuilder idxBuilder = new StringBuilder("\n");
    if (isFulltext) {
        idxBuilder.append("CREATE FULLTEXT INDEX ").append(idxName).append(" ON ").append(tableName).append("(").append(colName).append(");");
    } else if (unique) {
        idxBuilder.append("CREATE UNIQUE INDEX ").append(idxName).append(" ON ").append(tableName).append("(").append(colName).append(");");
    } else {
        idxBuilder.append("CREATE INDEX ").append(idxName).append(" ON ").append(tableName).append("(").append(colName).append(");");
    }
    ddlBuilder.append(idxBuilder);
}

String finalDdl = ddlBuilder.toString();

内容的提问来源于stack exchange，提问作者陈平安