MySQL - REPLACE INTO 레코드 바꿔치기(INSERT IGNORE) 중복 피하기

INSERT와 UPDATE를 하나의 쿼리에서 시행할 수 있는 REPLACE INTO

REPLACE INTO는 갱신(수정)하고자 하는 ROW데이터가 있으면 UPDATE, 없으면 INSERT 한다.

REPLACE INTO [TABLE명](변경 하고자 하는 컬럼) VALUES ()

---

A 테이블의 ROW를 B 테이블에 복사하고 싶을 때에, 키 값이 겹치지 않는 다면 그냥 INSERT해도 된다.

 

하지만, PK값이 있을 때 INSERT를 시도하면 Duplicate entry '1' for key 'PRIMARY' 오류를 만난다.

중복 키 오류를 피하기 위해서는 여러 방법이 있다. (REPLACE INTO는 PK값이 있을 때에)

 

 

애플리케이션 로직단에서 분기 처리

 - ROW 존재 유무에 따라 INSERT, UPDATE

  1. ROW데이터가 존재하는지 판별이 필요한데, 보통 SELECT 쿼리를 수행한다.

  2. SELECT 결과를 토대로 INSERT 또는 UPDATE 분기 처리

 

  단점으로 로직 단에서 분기 처리 코드가 필요하고, ROW마다 2번씩 쿼리를 수행한다. 

  무조건 적인 SELECT 쿼리 1번과 SELECT 결과에 따라 INSERT 또는 UPDATE,

  

  1000개의 ROW일 때, 2000번의 쿼리 실행

 

 

INSERT IGNORE

기본 INSERT쿼리를 실행하는 데, 중복 키가 발생할 경우 구문 오류는 발생하지 않는다.

대신 0 rows affected를 확인할 수 있다. 

 

존재하지 않는 ROW에 대해서는 신규로 입력할 수 있지만, 기존 ROW를 업데이트할 수는 없다.

그러므로 ROW데이터의 갱신은 필요치 않고, 단순히 없을 때에만 INSERT 하는 작업에 알맞다.

(Auto_Increment 값은 변하지 않는다.)

 

하지만, 여기서 디벨롭하여 rows affected value를 체크하여 INSERT 성공 건수가 없다면,

이미 로우가 존재하는 걸로 판단 후 UPDATE 쿼리를 수행할 수 있다.

 

REPLACE INTO

REPLACE INTO는 내부적으로 무조건 PK값에 해당하는 ROW를 DELETE 하고 INSERT 한다.

(성능은그닥그닥다그닥)

 

1000개의 row일 때 2000번의 쿼리 실행

 

로직단에서 분기 처리하는 것과 비교했을 때, 코드를 간결하게 할 수 있다.

 

또한, ROW가 많아질수록 이점이 있는데 REPLACE INTO는 Multiplerow가 가능하다.

(물론, BULK INSERT도 가능하지만 UPDATE는 Multiplerow가 안되므로)

 

주의할 점

 

1. Not Null 필드인 컬럼이 존재할 때

(변경하고자 하는 컬럼)을 명시할 때 Not Null인 컬럼과 value를 지정해주지 않을 경우 

field doesn't have a default value 오류를 만난다.

 

당연한 부분인데, ROW를 DELETE 한 후에 INSERT 하는데 기존 데이터가 어디 있겠나

해당 컬럼에 default value를 선언해주어도 REPLACE INTO에서는 지정이 안된다.

(예외: auto_increment 속성)

 

2. Auto_Increment 컬럼

DELETE후 INSERT이니 auto_increment가 1씩 증가하므로 해당 필드가 레코드 식별에 큰 역할인지 확인이 필요하다.

---

REPLACE INTO는 ROW가 있든, 없든 알아서 지우고 INSERT 하기에 편리할 수 있다.

하지만 성능상으로 봤을 때, 우리는 ROW가 있을때엔 INSERT 없을 때엔 UPDATE로 작업을 나누어,

한 개의 데이터는 한 개의 작업을 하도록 해야 한다.

당장 하나의 데이터일 땐 2번의 작업(DELETE, INSERT)이지만, 천 건, 만 건, 몇 만건이 되었을 때

불필요한 작업으로 인한 성능 저하는 당연한 수순이기에

 

나는 레코드 치환 시 ON DUPLICATE KEY UPDATE를 사용하는데, 다음 포스팅에서 기술하겠다.

https://brush-describr.tistory.com/5

MySQL - ON DUPLICATE KEY UPDATE 중복없이 레코드 바꿔치기(대량 레코드) MyBatis foreach문

오타와 지적, 댓글은 환영입니다.😆