303
數據集成通過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/
最後更新：2017-05-08 14:01:30