MySQL on duplicate key update & affected rows

 

오늘은 upsert를 위해 많이 사용하는 on duplicated key update와 이 때 상황에 따른 affected rows를 알아보려고 합니다.

 

on duplicate key update는 

중복되는 고유키(Unique Key)가 없으면 insert, 고유키가 있으면 update를 하는 명령입니다.

(고유키이므로 당연히 PK도 포함입니다.)

INSERT INTO TABLE(A, B, C)
VALUES
(
	X,
	Y,
	Z
)
ON DUPLICATE KEY UPDATE A = X, C = Z

 

위 구문은 다음을 의미합니다.

중복되는 키가 없다면 insert구문을 실행하고, 중복되는 키가 있다면 update 구문을 실행하라.

 

이 때 해당 테이블에 설정된 고유키의 수와 관계 또한 중요하지만 이것은 다음에 알아보도록 하고 이 때 영향을 받는 행(affected rows)에 대해 알아보겠습니다. 

 

MySQL 공식 문서에 의하면, affected rows는 영향을 받은 row 수이고, on duplicate key update에서는 다음과 같은 정수를 가질 수 있습니다. 

• 기존 행이 현재 값으로 설정되는 경우 affected rows는 0 (설정에 따라 1이 될 수도 있습니다)
• 중복되는 키가 없어서 insert 구문이 수행되면 affected rows는 1
• 중복되는 키가 있어서 update 구문이 수행되면 affected rows는 2
• (물론 오류 발생시 음수가 발생할 수 있습니다.)

(위에서 affected rows가 영향을 받은 row 수라고 되어있지만, 영향을 받았다는 정의가 매우 모호합니다. 

여러가지 테스트 추정상으로는 정말 데이터가 변경이 시도된 경우로 보고 있습니다. affected rows를 어떤 수행결과의 판단 근거로 사용할 때는 버전에 맞는 docs를 보고 정확히 어떤 케이스에 어떤 정수가 나오는지 확인할 필요가 있습니다.) 

 

다시 돌아와서, update시에 affected rows가 2가 되는 이유는

내부적으로 insert를 수행한 후 중복이 발생되어 update를 수행하기 때문입니다. 

 

보통 쿼리 수행 후 아래와 같은 구문으로 성공/실패 여부를 판별하는 경우가 왕왕 있습니다. 

if (accectedRows == 1) // 성공
if (affectedRows < 1 || affectedRows > 1) // 실패

 

on duplicate key update를 사용한다면 affected rows가 2인 경우도 정상 케이스이므로 affected rows로 쿼리의 성공/실패 여부를 확인한다면 이 부분을 놓치면 안됩니다. 

 

그리고 중간에 얘기한 것처럼, affected rows를 판단의 근거로 사용한다면

휴리스틱에 의한 것이 아닌 공식 문서를 보고 검증을 수행한 후 활용하는 것이 좋겠습니다. 

 

 

+) 분산시스템에는 글로벌테이블 개념이 있고, 이 글로벌 테이블은 노드간 같은 데이터를 가짐으로써 달성됩니다. 

그러므로 노드간 데이터 복제가 필요하며 이 때 구문복제를 수행하는 경우가 있습니다.

그리고 이런 경우는 같은 구문을 수행했을 때 리턴되는 affected rows를 통해 노드간 복제에 대한 일관성을 확인하곤 합니다. 

on duplicate key update를 사용하여 데이터를 update할 때 update 값으로 변경되는 값(ex. sysdatetime)을 사용한다면, 

이 구문이 복제될 때 변경되는 값에 의해 affected rows가 상황에 따라 달라져 문제가 발생할 수 있습니다. 

 

텍스트로는 한계가 있어서 위 케이스에 대해 그림을 그려보았습니다.

그림도 딱히 이해가 잘 되진 않을수도 ㅎㅎㅎ~~

 

도움

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

https://dev.mysql.com/doc/c-api/8.3/en/mysql-affected-rows.html