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

用户1637609

发布于 2022-05-11 08:42:26

1.1K0

发布于 2022-05-11 08:42:26

文章被收录于专栏：马洪彪马洪彪

业务需求

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

解决方案

使用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 ;