REPLACE INTO

1.前言

replace into 可以说是 insert 的增强版，但由于其独特的特性，误用会带来一些副作用，
其功能与 insert 功能类似，不同点是replace into 首先尝试插入数据到表中

  • 如果数据已经存在（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据(特性：先删除再插入) ;
  • 否则，直接执行插入新数据;

官方介绍

refreshscope 性能 replace into 性能_自增


简译

  • REPLACE与INSERT工作方式基本相同。不同点在于如果表中旧行与主键或唯一索引列具有相同的值，则插入新行之前删除该旧行。
CREATE TABLE `insert_relpace_into_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `uniq_id` varchar(32) COMMENT '字段 a',
  `filed_a` varchar(32) DEFAULT '' COMMENT '字段 b',
  `filed_b` varchar(32) DEFAULT '' COMMENT '字段 c',
  `version` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_uniq_id` (`uniq_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='REPLACE INTO 语法测试表';

2.使用方式

创建测试表

CREATE TABLE `insert_relpace_into_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `uniq_id` varchar(32) DEFAULT NULL COMMENT '字段 a',
  `filed_a` varchar(32) DEFAULT '' COMMENT '字段 b',
  `filed_b` varchar(32) DEFAULT '' COMMENT '字段 c',
  `version` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_uniq_id` (`uniq_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='REPLACE INTO 语法测试表';

2.1. replace into values

注：支持单个或批量

REPLACE INTO insert_relpace_into_test 
( uniq_id, filed_a, filed_b, version )
VALUES
  ( '1003', '字段 a', '字段 b', 1 ), 
  ( '1005', '字段 _2_2', '字段 b_2', 1 );

2.2. **replace into select **

REPLACE INTO insert_relpace_into_test ( uniq_id, filed_a, filed_b, version )
SELECT uniq_id, filed_a, filed_b, version FROM  insert_relpace_into_test2;

**

2.3. replace into set

REPLACE INTO insert_relpace_into_test 
SET uniq_id='1003',filed_a='字段 _2_2',filed_b='字段 b_2';

注：INTO 关键句可以省略，建议加上，更通俗易懂

3.常见问题

3.1.数据已经存在但没有执行替换逻辑 ;

  • 检查字段中是否包含唯一索引列，若没有唯一索引或主键列则会一直新增下去 ;

3.2.自增主键不连续;

  • 由于存在则删除后新增的特性，在执行插入，则会造成自增ID一直增大，出现不连续的问题 ;

4.总结

1.由于若存在，则删除后新增的特性，插入新记录只是插入了某些业务指定字段，还存在一些原先不想更新的字段，若执行了，删除后新增则会导致部分数据丢失。
2.若表的自增ID与其它表有关联，删除后新增并生成新的id后，会导致关联丢失 。
3.而且使用replace into会导致自增主键id一直增大，很容易导致id值存在跳跃，范围不够使用了 。
4.若主从关系数据库，在主机器上进行了replace into操作之后，从机器上对应表的AUTO_INCREMENT是不会更新，导致从机器转为主机器时，新插入数据会出现异常，直到AUTO_INCREMENT增加到原来主机器的值为止。