Mysql数据抽取（extract）、转换（transform）、加载（load）实战

置顶

蒙奇·D·路飞-

已于 2023-05-03 09:45:42 修改

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库 database

于 2022-05-11 10:51:10 首次发布

本文链接：https://blog.csdn.net/gaowenhui2008/article/details/124703919

本文介绍了在面对大数据量时如何进行MySQL的数据抽取、转换和加载。通过分步汇总避免了SQL超时问题，创建临时表进行初步聚合，然后逐个关联更新其他表。此外，还展示了利用存储过程和数据库定时任务实现自动倒库，并给出了相关用户权限设置的操作步骤。

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

表格格式化

 pager less -SFX;

1、场景一：当基础数据表的数据量比较大（300万以上），需要关联多个表（数据量在100万左右）时，多表left join容易超时，需要分步汇总。

insert into lkg.lkg_source_population (rowguid,
Name,
Sex,
BirthDay,
Nation,
BirthPlace,
Marriage,
PartyOrgName,
Education,
Religion,
ContactsPhone,
NowDistrict,
NowAddress,
GridName,
HOUSEHOLD_NO,
Relation
)
select a.IDCard,
a.Name,
case when a.Sex='DBF2523E-9D19-465D-980A-280F2AA501C6' then '02'
when a.Sex='1F8A8A4C-2013-4988-9AB8-29CD3FB4F11A' then '01' end as Sex ,
a.BirthDate,
case when a.Nation='3434A21E-E783-413E-BAD6-3777DB12D8FA'  then '01' end as Nation ,
a.NativePlace as BirthPlace,
a.MaritalStatus as Marriage,
a.PoliticsStatus as PartyOrgName,
a.Education as Education,
a.ReligiousFaith as Religion,
a.Phone as ContactsPhone,
b.DomicileCode as NowDistrict,
b.DomicileDetail as NowAddress,
c.Name as GridName,
e.HuHao as HOUSEHOLD_NO,
d.Yhzgx as Relation
from test.WG_People_Resident a
 left join test.WG_People_Household b on a.Id = b.ResidentID
left join test.organize c on c.id =b.AreaID
left join test.wg_hjyrkglb d on d.ResidentID = a.id
left join test.WG_RegisterFamily e on e.ID = d.HhID

数据量大时上边的sql容易造成超时中断，分步汇总步骤如下：

创建临时表，先汇总两个表的数据：

CREATE TABLE temp_people_resident
select t1.IDCard,
t1.HouseId as Bcellid,
t1.Name,
t1.Sex,
t1.BirthDate,
t1.Nation,
t1.NativePlace,
t1.MaritalStatus,
t1.PoliticsStatus,
t1.Education,
t1.ReligiousFaith,
t1.Phone,
t2.CreateDate,
t2.DomicileCode,
t2.DomicileDetail,
t2.ResidentID,
t2.AreaID,
null as HOUSE_ID,
null as BUILDING_ID,
null as HhID,
null as GRID_CODE,
null as GRID_NAME,
null as HuHao,
null as Yhzgx 
from test.wg_people_resident t1 
left JOIN 
(select ResidentID,DomicileCode,DomicileDetail,Ar