百度已收录

前言

在mysql中有一个默认的数据表information_schema,information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。

再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面,所以请勿删改此表。

代码

  • 切换数据库
use information_schema;
  • 查看数据库使用大小
SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
TABLES 
WHERE
    table_schema = 'DB_Name';
  • 查看表使用大小
SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
TABLES 
WHERE
    table_schema = 'DB_Name' 
    AND table_name = 'Table_Name';
  • 查看所有数据库容量大小
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;
  • 查看所有数据表容量大小
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 = 'DB_Name';
  • 查看指定数据库各表容量大小
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 = 'DB_Name' 
ORDER BY
    data_length DESC,
    index_length DESC;