统计数据库中所有表的一条数据使用内存大小

• 一是表字段所能存储的字节数之和

#计算表数据的最大值,表中各字段能存储的字节数之和
#问题:一.字段长度给的不对(特别是通用字段) 二.很多字段存值为空
SELECT SUM(legth/1024) countlegth FROM (
    SELECT a.TABLE_NAME,SUM(a.LENGTH) legth FROM (
        SELECT TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,
            CASE DATA_TYPE
            WHEN 'varchar' THEN CHARACTER_OCTET_LENGTH +2
            WHEN 'char' THEN CHARACTER_OCTET_LENGTH 
            WHEN 'tinyblob' THEN CHARACTER_OCTET_LENGTH  + 1
            WHEN 'longblob' THEN  0 #CHARACTER_OCTET_LENGTH  + 4
            WHEN 'longtext' THEN CHARACTER_OCTET_LENGTH  + 4
            WHEN 'blob' THEN CHARACTER_OCTET_LENGTH +2
            WHEN 'text' THEN CHARACTER_OCTET_LENGTH +2
            WHEN 'tinyint' THEN 1
            WHEN 'smallint' THEN 2
            WHEN 'int' THEN 4
            WHEN 'bigint' THEN 8
            WHEN 'float' THEN 4
            WHEN 'double' THEN 8
            WHEN 'decimal' THEN NUMERIC_PRECISION +2
            WHEN 'date' THEN 3
            WHEN 'time' THEN 3
            WHEN 'year' THEN 1
            WHEN 'datetime' THEN 8
            ELSE CHARACTER_OCTET_LENGTH
        END
        AS LENGTH
    FROM COLUMNS n
    WHERE TABLE_SCHEMA = "ec_business_v3_test"
    #某个表
    #AND table_name = "table_name "
    #包含某字段的表
    AND table_name IN(SELECT a.table_NAME FROM TABLES a INNER JOIN COLUMNS b ON     a.TABLE_NAME = b.TABLE_NAME
    AND b.COLUMN_NAME = 'code' WHERE a.TABLE_SCHEMA = 'test' GROUP BY a.table_NAME)
    ) a
    GROUP BY a.TABLE_NAME
) b

 

二. 计算方式是表的存储大小/记录之和

#计算表数据大小的平均值,表存储大小/数据量之和
#问题:一.数据量小 二.分配大小以块递增,不是每添加一条内存就增加,初始16K
SELECT SUM(b.lenth)/1024 FROM (
    SELECT
      a.table_name,
      a.dates/a.table_rows AS lenth
    FROM (SELECT
            table_name,
            DATA_LENGTH           AS dates,
            table_rows
          FROM information_schema.TABLES
          WHERE table_schema = 'test'
          #某个表
          #AND table_name = "table_name"
          #包含code的表
          AND table_name IN(SELECT a.table_NAME FROM TABLES a INNER JOIN COLUMNS b ON                 
          a.TABLE_NAME = b.TABLE_NAME
          AND b.COLUMN_NAME = 'code' WHERE a.TABLE_SCHEMA = 'test' GROUP BY a.table_NAME)
     ) a
) b

有问题留言交流