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 '%';