What's the Meaning of "/ 1024 / 1024, 2" in the next SQL query?-CodePudding

I was looking for a way to know the sizes of my databases so I ended up using this query.

ROUND(SUM(data_length   index_length) / 1024 / 1024, 2) AS "Tamaño (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;```

CodePudding user response：

The data_length and index_length are probably bytes.

The / 1024 / 1024 is divide by 1024 then divide by 1024.

Bytes divided by 1024 gives you kilobytes.

Kilobytes divided by 1024 gives you megabytes.

Thus the result is the number of megabytes what is also suggested by the label Tamaño (MB) (even though I don't speak this language, but the MB is obvious).

, 2 is the second parameter to ROUND, giving the number of decimal places to round to.

CodePudding user response：

Sum up the data_length index_length is equal to the total table size.

data_length – store the real data.
index_length – store the table index.

Bytes divided by 1024 gives you kilobytes.

Kilobytes divided by 1024 gives you megabytes.

If you want to find the size of all MySQL databases, us this command, it will show their respective sizes in megabytes;

SELECT table_schema "database", sum(data_length   index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

If you have large databases, you can use the following command to show the result in gigabytes;

SELECT table_schema "database", sum(data_length   index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;