Data integration Several modes for importing data into MySQL via JDBC

Summary: currently MySQL JDBC provides a variety of ways to write data to MySQL, this article introduces several modes supported by data integration (datax, Sync Center, original CDP): * INSERT into XXX values (..), (..), (..) * Replace into XXX values (..), (..), (..) * insert into XXX values (..), (..),

Currently MySQL JDBC provides a variety of ways to write data to MySQL, this article describes several modes supported by data integration (datax, Sync Center, original CDP):

• Insert into XXX values (..), (..), (..)
• Replace into XXX values (..), (..), (..)
• Insert into XXX values (..), (..), (..), ... on duplicate key update ...

1. function difference 1.1 INSERT INTO mode

Regular SQL INSERT, if the data submitted by the MySQL server to violate the database constraints (primary key conflict, data type mismatch) will be directly error;
The corresponding dirty data is reported in the data integration. Often used to insert data into an empty table;

1.2 Replace into mode

Similar to insert into, the replace into itself has the ability to handle conflicts if the primary key (PrimaryKey or unique index) in the new record that will be inserted into the table conflicts with the old record in the table:

• 1, when there is a PK conflict is the first delete and then insert
• 2. Direct update when there is a UK conflict

* * Use REPLACE into considerations * *

• 1. To be able to use replace, you must have both insert and delete permissions for the table;
• 2, the conflict record: The new record and the old record's primary key value is different, so all other tables in the table old data primary key ID established by the association will be destroyed;
• 3. Conflicting records: The values of all columns are taken from the values specified in the hot replace statement. All missing columns are set to their default values, that is, if you do not synchronize all of the columns of the table, there will be some columns in the old record with values, replace into after the case of no value;
• 4. The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted.

1.3 INSERT INTO ... on duplicate key update mode

Update the old record if the primary key (PrimaryKey or unique index) in the new record that will be inserted is in conflict with the old record in the table (with the same value).

3, Replace into the existence of the pit

• If the inventory is in the main, based on the UK to do replace into, will cause the main auto_increment inconsistency (the repository because the auto_increment is less than the actual data maximum), when the primary and standby switch insertion caused replace into error, once failed, Will update auto_increment to the maximum value of +1;

3.1 Example

MasterUseTestCREATETABLE' Test ' (' ID 'int10)UnsignedNotNULL Auto_increment,' K 'int10)UnsignedNotNull' V 'varchar100)DEFAULTNull' Extra 'varchar200) default NULL, PRIMARY key ( ' id '), unique key  ' Uk_k ' ( ' K ')) engine=INNODB; into test (K,v,extra)  VALUES (1,1, ' Extra1 '), (2,2, ' Extra2 ', 3,< Span class= "Hljs-number" >3, ' extra3 ');         

After the insert is complete, the master and repository data are in full accord with the schema; Execute replace into:

replace into test(k,v) values(1,‘1-1‘);

The Master repository data is consistent, but the schema is inconsistent.

The main library table structure is as follows:CREATETABLE' Test ' (' ID 'int10)UnsignedNotNULL Auto_increment,' K 'int10)UnsignedNotNull' V 'varchar100)DEFAULTNull' Extra 'varchar200)DEFAULTNULL, PRIMARYKEY (' ID '),UNIQUEKEY' Uk_k ' (' K '))Engine=InnoDB auto_increment=5DEFAULTCHARSET=GBK: to prepare a library;CREATETABLE' Test ' (' ID 'int10)UnsignedNotNULL Auto_increment,' K 'int10)unsigned not NULL,  ' V ' varchar (100)  DEFAULT null,  ' extra ' varchar ( Span class= "Hljs-number" >200) default NULL, PRIMARY key ( ' id '), unique key  ' Uk_k ' ( ' K ')) engine=innodb auto_increment=4 default CHARSET=GBK;         

Cause Analysis:

binlog中记录的SQL:### UPDATE test.test### WHERE###   @1=1###   @2=1###   @3=‘1‘### @4=‘extra1‘### SET### @1=4### @2=1### @3=‘1-1‘### @4=NULL

As described in the first section:
Replace into when there is a UK conflict the direct update,update operation will not involve auto_increment modification.

Based on this, some replace operations are recommended to use INSERT into on duplicate key update.

2. Data Integration Best Practices

At present, data integration for the above three modes have been supported, corresponding to the Datax Mysqlwriter plug-in configuration item writemode field;

{"Job": {"Setting": {"Speed": {"Channel":1}},"Content": [{"Reader": {"Name":"StreamReader","Parameter": {"Column": [{"Value":"Datax","Type":"String"}], "Slicerecordcount": 1000}},  "writer": {  "name":  "Mysqlwriter",  "parameter": { Span class= "hljs-attr" > "Writemode":  "Insert/replace/update", " root ", " password ": " root " ,  "column": [ "id",  "name"],  "connection": [{ "jdbc:mysql:// 127.0.0.1:3306/DATAX?USEUNICODE=TRUE&CHARACTERENCODING=GBK ", " table ": [" test "]}]}}}}}            

4.1 How data integration guarantees the power of synchronization to MySQL jobs

Simple explanation idempotent: multiple runs of the same synchronization job results are consistent;

• Scenario One: Data in a table can be deleted

When you configure a synchronization task for data integration, configure the pre-SQL (delete or TRUNCATE TABLE statement), the synchronization task executes the pre-SQL before the actual synchronization executes, and clears the table, which enables the power of the synchronization task to run multiple times.

• Scenario Two: The table data can not be deleted, common reflow line on the business MySQL library configuration writemode for replace or update, when synchronizing will use replace into or INSERT into ... on duplicate key update party into the MySQL database.

Reference:

https://askdba.alibaba-inc.com/libary/control/getArticle.do?articleId=12735
https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

original link

Data integration Several modes for importing data into MySQL via JDBC