[MySQL] last_insert_id() 사용시 주의

많은 개발자들의 Database 관련 코드를 보면 심심치 않게 위험한 요소들을 가지고 있다.

MySQL 를 다루면서 많은 실수를 하는 부분이 바로  Last_insert_id() 사용과 관련된 부분이다.

오늘은 잘못된 auto_increment 과 last_insert_id() 사용을 이야기 하려 한다.

1. auto_increment 

MySQL ( or MariaDB) 사용 시, 테이블은 InnoDB로 생성하고 PK를  auto_increment 로 설정 하는 경우가 많다.

성능면에서 보면 당연한 선택이다.

헌데 가끔 MySAM로 테이블을 생성 하는 경우가 있다. 

이 부분은 나중에 Engine을 설명할 기회가 있다면 그때 언급하자.

반드시 engine은 특별한 경우가 아니면 InnoDB로 생성하여야 한다.

2. last_insert_id()

last_insert_id 함수는 테이블의 마지막 auto_increment 값을 리턴한다.

간혹 이를 잘못 이해 해서  해당테이블에 insert 성공된 마지막 값이라고 설명하거나 인터넷에 게시된  글들을 보게 된다.

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement

last_insert_id()를 이해 하려면 트랜젝션을 같이 이해 해야 한다.

샘플코드를 보자

DROP TABLE IF EXISTS `last_insert_id_table`;

CREATE TABLE `last_insert_id_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `col` VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO last_insert_id_table(col)
VALUES('1row'),('2row'),('3row');


SELECT last_insert_id();
/*
last_insert_id()  
------------------
                 ? <----------------- last_insert_id() 값은?
*/

여러분들이 예상했던 결과는?

3이 아니라 1을 반환한다.

아래 쿼리문을 실행해보자

DROP TABLE IF EXISTS `last_insert_id_table`;

CREATE TABLE `last_insert_id_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `col` VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO last_insert_id_table(col)
VALUES('1row');

INSERT INTO last_insert_id_table(col)
VALUES('2row');

INSERT INTO last_insert_id_table(col)
VALUES('3row');


SELECT last_insert_id();
/*
last_insert_id()  
------------------
                 ? <----------------- last_insert_id() 값은?
*/

예상한 3이 나온다.

대략 두개의 쿼리문의 차이를 알것이다.

두개의 쿼리 모두 데이터는 정상적으로 에러 없이 insert 되었다.

왜 이런결과가 나올까?

LAST_INSERT_ID() 함수는 1개의 insert 쿼리에 대해서 성공시 마지막 auto_increment 값이다.

즉, INSERT INTO last_insert_id_table(col) VALUES('1row'),('2row'),('3row'); 쿼리는 1개이고 이에 대해서 auto_increment 은 1인 것이다.

대량의 데이터를 배치로 insert 하는 경우가 종종 발생하는데, 이 경우 위처럼 LAST_INSERT_ID()를 잘못 사용하는 오류가 없는지 확인하자.

이번 샘플은  transaction 이 포함되는 문제다.

DROP TABLE IF EXISTS `last_insert_id_table`;

CREATE TABLE `last_insert_id_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `col` VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

START TRANSACTION;
INSERT INTO last_insert_id_table(col)
VALUES('1row');
INSERT INTO last_insert_id_table(col)
VALUES('2row');
INSERT INTO last_insert_id_table(col)
VALUES('3row');

ROLLBACK;

SELECT last_insert_id();
/*
last_insert_id()  
----------------
                 ? <-----------------1번 : last_insert_id() 값은?
*/

INSERT INTO last_insert_id_table(col)
VALUES('4row');

SELECT last_insert_id();
/*
last_insert_id()  
------------------
                 ? <-----------------2번 : last_insert_id() 값은?
*/

1번에 리턴되는 LAST_INSERT_ID() 값은? 

2번에 리턴되는 LAST_INSERT_ID() 값은?

물론 테이블에 데이터는 '4row' 라는 1개의 데이터만 등록된다.

답은 

1번은 3

2번은 4 

가 나온다.

잘 이해하자... 

비록 rollback 으로 인해서 데이터 insert는 취소 되었지만, insert 쿼리는 정상적으로 수행되었고

이로 인해 auto_increment 값은 증가 한 것이다.

따라서 마지막 insert 쿼리에 의 해서 수행된 auto_increment 값 4가 된 것이다.

가끔 프로젝트를 진행 하다 보면 insert 된 데이터의 Key 값(auto_increment 값)을 트랜젝션과 무관하게 LAST_INSERT_ID()를 리턴하는 경우를 종종 본다.(매우 심각한 결과를 초래 할 수 있다)

---

[바부생각]

내 관점에서 보면 

1번의 값은 0, 2번의 값은 1로 나오고 

최종적으로 테이블에는 아래처럼 나왔으면 하는데

   id  col     

------  --------

     1  4row    

MySQL, MariaDB  내가 개발한 것도 아니니 어쩔수 없다.

그러나 반드시 개발시 확실하게 이를 주지 하지 않으면

버그를 찾는다고 생고생  할수 있다.

---