How to implement the SQL statement to look up the size of the table space by mysql

This article will explain in detail how to implement the SQL statement about mysql to look up the size of the table space. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

The SQL statement is as follows:

Select concat (truncate (sum (data_length) / 1024 sum 1024), 'MB') as data_size

Concat (truncate (sum (max_data_length) / 1024 sum 1024), 'MB') as max_data_size

Concat (truncate (sum (data_free) / 1024 sum 1024), 'MB') as data_free

Concat (truncate (sum (index_length) / 1024 sum 1024), 'MB') as index_size

From information_schema.tables where TABLE_SCHEMA = 'databasename'

For example, check the tablespace size of the mysql database:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | db_shop |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.52sec)

Mysql >

Mysql > select concat (truncate (sum (data_length) / 1024 Universe 1024 as data_size 2), 'MB') as data_size

-> concat (truncate (sum (max_data_length) / 1024 Universe 1024 as max_data_size 2), 'MB') as max_data_size

-> concat (truncate (sum (data_free) / 1024 Universe 1024 as data_free 2), 'MB') as data_free

-> concat (truncate (sum (index_length) / 1024 Universe 1024 as index_size 2), 'MB') as index_size

-> from information_schema.tables where TABLE_SCHEMA = 'mysql'

+-+

| | data_size | max_data_size | data_free | index_size | |

+-+

| | 0.51MB | 1938103992319.99MB | 0.00MB | 0.09MB | |

+-+

1 row in set (1.49 sec)

Mysql >

This is the end of the article on "how mysql implements the SQL statement to look up the size of the table space". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.