MySQL查询数据库表空间大小

时间:2024-05-06 00:06:49

一、查询所有数据库占用空间大小

SELECT
TABLE_SCHEMA,
CONCAT(
TRUNCATE(SUM(data_length) / 1024 / 1024, 2),
' MB'
) AS data_size,
CONCAT(
TRUNCATE(SUM(index_length) / 1024 / 1024, 2),
'MB'
) AS index_size
FROM
information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY data_length DESC;

二、查询对应数据库表占用空间大小

SELECT
TABLE_NAME,
CONCAT(
TRUNCATE(data_length / 1024 / 1024, 2),
' MB'
) AS data_size,
CONCAT(
TRUNCATE(index_length / 1024 / 1024, 2),
' MB'
) AS index_size
FROM
information_schema.tables
WHERE TABLE_SCHEMA = '数据库名字'
GROUP BY TABLE_NAME
ORDER BY data_length DESC;

三、查询某个数据库对应表占用空间大小

SELECT
CONCAT(
ROUND(SUM(DATA_LENGTH / 1024 / 1024), 2),
'MB'
) AS DATA
FROM
TABLES
WHERE table_schema = '数据库名字'
AND table_name = '表名字';