数据导入工具中的replace

1，oracle impdp 导入数据的表已存在指定TABLE_EXISTS_ACTION参数，如果目标库存在相同名称的表，执行下面的操作

1) skip：默认跳过这张表，继续下一个对象。如果CONTENT设置了DATA_ONLY参数，则默认值为append。
2) replace：先drop表，然后创建表，最后插入数据
3) append：在原来数据的基础上增加数据
4) truncate：先truncate，然后再插入数据

2，oracle sqlloader 加载数据模式，遇到有数据时

1) APPEND //原先的表有数据就加在后面
2) INSERT // 装载空表如果原先的表有数据 sqlloader会停止默认值
3) REPLACE // 原先的表有数据原先的数据会全部删除
4) TRUNCATE // 指定的内容和replace的相同会用truncate语句删除现存数据

3，mysql load data 加载数据对有唯一键记录重复时

1）如果指定replace，新行将代替有相同的唯一键值的现有行。具体逻辑同mysql数据库内replace into语句
2）如果指定ignore，跳过有唯一键的现有行的重复行的输入。
3）如果不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。

4，DataX工具

导入数据碰到主键或唯一键冲突：选择导入模式，可以支持 insert/replace/insert ignore 方式

insert 指当主键/唯一性索引冲突，数据集成视为脏数据进行处理。
replace 指没有遇到主键/唯一性索引冲突时，与 insert 行为一致，当主键/唯一性索引冲突时会用新行替换原有行所有字段。
insert ignore 指当主键/唯一性索引冲突，数据集成将直接忽略更新丢弃，并且不记录！

注意

MySQL writer支持replace into，参考MySQL数据库的replace into语句。

Oracle writer不支持replace into，支持insert into...(当主键/唯一性索引冲突时会写不进去冲突的行)，因为Oracle不支持replace、replace into语句。OracleWriter和MysqlWriter不同，不支持配置writeMode参数。原因可见文末

数据库中的replace

1，MySQL数据库中的replace、replace into语句

replace在没有碰到主键、唯一索引重复值时，和insert完全相同。因此replace仅当表具有PRIMARY KEY或 UNIQUE索引时才有意义。
如果表中的旧行与a PRIMARY KEY或UNIQUE 索引的新行具有相同的值，则在插入新行之前删除该旧行。

要使用REPLACE，您必须同时拥有表的INSERT和 DELETE特权。

MySQL对REPLACE（和 LOAD DATA ... REPLACE）使用以下算法：

尝试将新行插入表中
虽然插入失败是因为主键或唯一索引发生重复键错误：

- 从表中删除具有重复键值的冲突行
- 再试一次将新行插入表中

mysql> select * from replace1;
+----+-------+
| id | name  |
+----+-------+
|  1 | aaa   |
|  2 | wang1 |
|  3 | ccc   |
+----+-------+
3 rows in set (0.00 sec)

mysql> replace into replace1 values (3,'ccc');
Query OK, 1 row affected (0.02 sec)

mysql> replace into replace1 values (3,'ccc');
Query OK, 1 row affected (0.01 sec)

mysql> replace into replace1 values (3,'ddd');
Query OK, 2 rows affected (0.00 sec)

mysql> replace into replace1 values (3,'ddd');
Query OK, 2 rows affected (0.00 sec)

mysql> replace replace1 values (3,'ddd');
Query OK, 1 row affected (0.00 sec)

mysql> replace replace1 values (3,'ccc');
Query OK, 2 rows affected (0.01 sec)

从执行结果来看，replace和replace into 具有相同的作用。

也可以使用MySQL help命令查看帮助：

mysql> help replace;

2，MySQL数据库的INSERT ... ON DUPLICATE KEY UPDATE语句

insert into UNE_CBILL_PRINT_CLOUD(fid,feinvoicecode,ftype)VALUES('1','1','1')
        ON DUPLICATE KEY UPDATE foperator = '1',ftype = '3';

replace into 效率比较低，因为在更新数据的时候，要先删除旧的，然后插入新的，在这个过程中，还要重新维护索引；

insert on duplicate 的更新操作虽然也会更新数据，但其对主键的索引却不会有改变，也就是说，insert　on duplicate　更新对主键索引没有影响，因此对索引的维护成本就低了一些。

相关测试：

mysql> 
mysql> create table replace1(id int primary key,name varchar(8));
Query OK, 0 rows affected (0.21 sec)

mysql> 
mysql> 
mysql> 
mysql> create unique index unique_name on replace1(name);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> insert into replace1 values(1,'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(2,'bbb');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 2 rows affected (0.01 sec)

mysql> replace into replace1 values(3,'ccc');
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> replace into replace1 values(4,'ccc');
Query OK, 2 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  4 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 2 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> desc replace1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(8) | YES  | UNI | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(4,'ddd');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
4 rows in set (0.00 sec)

mysql> 
mysql> replace into replace1 values(4,'ccc');
Query OK, 3 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  4 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> select * from replace1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 2 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> drop index unique_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> 
mysql> 
mysql> 
mysql> help drop index 
Name: 'DROP INDEX'
Description:
Syntax:
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

DROP INDEX drops the index named index_name from the table tbl_name.
This statement is mapped to an ALTER TABLE statement to drop the index.
See [HELP ALTER TABLE].

To drop a primary key, the index name is always PRIMARY, which must be
specified as a quoted identifier because PRIMARY is a reserved word:

DROP INDEX `PRIMARY` ON t;

URL: http://dev.mysql.com/doc/refman/8.0/en/drop-index.html


mysql> 
mysql> 
mysql> 
mysql> drop index unique_name on replace1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> 
mysql> replace into replace1 values(3,'ccc');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> 
mysql> insert into replace1 values(4,'ddd');
Query OK, 1 row affected (0.01 sec)

mysql> create unique index unique_name on replace1(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into replace1 values(5,'eee');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> desc replace1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(8) | YES  | UNI | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> update replace1 set name='ccc' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update replace1 set name='ccc' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> 
mysql> 
mysql> update replace1 set name='ddd' where id=3;
ERROR 1062 (23000): Duplicate entry 'ddd' for key 'unique_name'
mysql> 
mysql> 
mysql> update replace1 set name='www' where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> 
mysql> update replace1 set name='ccc' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> update replace1 set name='ccc' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> 
mysql> update replace1 set name='ccc' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

注意：

1，在有多个唯一索引的时候执行replace操作会出现一些奇怪的现象，如除主键外，还有个唯一索引。replace操作影响了2行，所以先删掉冲突的2行，并进行插入一行，返回提示影响了3行数据，replace一条数据，表的总行数少了一条。

2，除主键外有其他唯一索引时，replace语句values如果完全相同时，影响行数为2，是先delete再insert，当仅有主键没有其他唯一索引时，影响行数是1，但实际没有做delete操作（类似于update完全相同值的场景），上述推测根据binlog分析的，再深入可能得根据MySQL源码分析。

由此得出以下2个结论：

当有多个唯一索引时会多删除数据，慎用replace，同时也尽量避免这种情形，满足数据库设计的第二范式。
只有一个主键or唯一索引的情况，可以使用insert on duplicate 操作，比replace操作性能好一些，减少对索引维护带来的性能开销。

3，Oracle数据库中对应的实现是merge into语句

MERGE INTO --要插入的表别名
USING (

  --查询的SQL
  )别名 ON 
     --(连接条件)

WHEN MATCHED THEN --如果符合条件

 UPDATE SET

WHEN NOT MATCHED THEN

 INSERT ()VALUES();--不符合就插

/*语法：
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
*/

Oracle数据库没有和MySQL一样的replace into语句，
但是有merge into 语句，可以同时实现update和insert的功能。
update子句后面可以跟delete子句来去掉一些不需要的行
delete只能和update配合，从而达到删除满足where条件的子句的记录

Oracle 的 merge into 语句的insert、update、delete互相配合能达到跟MySQL replace语句一样的效果。
由于没有根据唯一约束重复值进行操作的replace，因此datax oracle writer不支持配置writeMode参数。

Oracle MySQL相关工具replace操作实现方式区别

数据导入工具中的replace

1，oracle impdp 导入数据的表已存在指定TABLE_EXISTS_ACTION参数，如果目标库存在相同名称的表，执行下面的操作

2，oracle sqlloader 加载数据模式，遇到有数据时

3，mysql load data 加载数据对有唯一键记录重复时

4，DataX工具

导入数据碰到主键或唯一键冲突：选择导入模式，可以支持 insert/replace/insert ignore 方式

注意

数据库中的replace

1，MySQL数据库中的replace、replace into语句

MySQL对REPLACE（和 LOAD DATA ... REPLACE）使用以下算法：

2，MySQL数据库的INSERT ... ON DUPLICATE KEY UPDATE语句

相关测试：

注意：

3，Oracle数据库中对应的实现是merge into语句

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Oracle MySQL相关工具replace操作实现方式区别

数据导入工具中的replace

1，oracle impdp 导入数据的表已存在指定TABLE_EXISTS_ACTION参数，如果目标库存在相同名称的表，执行下面的操作

2，oracle sqlloader 加载数据模式，遇到有数据时

3，mysql load data 加载数据 对有唯一键记录重复时

4，DataX工具

导入数据碰到主键或唯一键冲突：选择导入模式，可以支持 insert/replace/insert ignore 方式

注意

数据库中的replace

1，MySQL数据库中的replace、replace into语句

MySQL对REPLACE（和 LOAD DATA ... REPLACE）使用以下算法 ：

2，MySQL数据库的INSERT ... ON DUPLICATE KEY UPDATE语句

相关测试：

注意：

3，Oracle数据库中对应的实现是merge into语句

热门文章

最新文章

相关课程

相关电子书

推荐镜像

3，mysql load data 加载数据对有唯一键记录重复时

MySQL对REPLACE（和 LOAD DATA ... REPLACE）使用以下算法：