033.mysql-mysql查询每个表的数据量和每个表数据中最大的时间错-循环游标

创建表

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')