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脚本实现


#!/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()
{
        [ 
-$RESULTFILE ] && rm -rf $RESULTFILE
        get_count
        compute_total
}

main