使用mysql存储过程-统计某个数据库下的所有表的记录数

3/7/2017来源:SQL技巧人气:2060

其中用到了游标(cursor),循环(loop),动态SQL预处理(PRepare)等技术,特此记录一下。

CREATE  PROCEDURE statis_rows(in v_schema varchar(50))
BEGIN


DECLARE sql_str VARCHAR (200);
declare no_more_departments integer DEFAULT 0;


DECLARE sql_cur CURSOR FOR (
	SELECT
		CONCAT(
			'select ',"'",table_schema,"'",",'",table_name,"',",'count(1)',' into @v_tab_schema,@v_tab_name,@v_count  from ',
			TABLE_SCHEMA,
			'.',
			TABLE_NAME,
			''
		)
	FROM
		information_schema.TABLES
	WHERE table_schema = v_schema
  and table_name <> 'table_rows'
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;


/* 打开游标,进入循环统计各表的记录 */
OPEN sql_cur;
myLoop:LOOP
fetch sql_cur into sql_str;


if no_more_departments = 1 THEN
leave myLoop;
end if;


set @v_sql=sql_str;
prepare stmt from @v_sql;




EXECUTE stmt;


/* 将统计结果保存到表中,以便随时都可以查看 */
INSERT INTO table_rows
VALUES
	(
		@v_tab_schema,
		@v_tab_name,
		@v_count,
		sysdate()
	);


DEALLOCATE prepare stmt;
end loop myLoop;


close sql_cur;
END
最后测试一下

call statis_rows('MySQL'); call statis_rows('scott'); select * from table_rows;