开发者社区> 祁然> 正文

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

简介: 目前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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
jdbc 工具类 JdbcUtils  简化数据…
jdbc 工具类 JdbcUtils  简化数据…
42 0
【J2EE十三个规范】数据层之JDBC
<span style="font-family:楷体; font-size:19px; line-height:27px; widows:auto"><strong>【学习概要】</strong></span> <div style="font-family:楷体; font-size:19px; line-height:27px; widows:auto"><span style="
1881 0
mysql导入导出数据
从文档中导入到数据库 load data local infile '/tmp/test.txt'  into table db.new  fields terminated by ':'                         //列的分割符  lines terminated by '\n';                      //行的分割符 load d
1188 0
通过SQL Server 2008数据库复制实现数据库同步备份
原文 通过SQL Server 2008数据库复制实现数据库同步备份 SQL Server 2008数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步,我们把它用于数据库的同步备份。这里的同步备份指的是备份服务器与主服务器进行 实时数据同步,正常情况下只使用主数据库服务器,备份服务器只在主服务器出现故障时投入使用。
1564 0
将.csv数据导入到mysql中
1.首先看一下我需要导入的数据: 用excel打开的时候显示: 用notepad++打开显示为: 2.使用notepad++打开改变字符集为UTF-8 3,建立表,表中的字段要和文件中的一致       3.
1043 0
Java数据库编程--JDBC
一、定义 JDBC,Java DataBase Connectivity。 是Java运行平台核心类库中的一部分,提供了访问数据库的API,由一些Java类和接口组成。 在Java中可以使用JDBC实现对数据库中表记录的查询,修改,和删除等操作 JDBC技术在数据库开发中占有很重要的地位。
954 0
【RDS MySQL】将Excel的数据导入数据库
您可以将Excel的数据通过数据管理服务DMS(Data Management Service)导入到RDS MySQL数据库中。
198 0
+关注
祁然
数据集成
3
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载