Mysql method to query database capacity: 1. Open the DOS window, and then enter the bin directory of mysql; 2. Execute "SELECT table_schema AS 'shujuku', table_name AS 'biaoming', table_rows AS 'jilushu', TRUNCATE (data_length / 1024 / 1024, 2) ..." statement can be used to view the capacity of each table in all databases.
The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.
How to query the database capacity with mysql?
MySql View database and table capacity and sort
MySql View database and table capacity and sort View all database capacities
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;
View all database table capacities
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;
View the capacity of the specified database
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 = 'your_table_name';
View the capacity of each table in the specified database
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 = '指定的库名' ORDER BY data_length DESC, index_length DESC;
Recommended study: "MySQL Video Tutorial"
The above is the detailed content of How to query database capacity in mysql. For more information, please follow other related articles on the PHP Chinese website!