数据写入mysql的方式_数据集成通过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数据库。