概要描述

本文主要介绍在kundb中如何通过存储过程统计所有表的条数，仅供参考。

详细说明

获取某个database下表的条数

DROP PROCEDURE IF EXISTS count_rows;

DELIMITER //
CREATE PROCEDURE count_rows()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_database';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
    DROP TABLE IF EXISTS temp_table;
    CREATE  TABLE IF NOT EXISTS temp_table (table_name VARCHAR(255), row_count INT );
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('SELECT COUNT(*) INTO @row_count FROM ', v_table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        INSERT INTO temp_table (table_name, row_count) VALUES (v_table_name, @row_count);
    END LOOP;
    CLOSE cur;
    SELECT * FROM temp_table;
END //
DELIMITER ;

CALL test_database.count_rows_all();

获取所有database下表的条数

DROP PROCEDURE IF EXISTS count_rows_all;
DELIMITER //
CREATE PROCEDURE count_rows_all()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_db_name VARCHAR(255);
    DECLARE v_table_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_schema,table_name FROM information_schema.tables where table_schema not in ('information_schema','performance_schema','mysql','sys');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
    DROP TABLE IF EXISTS temp_table;
    CREATE  TABLE IF NOT EXISTS temp_table (db_name VARCHAR(255),table_name VARCHAR(255), row_count INT );
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_db_name,v_table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('SELECT COUNT(*) INTO @row_count FROM ', v_db_name,'.',v_table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        INSERT INTO temp_table (db_name,table_name, row_count) VALUES (v_db_name,v_table_name, @row_count);
    END LOOP;
    CLOSE cur;
    SELECT * FROM temp_table;
END //
DELIMITER ;

CALL test_database.count_rows_all();