mysql 统计表的数量

1. 创建存储过程，具体代码如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_cal_count`( )
BEGIN

    /*---------------------------------------------------------
   -- 库\表：`beststock`.`t_count`
   -- 功能： 定期统计各表的数据量
   -- 入参： 
     -- 出参： 
   -- added by Bruce He 7/21/2020
   -- modified by 
-----------------------------------------------------------*/
    /*用于判断是否结束循环*/
  declare done int default 0;
    
    /*存储表名称的变量*/
    declare curtable VARCHAR(200);
    
    /*获取所有符合条件的表名称，并存入变量 tbs_list*/
    declare tbs_list cursor for select table_name from information_schema.`TABLES` where table_schema='beststock' and (table_name like 'ts%' or table_name like 'rim%');
    
    /*定义 设置循环结束标识done值怎么改变 的逻辑*/
    declare continue handler for not found set done =1;
    
    
    open tbs_list;
    
    /* 循环开始 */
    REPEAT
    
    fetch tbs_list into curtable;
    
    if not done then 
    
            /* 初始化 0 */
            set @v_count = 0;

            /* 动态语句执行 */
            set @sqlscript = concat('select count(1) into @v_count from ', curtable);
            prepare tem from @sqlscript;
            execute tem;

            insert into beststock.t_count (tablename, count, date) values (curtable, @v_count, CURRENT_DATE);
            
            deallocate prepare tem; 

        
    end if;

UNTIL done END REPEAT;

close tbs_list;

END

 

2. 开启定时任务；

-- 查看定时任务是否开启；ON 开启， OFF 未开启
show variables like '%schedule%';

-- 开启定时任务
set global event_scheduler = 1;

 

3. 设置定时任务事件