truncate(x,y) 返回数字X截断为Y位小数的值

一、查看所有数据库容量大小

SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
    information_schema.TABLES
GROUP BY
    table_schema
ORDER BY
    sum( data_length ) DESC,
    sum( index_length ) DESC;

【注意】table_rows在MySQL中，information_schema.TABLES表中的table_rows列并不总是准确反映实际表中的行数。这是因为table_rows列的值是基于表的统计信息估算的，而不是实时计算的。因此，它可能与实际行数存在差异。

二、查看所有数据库各表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
    information_schema.TABLES
ORDER BY
    data_length DESC,
    index_length DESC;

三、查看指定数据库容量大小

    SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
    information_schema.TABLES
WHERE
    table_schema = 'voyage_ma';

四、查看指定数据库各表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
    information_schema.TABLES
WHERE
    table_schema = 'voyage_ma'
ORDER BY
    data_length DESC,
    index_length DESC;

五、查看指定数据库指定表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
    information_schema.TABLES
WHERE
    table_schema = 'voyage_ma'
    and table_name = 't_plugin';