直接上结果

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://192.168.1.1:3306/test";
        String user = "root";
        String password = "123";

        int i = 0;
        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);

                i++;

                long dataSize = getDataSize(conn, tableName);
                long indexSize = getIndexSize(conn, tableName);
                String ds = formatSize(dataSize);
                String is = formatSize(indexSize);
                String totalSize = formatSize(dataSize + indexSize);
                System.out.println("Table: " + tableName + "\t\t 数据量: " + rowCount+"\t\t 数据大小:"+ds
                        +"\t\t 索引大小:"+is+"\t\t 总大小:"+totalSize);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("共:"+i+"张表");
    }

    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);
    }
}


分类: javamysql

站点统计

  • 文章总数:315 篇
  • 分类总数:20 个
  • 标签总数:193 个
  • 运行天数:1147 天
  • 访问总数:26773 人次

浙公网安备33011302000604

辽ICP备20003309号