Navicat query MySQL database size_ Navicat view MySQL database and table capacity

u011042325 2022-02-13 05:53:03 阅读数:609

navicat query mysql database size_

1. View all database capacity sizes

select

table_schema as ' database ',

sum(table_rows) as ' Record number ',

sum(truncate(data_length/1024/1024, 2)) as ' Data capacity (MB)',

sum(truncate(index_length/1024/1024, 2)) as ' Index capacity (MB)'

from information_schema.tables

group by table_schema

order by sum(data_length) desc, sum(index_length) desc;

 

Be careful : Click on the run , If something goes wrong :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' database ' at line 1

terms of settlement : Just click run multiple times

2. Check the capacity of all databases

select

table_schema as ' database ',

table_name as ' Table name ',

table_rows as ' Record number ',

truncate(data_length/1024/1024, 2) as ' Data capacity (MB)',

truncate(index_length/1024/1024, 2) as ' Index capacity (MB)'

from information_schema.tables

order by data_length desc, index_length desc;

3. View the specified database capacity size

example : see mysql Library capacity size

select

table_schema as ' database ',

sum(table_rows) as ' Record number ',

sum(truncate(data_length/1024/1024, 2)) as ' Data capacity (MB)',

sum(truncate(index_length/1024/1024, 2)) as ' Index capacity (MB)'

from information_schema.tables

where table_schema='mysql';

4. Check the capacity of each table in the specified database

example : see mysql The capacity of each table in the library

select

table_schema as ' database ',

table_name as ' Table name ',

table_rows as ' Record number ',

truncate(data_length/1024/1024, 2) as ' Data capacity (MB)',

truncate(index_length/1024/1024, 2) as ' Index capacity (MB)'

from information_schema.tables

where table_schema='mysql'

order by data_length desc, index_length desc;
 

copyright:author[u011042325],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130553015546.html