linux 中mysql的定时任务crontab -e

最新推荐文章于 2023-05-11 23:15:28 发布

原创最新推荐文章于 2023-05-11 23:15:28 发布 · 635 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#crontab -e

mysql 专栏收录该内容

1 篇文章

订阅专栏

本脚本用于每日12点自动执行MySQL数据库维护任务。主要功能包括删除前一天的数据记录，并插入新的统计数据。涉及的数据表有b_building_property_demographic_filter等，通过复杂的SQL查询实现数据聚合和更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

写的脚本记一下

1 12 * * * /bin/sh /home/shaolei/mysql_sql.sh 每天12 点执行

#!/bin/bash
# to call SQL statement at MySQL prompt
mysql -h ip -P 3306 -ubunny -p83OGCufr9 <<EOF
use bunny;
#delete yesterday data
delete from b_building_property_demographic_filter;
#insert into new data
insert into b_building_property_demographic_filter
select * from
(select did as id, bid as buildingPropertyId,counts.demographicTypeId as demographicTypeId,counts.demographicId as demographicId,
labercount as deviceCount,source,bcityid as cityid,buildcount as buildingDeviceCount,
round((labercount*100/buildcount)) as accouning,
round((labercount/buildcount)*100/(laber/citycount)) as tgi,updateTime
from
(select demo.id did,build.id as bid,buildingPropertyId, build.cityid as bcityid,IF(xinchaoid is null ,0,1) as source,
demographicTypeId,demographicId,build.deviceCount buildcount,
demo.DeviceCount labercount,citycount,updateTime
from
b_building_property build ,
b_building_property_demographic demo ,
(select cityid,sum(devicecount) citycount
from b_building_property
GROUP BY cityid
) city
where build.id=demo.buildingPropertyId
and city.cityid=build.cityid
) counts,

(select cityid,sum(bb.devicecount) as laber,demographicId
from b_building_property aa ,b_building_property_demographic bb
where aa.id=bb.buildingPropertyId
group by cityid,demographicId
) labercouns
where labercouns.cityid=counts.bcityid
and counts.demographicId=labercouns.demographicId
order by id ) hhhh;
EOF
exit;