许多数据库的元数据都是存储在mysql中的，例如hive、startrockes，因此可以通过mysql中的“information_schema.TABLES”表来查询对应数据库或对应数据表的具体信息。
1、查询各个数据库中的数据条数和数据大小
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;
2、查询各个数据表中的数据条数和数据大小
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;
3、查看指定数据库容量大小
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 = 'test';
4、查看指定数据库各表容量大小
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 = 'test' ORDER BY data_length DESC, index_length DESC;
5、查看指定数据库各表的列数
SELECT TABLE_NAME, COUNT(*) AS COLUMN_COUNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' GROUP BY TABLE_NAME;
到此这篇关于Mysql 查询数据库或数据表中的数据量以及数据大小的文章就介绍到这了,更多相关Mysql 查询数据量及数据大小内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
