Mysql 工具类（一键生成所有表的表字段设计和建表语句）

工具描述：一键获取所有表的建表语句、一键获取所有表的表字段设计

最后生成在项目的根路径下：

说明：本工具类由于需要导出表格，因此需要用到我的Excel工具类。

详见（https://zyqok.blog.csdn.net/article/details/121994504）

工具类源码：

package com.zyq.utils.mysql;

import com.zyq.utils.excel.ExcelUtils;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * @author Yuanqiang.Zhang
 * @since 2023/4/11
 */
public class MySqlUtils {

    private static String ip = "127.0.0.1";
    private static int port = 3306;
    private static String db = "zyq";
    private static String username = "root";
    private static String password = "123456";
    private static Connection conn;
    private static Map<String, String> tables;

    static {
        conn = getConnection();
        tables = getAllTable();
    }

    public static void main(String[] args) {
        // 获取全表的建表语句
        getAllTableCreateSql();
        // 获取全表的表设计
        getAllTableFields();
    }

    /**
     * 获取全表设计
     */
    private static void getAllTableFields() {
        List<Object> head = new ArrayList<>();
        head.add("表名称");
        head.add("字段名称");
        head.add("字段描述");
        head.add("字段类型");
        head.add("是否可空");
        head.add("默认值");
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(head);
        int total = tables.size();
        int current = 0;
        for (Map.Entry<String, String> each : tables.entrySet()) {
            String tableName = each.getKey();
            List<List<Object>> tableFields = getTableFields(tableName);
            dataList.addAll(tableFields);
            current++;
            System.out.println(String.format("%s/%s %s 字段获取完成", current, total, tableName));
        }
        File file = new File("表设计.xlsx");
        ExcelUtils.exportFile(file, dataList);
        System.out.println("表设计.xlsx 生成完成！！");
    }

    /**
     * 获取单个表设计
     *
     * @param tableName 单个表名
     * @return List<List < Object>>
     */
    private static List<List<Object>> getTableFields(String tableName) {
        String sql = "SELECT * FROM information_schema.columns WHERE table_schema = '" + db + "' AND table_name = '" + tableName + "'";
        List<List<Object>> fields = new ArrayList<>();
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String columnDefault = rs.getString("COLUMN_DEFAULT");
                String columnComment = rs.getString("COLUMN_COMMENT");
                String isNullable = rs.getString("IS_NULLABLE");
                String columnType = rs.getString("COLUMN_TYPE");
                List<Object> column = new ArrayList<>();
                column.add(tableName);
                column.add(columnName);
                column.add(columnComment);
                column.add(columnType);
                column.add(isNullable);
                column.add(columnDefault);
                fields.add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fields;
    }

    /**
     * 获取所有表的建表语句
     */
    private static void getAllTableCreateSql() {
        StringBuilder sb = new StringBuilder();
        int total = tables.size();
        int current = 0;
        for (Map.Entry<String, String> each : tables.entrySet()) {
            String tableName = each.getKey();
            String tableComment = each.getValue();
            String desc = "";
            if (Objects.nonNull(tableComment) && !tableComment.trim().isEmpty()) {
                desc = String.format("-- %s（%s）", tableName, tableComment);
            } else {
                desc = String.format("-- %s", tableName);
            }
            String createTableSql = getCreateTableSql(tableName);
            sb.append(desc).append("\n");
            sb.append(createTableSql).append("\n\n");
            current++;
            System.out.println(String.format("%s/%s %s 建表SQL获取完成", current, total, tableName));
        }
        File file = new File("create_tables.sql");
        writeFileContent(file, sb.toString());
        System.out.println("create_tables.sql 文件生成！！");
    }

    /**
     * 获取某张表的建表语句
     *
     * @param tableName 表名
     * @return 建表语句
     */
    private static String getCreateTableSql(String tableName) {
        String sql = "SHOW CREATE TABLE " + tableName;
        String createTableSql = "";
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                createTableSql = rs.getString("Create Table") + ";";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return createTableSql;
    }

    /**
     * 获取所有数据表名
     *
     * @return Map<表名, 备注>
     */
    private static Map<String, String> getAllTable() {
        String sql = "SHOW TABLE status";
        Map<String, String> map = new LinkedHashMap<>();
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                String name = rs.getString("name");
                String comment = rs.getString("comment");
                map.put(name, comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }


    /**
     * 获取数据库链接
     *
     * @return Connection
     */
    private static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = String.format("jdbc:mysql://%s:%s/%s", ip, port, db);
            return DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 将内容写入到文件中
     *
     * @param file    文件
     * @param content 内容
     */
    public static void writeFileContent(File file, String content) {
        try (FileWriter writer = new FileWriter(file)) {
            writer.write(content);
            writer.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}