工具（十二）：Java导出MySQL数据库表结构信息到excel
一、背景
遇到需求：将指定数据库表设计，统一导出到一个Excel中，存档查看。
如果一个一个弄，很复杂，耗时长。
二、写一个工具导出下
废话少絮，上码：
2.1 pom导入
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.4.0</version>
</dependency>
2.2 工具类
这里提供思路和示例
package com.eduer.books.modules.app.controller;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.HashSet;
import java.util.Set;
import java.util.regex.Pattern;
/**
* Java导出mysql数据库表结构信息到excel
* @author wangdy
* 2025/3/13
*/
public class DatabaseExporter {
private static final Pattern INVALID_SHEETNAME_CHARS = Pattern.compile("[\\\\/*?\\[\\]:]");
private static final int MAX_SHEETNAME_LENGTH = 31;
public static void exportToExcel(String dbName, String jdbcUrl, String username, String password, String outputPath)
throws Exception {
Set<String> usedSheetNames = new HashSet<>();
try (Workbook workbook = new XSSFWorkbook(); Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet tables = metaData.getTables(dbName, null, "%", new String[]{"TABLE"});
while (tables.next()) {
String catalog = tables.getString("TABLE_CAT");
String schema = tables.getString("TABLE_SCHEM");
String tableName = tables.getString("TABLE_NAME");
// 生成合法的Sheet名称
String baseSheetName = generateBaseSheetName(catalog, schema, tableName);
String uniqueSheetName = generateUniqueSheetName(baseSheetName, usedSheetNames);
Sheet sheet = workbook.createSheet(uniqueSheetName);
usedSheetNames.add(uniqueSheetName);
createHeaderRow(sheet);
processTableColumns(metaData, tableName, sheet);
autoSizeColumns(sheet, 7);
}
try (FileOutputStream outputStream = new FileOutputStream(outputPath)) {
workbook.write(outputStream);
}
}
}
private static String generateBaseSheetName(String catalog, String schema, String tableName) {
// 优先使用schema信息，MySQL中一般用catalog表示数据库
String prefix = "";
if (schema != null && !schema.isEmpty()) {
prefix = schema;
} else if (catalog != null && !catalog.isEmpty()) {
prefix = catalog;
}
String rawName = prefix.isEmpty()
? tableName
: prefix + "_" + tableName;
// 替换非法字符并格式化
return formatSheetName(rawName);
}
private static String formatSheetName(String rawName) {
// 1. 替换非法字符
String sanitized = INVALID_SHEETNAME_CHARS.matcher(rawName).replaceAll("_");
// 2. 去除首尾特殊字符
sanitized = sanitized.replaceAll("^[\\s']+", "").replaceAll("[\\s']+$", "");
// 3. 压缩连续下划线
sanitized = sanitized.replaceAll("_{2,}", "_");
// 4. 截断长度
return sanitized.length() > MAX_SHEETNAME_LENGTH
? sanitized.substring(0, MAX_SHEETNAME_LENGTH)
: sanitized;
}
private static String generateUniqueSheetName(String baseName, Set<String> usedNames) {
if (!usedNames.contains(baseName)) {
return baseName;
}
int suffix = 1;
String candidateName;
do {
String suffixStr = "_" + suffix++;
int maxBaseLength = MAX_SHEETNAME_LENGTH - suffixStr.length();
candidateName = (baseName.length() > maxBaseLength
? baseName.substring(0, maxBaseLength)
: baseName) + suffixStr;
} while (usedNames.contains(candidateName));
return candidateName;
}
private static void processTableColumns(DatabaseMetaData metaData, String tableName, Sheet sheet)
throws SQLException {
ResultSet columns = metaData.getColumns(null, null, tableName, null);
Set<String> primaryKeys = getPrimaryKeys(metaData, tableName);
int rowNum = 1;
while (columns.next()) {
Row row = sheet.createRow(rowNum++);
fillRowData(columns, primaryKeys, row);
}
columns.close();
}
// 以下方法保持不变（createHeaderRow, createHeaderStyle, getPrimaryKeys, fillRowData, autoSizeColumns）
private static void createHeaderRow(Sheet sheet) {
Row headerRow = sheet.createRow(0);
String[] headers = {"字段名称", "字段类型", "长度", "是否主键", "允许空值", "默认值", "字段注释"};
CellStyle headerStyle = createHeaderStyle(sheet.getWorkbook());
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
}
private static CellStyle createHeaderStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
private static Set<String> getPrimaryKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
Set<String> primaryKeys = new HashSet<>();
ResultSet pkResultSet = metaData.getPrimaryKeys(null, null, tableName);
while (pkResultSet.next()) {
primaryKeys.add(pkResultSet.getString("COLUMN_NAME"));
}
pkResultSet.close();
return primaryKeys;
}
private static void fillRowData(ResultSet columns, Set<String> primaryKeys, Row row) throws SQLException {
String columnName = columns.getString("COLUMN_NAME");
String typeName = columns.getString("TYPE_NAME");
int columnSize = columns.getInt("COLUMN_SIZE");
String isNullable = columns.getString("IS_NULLABLE");
String defaultValue = columns.getString("COLUMN_DEF");
String remarks = columns.getString("REMARKS");
row.createCell(0).setCellValue(columnName);
row.createCell(1).setCellValue(typeName);
row.createCell(2).setCellValue(columnSize);
row.createCell(3).setCellValue(primaryKeys.contains(columnName) ? "是" : "否");
row.createCell(4).setCellValue("YES".equalsIgnoreCase(isNullable) ? "是" : "否");
row.createCell(5).setCellValue(defaultValue != null ? defaultValue : "");
row.createCell(6).setCellValue(remarks != null ? remarks : "");
}
private static void autoSizeColumns(Sheet sheet, int columnCount) {
for (int i = 0; i < columnCount; i++) {
sheet.autoSizeColumn(i);
}
}
public static void main(String[] args) {
try {
String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/books-service?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "xxxxxxx";
String outputPath = "数据库表结构.xlsx";
exportToExcel("books-service", jdbcUrl, username, password, outputPath);
System.out.println("导出成功！");
} catch (Exception e) {
e.printStackTrace();
}
}
}
main方法运行即可。
三、结果截图：
