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<DATE_ADD(now(),interval -30 day)"|mysql -h${mysql_HOST} -u${mysql_USERNAME} -p${mysql_PASSWORD} -P${mysql_PORT} -s;
    echo delete data success ;
    break;
  fi
 
  sleep 600;
  echo loop;
done

 

 

 

输出log：

declare @html varchar(max)='<table border=1 align=''center'' width=500px>
<caption>Nearly 1 Week Inc rows</caption>
<tr  align=''center'' ><td>JobID</td><td>ETLDate</td><td>Rows</td></tr>'

select @html+=tr
from (
select top 7 '<tr><td>'+batchid+'</td><td>'+cast(cast(dt as date) as varchar(10))+'</td><td>'+cast(sum(rows) as varchar(20))+'</td></tr>' tr
from table_log group by batchid,cast(dt as date) order by batchid desc
)a

declare @htmletl varchar(max)='<table border=1 align=''center'' width=800px>
<caption>ETL LOG</caption>
<tr  align=''center'' ><td>Step Name</td><td>Message</td><td>Start Time</td><td>End Time</td></tr>'

select @htmletl+=tr
from (
select  '<tr><td>'+tb+'</td><td>'+msg+'</td><td>'+convert(varchar(20),sdt,120)+'</td><td>'+isnull(convert(varchar(20),edt,120),'null')+'</td></tr>' 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+'</table>' html ,@htmletl+'</table>' htmletl,@date lastdate, @datetag datetag ,@datelevel datelevel