DROP PROCEDURE IF EXISTS  sp_getmsgtotal;


DELIMITER//
CREATE PROCEDURE sp_getmsgtotal()
BEGIN
    declare DBNAME varchar(64) ;
    declare TBNAME varchar(64);
    declare total       integer default 0 ;
    declare dbindex   integer default 0 ;
    declare tbindex  integer default 0 ;

    loop1:WHILE dbindex< 8 DO
        set DBNAME=concat("db_im_msg_record_" , dbindex);        
        set tbindex = 0;
            
            loop2:WHILE tbindex<32 DO
                set TBNAME=concat("t_im_msg_",tbindex);    

                set @SQL= concat("SELECT COUNT(*) INTO @num FROM ",DBNAME,".",TBNAME,";");
                select @SQL;
                prepare stmt_1 from @SQL ;
                execute stmt_1 ;
                deallocate prepare stmt_1 ;
                set total = total +@num;
                set tbindex = tbindex+ 1;
            END WHILE loop2;    
        
        set dbindex=dbindex+1;
           

    END WHILE loop1;
    
    select total ;
END
//

但是存儲過程，面臨著 這樣的問題：
Thread stack overrun:  10368 bytes used of a 131072 byte stack, and 128000 bytes needed. 
需要重啟mysqld。
麻煩。

換一種SHELL腳本實(shí)現(xiàn)


#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql
#SQLOPT=" -u$USER -p$PASS -S/tmp/mysql-new.sock $@"
SQLOPT="-h192.168.1.10 -uusername -ppassword"
RESULTFILE=./result.txt


function mysql_opt() 
{
        DBNAME=$1
        SQLSTR=$2
        $MYSQL $SQLOPT << EOF
        use $DBNAME;
        set names utf8;
        $SQLSTR;
        
EOF
}

function get_count()
{
        for((nDBIndex=0;nDBIndex<8;nDBIndex++))
        do
                 DBNAME=db_im_msg_record_$nDBIndex
                  #DBNAME=db_im_user_msg_$nDBIndex

                                for((nTBIndex=0;nTBIndex<1;nTBIndex++))
                                do
                                        TBNAME=t_im_msg_$nTBIndex
                                        #TBNAME=t_im_user_msgidlist_$nTBIndex

                                        RESULT=`mysql_opt $DBNAME "select count(*) from $TBNAME;"`
                                        echo "$RESULT  "
                                        echo $RESULT >> $RESULTFILE
                                done

        done
}

function compute_total()
{
        echo "begin"
        awk 'BEGIN{sum=0}{print $2;sum+=$2;}END{print sum} ' $RESULTFILE
        echo "end.."
}

function main()
{
        [ -f $RESULTFILE ] && rm -rf $RESULTFILE
        get_count
        compute_total
}

main