mysql杂记-使用FEDERATED汇总数据

业务需求

核酸采样登记数据分布在多个数据库中（减轻单数据库并发写入压力，按用户区域、终端等进行了划分），需要将分散的数据汇总起来后续做统一业务处理。

解决方案

使用mysql的FEDERATED，类似Oracle的dblink等，在汇总库中建立对各源库表的映射表，然后在汇总库中操作这些映射表，数据汇总。

实现步骤

业务表定义（在源和目标中定义一致的数据库）

CREATE TABLE `sample_record` (
  `ID` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键',
  `CREATE_BY` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间（采样时间）',
  `UPDATE_BY` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  `SYS_ORG_CODE` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所属部门',
  `SAMPLE_TUBE` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '样品管编号',
  `NAME` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `CARD_TYPE` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '证件类型',
  `CARD_NO` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '证件号码',
  `BIRTHDAY` datetime DEFAULT NULL COMMENT '出生年月',
  `SEX` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
  `ADDRESS` text COLLATE utf8mb4_unicode_ci COMMENT '联系地址',
  `TELEPHONE` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '联系电话',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IX_ASR_CREATE_TIME2` (`CREATE_TIME`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='样品采集记录';

源表在目标库中的映射表定义

在汇总数据库中建立各源数据库表的映射，映射为xxx_a\xxx_b\xxx_c等。
关键为以下存储引擎的定义，connection指定了源数据库及源表。
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://root:MYSQL57@10.0.0.6:3306/testdb/sample_record';

CREATE TABLE `sample_record_a` (
  `ID` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键',
  `CREATE_BY` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间（采样时间）',
  `UPDATE_BY` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  `SYS_ORG_CODE` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所属部门',
  `SAMPLE_TUBE` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '样品管编号',
  `NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `CARD_TYPE` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '证件类型',
  `CARD_NO` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '证件号码',
  `BIRTHDAY` datetime DEFAULT NULL COMMENT '出生年月',
  `SEX` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
  `ADDRESS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '联系地址',
  `TELEPHONE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '联系电话'
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://root:MYSQL57@10.0.0.6:3306/testdb/sample_record';

使用存储过程实现数据的汇总

在汇总库中定义存储过程，按时间段抽取各源表的数据写入到目标表中，并删除源表中数据。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `extractApp1`()
begin
	# 获取时间戳
	set @timePoint = now() - interval 10 minute;
	# 查询该时间点之前上传的数据汇总到web库中
	insert into sample_record(ID, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME, SYS_ORG_CODE, SAMPLE_TUBE, NAME, CARD_TYPE, CARD_NO, BIRTHDAY, SEX, ADDRESS, TELEPHONE) 
	select replace(uuid(),"-","") ID, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME, SYS_ORG_CODE, SAMPLE_TUBE, NAME, CARD_TYPE, CARD_NO, BIRTHDAY, SEX, ADDRESS, TELEPHONE
	from sample_record_a asr
	where asr.create_time <= @timePoint;
	# 删除已汇总来的数据
	delete from sample_record_a  where CREATE_TIME <= @timePoint;
  end$$
DELIMITER ;

使用event定时执行汇总，或使用应用层quartz定时调用存储过程

使用定时器定时调用存储过程来汇总数据。

其他

可以使用kettle、datax甚至自定义业务程序实现从源头数据库中查询数据并写入目标数据库中。
同构数据库或mysql等可使用binlog的方式来同步数据库。