MySQL 의 UPSERT 쿼리

공부를 통해 배운 내용을 작성하고 있습니다. 혹여 해당 포스팅에서 잘못된 부분이 있을 경우, 알려주시면 빠르게 수정 조치하도록 하겠습니다.

 

 

 

MySQL 쿼리는 UPSERT 명령어가 별도로 존재하지 않는다.
하지만 INSERT ... ON DUPLICATE KEY UPDATE 문을 통해 UPSERT 문을 구현할 수 있다.
나처럼 UPSERT 쿼리에 대해 익숙하지 않은 분들을 위해, 구현하면서 배운 몇 가지 사항을 정리해보려고 한다.

 

 

목차

MySQL 의 UPSERT 문

UNIQUE KEY vs PRIMARY KEY

CREATE DATE, UPDATE DATE 컬럼 설정

 

---

 

 

01. MySQL 의 UPSERT 문

 

UPSERT 란,  UNIQUE 인덱스 또는 PRIMARY KEY 와 동일한 값이 있는 데이터가 기존에 존재하지 않으면 INSERT & 존재하면 변경사항에 대해 UPDATE를 해주는 것을 의미한다.

 

앞서 언급했듯, mysql 에서는 INSERT... ON DUPLICATE KEY UPDATE 문을 통해서 UPSERT를 수행할 수 있다.

 

 

 

예시 쿼리를 살펴보자. (예시 쿼리는 아래 공식 문서 링크를 출처로 발췌하였다.)

 

INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

 

이는 t1 이라는 테이블에 a, b, c 컬럼에 각각 1,2,3이라는 데이터를 넣는데, 이때 만약 t1 테이블의 unique index 또는 primary key와 동일한 값이 있는 데이터가 기존에 존재할 경우, 해당 행의 c 컬럼 데이터만 3으로 변경하라는 의미를 지닌다.

 

표로 보면 아래와 같은 형식일 것이다.

 

 

Before - 이미 기존에  a,b,c 컬럼에 각각 1,2,3이라는 데이터가 들어가 있는 경우

a	b	c
1	2	2

 

After - 조건과 동일한 행의 c 컬럼 값만 3으로 변경됨 

a	b	c
1	2	3

 

 

---

 

02. UNIQUE KEY vs PRIMARY KEY

 

그렇다면 unique key 와 primary key는 무엇인가!

아래와 같은 표로 정리해보았다.

 

분류	PRIMARY KEY	UNIQUE KEY
설명	해당 테이블의 레코드 식별자 역할을 하는 키	해당 테이블 내 항상 고유해야 하는 키
중복 허용 여부	중복 불가. 값이 모두 unique 해야 함	중복 불가. 값이 모두 unique 해야 함
단일 설정 여부	단일해야 함. 한 컬럼(혹은 하나의 묶음 컬럼)으로만 설정해야 함.	여러 개 컬럼(혹은 여러 개의 묶음 컬럼)으로 설정해도 됨.
NULL 허용 여부	NULL 허용되지 않음	NULL 허용됨

 

즉, PRIMARY KEY는 UNIQUE KEY 속성에 포함이 되나,

null 이 허용되지 않으며 반드시 테이블 당 한 개만 설정되어야 한다는 추가적인 제약이 있다. 

 

그래서 주로, 테이블의 id(레코드 식별을 할 때 주로 사용)에 부여한다.

 

 

결론적으로, INSERT... ON DUPLICATE KEY UPDATE 문은, insert 하려는 값 중 unique key에 해당하는 컬럼이 table에 이미 존재하는지 살펴보고, 만약 없으면 insert 를 & 있다면 update 를 한다고 생각할 수 있겠다.

 

---

 

03. CREATE DATE, UPDATE DATE 컬럼 설정

 

내가 설계했던 테이블의 경우, create 한 date와 update 한 date를 담는 필드를 설계했었다.

그래서, python에서 데이터를 넣을 때 datetime 함수를 이용하여, update컬럼에 데이터를 삽입하여 upsert를 하였다.

 

그런데 위와 같이 설정을 했을 때, unique key 에는 update 컬럼이 포함되지 않았으므로, 

한번 upsert 쿼리문을 돌릴 때마다, 다른 값들이 변경되지 않았음에도, 모든 필드의 update 컬럼이 쫙 업데이트되는 기이한 상황이 생겼다.

 

이건 뭔가 잘못된 설계 같다는 생각에 고민을 한 결과, CURRENT_TIMESTAMP를 사용하면 해결될 문제일 것 같다는 생각이 들었다. 

결론적으로 말하면, 실제로 이슈가 해결됐고, 다른 분들의 테이블 설계에서도 아주 많이 보이는 설정임을 알 수 있었다. 

 

create date 필드에는 DEFAULT CURRENT_TIMESTAMP를,

update date 필드에는 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 설정을 해주면 됐다.

 

• DEFAULT CURRENT_TIMESTAMP : INSERT 시 기본 값으로 mysql에 설정된 timezone 에 해당하는 현재 시간이 삽입
• ON UPDATE CURRENT_TIMESTAMP : UPDATE 시 기본 값으로 mysql 에 설정된 timezone 에 해당하는 현재 시간이 삽입

 

 

예시로 테이블을 생성하는 쿼리를 보면 아래와 같다.

 

CREATE TABLE `t1` (

`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,

`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 

...... );

 

 

 

---

 

요즘, 옛날에 썼던 블로그 포스팅을 보면 '내가 왜 이걸 몰랐었지' 하는 부끄러움이 들기도 하곤 한다.

이 게시물의 내용도 나중에 보면 조금은 부끄러운 내용일 수 있겠지만, 조금씩이라도 배워가고 느껴가고 성장하는 데에 의의를 두고 생각해보니 뿌듯하기도 하고 그렇다...

성장하는 데에 더 의의를 두며 조금씩 천천히 발전해나가도록 하겠다! 아자아자!

 

 

 

 

 

(참고) 공식 문서는 아래와 같다.

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html