mysql容量预估两位数对齐 |mysql数据备份方案

数据量预估

SELECT 
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Total Size (GB)' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'yourdatabase'

以上SQL语句可以获取MySQL数据库的总容量大小。

数据表大小预估

SELECT 
TABLE_NAME AS `Table`, 
ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'yourdatabase'
ORDER BY (data_length + index_length) DESC;

以上SQL语句可以获取MySQL数据库中每个表的容量大小。

容量预估结果对齐

SELECT
TABLE_NAME AS `Table`,
CONCAT(
LPAD(ROUND((data_length + index_length) / 1024 / 1024 / 1024), 2), ' GB '
),
CONCAT(
LPAD(ROUND((data_length + index_length) / 1024 / 1024), 2), ' MB '
),
CONCAT(
LPAD(ROUND((data_length + index_length) / 1024), 2), ' KB '
),
CONCAT(
LPAD(ROUND(data_length / 1024 / 1024), 2), ' MB '
) "Data Size",
CONCAT(
LPAD(ROUND(index_length / 1024 / 1024), 2), ' MB '
) "Index Size",
CONCAT(
LPAD(ROUND((data_length + index_length) / 1024 / 1024), 2), ' MB '
) "Total Size"
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'yourdatabase'
ORDER BY
(data_length + index_length) DESC;

以上SQL语句可以得到MySQL数据库中每个表的容量大小，并将结果对齐。