【MySQL】MySQL查数据库中每个表的数据量

---

一、方法1，数据字典表

/* Mysql查数据库中每个表的数据量 */
SELECT table_schema
      ,table_name
      ,table_rows
  FROM information_schema.tables
 WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

二、方法2，存过，循环count()每张表

drop PROCEDURE if exists GetAllTableCounts;
CREATE PROCEDURE GetAllTableCounts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE aDatabase CHAR(64);
    DECLARE aTable CHAR(64);
    DECLARE dateString VARCHAR(23);
    DECLARE cur1 CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
    DECLARE cur2 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = aDatabase;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    set @sqlString = 'select ''数据库名'' as dbname,''表名'' as tablename,''数量'' as count ';
    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO aDatabase;
        IF done THEN
            LEAVE read_loop;
        END IF;

        OPEN cur2;
        table_loop: LOOP
            FETCH cur2 INTO aTable;
            IF done THEN
                LEAVE table_loop;
            END IF;
            
            set @sqlString = CONCAT(@sqlString ,' union all select ''',aDatabase,''' as dbname, ''',aTable,''' as tablename, count(*) from ',aDatabase,'.',aTable);
            
        END LOOP table_loop;

        CLOSE cur2;
        SET done = FALSE;
    END LOOP read_loop;
    
    CLOSE cur1;
    select @sqlString;
    
    PREPARE stmt FROM @sqlString;
    execute stmt;
    DEALLOCATE PREPARE stmt;
    
END;

-- 调用方式1
-- call GetAllTableCounts();