MySQL 命令列查看資料庫/資料表大小完整指令集

前置動作：進入 information_schema

USE information_schema;

1. 所有資料庫總大小

SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024, 2), ' MB') AS total_size
FROM TABLES;

2. 指定資料庫大小 (例：home)

SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024, 2), ' MB') AS db_size
FROM TABLES
WHERE TABLE_SCHEMA = 'home';

3. 指定資料庫下的特定資料表大小 (例：home.members)

SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024, 2), ' MB') AS table_size
FROM TABLES
WHERE TABLE_SCHEMA = 'home' AND TABLE_NAME = 'members';

4. 列出資料庫內所有資料表大小排序

SELECT 
    TABLE_NAME,
    CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2), ' MB') AS total_size,
    TABLE_ROWS
FROM TABLES
WHERE TABLE_SCHEMA = 'home'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;

補充：含索引與資料分開顯示

SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
    ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_mb
FROM TABLES
WHERE TABLE_SCHEMA = 'home';