统计所有表的信息,并按倒序排列数据量
直接上结果
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;
}
}
}