資料整合通過JDBC將資料匯入MySQL的幾種模式

目前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/