代码背景:假设在数据库database1中存在100张前缀相同的分表,即table_{0-99},此时需要遍历以进行某些操作。
shell文件内容:
#!/bin/bash
#define log
TIMESTAMP=`date +%y%m%d%H%M%S` #注意格式
log=sql_${TIMESTAMP}.log
echo "Start execute sql statement at `date`.">>${log}
#execute sql stat
#-p后接密码,貌似不能有空格;delimiter是规定以//作为结束
mysql -u root -h 0.0.0.0 -ppassword -e "
use database1;
delimiter //
create procedure select_sql()
begin
declare num int;
set num = 0;
while num < 100 do
set @sqlString='select count(*) from table_';
set @sqlString=concat(@sqlString,num);
set @sqlString=concat(@sqlString,' where condition');
prepare stmt from @sqlString;
execute stmt;
set num=num+1;
end while;
end//
DELIMITER ;
call select_sql();
DROP PROCEDURE select_sql;
">>${log}
echo -e '\n'>>${log}
echo 'below is output result.'>>${log}
echo 'script executed successful.'>>${log}
exit;