统计所有表的信息，并按倒序排列数据量

直接上结果

Table: all_info      数据量: 563009         数据大小:30.56 MB       索引大小:21.56 MB       总大小:52.13 MB
Table: all_invent        数据量: 3252577        数据大小:1009.00 MB         索引大小:343.00 MB      总大小:1.32 GB
Table: all_on_way        数据量: 565768         数据大小:149.56 MB      索引大小:65.38 MB       总大小:214.94 MB
Table: all_scan      数据量: 3368       数据大小:2.16 MB        索引大小:0.00 MB        总大小:2.16 MB

代码：

package com.xx.xx

import java.sql.*;


public class TableRowCount {
    public static void main(String[] args) {
        String url = "jdbc:mysql://rm-bertyuhi7utybho.mysql.rds.aliyuncs.com/oto";
        String user = "root";
        String password = "root;

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        }

        // 用于存储表信息的列表
        List<TableInfo> tableInfoList = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                int rowCount = getRowCount(conn, tableName);
                long dataSize = getDataSize(conn, tableName);
                long indexSize = getIndexSize(conn, tableName);
                System.out.println("table:"+tableName+"处理完成");

                // 将表信息添加到列表中
                tableInfoList.add(new TableInfo(tableName, rowCount, dataSize, indexSize));
            }

            // 按照数据量倒序排序
            Collections.sort(tableInfoList, new Comparator<TableInfo>() {
                @Override
                public int compare(TableInfo t1, TableInfo t2) {
                    return Integer.compare(t2.getRowCount(), t1.getRowCount()); // 倒序
                }
            });

            // 输出排序后的结果
            for (TableInfo tableInfo : tableInfoList) {
                String ds = formatSize(tableInfo.getDataSize());
                String is = formatSize(tableInfo.getIndexSize());
                String totalSize = formatSize(tableInfo.getDataSize() + tableInfo.getIndexSize());
                System.out.println("Table: " + tableInfo.getTableName() + "\t\t 数据量: " + tableInfo.getRowCount()
                        + "\t\t 数据大小:" + ds + "\t\t 索引大小:" + is + "\t\t 总大小:" + totalSize);
            }

            System.out.println("共：" + tableInfoList.size() + "张表");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT COUNT(*) AS row_count FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt("row_count");
            }
        }
        return 0;
    }

    private static long getDataSize(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT data_length FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getLong("data_length");
                }
            }
        }
        return 0;
    }

    private static long getIndexSize(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT index_length FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getLong("index_length");
                }
            }
        }
        return 0;
    }

    private static String formatSize(long size) {
        double newSize = size;
        String unit = "MB";
        if (size >= 1024 * 1024 * 1024) {
            newSize = newSize / (1024 * 1024 * 1024);
            unit = "GB";
        } else if (size >= 1024 * 1024) {
            newSize = newSize / (1024 * 1024);
        } else if (size >= 1024) {
            newSize = newSize / 1024;
            unit = "KB";
        }
        return String.format("%.2f %s", newSize, unit);
    }

    // 用于存储表信息的内部类
    static class TableInfo {
        private String tableName;
        private int rowCount;
        private long dataSize;
        private long indexSize;

        public TableInfo(String tableName, int rowCount, long dataSize, long indexSize) {
            this.tableName = tableName;
            this.rowCount = rowCount;
            this.dataSize = dataSize;
            this.indexSize = indexSize;
        }

        public String getTableName() {
            return tableName;
        }

        public int getRowCount() {
            return rowCount;
        }

        public long getDataSize() {
            return dataSize;
        }

        public long getIndexSize() {
            return indexSize;
        }
    }

}