[mysql] merge into..?

환경: mysql 5.7

oracle에는 merge into 가 있어 값이 있을때는 수정하고 없을때는 추가하여 pk 없음 에러 혹은 중복키 저장 에러가 나지 않고 작업을 진행할 수 있었는데, mysql에는 같은 기능을 하는 쿼리가 있는지 알아본다.

oracle

MERGE INTO [UPDATE되거나 INSERT 될 테이블]
USING [MERGE를 진행하고 싶은 대상, 조인, 서브쿼리도 사용 가능]
ON [조건]
WHEN MATCHED THEN [조건에 맞는 데이터가 있을 시 실행할 구문, UPDATE, DELETE]
WHEN NOT MATCHED THEN [조건에 맞는 데이터가 없을 시 실행할 구문, INSERT]
;

 

mysql

INSERT INTO 테이블 (
	[콜롬들...]
)VALUES(
	[값들...]
)
ON DUPLICATE KEY UPDATE
	[PK값들..]

예시

category 테이블 pk가 service_code, category_code 인 경우, category_name을 추가하거나 수정하려고 한다면..

CREATE TABLE `category` (
  `service_code` varchar(20) NOT NULL COMMENT '서비스 코드',
  `category_code` varchar(20) NOT NULL COMMENT '카테고리 코드',
  `category_name` varchar(20) NOT NULL COMMENT '카테고리 명',
  PRIMARY KEY (`service_code`,`category_code`)

 

INSERT INTO category 
	(service_code, category_code, category_name) 
VALUES
	('admin', 'character', '캐릭터')
ON DUPLICATE KEY UPDATE 
	service_code = 'admin' , category_code = 'character'
;

위처럼 쓸 수도 있고 아래처럼 작성해도 동일하다.

INSERT INTO category 
	(service_code, category_code, category_name) 
VALUES
	('admin', 'character', '캐릭터')
ON DUPLICATE KEY UPDATE 
	service_code = VALUES(service_code), category_code = VALUES(category_code)
;

날려보면 아래처럼 성공하는 로그가 찍힌다.

하지만 실제 row는 수정되지 않았다! 그렇다고 신규 row가 생기지도 않았다.

참고로 기존 row를 지우고 날리면 1로 결과가 떨어지고 신규 row가 추가된다. 허나 이 상태에서 category name을 바꾸고 날려보면 1로 떨어져도 반응이 없다..

 

쿼리를 아래처럼 수정하면 

INSERT INTO category (service_code, category_code, category_name) 
VALUES ('admin', 'character', '캐릭터797')
ON DUPLICATE KEY UPDATE 
    service_code = VALUES(service_code),
    category_code = VALUES(category_code),
    category_name = VALUES(category_name);

기존 row가 있을 경우 결과가 2로 떨어지고 데이터도 수정된 것을 확인할 수 있다. 없을 경우 1로 떨어지고 추가된다.

왜?!???

 

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

결과에 대해 더 찾아보니 아래와 같이 내린다는 것을 알게 되었다.

• 1: 신규 row로 insert
• 2: 기존 row update
• 0: 변경 없음

---

 참고

mysql 5.7의 경우

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

MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

 

mysql 8부터는 아래 문서를 확인해야 한다.

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

MySQL :: MySQL 8.0 Reference Manual :: 15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement