Oracle MySQL相关工具replace操作实现方式区别-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: 本文列举MySQL、Oracle、常用数据导入工具碰到主键冲突、唯一约束、以及其他对重复数据、已有数据的处理区别,并介绍MySQL、Oracle replace相关语句实现区别。 由此可见在实际使用不同数据库过程中,还是有许多差别,需要大家细心留意。

数据导入工具中的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)使用以下算法 :

  1. 尝试将新行插入表中
  2. 虽然插入失败是因为主键或唯一索引发生重复键错误:
    • 从表中删除具有重复键值的冲突行
    • 再试一次将新行插入表中
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参数。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: