[SQL,MyBatis] INSERT ... ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE 

위 구문은 데이터를 삽입할 때 중복 데이터가 없으면 예정대로 삽입. 그런데 중복된 데이터가 있을 시 기존 중복데이터를 업데이트 시킬 수 있도록 하는 구문이다.

정확하게 말하자면, 중복키 충돌이 발생할 때 INSERT가 아니라 UPDATE문으로 전환되어 데이터 중복없이 인서트할 수 있도록한다.

따라서 그전에 앞서 반드시 Primary Key(기본키)나 Unique Key(고유키)가 있어야한다. 없으면 키 충돌의 기준이 없어 아예 작동되지 않기 때문이다.

 

 

정확하게 이해가 안되도 괜찮다. 아래 예시를 봐보자.

*예시

INSERT INTO user_score (user_id,score)
VALUES (1,10)
ON DUPLICATE KEY UPDATE score = score + VALUES(score)

 

 ->user_id =1인게 없다면 10점이 인서트되지만, 만약 1인 id가 있다면 기존 score에서 10점이 추가되는 업데이트문이 실행된다.

여기서 뭔가 낯선 것이 있을 것이다. 바로 UPDATE문에 쓰인 VALUES()다

이는 ON DUPLICATE KEY UPDATE 구문에서만 특별히 예외적으로 사용할 수 있는 값으로 앞의 INSERT 문에 사용된 값을 참조할 수 있게 한 전용 문법이다.

(그러나 MySQL 8.0.20 부터는 VALUES()대신 그냥 NEW. 키워드를 사용할 것을 권장하고 있다)

 

INSERT INTO daily_log (user_id,log_date,login_count)
VALUES (1,CURDATE(),1)
ON DUPLICATE KEY UPDATE login_count = login_count +1;

 

 -> 이 테이블은 user_id와 log_date에 묶어서 고유키가 걸려있다고 하자. 
그러면 이 SQL문은  user_id 칼럼에 1, log_date에 오늘날짜, login_count에 1 값이 인서트된다.

그런데 만약 이미 1번인 유저가 오늘 로그인한 기록이 있다면 인서트문이 아니라 업데이트문이 실행되어 login_count값만 2로 업데이트 되는 것이다.

 

 

---

*심화버전 (LAST_INSERT_ID()키워드와 함께 사용)

좀 더 심화된 버전으로 나아가보자.

해시태그 테이블이 있다.
한 사용자가 본인의 게시글(post_id=31)에 #STUDY를 추가한다.

그러면 tag테이블에 tag_id=94에 study가 추가 되었다고 하자.

그러면 post_tag 테이블에 post_id=31, tag_id=94로 인서트 되어야한다.

이때 다른 사용자도 본인의 게시글(post_id=32)에  #STUDY를 추가했다.

그러면 tag테이블에는 이미 id가 94인 STUDY가 또 추가될 필요가 없다. 그냥  그 id값을 받아 post_tag 테이블에 post_id=32, tag_id=94로 인서트하면 되는 상황이다.

이때는  INSERT ... ON DUPLICATE KEY UPDATE 구문과 LAST_INSERT_ID()키워드를 같이 쓰면 된다.

 

마이바티스에서 사용예시를 한번 봐보자.

<insert id="insertNewTag"
            parameterType="dto"
            useGeneratedKeys="true"
            keyProperty="tagId">
        INSERT INTO tag (tag_name)
        VALUES (#{tagName})
        ON DUPLICATE KEY UPDATE tag_id = LAST_INSERT_ID(tag_id);
    </insert>

 

 

 

• 첫번째 케이스

tag_name='STUDY'가 처음 등장하면 인서트가 되고 auto_increment설정이 되어있어 tag_id=94로 인서트되었다.
이때 dto.tagId에는 94로 리턴받을 것이다.

•  두번째 케이스

tag_name='STUDY'가 이미 tag_id=94로 존재하여 중복키가 충돌하였다.(tag_name에 유니크키가 기존에 걸려있다)

충돌이 발생함으로써 ON DUPLICATE KEY UPDATE구문이 실행된다.

tag_id = LAST_INSERT_ID(tag_id)로 업데이트 된다.

원래 LAST_INSERT_ID()는 현재 세션에서 가장 최근에 생성된 auto_increment값을 반환한다.

그런데 LAST_INSERT_ID(tag_id)는 현재 세션의 LAST_INSERT_ID()값을 tag_id로 덮어씌우고 tag_id를 리턴한다.

즉 tag_id인 94가 리턴되는데 tag_id=94로 업데이트 하란말이지만 원래 94니까 사실상 db는 변하지않는다.

그리고 useGeneratedKeys="true", keyProperty="tagId" 설정으로 인해 dto.tagId에 94로 리턴받을 수 있게 된다.

 

---

 

*IGNORE 키워드

 

그런데 그냥 중복데이터면 UPDATE를 안하고 그냥 인서트도 안되게 하고 싶다면 

그 때 사용할 수 있는 키워드는 IGNORE이다.

 

INSERT IGNORE은 도중 에러가 발생하면 그냥 스킵이 되는 것이다.

중복에러가 발생하면 업데이트로 수정해야한다면 INSERT ... ON DUPLICATE KEY UPDATE구문을 쓰면 되는 것이고

그게 아니라 중복에러가 발생하면 그냥 스킵해야한다면 INSERT IGNORE 구문을 사용하면 된다.