[MySQL/MariaDB] merge(값이 있으면 update, 아니면 insert)(feat. ON DUPLICATE KEY UPDATE...)

목차

MySQL INSERT INTO...ON DUPLICATE KEY UPDATE... 😃

mysql에도 Oracle의 merge into와 같은 기능이 있다는 걸 알게되어 미래의 내가 보기위해 기록함

테스트를 위해 테이블 하나 생성

CREATE TABLE `test_220714` (
  `id` bigint(20) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

테스트 데이터를 2개 정도 입력

INSERT INTO test_220714(id, name)VALUES(1, 'ironMan');
INSERT INTO test_220714(id, name)VALUES(2, 'thor');

DBeaver와 같은 툴을 사용하면 SQL을 생성해주니까 이를 이용하면 편하다.

이 상태에서 값이 이미 있으면 업데이트를 하고 없으면 insert를 하기 위해 아래와 같이 SQL을 작성해서 실행해보겠다.

INSERT INTO test_220714(id, name)
VALUES('1', 'odin')
ON DUPLICATE KEY UPDATE
	name = 'odin'
;

그러면 아래처럼 id가 1번인 데이터의 name이 odin으로 변경된걸 확인 할 수 있다.

아래처럼 SQL을 실행하면 3번 째 항목이 추가가 된다.

INSERT INTO test_220714
(id, name)
VALUES('3', 'xman')
ON DUPLICATE KEY UPDATE
	name = 'xman'
;

INSERT INTO... 뒤에 붙은 ON DUPLICATE KEY UPDATE 이 구문이 이 역할을 해준다.

DUPLICATE KEY, 즉 중복키(pk를 뜻함)가 있으면 업데이트 문을 실행하겠다는 소리다.

-- 그래서 이 경우는 id가 1번인 데이터는 이미 있으니 name이 'ironMan'에서 'odin'으로 변경되었다.
INSERT INTO test_220714(id, name)
VALUES('1', 'odin')
ON DUPLICATE KEY UPDATE
	name = 'odin'
;

-- 이 경우는 id가 3번인 데이터는 존재하지 않으니 'xman'이 insert 되었다.
INSERT INTO test_220714
(id, name)
VALUES('3', 'xman')
ON DUPLICATE KEY UPDATE
	name = 'xman'
;

 

만일 위와 같은 기능을 이용하지 않고 등록/수정을 분기 처리할 경우 아래와 같은 로직을 수행해야 한다.

1.  키값에 해당되는 데이터의 존재유무 체크 SQL 실행
2.  해당 SQL의 값이 있으면 INSERT문, 없으면 UPDATE문 실행

불 필요한 존재유무 체크하는 SQL을 한번 더 실행해야 하는 번거로움, 비효율적인 측면이 생긴다.

사용하는 DBMS의 버전이 해당 기능을 지원하지 않는 버전이거나 그 외 상황이 아니면 DUPLICATE KEY UPDATE...를 사용하면 된다.

mybatis에서는 아래처럼 사용하면 된다.

<insert id="insertMember" parameterType="MemberVO">
 INSERT INTO member
(
    id,
    name,
    email,
    regDate
)
VALUES
(
    #{id},
    #{name},
    #{email},
    now()
)
ON DUPLICATE KEY UPDATE
    name = #{name},
    email = #{email},
    updateDate = NOW()
</insert>