查询mysql数据库中各shema中的表数量【存储过程】

原创已于 2022-04-15 15:20:08 修改 · 851 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2022-04-15 15:14:42 首次发布

该博客介绍了一个存储过程，用于遍历所有数据库并统计每个数据库中的表数量。通过设置游标、处理循环和使用临时表，该过程能有效收集并返回每个数据库的表格统计信息。

CREATE DEFINER=`root`@`%` PROCEDURE `shema_tables_statistics`()
BEGIN
   -- 定义数据库名接收变量
   DECLARE _schema_name VARCHAR ( 255 );
   -- 循环游标结束的标记
   DECLARE done INT DEFAULT FALSE;
   -- 查询数据库信息的游标，查询有多少个数据库（shema）
   DECLARE shema_cursor CURSOR FOR SELECT schema_name FROM information_schema.schemata;
   -- 循环到没有数据时的状态值，用于退出游标
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   -- 创建临时表，用于临时缓存各数据库表的数量信息
   CREATE TEMPORARY TABLE IF NOT EXISTS result_sets(_schema VARCHAR ( 255 ) NOT NULL, _count INT);
   -- 初始化临时表
   TRUNCATE TABLE result_sets;

   -- 打开游标
   OPEN shema_cursor;
   -- 开始循环处理
   cur_loop : LOOP
   -- 获取游标的一行数据，循环一次，指针向下移动一条
       FETCH shema_cursor INTO _schema_name;
       -- 因为数据库名是动态的，所以采用拼接sql字符串，执行字符串的方式
       SET @select_count_table = CONCAT( "insert into result_sets(_schema,_count) select '", _schema_name, "' _schema, count(*) from information_schema.tables where table_schema='", _schema_name, "'" );
       -- 将sql字符串进行执行
       PREPARE select_count_table FROM @select_count_table;
       EXECUTE select_count_table;

       -- 如果游标已无下一条，则退出循环
       IF done THEN
           LEAVE cur_loop;
       END IF;
   -- 结束游标
   END LOOP cur_loop;
   -- 临时表作为结果集返回
   SELECT * FROM result_sets;

END