mysql统计数据库表记录行数

一、创建一张临时表用于存储表名和表记录行数

DROP TABLE IF EXISTS `t_tables_rows`;
CREATE TABLE `t_tables_rows`  (
  `tb_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `tb_count` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

二、存储过程统计表行记录

CREATE  PROCEDURE `count_table_rows`(_db_name VARCHAR(50))
BEGIN

	 DECLARE _done INT DEFAULT false; 
	 -- 记录表的条数
	 DECLARE _tc INT;
	 -- 记录表名
	 DECLARE _tn  VARCHAR(50);	
	 
	 -- 查询指定数据库表名 绑定游标
	  DECLARE my_cursor CURSOR FOR  (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = _db_name AND TABLE_NAME <>'t_tables_rows');
	 
	  -- 声明当游标遍历完后将标志变量置成某个值
   DECLARE CONTINUE HANDLER FOR NOT found SET _done = true;
	 
	 OPEN my_cursor;
		  my_loop:LOOP   
		     -- 获取游标中查询出的表名
         FETCH my_cursor INTO  _tn;		
				 
				 SET  @strSQL = CONCAT(' SELECT COUNT(1)  INTO  @tc FROM ',_tn);
				 
			     PREPARE  sqlstmt  FROM  @strSQL;
				 
				 EXECUTE  sqlstmt;
				 
				 SET  _tc = @tc;
				 
				 INSERT INTO t_tables_rows(tb_name,tb_count) VALUES(_tn,_tc);
				 
			   IF _done THEN LEAVE my_loop; 
				 
		     END IF;
		    -- 结束循环   
		   END LOOP my_loop;     
       -- 关闭游标
   CLOSE my_cursor;
	 
	 SELECT  * FROM  t_tables_rows;
	 
END

三、调用存储过程

CALL    count_table_rows('db_name');