SELECT 
    TABLE_NAME AS `表名`,
    TABLE_ROWS AS `行數(shù)`,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS `數(shù)據(jù)大小(MB)`,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS `索引大小(MB)`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `總占用空間(MB)`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS `總占用空間(GB)`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = 'your_database_name' -- 替換為你的數(shù)據(jù)庫名
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;

SELECT 
    TABLE_SCHEMA AS `數(shù)據(jù)庫名`,
    SUM(TABLE_ROWS) AS `總行數(shù)`,
    ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2) AS `數(shù)據(jù)總大小(MB)`,
    ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2) AS `索引總大小(MB)`,
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `總占用空間(MB)`,
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS `總占用空間(GB)`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = 'your_database_name'
GROUP BY 
    TABLE_SCHEMA;

select
table_schema as '數(shù)據(jù)庫',
table_name as '表名',
table_comment as '表注釋',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='test'
order by table_rows desc, index_length desc;

select concat(
'select "',
TABLE_name,
'", count(*) as t from `',
TABLE_SCHEMA,
'`.`',
TABLE_name,
'` union all'
) from information_schema.tables

-- 排除指定庫
where
 table_schema not in('mysql','information_schema','performance_schema','sys');