创建表
CREATE TABLE `count_table` ( `schema_in` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '库名', `table_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '表名', `coun` int DEFAULT NULL COMMENT '数据量', `max_occ` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最大时间戳' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE DEFINER=`xx`@`%` PROCEDURE `count_table`(in in_schema VARCHAR(100)) BEGIN #Routine body goes here... DECLARE c_n VARCHAR(50); DECLARE c_n1 VARCHAR(50); DECLARE done int default false; DECLARE cur CURSOR FOR SELECT distinct a.table_name FROM information_schema.TABLES a LEFT JOIN information_schema.COLUMNS b ON a.table_name = b.TABLE_NAME and a.TABLE_SCHEMA = b.TABLE_SCHEMA WHERE a.TABLE_SCHEMA = in_schema and b.COLUMN_NAME = 'update_time'; DECLARE continue HANDLER for not found set done = true; open cur; read_loop:loop fetch cur into c_n; if done then leave read_loop; end if; SET @s_l = CONCAT(' insert into count_table ( schema_in,table_name,coun,max_occ ) select ', "'", in_schema,"'",',',"'",c_n,"'",', count(*) as coun ,max(update_time) as max_occ from ',"`", in_schema,"`",'.',"`", c_n,"`") ; PREPARE stmt FROM @s_l; EXECUTE stmt ; deallocate prepare stmt; end loop; close cur; END
调用
call count_table('db_test')