xshell运行mysql_xshell每个月1号执行mysql语句

while [ 1 = 1 ]

do

if [ $(date '+%d') != 01 ];

then

echo Today is not No.1 no data check

break;

fi

if [ $(date '+%H') = 23 ];

then

echo No Data Until 23:00

break;

fi

echo check start;

isok=1;

for i in `echo  "select count(1) rows from ebizcn_ConsultantRelationship where monthkey=date_format(date_sub(now(), interval -8 hour),'%Y%m')

union all

select count(1) from ebizcn_ConsultantActivityStatus where monthkey=date_format(date_sub(now(), interval -8 hour),'%Y%m')

union all

select count(1) from ebizcn_ConsultantCareerLevel where monthkey=date_format(date_sub(now(), interval -8 hour),'%Y%m')"|mysql -h${mysql_HOST} -u${mysql_USERNAME} -p${mysql_PASSWORD} -P${mysql_PORT} -s`;

do

if [ $i = "0" ];

then

isok=0;

fi

done;

if [ $isok = 1 ];

then

echo check end;

echo "delete from contacts.a_member_communication where expire_date

echo delete data success ;

break;

fi

sleep 600;

echo loop;

done

输出log：

declare @html varchar(max)='

Nearly 1 Week Inc rows

JobID

ETLDate

Rows

select @html+=tr

from (

select top 7 '

'+batchid+''+cast(cast(dt as date) as varchar(10))+''+cast(sum(rows) as varchar(20))+'' tr

from table_log group by batchid,cast(dt as date) order by batchid desc

)a

declare @htmletl varchar(max)='

ETL LOG

Step Name

Message

Start Time

End Time

select @htmletl+=tr

from (

select  '

'+tb+''+msg+''+convert(varchar(20),sdt,120)+''+isnull(convert(varchar(20),edt,120),'null')+'' tr

from etl_log

where sdt>cast(getdate() as date) and (tb not like 'p$%' or msg<>'success')

)a

declare @date varchar(20)

select @date=convert(varchar(20),isnull(dateadd(hour,-8,max(sdt)),'1900-01-01 00:00:00'),120) from etl_log where tb='a_member_list' and msg='success'

declare @datetag varchar(20)

select @datetag=convert(varchar(20),isnull(max(sdt),'1900-01-01 00:00:00'),120) from etl_log where tb='a_member_tag' and msg='success'

declare @datelevel varchar(20)

select @datelevel=convert(varchar(20),isnull(max(sdt),'1900-01-01 00:00:00'),120) from etl_log where tb='a_member_level' and msg='success'

select @html+'

' html ,@htmletl+'' htmletl,@date lastdate, @datetag datetag ,@datelevel datelevel