数据集成通过JDBC将数据导入MySQL的几种模式

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 目前MySQL JDBC提供了多种将数据写入MySQL的方式,本文将介绍数据集成(DataX、同步中心、原CDP)支持的几种模式: * insert into xxx values (..), (..), (..) * replace into xxx values (..), (..), (..) * insert into xxx values (..), (..),

目前MySQL JDBC提供了多种将数据写入MySQL的方式,本文将介绍数据集成(DataX、同步中心、原CDP)支持的几种模式:

  • insert into xxx values (..), (..), (..)
  • replace into xxx values (..), (..), (..)
  • insert into xxx values (..), (..), (..), … on duplicate key update …

1、功能区别

1.1 insert into 方式

常规的SQL插入,如果提交的MySQL Server端的数据违反了数据库约束(主键冲突、数据类型不匹配)会直接报错;
对应在数据集成中会报脏数据。 常用于向一张空表里面插入数据

1.2 replace into 方式

与insert into类似,区别:假如将要插入表新记录中主键(PRIMARYKEY或UNIQUE索引)与表中旧记录冲突,replace into自身具有处理冲突的能力:

  • 1、当存在pk冲突的时候是先delete再insert
  • 2、当存在uk冲突的时候是直接update

使用replace into 注意事项

  • 1、能够使用replace,您必须同时拥有表的insert和delete权限;
  • 2、冲突记录:新记录与旧记录的主键值不同,所以其他表中所有与本表老数据主键id建立的关联全部会被破坏;
  • 3、冲突记录:所有列的值均取自在热replace语句中被指定的值。所有缺失的列被设置为各自的默认值,即如果您每次同步的不是表的所有列,会存在一些列在旧记录中有值,replace into后无值的情况;
  • 4、replace语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。

1.3 insert into… on duplicate key update 方式

将要插入表新记录中主键(PRIMARYKEY或UNIQUE索引)与表中旧记录冲突(具有相同的值),则update旧记录。

3、Replace into 存在的坑

  • 如果库存在主备,基于uk去做replace into时,会造成主备的auto_increment不一致(备库因auto_increment小于实际数据的最大值),在主备切换插入时造成replace into出错,失败一次后,会更新auto_increment为最大值+1;

3.1 实例

master:
use test;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB ;

insert into test(k,v,extra) values(1,1,'extra1'),(2,2,'extra2',3,3,'extra3');

插入完成后,主库和备库数据和schema完全一致;执行replace into:

replace into test(k,v) values(1,'1-1');

主备库数据一致,但是schema不一致。

主库表结构如下:
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
备库:
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

原因分析:

binlog中记录的SQL:
### UPDATE test.test
### WHERE
###   @1=1
###   @2=1
###   @3='1'
###   @4='extra1'
### SET
###   @1=4
###   @2=1
###   @3='1-1'
###   @4=NULL

如第一章节所述:
replace into 当存在uk冲突的时候是直接update,update操作不会涉及到auto_increment的修改。

基于此,一些replace操作会被建议使用insert into on duplicate key update。

2、数据集成最佳实践

目前数据集成对于上述三种模式均已经支持,对应DataX MySQLWriter插件配置项中writeMode字段;

{
  "job": {
    "setting": {
      "speed": {
        "channel": 1
      }
    },
    "content": [
      {
        "reader": {
          "name": "streamreader",
          "parameter": {
            "column": [
              {
                "value": "DataX",
                "type": "string"
              }
            ],
            "sliceRecordCount": 1000
          }
        },
        "writer": {
          "name": "mysqlwriter",
          "parameter": {
            "writeMode": "insert/replace/update",
            "username": "root",
            "password": "root",
            "column": [
              "id",
              "name"
            ],
            "connection": [
              {
                "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/datax?useUnicode=true&characterEncoding=gbk",
                "table": [
                  "test"
                ]
              }
            ]
          }
        }
      }
    ]
  }
}

4.1 数据集成如何保证同步到MySQL作业的幂等性

简单解释 幂等性 :多次运行同一个同步作业得到的结果是一致的;

  • 场景一:表中数据可以删除

在数据集成配置同步任务时,配置前置SQL(delete or truncate表的语句),同步任务在每次执行的时候,在真正同步执行前会执行前置SQL,去清空表,这样即可以实现多次运行同步任务的幂等性。

  • 场景二:表中数据不能删除,常见回流线上业务MySQL库
    配置writeMode为 replace 或者 update,同步的时候即会采用replace into 或者 insert into… on duplicate key update 方式插入MySQL数据库。

参考:

https://askdba.alibaba-inc.com/libary/control/getArticle.do?articleId=12735
https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

目录
相关文章
|
16天前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
65 0
|
28天前
|
Java 关系型数据库 数据库连接
实时计算 Flink版操作报错之在使用JDBC连接MySQL数据库时遇到报错,识别不到jdbc了,怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
2天前
|
Java 关系型数据库 MySQL
JavaWeb基础第一章(MySQL数据库与JDBC)
JavaWeb基础第一章(MySQL数据库与JDBC)
|
4天前
|
JSON 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在使用CDAS语法同步MySQL数据到Hologres时,如果开启了字段类型宽容模式,MySQL中的JSON类型会被转换为什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之数据源同步时,使用脚本模式采集mysql数据到odps中,使用querySql方式采集数据,在脚本中删除了Reader中的column,但是datax还是报错OriginalConfPretreatmentUtil - 您的配置有误。如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
10天前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之在数据集成到MySQL时,遇到特殊字符导致的脏数据如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
11天前
|
存储 DataWorks 关系型数据库
DataWorks产品使用合集之在使用数据集成中的同步任务从mysql同步表到oss,存储为csv时,最终生成的文件中没有表头,这个属性可以在哪里配置么
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
12天前
|
分布式计算 DataWorks 关系型数据库
MaxCompute产品使用合集之DataWorks是否支持通过SQL方式在MaxCompute中查询数据,并通过数据集成服务将查询结果同步至MySQL数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
27天前
|
SQL Java 关系型数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
16 0
|
1月前
|
传感器 人工智能 前端开发
JAVA语言VUE2+Spring boot+MySQL开发的智慧校园系统源码(电子班牌可人脸识别)Saas 模式
智慧校园电子班牌,坐落于班级的门口,适合于各类型学校的场景应用,班级学校日常内容更新可由班级自行管理,也可由学校统一管理。让我们一起看看,电子班牌有哪些功能呢?
128 4
JAVA语言VUE2+Spring boot+MySQL开发的智慧校园系统源码(电子班牌可人脸识别)Saas 模式