阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

简单介绍mysql中数据库覆盖导入的几种方式

63次阅读
没有评论

共计 2127 个字符,预计需要花费 6 分钟才能阅读完成。

导读 这篇文章主要介绍了 mysql 中数据库覆盖导入的几种方式总结,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教

众所周知,数据库中 INSERT INTO 语法是 append 方式的插入,而最近在处理一些客户数据导入场景时,经常遇到需要覆盖式导入的情况

常见的覆盖式导入主要有下面两种:

1、部分覆盖:新老数据根据关键列值匹配,能匹配上则使用新数据覆盖,匹配不上则直接插入。

2、完全覆盖:直接删除所有老数据,插入新数据。

简单介绍 mysql 中数据库覆盖导入的几种方式

本文主要介绍如何在数据库中完成覆盖式数据导入的方法。

部分覆盖
业务场景

某业务每天给业务表中导入大数据进行分析,业务表中某列存在主键,当插入数据和已有数据存在主键冲突时,希望能够对该行数据使用新数据覆盖或者说更新,而当新老数据 userid 不冲突的情况下,直接将新数据插入到数据库中。以将表 src 中的数据覆盖式导入业务表 des 中为例:

应用方案

方案一:使用 DELETE+INSERT 组合实现(UPDATE 也可以,请读者思考)

-- 开启事务
START TRANSACTION;
-- 去除主键冲突数据
DELETE FROM des
USING src
WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
-- 导入新数据
INSERT INTO des
SELECT *
FROM src
WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
-- 事务提交
COMMIT;

方案优点:使用最常见的使用 DELETE 和 INSERT 即可实现。

方案缺点:1、分了 DELETE 和 INSERT 两个步骤,易用性欠缺;2、借助子查询识重,DELETE/INSERT 性能受查询性能制约。

方案二:使用 MERGE INTO 功能实现

MERGE INTO des USING src ON (des.userid = src.userid)
WHEN MATCHED THEN UPDATE SET des.b = src.b
WHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);

方案优点:MERGE INTO 单 SQL 搞定,使用便捷,内部去重效率高。

方案缺点:需要数据库产品支持 MERGE INTO 功能,当前 Oracle、GaussDB(DWS) 等数据库已支持此功能,mysql 的 insert into on duplicate key 也类似此功能。

完全覆盖
业务场景

某业务每天给业务表中导入一定时间区间的数据进行分析,分析只需要导入时间区间的去除,不需要以往历史数据,这种情况就需要使用到覆盖式导入。

应用方案

方案一:使用 TRUNCATE+INSERT 组合实现

-- 开启事务
START TRANSACTION;
-- 清除业务表数据
TRUNCATE des;
-- 插入 1 月份数据
INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time 

方案优点:简单暴力,先清理在插入直接实现类似覆盖写功能。

方案缺点:TRUNCATE 清理业务表 des 数据时对表加 8 级锁直到事务结束,在因数据量巨大而 INSERT 时间很长的情况下,des 表在很长时间内是不可访问的状态,业务表 des 相关的业务处于中断状态。

方案二:使用创建临时表过渡的方式实现

-- 开启事务
START TRANSACTION;
-- 创建临时表
CREATE TABLE temp(LIKE desc INCLUDING ALL);
-- 数据先导入到临时表中
INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME des
ALTER TABLE temp RENAME TO des;
-- 提交事务
COMMIT;

方案优点:相比方案一,在 INSERT 期间,业务表 des 可以继续被访问(老数据),即事务提交前分析业务可继续访问老数据,事务提交后分析业务可以访问新导入的数据。

方案缺点:1、组合步骤较多,不易用;2、DROP TABLE 操作会删除表的依赖对象,例如视图等,后面依赖对象的还原可能会比较复杂。

方案三:使用 INSERT OVERWRITE 功能

INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time 

方案优点:单条 SQL 搞定,执行便捷,能够支持一键式切换业务查询的新老数据,业务不中断。

方案缺点:需要产品支持 INSERT OVERWRITE 功能,当前 impala、GaussDB(DWS) 等数据库均已支持此功能。

总结

随着大数据的场景越来越多,数据导入的场景也越来越丰富,除了本文介绍的覆盖式数据导入,还有其他诸如忽略冲突的 INSERT IGNORE 导入等等其他的导入方式,这些导入场景可以以使用基础的 INSERT、UPDATE、DELETE、TRUNCATE 来组合实现,但是也同样会对高级的一键 SQL 功能有直接诉求,后面有机会再叙述。

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年,有高配

腾讯云新客低至 82 元 / 年,老客户 99 元 / 年

代金券:在阿里云专用满减优惠券

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2024-07-25发表,共计2127字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中