INSERT 후 방금 만든 행을 다시 SELECT 하지 않아도 되는 RETURNING

문제 발생

주문을 넣고 나서 생성된 id와 DB가 채운 기본값(생성 시각, 상태)이 필요해서, 삽입 직후 한 번 더 조회하고 있었습니다.

INSERT INTO orders (user_id, amount) VALUES (?, ?);
SELECT id, created_at, status FROM orders WHERE id = LAST_INSERT_ID();

왕복이 두 번인 것도 그렇지만, 두 문장이 같은 트랜잭션·같은 커넥션에서 실행된다는 보장을 애플리케이션 쪽에서 계속 신경 써야 했습니다. 커넥션 풀에서 두 번째 쿼리가 다른 커넥션으로 나가면 LAST_INSERT_ID()는 엉뚱한 값을 돌려줍니다.

원인 분석

LAST_INSERT_ID()는 커넥션 단위 상태입니다. 같은 커넥션에서 직전에 넣은 AUTO_INCREMENT 값을 돌려주는 함수라, 커넥션이 달라지면 의미가 없고, 여러 행을 한 번에 넣으면 첫 행의 id만 알려줍니다. ORM이 커넥션을 자동으로 관리해 주는 환경에서는 이 전제가 조용히 깨지기 쉽습니다.

MariaDB는 10.5.0부터 INSERT ... RETURNING 을 지원합니다. 삽입된 행들로 이루어진 결과 집합을 그 자리에서 돌려주므로, 두 번째 조회도 커넥션 가정도 필요 없어집니다.

해결 방안

1. 삽입과 조회를 한 문장으로 합칩니다.

INSERT INTO orders (user_id, amount)
VALUES (?, ?)
RETURNING id, created_at, status;

2. 여러 행을 넣어도 전부 돌려받습니다. LAST_INSERT_ID()로는 불가능했던 부분입니다.

INSERT INTO tags (name) VALUES ('css'), ('sql'), ('java')
RETURNING id, name;

3. 표현식도 담을 수 있습니다. 컬럼뿐 아니라 가상 컬럼, 별칭, 문자열·날짜·수치 함수, 스토어드 함수까지 쓸 수 있습니다. 다만 집계 함수는 쓸 수 없고, 여러 행이나 여러 컬럼을 돌려주는 서브쿼리도 쓸 수 없습니다.

INSERT INTO orders (user_id, amount)
VALUES (?, ?)
RETURNING id, amount, amount * 0.1 AS fee;

4. DELETE에도 같은 절이 있습니다. 지우기 전에 무엇을 지웠는지 확인해야 하는 배치 정리 작업에서 특히 유용합니다.

DELETE FROM sessions WHERE expires_at < NOW()
RETURNING id, user_id;

5. 이식성은 확인하고 씁니다. RETURNING은 MariaDB와 PostgreSQL에는 있지만 MySQL에는 없습니다. 두 엔진을 함께 지원해야 하는 코드라면 쿼리 빌더 계층에서 갈라줘야 합니다.

공식 문서

• MariaDB: INSERT ... RETURNING
• MariaDB: DELETE ... RETURNING
• MySQL: LAST_INSERT_ID()