`

replace into 和 insert into ON DUPLICATE KEY 用法

 
阅读更多

replace into 和 insert into ON DUPLICATE KEY 用法

 
-- 查询创建表语句
show create table person;
-- 创建person表,主键为int自增
CREATE TABLE `person` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 新增测试数据
insert into person(name,phone,address) values ('张三','18812345678','浙江省杭州市');
insert into person(name,phone,address) values ('李四','18712345678','湖南省长沙市');
insert into person(name,phone,address) values ('王五','18612345678','江苏省南京市');
-- 查询数据
select * from person;

-- 执行replace into语句
replace into person(id,name,phone) values (1,'张三','18812345678') ;

-- 查询结果
select * from person;

-- 执行replace into语句
replace into person(id,name,phone,address) values (4,'赵六','18512345678','河北省石家庄市');

-- 查询结果
select * from person;

根据如上执行过程可以看出:
第一次的replace into语句,为更新id=1的数据,结果影响了2行,并且更新后address字段值内容为空。
第二次的replace into语句,为新增id=4的数据,结果影响了1行。
通过查询资料发现,若更新原有数据,replace into会先删除掉原有数据,此时结果影响1行,随后又新增该更新后的语句,结果又影响1行,由于values中未带有address字段值内容,所以更新后数据address字段值为空。
-- 执行insert into ON DUPLICATE KEY 语句
insert into person values (1,'张三','18888888888','浙江省杭州市') ON DUPLICATE KEY UPDATE address = '黑龙江省哈尔滨市'

-- 查询结果
select * from person;

-- 执行insert into ON DUPLICATE KEY 语句
insert into person values (5,'孙七','18412345678','内蒙古呼和浩特') ON DUPLICATE KEY UPDATE address = '黑龙江省哈尔滨市';

-- 查询结果
select * from person;

根据如上执行过程可以看出:
第一次的insert into ON DUPLICATE KEY UPDATE语句,为更新id=1的数据,结果影响了2行,并且更新后phone没有改变,address改变,说明执行更新的为update后面的字段。
第二次的insert into ON DUPLICATE KEY UPDATE语句,为新增id=5的数据,结果影响了1行,并且字段address为前者的“内蒙古呼和浩特”,并非update后面的字段值。
同replace into,先执行delete,后执行insert into,区别为,insert into ON DUPLICATE KEY UPDATE语句相当于执行的是if exist do update else do insert,因此,如果replace into填充的字段不全,则会导致未被更新的字段都会修改为默认值,insert into ON DUPLICATE KEY UPDATE语句只是更新部分字段,对于未被更新的字段不会变化。
所以:在相关应用场景中,如数据同步服务,尽量使用insert into ON DUPLICATE KEY UPDATE语句,保证不会造成数据字段丢失。
 
分类: 数据库

 

分享到:
评论

相关推荐

    MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

    MySQL中的`REPLACE INTO`和`INSERT INTO ... ON DUPLICATE KEY UPDATE`是两种处理数据冲突的方法,它们在处理已有键值冲突时的行为有所不同。这两种语句都是在插入新记录时,如果发现存在相同的唯一键值,则采取不同...

    mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

    在MySQL中,`REPLACE INTO` 和 `INSERT INTO ON DUPLICATE KEY UPDATE` 是两种处理数据插入时冲突的方法,主要用于处理当试图插入一条记录时,若已有相同唯一键值的记录存在,应该如何操作的情况。 1. **REPLACE ...

    insert into … on duplicate key update / replace into 多行数据介绍

    ON DUPLICATE KEY UPDATE` 和 `REPLACE INTO`。这两种方法都可以实现“合并”操作,即在保持唯一键约束的同时,对重复的数据进行更新。 首先,我们来看一下`INSERT INTO ... ON DUPLICATE KEY UPDATE` 语句。这个...

    mysql ON DUPLICATE KEY UPDATE语句示例

    总结一下`ON DUPLICATE KEY UPDATE`和`REPLACE`语句的区别: 1. 如果表中没有找到匹配的唯一键记录,两者都等同于`INSERT`。 2. 当存在匹配的唯一键记录时,`REPLACE`会删除原有记录并插入新的记录,这可能导致未...

    MySQL replace into 用法.txt

    根据提供的文件信息,本文将详细解释 MySQL 中 `REPLACE INTO` 的使用方法及特性,并对比其与 `INSERT INTO` 的不同之处。 ### MySQL REPLACE INTO 介绍 #### 1. REPLACE INTO 的基本概念 `REPLACE INTO` 是 ...

    MySQL中如何处理重复数据(Duplicate)?

    ON DUPLICATE KEY UPDATE 语句或 REPLACE INTO 语句。下面是这两种方法的详细解释: 一、INSERT INTO ... ON DUPLICATE KEY UPDATE 语句 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句是在 MySQL 中处理重复数据...

    MYSQL的REPLACE和ON DUPLICATE KEY UPDATE语句介绍解决问题实例

    在MySQL数据库中,面对特定的数据更新需求,如调整表中数据的排序顺序,可以使用`REPLACE`和`INSERT ... ON DUPLICATE KEY UPDATE`这两种语句。这两种SQL语句可以帮助我们高效地处理数据冲突,避免繁琐的循环操作,...

    mysql 批量更新及效率对比.docx

    Replace Into 和 Insert Into On Duplicate Key Update 两种方法都可以实现批量更新,但它们之间有着很大的区别。 Replace Into 操作本质是对重复的记录先 delete 后 insert,如果更新的字段不全会将缺失的字段置为...

    mysql 批量更新及效率对比

    这种方法使用 Replace Into 或 Insert Into ... On Duplicate Key Update 语句来批量更新记录,例如: ```sql replace into test_tbl (id, dr) values (1, '2'), (2, '3'), ... (x, 'y'); ``` 或者: ```sql ...

    解析SQL语句中Replace INTO与INSERT INTO的不同之处

    - `INSERT INTO ON DUPLICATE KEY UPDATE` 与 `REPLACE INTO` 类似,但在冲突时更新已存在的记录,而不是删除。 - `SELECT INTO` 语句通常用于创建新表并复制数据,而 `INSERT INTO SELECT` 用于将查询结果插入到...

    mysql如果数据不存在,则插入新数据,否则更新的实现方法

    下面我们将介绍其中的两种常见方法:replace Into 和 insert into … on duplicate key update。 1. Replace Into Replace Into 语句可以实现数据不存在则插入新数据,否则更新的功能。其基本语法为: ```sql ...

    关于避免MySQL替换逻辑SQL的坑爹操作详解

    replace into和insert into on duplicate key 区别 replace的用法 当不冲突时相当于insert,其余列默认值 当key冲突时,自增列更新,replace冲突列,其余列默认值 Com_replace会加1 Innodb_rows_updated会加1 ...

    浅析MySQL replace into 的用法

    MySQL中的`REPLACE INTO`语句是一个非常实用的命令,它结合了`INSERT`和`DELETE`的...在实际应用中,根据业务需求和数据模型,可能需要结合`INSERT`、`UPDATE`和`ON DUPLICATE KEY UPDATE`等多种语句来达到最佳效果。

    Mysql中replace与replace into的用法讲解

    而 `REPLACE` 的效率相比 `INSERT IGNORE` 或 `INSERT ON DUPLICATE KEY UPDATE` 在某些情况下可能会更高,因为它不需要检查每一行是否存在冲突,而是直接进行替换。 总结来说,`REPLACE` 和 `REPLACE INTO` 是...

    mysql 的replace into实例详解

    `REPLACE INTO`与`INSERT INTO ON DUPLICATE KEY UPDATE`语句的区别在于,后者在遇到重复键时会更新已有行,而`REPLACE INTO`则会删除并插入新行。这使得`REPLACE INTO`更适合于希望保持数据唯一性的场景,而`INSERT...

    MySQL中replace into语句的用法详解

    MySQL中的`REPLACE INTO`语句是一个非常实用的命令,它结合了`INSERT`和`UPDATE`的功能,简化了在数据库表中处理已有数据的工作流程。这个语句主要用于当你要插入一条新记录,但该记录的主键或唯一索引字段与现有...

Global site tag (gtag.js) - Google Analytics