計算 Mysql database 大小

計算 Mysql database 大小

參考: https://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/

Sql

SELECT TABLE_SCHEMA, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 "DATA_SIZE(MB)"
FROM information_schema.TABLES 
GROUP BY TABLE_SCHEMA;

GROUP BY 可以替換成 WHERE TABLE_SCHEMA= your-data-base-name

SELECT TABLE_SCHEMA, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 "DATA_SIZE(MB)"
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA=erp;

LENGTH(str) 用途

參考：https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_length

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

簡單翻譯 LENGTH() 會計算 string 的 byte.

SELECT LENGTH('hello_world')
# 回傳 11

計算 all table 某個欄位大小

SELECT SUM(LENGTH(`column_name`)) 
FROM table_name
WHERE id = 1234567;

計算 one raw data 大小

SELECT (LENGTH(`id`) + LENGTH(`column_1`) + LENGTH(`column_2`) + LENGTH(`column_3`) + LENGTH(`column_4`)) RAW_SIZE
FROM table_name
WHERE id = 1234567;