#shell实现,将mysql中存储过程代码直接导出为文件
dbcn="mysql -h172.16.1.194 -uroot -p123456 ";
db=BCReport_Sync_Executor;
ii=0;
ct=`$dbcn -N -e " select count(1) from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%';"`;
mkdir -p /chenenhui/$db
while true;
do
if [ $ii -lt $ct ]
then
p=$ii;
let ii++;
echo p=$p
echo ii=$ii
spname=`$dbcn -N -e " select p.name from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%' limit $p,1;"`;
echo spname=$spname
sleep 0;
ss=`$dbcn -N -e "
SELECT
CONCAT(
'
DELIMI','TER ',REPEAT(CHAR(36),2),'
USE ',CHAR(96), p.db,CHAR(96),REPEAT(CHAR(36),2),'
DROP PROCEDURE IF EXISTS ',CHAR(96),p.name,CHAR(96), REPEAT(CHAR(36),2),'
CREATE DEFINER=',CHAR(96),
LEFT(DEFINER, -1+LOCATE('@',DEFINER)),
CHAR(96),'@',CHAR(96),
RIGHT(DEFINER,LENGTH(DEFINER)-LOCATE('@',DEFINER)),
CHAR(96)
,' PROCEDURE ',CHAR(96),p.name ,CHAR(96),'(',p.param_list,')
', p.body ,REPEAT(CHAR(36),2) ,'
DELIMI','TER ;
' ) AS sql_create
FROM mysql.proc AS p
WHERE 1=1
and p.db='$db'
and p.type LIKE 'P%'
AND p.name ='$spname'
;"
`
echo -e "$ss" > /chenenhui/$db/$spname.sql
echo $spname
else
echo '_while finished';
exit 0;
fi
done