存储过程 - 《MySql》

CREATE DEFINER=`szsnwbmqzkdb`@`%` PROCEDURE `table_count_procedure`()
BEGIN
    -- 运行前清空对应表结构
    TRUNCATE table_count;
    -- 将查询出来的库名、表名、拼接的sql语句写入表\
    -- 此处sql语句是将查询的count(1)结构赋值给@count变量！
    INSERT INTO test.table_count ( table_schema, table_name, table_sql ) SELECT
    TABLE_schema,
    table_name,
    CONCAT( "select count(1) from ", TABLE_schema, ".", table_name, " into @count",";" ) 'table_sql' 
    FROM
        information_schema.TABLES 
    WHERE
        table_name LIKE 'biz%';
    -- 定义开始、结束值、开始从id为、开始、结束为表的行数
    set @start_xh = 1;
    set @end_xh= (select count(1) from test.table_count);
    WHILE @start_xh < @end_xh+1 DO
            -- 查询需要运行的sql、即拼接的语句
            set @real_sql = (select table_sql from test.table_count where id = @start_xh);
            -- 将sql预处理
            PREPARE ex_sql from @real_sql;
            -- 使用execute执行sql
            EXECUTE ex_sql;
            -- 将变量的值赋值给表中的res_count字段
            update test.table_count set count_res=@count where id = @start_xh;
            -- 序号需要自增
            set @start_xh = @start_xh + 1;
    END WHILE;
END