MySQL表結構導出（Excel）

???????

一、java實現MySQL表結構導出（Excel）

二、python實現MySQL表結構導出（Excel）

????????又到了寫畢設的時候了，計算機專業在寫論文第四章系統設計的時候肯定會遇到和我一樣的難題——要在論文中將數據庫的表結構以表格形式展示出來，小編在度娘搜了很多文章，但是收獲不大，很多沒有達到我的預期（以表格形式展示出來）。

????????最后，小編決定發揮一下idea的作用，自己寫一個工具類，打印數據庫中的表的表結構，最后將其保存到excel表中，這樣更加方便移到論文中。

? ? ? ? 廢話不多說，咱們直接開始。

一、java實現MySQL表結構導出（Excel）

MysqlExporterToExcel.java類

package com.example.demo.utils;import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class MysqlExporterToExcel extends JFrame {private JTextField hostField;private JTextField userField;private JPasswordField passwordField;private JTextField databaseField;private JTextField outputFileField;public MysqlExporterToExcel() {setTitle("MySQL 表結構導出工具");setSize(600, 400);setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);setLocationRelativeTo(null);JPanel panel = new JPanel();panel.setLayout(new GridLayout(6, 2));JLabel hostLabel = new JLabel("主機:");hostField = new JTextField("localhost");JLabel userLabel = new JLabel("用戶名:");userField = new JTextField("root");JLabel passwordLabel = new JLabel("密碼:");passwordField = new JPasswordField("123456");JLabel databaseLabel = new JLabel("數據庫:");databaseField = new JTextField("");// 設置默認輸出路徑和文件名String defaultPath = "C:/software/mysql/table_structure.xlsx";JLabel outputFileLabel = new JLabel("輸出文件:");outputFileField = new JTextField(defaultPath);JButton exportButton = new JButton("導出");panel.add(hostLabel);panel.add(hostField);panel.add(userLabel);panel.add(userField);panel.add(passwordLabel);panel.add(passwordField);panel.add(databaseLabel);panel.add(databaseField);panel.add(outputFileLabel);panel.add(outputFileField);panel.add(new JLabel());panel.add(exportButton);add(panel);exportButton.addActionListener(new ActionListener() {@Overridepublic void actionPerformed(ActionEvent e) {String host = hostField.getText();String user = userField.getText();String password = new String(passwordField.getPassword());String database = databaseField.getText();String outputFile = outputFileField.getText();// 輸入驗證if (host.isEmpty() || user.isEmpty() || database.isEmpty() || outputFile.isEmpty()) {JOptionPane.showMessageDialog(MysqlExporterToExcel.this,"請確保主機、用戶名、數據庫和輸出文件都已填寫", "輸入錯誤", JOptionPane.ERROR_MESSAGE);return;}// 檢查并創建目錄File file = new File(outputFile);File parentDir = file.getParentFile();if (!parentDir.exists()) {if (!parentDir.mkdirs()) {JOptionPane.showMessageDialog(MysqlExporterToExcel.this,"無法創建目錄: " + parentDir.getAbsolutePath(), "目錄創建失敗", JOptionPane.ERROR_MESSAGE);return;}}exportTableStructure(host, user, password, database, outputFile);}});}private void exportTableStructure(String host, String user, String password, String database, String outputFile) {String url = "jdbc:mysql://" + host + ":3306/" + database;try (Connection connection = DriverManager.getConnection(url, user, password);Statement statement = connection.createStatement();Workbook workbook = new XSSFWorkbook()) {// 檢查數據庫連接是否成功if (connection.isValid(5)) {Sheet sheet = workbook.createSheet("表結構");ResultSet tables = statement.executeQuery("SHOW TABLES");int rowNum = 0;String[] headers = {"序號", "名稱", "類型", "空", "長度", "主鍵", "說明", "其他備注"};while (tables.next()) {String tableName = tables.getString(1);// 寫入表名相關信息Row tableNameRow = sheet.createRow(rowNum++);tableNameRow.createCell(0).setCellValue("表名:");tableNameRow.createCell(1).setCellValue(tableName);// 寫入表頭Row headerRow = sheet.createRow(rowNum++);for (int col = 0; col < headers.length; col++) {Cell cell = headerRow.createCell(col);cell.setCellValue(headers[col]);}// 獲取主鍵信息ResultSet primaryKeys = connection.getMetaData().getPrimaryKeys(null, null, tableName);Map<String, Boolean> primaryKeyMap = new HashMap<>();while (primaryKeys.next()) {String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");primaryKeyMap.put(primaryKeyColumn, true);}primaryKeys.close();ResultSet columns = connection.getMetaData().getColumns(null, null, tableName, null);ResultSetMetaData metaData = columns.getMetaData();int extraColumnIndex = -1;for (int i = 1; i <= metaData.getColumnCount(); i++) {if ("EXTRA".equalsIgnoreCase(metaData.getColumnName(i))) {extraColumnIndex = i;break;}}int serialNumber = 1;while (columns.next()) {String columnName = columns.getString("COLUMN_NAME");String columnType = columns.getString("TYPE_NAME");int nullable = columns.getInt("NULLABLE");String isNullable = (nullable == ResultSetMetaData.columnNullable)? "是" : "否";int columnSize = columns.getInt("COLUMN_SIZE");String isPrimaryKey = primaryKeyMap.containsKey(columnName)? "是" : "否";// 簡單的字段名翻譯示例，可根據實際情況擴展String description = translateColumnName(columnName);String extra = "";if (extraColumnIndex != -1) {extra = columns.getString(extraColumnIndex);}if ("".equals(extra)) {extra = "<空>";}Row row = sheet.createRow(rowNum++);row.createCell(0).setCellValue(serialNumber++);row.createCell(1).setCellValue(columnName);row.createCell(2).setCellValue(columnType);row.createCell(3).setCellValue(isNullable);row.createCell(4).setCellValue(columnSize);row.createCell(5).setCellValue(isPrimaryKey);row.createCell(6).setCellValue(description);row.createCell(7).setCellValue(extra);}// 在每個表的信息后插入一個空行sheet.createRow(rowNum++);}tables.close();// 調整列寬for (int col = 0; col < headers.length; col++) {sheet.autoSizeColumn(col);}// 保存 Excel 文件try (FileOutputStream fileOut = new FileOutputStream(outputFile)) {workbook.write(fileOut);JOptionPane.showMessageDialog(this, "表結構已成功導出到 " + outputFile);}} else {JOptionPane.showMessageDialog(this, "無法連接到數據庫", "連接錯誤", JOptionPane.ERROR_MESSAGE);}} catch (SQLException sqlEx) {if (sqlEx.getSQLState().startsWith("28")) {JOptionPane.showMessageDialog(this, "用戶名或密碼錯誤", "認證錯誤", JOptionPane.ERROR_MESSAGE);} else if (sqlEx.getSQLState().startsWith("08")) {JOptionPane.showMessageDialog(this, "無法連接到數據庫，請檢查主機和端口", "連接錯誤", JOptionPane.ERROR_MESSAGE);} else {JOptionPane.showMessageDialog(this, "導出失敗: " + sqlEx.getMessage(), "錯誤", JOptionPane.ERROR_MESSAGE);}} catch (IOException ioEx) {JOptionPane.showMessageDialog(this, "文件寫入失敗: " + ioEx.getMessage(), "文件錯誤", JOptionPane.ERROR_MESSAGE);}}private String translateColumnName(String columnName) {// 簡單的翻譯映射，可根據實際情況擴展Map<String, String> translationMap = new HashMap<>();translationMap.put("id", "編號");translationMap.put("name", "名稱");translationMap.put("age", "年齡");// 可以繼續添加更多的翻譯映射return translationMap.getOrDefault(columnName, "");}public static void main(String[] args) {SwingUtilities.invokeLater(new Runnable() {@Overridepublic void run() {MysqlExporterToExcel exporter = new MysqlExporterToExcel();exporter.setVisible(true);}});}
}

在java中還要引入依賴，這我就不細說了。

<!--導出表--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency>

這樣就可以了。

我們看一下運行效果：

、

上面的都可以修改。

小提醒：最后就是數據庫的問題

在java類中，我寫的是

String url = "jdbc:mysql://" + host + ":3306/" + database;

因為安裝數據庫時默認端口為3306，可能你在安裝時3306被占用，比如你在安裝時輸入的是3308，這里也要改為3308。

二、python實現MySQL表結構導出（Excel）

可能有的人要問了，小編小編，我不會java怎么辦啊，考慮到一些同學沒有學習java，我寫了一個pyhton版。下面就是python的代碼

import tkinter as tk
from tkinter import messagebox
import pymysql
from openpyxl import Workbook
import osclass MysqlTableStructureExporter:def __init__(self):self.root = tk.Tk()self.root.title("MySQL 表結構導出工具")# 定義輸入框和標簽tk.Label(self.root, text="主機:").grid(row=0, column=0)self.host_field = tk.Entry(self.root, width=30)  # 修改寬度為 30self.host_field.insert(0, "localhost")self.host_field.grid(row=0, column=1)tk.Label(self.root, text="用戶名:").grid(row=1, column=0)self.user_field = tk.Entry(self.root, width=30)  # 修改寬度為 30self.user_field.insert(0, "root")self.user_field.grid(row=1, column=1)tk.Label(self.root, text="密碼:").grid(row=2, column=0)self.password_field = tk.Entry(self.root, show="*", width=30)  # 修改寬度為 30self.password_field.insert(0, "123456")self.password_field.grid(row=2, column=1)tk.Label(self.root, text="數據庫:").grid(row=3, column=0)self.database_field = tk.Entry(self.root, width=30)  # 修改寬度為 30self.database_field.grid(row=3, column=1)tk.Label(self.root, text="輸出文件:").grid(row=4, column=0)default_path = "C:/software/mysql/table_structure.xlsx"self.output_file_field = tk.Entry(self.root, width=30)  # 修改寬度為 30self.output_file_field.insert(0, default_path)self.output_file_field.grid(row=4, column=1)# 導出按鈕export_button = tk.Button(self.root, text="導出", command=self.export_table_structure)export_button.grid(row=5, column=0, columnspan=2)def run(self):self.root.mainloop()def export_table_structure(self):host = self.host_field.get()user = self.user_field.get()password = self.password_field.get()database = self.database_field.get()output_file = self.output_file_field.get()# 輸入驗證if not host or not user or not database or not output_file:messagebox.showerror("輸入錯誤", "請確保主機、用戶名、數據庫和輸出文件都已填寫")return# 檢查并創建目錄output_dir = os.path.dirname(output_file)if not os.path.exists(output_dir):try:os.makedirs(output_dir)except OSError:messagebox.showerror("目錄創建失敗", f"無法創建目錄: {output_dir}")returntry:# 連接數據庫connection = pymysql.connect(host=host, user=user, password=password, database=database)cursor = connection.cursor()# 創建 Excel 工作簿和工作表workbook = Workbook()sheet = workbook.activesheet.title = "表結構"# 獲取所有表名cursor.execute("SHOW TABLES")tables = cursor.fetchall()row_num = 0for table in tables:table_name = table[0]# 寫入表名sheet.cell(row=row_num + 1, column=1, value="表名:")sheet.cell(row=row_num + 1, column=2, value=table_name)row_num += 1# 寫入表頭headers = ["序號", "名稱", "類型", "空", "長度", "主鍵", "說明", "其他備注"]for col, header in enumerate(headers, start=1):sheet.cell(row=row_num + 1, column=col, value=header)row_num += 1# 獲取主鍵信息cursor.execute(f"SHOW KEYS FROM {table_name} WHERE Key_name = 'PRIMARY'")primary_keys = [row[4] for row in cursor.fetchall()]# 獲取表的列信息cursor.execute(f"SHOW FULL COLUMNS FROM {table_name}")columns = cursor.fetchall()serial_number = 1for column in columns:column_name = column[0]column_type = column[1]is_nullable = "是" if column[2] == "YES" else "否"column_size = column[1].split("(")[-1].rstrip(")") if "(" in column[1] else ""is_primary_key = "是" if column_name in primary_keys else "否"description = self.translate_column_name(column_name)extra = column[8] if column[8] else "<空>"sheet.cell(row=row_num + 1, column=1, value=serial_number)sheet.cell(row=row_num + 1, column=2, value=column_name)sheet.cell(row=row_num + 1, column=3, value=column_type)sheet.cell(row=row_num + 1, column=4, value=is_nullable)sheet.cell(row=row_num + 1, column=5, value=column_size)sheet.cell(row=row_num + 1, column=6, value=is_primary_key)sheet.cell(row=row_num + 1, column=7, value=description)sheet.cell(row=row_num + 1, column=8, value=extra)row_num += 1serial_number += 1# 插入空行row_num += 1# 調整列寬for column in sheet.columns:max_length = 0column_letter = column[0].column_letterfor cell in column:try:if len(str(cell.value)) > max_length:max_length = len(str(cell.value))except:passadjusted_width = (max_length + 2)sheet.column_dimensions[column_letter].width = adjusted_width# 保存 Excel 文件workbook.save(output_file)messagebox.showinfo("導出成功", f"表結構已成功導出到 {output_file}")except pymysql.Error as e:if e.args[0] in [1045]:  # 認證錯誤messagebox.showerror("認證錯誤", "用戶名或密碼錯誤")elif e.args[0] in [2003]:  # 連接錯誤messagebox.showerror("連接錯誤", "無法連接到數據庫，請檢查主機和端口")else:messagebox.showerror("錯誤", f"導出失敗: {str(e)}")except Exception as e:messagebox.showerror("文件錯誤", f"文件寫入失敗: {str(e)}")finally:if 'connection' in locals():connection.close()def translate_column_name(self, column_name):# 簡單的翻譯映射，可根據實際情況擴展translation_map = {"id": "編號","name": "名稱","age": "年齡"}return translation_map.get(column_name, "")if __name__ == "__main__":exporter = MysqlTableStructureExporter()exporter.run()

復制代碼后直接導入包就行。

我們來看一下運行效果