AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2020-06-28 817
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 本文列举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参数。

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
DataX
MySQL
Oracle
算法
数据库
关键词：
云数据库 RDS MySQL 版区别
oracle云数据库 RDS MySQL 版
Oracle数据库一键上云MySQL
云数据库 RDS MySQL 版oracle
oracle云数据库 RDS MySQL 版区别
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
虽然
目录
相关文章
智物科技库
|
6天前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性，通过记录数据页的物理修改信息来恢复未提交的事务；而二进制日志记录SQL语句的逻辑变化，支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
智物科技库
27 6
qhhf_地道酬勤
|
2月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB区别，适用场景
一个项目用5款数据库？MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
qhhf_地道酬勤
377 0
东方睿赢
|
3月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具，凭借其基于MySQL binlog的增量同步机制，在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具，凭借其基于MySQL binlog的增量同步机制，在数据同步领域展现了强大的应用价值
东方睿赢
776 4
yuanzhengme
|
4月前
|
存储 关系型数据库 MySQL
ES的全文索引和MySQL的全文索引有什么区别？如何选择？
【8月更文挑战第26天】ES的全文索引和MySQL的全文索引有什么区别？如何选择？
yuanzhengme
429 5
WangBai
|
8天前
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别？
InnoDB是MySQL默认的事务型存储引擎，支持事务、行级锁、MVCC、在线热备份等特性，主索引为聚簇索引，适用于高并发、高可靠性的场景。MyISAM设计简单，支持压缩表、空间索引，但不支持事务和行级锁，适合读多写少、不要求事务的场景。
WangBai
33 9
蓝易云
|
20天前
|
存储 NoSQL 关系型数据库
MySQL和Redis的区别
**MySQL和Redis的区别** MySQL和Redis都是流行的数据存储解决方案，但它们在设计、用途和特性上有显著区别。理解这些区别有助于选择合适的数据库来满足不同的应用需求。本文将详细介绍MySQL和Redis的区别，包括它们的架构、使用场景、性能和其他关键特性。 ### 一、基本概述 **MySQL**： MySQL是一个关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）进行数据管理。它支持事务、复杂查询和多种存储引擎，广泛应用于各种Web应用、企业系统和数据分析项目。 **Redis**： Redis是一个基于内存的键值数据库，通常被称为NoSQL数
蓝易云
42 4
大数据文摘
|
1月前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志（Redo Log）和二进制日志（Binary Log）是两种重要的日志系统。重做日志主要用于保证事务的持久性和原子性，通过记录数据页的物理修改信息来恢复未提交的事务更改。二进制日志则记录了数据库的所有逻辑变化操作，用于数据的复制、恢复和审计。两者在写入时机、存储方式、配置参数和使用范围上有所不同，共同确保了数据库的稳定性和可靠性。
大数据文摘
43 2
程序员小海绵
|
3月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别？从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
程序员小海绵
626 18
Oracle和MySQL有哪些区别？从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
武子康
|
2月前
|
SQL 分布式计算 关系型数据库
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
武子康
69 3
1015608500650149
|
2月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序，叶节点仅存储数据区，而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成，遵循最左前缀原则，可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则，并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
1015608500650149
134 1

热门文章

最新文章

  • 1
    oracle11g监听网卡总断（一）
  • 2
    解决一例oracle archiver error
  • 3
    再次明确Oracle插入与读取的顺序问题
  • 4
    误删Oracle数据文件的恢复方法
  • 5
    Script:计算Oracle Streams进程所占用的内存大小
  • 6
    Oracle数据库中的分页--rownum
  • 7
    oracle sqlplus常用命令
  • 8
    Oracle 用户、对象权限、系统权限
  • 9
    Oracle VARRAY的实际应用简介
  • 10
    oracle express 11g 创建表空间
  • 1
    实时计算 Flink版操作报错合集之程序初始化mysql没有完成就报错如何解决
    295
  • 2
    实时计算 Flink版操作报错合集之sqlserver mysql都用的胖包，sqlserver的成功了，mysql报这个错如何解决
    302
  • 3
    实时计算 Flink版操作报错合集之连接器换成2.4.2之后，mysql作业一直报错如何解决
    184
  • 4
    解决centos7.0安装mysql后出现access defind for user@'localhost'的错误
    114
  • 5
    实时计算 Flink版产品使用合集之在抓取 MySQL binlog 数据时，datetime 字段会被自动转换为时间戳形式如何解决
    79
  • 6
    MySQL 到 Kafka 实时数据同步实操分享(1)，字节面试官职级
    126
  • 7
    MySQL 到 Greenplum 实时数据同步实操分享，2024年最新【Python面试题
    45
  • 8
    MySQL窗口函数怎么用
    44
  • 9
    实时计算 Flink版产品使用合集之可以支持 MySQL 数据源的增量同步到 Hudi 吗
    96
  • 10
    MySQL数据类型详解及实例应用
    154

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • oracle
  • postgresql
    • 下一篇
    阿里云对象存储OSS收费标准：存储、流量和请求等多个计费项详解