记录一个Mysql存储过程案例

有这样一个需求场景：统计所有用户使用公交卡的用卡数据（要满足一定的查询条件）到一张临时表。用户的公交卡用卡数据表是分表记录的（每天一张表，表名后缀加上当天日期，如：user_info_20190120）。

 

CREATE DEFINER=`root`@`%` PROCEDURE `user_infosum_fun`( )
BEGIN
DECLARE
    table_list LONGTEXT DEFAULT '';
DECLARE
    table_list_index01 VARCHAR ( 200 ) DEFAULT '';
DECLARE
    sum INT DEFAULT 1;
DECLARE
    i INT DEFAULT 1;

SET GLOBAL group_concat_max_len=10240000;
SET SESSION group_concat_max_len=10240000;
    
    
SELECT
    GROUP_CONCAT( table_name ) 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名' 
    AND table_name LIKE "user_info_%" INTO table_list;
SELECT
    count( table_name ) 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名' 
    AND table_name LIKE "user_info_%" INTO sum;
WHILE
    i <= sum DO
SELECT REPLACE
    (
    SUBSTRING( SUBSTRING_INDEX( table_list, ',', i ), LENGTH( SUBSTRING_INDEX( table_list, ',', i - 1 ) ) + 1 ),
    ',',
    '' 
    ) INTO table_list_index01;

SET @sqlStr := CONCAT( "INSERT INTO tab_cont(num) (select count(*) from ", table_list_index01, "where ...)" );
PREPARE stmt 
FROM
    @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET i = i + 1;

END WHILE;

SELECT
    table_list_index01;
END

 修改成游标循环表列表：

CREATE DEFINER=`root`@`%` PROCEDURE `user_infosum_fun_yb`( )
BEGIN
DECLARE
    table_list LONGTEXT DEFAULT '';
DECLARE
    tab_name VARCHAR ( 200 ) DEFAULT '';
DECLARE
    sum INT DEFAULT 1;
DECLARE
    i INT DEFAULT 1;
DECLARE
    curSales CURSOR FOR SELECT
    table_name 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名' 
    AND table_name LIKE "user_info_%";-- 声明游标
SELECT
    count( table_name ) 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名' 
    AND table_name LIKE "user_info_%" INTO sum;
OPEN curSales;-- 开启游标
curSales_loop :
LOOP
    FETCH curSales INTO tab_name;
IF
    i > sum THEN-- 循环结束
    LEAVE curSales_loop;
ELSE 
    SET @sqlStr := CONCAT( "INSERT INTO tab_cont(num) (select '",tab_name,"',count(*) from ", tab_name, "where ...)" );
PREPARE stmt 
FROM
    @sqlStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET i = i + 1;

END IF;

END LOOP curSales_loop;
CLOSE curSales;-- 关闭游标
SELECT
    tab_name;

END