快捷導(dǎo)航

Mysql查詢數(shù)據(jù)庫或數(shù)據(jù)表中的數(shù)據(jù)量以及數(shù)據(jù)大小

更新時間：2024年02月21日 14:58:11 作者：Serendipity.

許多數(shù)據(jù)庫的元數(shù)據(jù)都是存儲在mysql中的,本文主要介紹了Mysql查詢數(shù)據(jù)庫或數(shù)據(jù)表中的數(shù)據(jù)量以及數(shù)據(jù)大小,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

許多數(shù)據(jù)庫的元數(shù)據(jù)都是存儲在mysql中的，例如hive、startrockes，因此可以通過mysql中的“information_schema.TABLES”表來查詢對應(yīng)數(shù)據(jù)庫或?qū)?yīng)數(shù)據(jù)表的具體信息。

1、查詢各個數(shù)據(jù)庫中的數(shù)據(jù)條數(shù)和數(shù)據(jù)大小

SELECT
	TABLE_SCHEMA AS '數(shù)據(jù)庫',
	sum( table_rows ) AS '記錄數(shù)',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(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、查詢各個數(shù)據(jù)表中的數(shù)據(jù)條數(shù)和數(shù)據(jù)大小

SELECT
	table_schema AS '數(shù)據(jù)庫',
	table_name AS '表名',
	table_rows AS '記錄數(shù)',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(MB)',
	TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
ORDER BY
	data_length DESC,
	index_length DESC;

3、查看指定數(shù)據(jù)庫容量大小

SELECT
	table_schema AS '數(shù)據(jù)庫',
	sum( table_rows ) AS '記錄數(shù)',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(MB)',
	sum(
	TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test';

4、查看指定數(shù)據(jù)庫各表容量大小

SELECT
	table_schema AS '數(shù)據(jù)庫',
	table_name AS '表名',
	table_rows AS '記錄數(shù)',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(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、查看指定數(shù)據(jù)庫各表的列數(shù)

SELECT TABLE_NAME, COUNT(*) AS COLUMN_COUNT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
GROUP BY TABLE_NAME;

到此這篇關(guān)于Mysql 查詢數(shù)據(jù)庫或數(shù)據(jù)表中的數(shù)據(jù)量以及數(shù)據(jù)大小的文章就介紹到這了,更多相關(guān)Mysql 查詢數(shù)據(jù)量及數(shù)據(jù)大小內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: