我如何findMySQL数据库的大小？

你想要的两个领域是。

• DATA_LENGTH

  这是表中数据的大小，以字节为单位。

• Index_length

  这是表索引的大小（以字节为单位）。

您可以将输出限制为带有数据库的一个表。

 SHOW TABLE STATUS LIKE 'tablename'; 

您不能从输出中删除其他列。 通过垂直打印输出行，您可能会发现输出更容易从控制台读取。 只需replace“ \G ”为“ \G ”即可。

我有一个相当野生的查询，可以很好地解决这个问题。 我在2年前写了这些，他们工作得很好。 我仍然使用它们来向客户报告。

查询以MB为单位给您由存储引擎分组的数据库大小

 SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR('KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM ( SELECT engine,SUM(data_length) DSize, SUM(index_length) ISize, SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B, (SELECT 2 pw) A ORDER BY TSize; 

查询给你数据库大小按MB分组的数据库大小

 SELECT DBName, CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM ( SELECT IFNULL(DB,'All Databases') DBName, SUM(DSize) SDSize, SUM(XSize) SXSize, SUM(TSize) STSize FROM ( SELECT table_schema DB, data_length DSize, index_length XSize, data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP ) AA, (SELECT 2 pw) BB ORDER BY (SDSize+SXSize); 

查询给你数据库大小按MB分组的数据库和存储引擎

 SELECT Statistic,DataSize "Data Size", IndexSize "Index Size", TableSize "Table Size" FROM ( SELECT IF(ISNULL(table_schema)=1,10,0) schema_score, IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname, IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2, "Storage for All Databases", IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1, CONCAT("Storage for ",B.table_schema), CONCAT(B.engine," Tables for ",B.table_schema))) Statistic, CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize, CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize, CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine, SUM(data_length) DSize, SUM(index_length) ISize, SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B, (SELECT 2 pw) A) AA ORDER BY schemaname, schema_score,engine_score; 

所有这三个查询都有一个共同点：一个简单的SELECT查询（SELECT 2 pw）。

pw代表power，指数用于数字1024.您可以调整查询来为您提供不同单位的数据库大小：

 (SELECT 0 pw) --reports the Database Size in Bytes (SELECT 1 pw) --reports the Database Size in Kilobytes (SELECT 2 pw) --reports the Database Size in Megabytes (SELECT 3 pw) --reports the Database Size in Gigabytes (SELECT 4 pw) --reports the Database Size in Terabytes (SELECT 5 pw) --reports the Database Size in Petabytes (email me if you reach this size) 

给他们一个尝试！

find一个脚本应该做你想要的 –

http://www.modwest.com/help/kb6-199.html

 <? mysql_connect("db.modwest.com", "username", "password"); mysql_select_db("yourdb"); $result = mysql_query("show table status"); $size = 0; $out = ""; while($row = mysql_fetch_array($result)) { $size += $row["Data_length"]; $out .= $row["Name"] .": ". round(($row["Data_length"]/1024)/1024, 2) ."<br>\n"; } $size = round(($size/1024)/1024, 1); echo $out ."<br>\n"; echo "Total MySQL db size: $size"; ?> 

如果您正在查找数据库在磁盘上占用的实际大小，则只需查找data / dbname目录的大小即可。

试试这个脚本：

 SELECT table_schema AS DataBase_Name ,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB ,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB FROM information_schema.TABLES GROUP BY table_schema ;