mysql定时任务

最新推荐文章于 2024-08-20 08:24:53 发布

yinke2022

最新推荐文章于 2024-08-20 08:24:53 发布

阅读量157

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/yinke2022/article/details/105302533

版权

本文详细介绍了一个存储过程的创建，用于定期清理和更新组织地址信息。通过使用SQL语句，该过程能够从多个表中选择并组合数据，形成详细的地址信息，并插入到指定表中。此外，还设置了一个每24小时执行一次的定时器，自动调用此存储过程，确保数据的实时性和准确性。

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

1.创建存储过程
CREATE PROCEDURE insertorgAddress()
BEGIN
delete from org_address;
INSERT INTO org_address(orgid, orgname, address )
SELECT
orgid,
orgname,
concat(
(
SELECT
GROUP_CONCAT(o.orgname SEPARATOR ‘’)
FROM
sys_org_info o,
sys_org_child child
WHERE
o. STATUS = ‘1’
AND child. STATUS = ‘1’
AND o.ORGID = child.PAYWAYID
AND child.SUBWAYID = ld.orgid
ORDER BY
o.orglevel
),
'’,
(
SELECT
o.orgname
FROM
sys_org_info o
WHERE
STATUS = ‘1’
AND o.orgid = ld.orgid
)
) AS detailaddress
FROM
sys_org_info ld
WHERE
ld. STATUS = ‘1’
AND ld.orgtype = ‘grid’
AND ld.orgid LIKE concat(‘37’, ‘%’);
END;
2.定时器
create event if not exists insertorgAddress
on schedule every 24 HOUR
on completion preserve
do call insertorgAddress();