sql查询表的大小

• 查询数据库的全部表的详细信息，包括表的大小

SHOW TABLE STATUS FROM database; -- 替换为你的数据库名称

注意：（DATA_LENGTH + INDEX_LENGTH）/1024/1024 得出的结果是大小单位是MB

• 查询数据库的全部表的大小MB

SELECT 
       SUM(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)) AS Size_MB
FROM 
     INFORMATION_SCHEMA.TABLES
WHERE 
      TABLE_SCHEMA = 'database'; -- 替换为你的数据库名称

• 查询某个数据库中多张表的大小MB

SELECT
    TABLE_NAME AS Name,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Size_MB
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'database' -- 替换为你的数据库名称
    AND TABLE_NAME IN ('table_name', 'table_name', 'table_name');

• 查询多个数据库的全部表的总大小

SELECT SUM(Size_MB) AS Total_Size_MB
FROM (
         SELECT sum(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)) AS Size_MB
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = 'database'
         UNION ALL
         SELECT sum(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)) AS Size_MB
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = 'database'
         UNION ALL
         SELECT sum(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)) AS Size_MB
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = 'database'
     ) AS total_sizes;