MySQL 实现将一个库表里面的数据实时更新到另一个库表里面
需求描述:MySQL 里面有很多的数据库，这些数据库里面都有同一种表结构的表 (tb_warn_log)，这张表的数据是实时更新的，现在需要将这些表的数据全部实时更新到另一个库的另一张表 (tb_alarm_management) 里面。
数据库结构如下:
tb_warn_log 表结构如下:
tb_alarm_management 表结构如下:
MySQL 存储过程定时任务:
-- 存储过程创建
use mdm_common_db;
delimiter //
create procedure timing_import_proce()
begin
DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值 0
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置 result_code 为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为 2
START TRANSACTION; -- 开始事务
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,10,' 上海 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 上海铁路局 ',' 杭州工务段 ',' 杭州钱塘江大桥 ','mdm_1_db' from mdm_1_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_1_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_1_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,6,' 郑州 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 郑州铁路局 ',' 洛阳工务段 ',' 洛阳邙山隧道 ','mdm_2_db' from mdm_2_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_2_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_2_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,21,' 麻城 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 武汉铁路局 ',' 麻城工务段 ',' 麻武线 K71' ,'mdm_3_db' from mdm_3_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_3_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_3_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,11,' 武汉 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 武汉铁路局 ',' 武汉桥工段 ',' 孝感大桥 京广线上行 1136+880','mdm_4_db' from mdm_4_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_4_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_4_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,14,' 广州 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 广州铁路局 ',' 怀化工务段 ',' 沪昆线 1592+130-160 上行 ','mdm_14_db' from mdm_14_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_14_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_14_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,11,' 武汉 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 武汉铁路局 ',' 荆门桥工段 ',' 宜万线 1296+120-300','mdm_15_db' from mdm_15_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_15_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_15_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,4,' 北京 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 北京铁路局 ',' 北京工务段 ',' 北京西站监测站 ','mdm_17_db' from mdm_17_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_17_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_17_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,51,' 青藏 (西宁)',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 青藏铁路局 ',' 西宁工务段 ',' 米拉湾隧道 ','mdm_18_db' from mdm_18_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_18_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_18_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,51,' 青藏 (西宁)',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 青藏铁路局 ',' 西宁工务段 ',' 付家寨三号隧道 ','mdm_19_db' from mdm_19_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_19_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_19_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,12,' 长沙 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 广州铁路局 ',' 长沙高铁工务段 ',' 艾家冲隧道 ','mdm_21_db' from mdm_21_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_21_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_21_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,10,' 上海 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 上海铁路局 ',' 杭州工务段 ',' 杭州铁路宏远 ', 'mdm_55_db' from mdm_55_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_55_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_55_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,21,' 麻城 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 武汉铁路局 ',' 麻城工务段 ',' 武汉局麻城工务段试点项目 ','mdm_64_db' from mdm_64_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_64_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_64_db');
INSERT INTO mdm_common_db.tb_alarm_management(key_name,warn_type,warn_level,alarm_time,alarm_cityId,alarm_cityName,alarm_user,state,ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,TLJ,GWD,JCZ,GWDDatabase) SELECT SensorType,WarnMainType,WarnLevel,Time,11,' 武汉 ',DealName,' 未处理 ',ID,SensorID,WarnChildType,SendMessage,SendFlag,Dealed,DealPhone,Note,' 武汉铁路局 ',' 武汉桥工段 ',' 武汉桥工段铁路桥试点项目 ','mdm_65_db' from mdm_65_db.tb_warn_log WHERE not exists (select * from mdm_common_db.tb_alarm_management where mdm_common_db.tb_alarm_management.ID = mdm_65_db.tb_warn_log.ID AND mdm_common_db.tb_alarm_management.GWDDatabase = 'mdm_65_db');
IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的 result_code，这里只定义了 1 和 0
ROLLBACK;
ELSE
COMMIT;
END IF;
select result_code;
end//
delimiter ;
-- 定时任务创建
create event timing_import_event
on schedule every 30 minute -- 每隔 30 分钟执行一次存储过程
on completion preserve disable
do call timing_import_proce();
-- 定时任务开启
alter event timing_import_event on completion preserve enable; -- 开启定时任务
-- 求和
select
(select count(*) from mdm_1_db.tb_warn_log)
+(select count(*) from mdm_2_db.tb_warn_log)
+(select count(*) from mdm_3_db.tb_warn_log)
+(select count(*) from mdm_4_db.tb_warn_log)
+(select count(*) from mdm_14_db.tb_warn_log)
+(select count(*) from mdm_15_db.tb_warn_log)
+(select count(*) from mdm_17_db.tb_warn_log)
+(select count(*) from mdm_18_db.tb_warn_log)
+(select count(*) from mdm_19_db.tb_warn_log)
+(select count(*) from mdm_21_db.tb_warn_log)
+(select count(*) from mdm_55_db.tb_warn_log)
+(select count(*) from mdm_64_db.tb_warn_log)
+(select count(*) from mdm_65_db.tb_warn_log) 求和;
select count(*) from mdm_common_db.tb_alarm_management;