欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

shell实现,将mysql每个存储过程导出为单个文件_MySQL

程序员文章站 2022-05-11 11:49:42
...
#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_utf8 ,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