当前位置:首页 > 数据库 > 数据库

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

优良自学吧提供数据集成透过JDBC将数据导入MySQL的几种模式,数据集成通过JDBC将数据导入MySQL的几种模式 摘要: 目前MySQL JDBC提供了多种将数据写入MySQL的方式,本文将介绍数据集成(Data

数据集成通过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/

(本文来自互联网,不代表搜站(http://www.ylzx8.cn/)的观点和立场)
本站所有内容来自互联网,若本站收录的信息无意侵犯了贵司版权,请给我们来信(ylzx8cn@163.com),我们会及时处理和回复,谢谢