mysql查数据量

Mysql查数据库全部的数据量

drop PROCEDURE if exists get_all_cnt;

DELIMITER //

CREATE PROCEDURE get_all_cnt()

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE db VARCHAR(100);

    DECLARE tb VARCHAR(100);

    DECLARE t_name VARCHAR(100);

    DECLARE cnt bigint;

    DECLARE cur CURSOR FOR

        SELECT  table_schema,table_name FROM information_schema.tables

        WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    

    DROP TEMPORARY TABLE IF EXISTS temp_cnt;

    CREATE TEMPORARY TABLE temp_cnt (

        db_tb VARCHAR(255),

        row_cnt INT

    );

    

    OPEN cur;

    

    read_loop: LOOP

        FETCH cur INTO db,tb;

        IF done THEN

            LEAVE read_loop;

        END IF;

        

        SET @sql = CONCAT(' INSERT INTO temp_cnt SELECT "', db, '.',tb,'", COUNT(*) FROM ', db, '.', tb);

        PREPARE stmt FROM @sql;

        EXECUTE stmt;        

        DEALLOCATE PREPARE stmt;

    END LOOP;

    

    CLOSE cur;   

END //

DELIMITER ;

CALL get_all_cnt();

SELECT sum(row_cnt) FROM temp_cnt;

DROP TEMPORARY TABLE IF EXISTS temp_cnt;

查每个数据库全部的数据量

SELECT table_schema,

       SUM(table_rows)

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

GROUP BY table_schema;

查当前数据库全部表的数据量

SHOW TABLE STATUS LIKE '%';