UPSERT의 동작원리와 기준 컬럼 설정 유의점

 

염려질환과 펫의 관계를 나타내는 테이블인 pet_diseases에 petId의 값이 있으면 수정을 해줘야 하고 없다면 추가를 해주는 기능이 필요했다. 그 때 유용하게 쓰이는 UPSERT에 대해 알아보겠다.

 

나는 MySQL을 사용중이기 때문에 MySQL 문법에 대해서만 다룰 예정이다!

 

코드 먼저 살펴보자

-- 이런 테이블이 있을 때
CREATE TABLE pet_disease (
    pet_id INT NOT NULL UNIQUE, -- 반려동물 ID
    disease VARCHAR(255) NOT NULL -- 질병 ID
);

INSERT INTO pet_disease (pet_id, disease) 
VALUES (#{petId}, #{disease}) 
ON DUPLICATE KEY UPDATE disease = #{disease};

 

 

다른 설명없어도 대충 감이 올 것이다. pet_id가 이미 존재하면 UPDATE, 존재하지 않으면 INSERT 실행된다.

SELECT로 따로 찾을 필요 없어서 쿼리가 아주 깔끔하다!

 

ON DUPLICATE KEY UPDATE의 동작 원리

• 기준이 되는 컬럼은 PRIMARY KEY 또는 UNIQUE KEY가 설정된 컬럼
• INSERT 시 중복된 값이 있는 경우에는 UPDATE 실행

 

기준이 되는 컬럼이 중요하다!

위에 동작원리에서 본 것처럼 기준이 되는 컬럼은  PRIMARY KEY 또는 UNIQUE KEY가 설정된 컬럼이다.

즉, 기준이 되는 컬럼에 따라 결과가 상당히 달라진다.

 

CREATE TABLE pet_disease (
    pet_id INT NOT NULL UNIQUE, -- 반려동물 ID
    disease VARCHAR(255) NOT NULL -- 질병 ID
);

 

pet_id (기준 컬럼)	disease
10	피부
10	눈

 

이런 테이블 구조가 있다고 가정하자. pet_id에 UNIQUE가 적용되어 있으니 기준 컬럼이 된다.

이 상태에서는 UPSERT를 실행하면 과연 이런식으로 데이터가 들어가게 될까 생각해보자.

 

pet_id가 기준 컬럼이 되므로 여러 pet_id 행이 만들어질 수 없다!

pet_id (기준 컬럼)	disease
10	피부

INSERT INTO pet_disease (pet_id, disease_id) 
VALUES (10, "눈") 
ON DUPLICATE KEY UPDATE disease_id = "눈";

 

 

이미 pet_id에 피부라는 데이터가 들어가있다. 같은 pet_id에 눈이라는 disease_id도 넣고 싶어서 아래 쿼리를 실행해 보았다.

 

• 예상한 결과

pet_id (기준 컬럼)	disease
10	피부
10	눈

• 실제 결과

pet_id (기준 컬럼)	disease
10	눈

 

기준이 되는 컬럼인 10이 이미 들어있으면 disease_id를 업데이트하고, 업다면 "눈"이라는 데이터가 추가된다.

그런데 이미 기준컬럼에 같은 값이 있으니 눈이라는 데이터가 추가되는게 아니라 기존 데이터가 수정이 된다!

 

해결하기

그렇다면 예상한 결과대로 값이 넣어지길 원한다면 어떻게 해야할까

pet_id + disease를 UNIQUE KEY로 지정해주면된다!

 

CREATE TABLE pet_disease (
    pet_id INT NOT NULL,
    disease VARCHAR(255) NOT NULL,
    PRIMARY KEY (pet_id, disease)  -- 복합 키 설정
);

 

기준 컬럼을 이렇게 바꿔주면 이제 동일한 pet_id라도 disease 값이 다르면 새로운 데이터가 삽입된다.

pet_id와 disease가 모두 같으면 UPDATE되고 하나라도 같지않으면 INSERT되기 때문이다.