[MySQL] UPSERT(UPDATE + INSERT)

데이터베이스를 사용할 때, 중복 데이터를 방지하면서 효율적으로 데이터를 삽입하고 싶을 때가 분명 올 것입니다 🤨

이를 해결하기위해 'UPSERT'라는 기능을 제공합니다. 

이번 글에서는 평소에 사용하던 UPSERT의 개념과 MySQL에서 UPSERT를 어떻게 사용하는지에 대해 알아보겠습니다!

 

 

UPSERT 란?

UPSERT는 "insert"와 "update"의 합성어입니다.

데이터베이스 테이블에 데이터를 삽입할 때 해당 데이터가 이미 존재한다면 업데이트를 하는 작업을 의미합니다.

이는 데이터베이스에 중복 데이터를 방지하면서 효율적으로 데이터를 갱신할 수 있는 방법입니다.

 

MySQL에서의 UPSERT 

INSERT INTO table_name (column1, column2, ...) 
	VALUES (value1, value2, ...) 
ON DUPLICATE KEY UPDATE 
	column1 = VALUES(column1),
	column2 = VALUES(column2);

1. INSERT table_name에 (value1, value2,...) 값을 가진 새로운 행을 삽입 
2. ON DUPLICATE KEY 삽입하려는 데이터가 이미 존재하는 경우를 처리, 중복 키는 주로 primary key 또는 unique 인덱스를 의미
3. UPDATE 중복 키가 발생하면 기존 행을 새로운 값으로 업데이트, VALUES(column)은 INSERT 구문에서 제공한 값을 참조

사용 예시

예시를 살펴보기 위해 테이블을 생성합니다.  저는 user_name을 unique key로 생성했습니다.

create table local_test.user_info (
    id BIGINT auto_increment primary key,
    user_name varchar(50) unique,
    age int 
);

 

수정되는 데이터를 확인하기 위해 데이터를 미리 삽입하겠습니다.

(저는 강아지🐶와 고양이🐱 사용자들을 추가해 줬습니다.)

insert into user_info(user_name, age) value("강아지", 10);

insert into user_info(user_name, age) value("고양이", 15);

삽입 결과

 

데이터 삽입 또는 업데이트를 진행해 보도록 하겠습니다.

INSERT INTO local_test.user_info (user_name, age) 
	VALUES ("강아지", 12) 
ON DUPLICATE KEY UPDATE 
	user_name = VALUES(user_name),
	age = VALUES(age);

실행 결과

강아지가 존재하기 때문에 강아지의 age 컬럼의 값만 업데이트되는 것을 확인할 수 있습니다.

 

VALUES를 사용하지 않고도 UPSERT문을 사용할 수 있습니다.

INSERT INTO local_test.user_info (user_name, age) 
	VALUES ("고양이", 6) 
ON DUPLICATE KEY UPDATE 
	user_name = "고양이2",
	age = 5;

실행결과

UPDATE 문에 명시적으로 작성한 값이 올바르게 업데이트 되는 것을 확인할 수 있습니다.

 

❗️주의 사항 ❗️

• ON DUPLICATE KEY UPDATE 구문은 PRIMARY KEY나 UNIQUE 제약 조건이 있는 컬럼에 대해서만 동작합니다.
  즉, 해당 키에 중복이 발생했을 때만 UPDATE 동작이 실행됩니다.
• VALUES(column) 함수는 INSERT 구문에서 지정한 값을 참조합니다.
  따라서 기존 값을 유지하려면 UPDATE 구문에서 해당  값을 명시적으로 유지해야 합니다.