mysql存储过程实现统计数据库下所有表行数
DROP PROCEDURE IF EXISTS sp_count_all_tables;
DELIMITER
CREATE PROCEDURE sp_count_all_tables(IN db_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tname VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_schema = db_name AND table_type = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_counts (
table_name VARCHAR(255),
row_count BIGINT
);
TRUNCATE TABLE temp_counts;
OPEN cur;
read_loop: LOOP
FETCH cur INTO tname;
IF done THEN
LEAVE read_loop;
END IF;
SET @sql = CONCAT('INSERT INTO temp_counts SELECT ''', tname, ''', COUNT(*) FROM `', db_name, '`.`', tname, '`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
SELECT * FROM temp_counts ORDER BY row_count desc;
DROP TEMPORARY TABLE temp_counts;
END
DELIMITER ;
CALL sp_count_all_tables('db_name');