【MySQL】MySQL查数据库中每个表的数据量

---

一、方法1，数据字典表

1. /* Mysql查数据库中每个表的数据量 */
2. SELECT table_schema
3.       ,table_name
4.       ,table_rows
5.   FROM information_schema.tables
6. WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

复制代码

二、方法2，存过，循环count()每张表

1. drop PROCEDURE if exists GetAllTableCounts;
2. CREATE PROCEDURE GetAllTableCounts()
3. BEGIN
4.     DECLARE done INT DEFAULT FALSE;
5.     DECLARE aDatabase CHAR(64);
6.     DECLARE aTable CHAR(64);
7.     DECLARE dateString VARCHAR(23);
8.     DECLARE cur1 CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
9.     DECLARE cur2 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = aDatabase;
10.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
11.     set @sqlString = 'select ''数据库名'' as dbname,''表名'' as tablename,''数量'' as count ';
12.     OPEN cur1;
14.     read_loop: LOOP
15.         FETCH cur1 INTO aDatabase;
16.         IF done THEN
17.             LEAVE read_loop;
18.         END IF;
20.         OPEN cur2;
21.         table_loop: LOOP
22.             FETCH cur2 INTO aTable;
23.             IF done THEN
24.                 LEAVE table_loop;
25.             END IF;
26.             
27.             set @sqlString = CONCAT(@sqlString ,' union all select ''',aDatabase,''' as dbname, ''',aTable,''' as tablename, count(*) from ',aDatabase,'.',aTable);
28.             
29.         END LOOP table_loop;
31.         CLOSE cur2;
32.         SET done = FALSE;
33.     END LOOP read_loop;
34.    
35.     CLOSE cur1;
36.     select @sqlString;
37.    
38.     PREPARE stmt FROM @sqlString;
39.     execute stmt;
40.     DEALLOCATE PREPARE stmt;
41.    
42. END;
44. -- 调用方式1
45. -- call GetAllTableCounts();

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。