INSERT와 다중 행 등록

INSERT는 테이블에 값을 넣는 가장 단순한 DML처럼 보이지만, 스키마가 바뀔 때 가장 쉽게 깨지는 문장이기도 합니다. 열 목록을 생략하면 값의 순서가 테이블에 열이 정의된 순서에 묶이고, 어느 열을 일부러 비워 두었는지도 알기 어렵습니다. INSERT에서 중요한 것은 값의 개수보다, 각 값이 어느 열에 어떤 뜻으로 들어가는가입니다.

이 글에서는 회원 두 명과 상태가 다른 게시글 몇 개를 넣어, 이어지는 UPDATE와 DELETE, 제약 오류 진단의 실습에 쓸 데이터를 만듭니다. 기본 키는 생략해 서버가 만들게 하고, 시각은 UTC 기준으로 명시하며, 공개 상태의 게시글에는 1장의 CHECK에 맞게 게시 시각을 함께 넣습니다. 앞 글에서 request_key를 더하고 제약을 강화한 스키마를 전제로 합니다.

열의 순서에 기대는 INSERT

테이블에 열이 늘어난 뒤에도 열 목록 없는 INSERT를 그대로 쓴다고 해 봅시다. 아래 문장은 posts의 열이 다섯 개일 때 쓰던 모양 그대로, 값 다섯 개를 열 이름 없이 정의된 순서에 맞춰 넣습니다. 첫 값 NULL은 AUTO_INCREMENT인 id에 번호를 만들게 하려는 것입니다.

INSERT INTO posts VALUES (
  NULL,
  1,
  'GROUP BY 복습',
  50,
  '2026-07-04 09:00:00'
);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

posts에는 열이 훨씬 많으므로 MySQL은 다섯 개의 값을 열에 맞출 수 없어 오류를 냅니다. 이렇게 바로 오류가 나는 것은 오히려 다행입니다. 더 위험한 것은 열의 수와 타입이 우연히 맞아, 값이 엉뚱한 열에 조용히 저장되는 경우입니다. 그래서 INSERT마다 넣을 열의 이름을 적고, 생략한 열은 기본값이나 NULL을 일부러 쓰겠다는 뜻으로 남겨 둡니다. AUTO_INCREMENT 열에 NULL을 넣어 번호를 만들게 하는 관습도 있지만, 그 열을 목록에서 빼는 편이 뜻이 분명합니다.

생략과 NULL은 다르다

INSERT INTO t (a, b, c) VALUES (...)에서 VALUES의 행마다 값의 개수와 순서가 열 목록과 같아야 합니다. 목록에서 뺀 열은 기본값이 있으면 그 값을, 없으면 NULL을 받는데, NULL을 허용하지 않는 열이라면 오류가 납니다. 반면 NULL을 직접 적으면 "값이 없다"는 뜻을 그대로 전하므로, NOT NULL 열에서는 기본값이 있어도 오류가 납니다.

posts로 보면, status를 생략하면 기본값 DRAFT가 들어가지만 status에 NULL을 적으면 오류 1048(Column 'status' cannot be null)이 납니다. published_at은 NULL을 허용하므로 생략하든 NULL을 적든 NULL이 되고, 기본값이 없는 필수 열 content는 생략해도 NULL을 적어도 오류입니다. id처럼 AUTO_INCREMENT인 열만은 생략하든 NULL을 적든 새 번호가 만들어집니다.

INSERT와 기본값은 표준 SQL의 개념이지만, AUTO_INCREMENT와 LAST_INSERT_ID()는 MySQL의 기능입니다. 여러 행을 한 번에 넣을 때 만들어진 번호가 연속이라고 가정하면 안 됩니다. MySQL 8.4의 INSERT에는 넣은 행을 돌려주는 RETURNING 절이 없으므로, 한 행을 넣고 그 번호를 받거나, 이메일처럼 유일한 키로 방금 넣은 행을 다시 찾습니다.

더 깊이: AUTO_INCREMENT 번호에 빈자리가 생기는 이유

InnoDB는 자동 번호를 한 번 배정하면 되돌리지 않습니다. 문장이 도중에 오류로 끝나든 트랜잭션을 롤백하든, 이미 배정한 번호는 다시 쓰지 않으므로 id에는 빈 번호가 생길 수 있습니다. 또 id를 직접 적어 지금의 카운터보다 큰 값을 넣으면 카운터가 그 값으로 올라가고, 다음 자동 번호는 그 뒤부터 이어집니다. 그래서 id의 최댓값을 행 수로 읽거나, 빈 번호를 삭제의 흔적으로 해석하면 안 됩니다.

번호를 나눠 주는 방식은 innodb_autoinc_lock_mode가 정합니다. MySQL 8.0부터 기본값은 2(interleaved)이고, 그 전의 기본값은 1(consecutive)이었습니다. 문장 기반 복제는 같은 문장을 다시 실행했을 때 같은 번호가 나와야 해서 정해진 순서로 배정하는 방식이 필요했지만, 지금의 기본인 행 기반 복제는 실행 순서에 영향을 받지 않기 때문입니다. 이 모드에서 실행 중인 문장이 모두 넣을 행 수를 미리 아는 단순 삽입(simple insert)이라면, 한 문장이 받은 번호에는 빈자리가 생기지 않습니다. 다만 일부 행에만 id를 직접 적은 혼합 삽입(mixed-mode insert)은 예외이고, INSERT ... SELECT처럼 행 수를 미리 모르는 대량 삽입(bulk insert)은 다른 INSERT가 동시에 실행되면 번호 사이에 빈자리가 생길 수 있습니다.

카운터를 보관하는 방식도 버전에 따라 다릅니다. MySQL 5.7까지는 카운터를 메모리에만 두었다가, 서버를 다시 시작한 뒤 첫 INSERT에서 SELECT MAX(id) ... FOR UPDATE에 해당하는 값으로 다시 정했습니다. 8.0부터는 카운터가 바뀔 때마다 redo 로그에 기록하고 체크포인트마다 데이터 사전에 저장하므로 재시작한 뒤에도 값이 이어집니다. 다만 MySQL 8.4 매뉴얼은 서버가 예기치 않게 종료된 경우에는 이미 배정한 번호가 다시 쓰이지 않는다고 보장할 수 없다고 적고 있습니다.

회원과 게시글을 넣는다

먼저 회원을 한 명씩 넣고, 방금 만들어진 기본 키를 변수에 담아 둡니다. LAST_INSERT_ID()는 지금 연결에서 가장 최근의 INSERT가 자동으로 만든 첫 번호를 돌려주므로, 회원을 한 행씩 넣으면 바로 그 회원의 번호입니다. @sora_id 같은 사용자 변수(user variable)는 한 연결 안에서만 값을 기억하므로, 회원부터 게시글까지 같은 연결에서 차례로 실행해야 합니다.

INSERT INTO members (email, password_hash, name, status, created_at)
VALUES ('[email protected]', '{demo}hash-sora', '김소라', 'ACTIVE', '2026-07-02 01:00:00');
SET @sora_id = LAST_INSERT_ID();

INSERT INTO members (email, password_hash, name, status, created_at)
VALUES ('[email protected]', '{demo}hash-doyun', '이도윤', 'ACTIVE', '2026-07-03 02:00:00');
SET @doyun_id = LAST_INSERT_ID();

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at, request_key
) VALUES
(@sora_id, 'SELECT 실습', '필요한 열만 조회하는 방법을 정리합니다.', 'PUBLISHED', 45,
 '2026-07-04 01:00:00', '2026-07-04 01:45:00', '2026-07-04 01:45:00', 'post-sora-001'),
(@sora_id, 'JOIN 예습', '회원과 게시글의 관계를 게시 전에 정리합니다.', 'DRAFT', 0,
 '2026-07-05 01:00:00', '2026-07-05 01:00:00', NULL, 'post-sora-002'),
(@doyun_id, 'WHERE 조건', '조건식과 경계값을 예제로 설명합니다.', 'PUBLISHED', 35,
 '2026-07-05 02:00:00', '2026-07-05 02:35:00', '2026-07-05 02:35:00', 'post-doyun-001');

회원의 INSERT가 한 행 성공했는지 확인한 뒤에만 다음 SET으로 넘어갑니다. 이메일이 겹쳐 INSERT가 실패했다면 거기서 멈춥니다. MySQL 8.4 문서에 따르면 앞 문장이 오류를 냈을 때 LAST_INSERT_ID()의 값은 정해져 있지 않으므로, 그 값을 이번 회원의 번호로 쓸 수 없습니다. 이미 저장된 회원을 다시 쓰고 싶다면 이메일로 그 행을 찾아 의도한 회원이 맞는지 확인하는 절차를 따로 둡니다.

게시글 세 행은 같은 열 모양을 가지며, 초안에는 published_at에 NULL을, 공개 글에는 실제 시각을 넣었습니다. 목록에서 뺀 version에는 기본값 0이 들어갑니다. 회원의 번호를 변수로 받는 대신 VALUES 안에 (SELECT id FROM members WHERE email = '[email protected]')처럼 값 하나만 돌려주는 SELECT를 둘 수도 있는데, 이를 스칼라 서브쿼리라고 하며 6장에서 다룹니다.

넣은 뒤 분포를 확인한다

마지막 문장이 몇 행에 영향을 주었는지만 보지 말고, 회원마다 게시글이 몇 개 들어갔는지와 상태의 조합이 맞는지 조회합니다. 아래의 LEFT JOIN과 GROUP BY는 4장과 5장에서 배우는 문법이므로, 지금은 결과만 확인합니다.

SELECT
  m.email,
  COUNT(p.id) AS post_count,
  SUM(p.status = 'PUBLISHED') AS published_count,
  SUM(p.status = 'DRAFT') AS draft_count,
  SUM(p.published_at IS NULL) AS null_published_at_count
FROM members AS m
LEFT JOIN posts AS p ON p.author_id = m.id
GROUP BY m.id, m.email
ORDER BY m.email;

SELECT COUNT(*) AS broken_rows
FROM posts
WHERE (status = 'PUBLISHED' AND published_at IS NULL)
   OR (status = 'DRAFT' AND published_at IS NOT NULL);

결과 가운데 이번에 넣은 두 회원의 행은 다음과 같아야 하고, broken_rows는 0이어야 합니다. [email protected]처럼 앞에서 넣은 회원의 행은 1장의 연습 문제를 실행했는지에 따라 수가 다릅니다.

email             | post_count | published_count | draft_count | null_published_at_count
[email protected] | 1          | 1               | 0           | 0
[email protected]  | 2          | 1               | 1           | 1

게시글이 없는 회원의 행을 읽을 때는 주의해야 합니다. COUNT(p.id)는 그 회원을 0으로 세지만, LEFT JOIN이 만든 행의 p.status는 NULL이므로 SUM(p.status = 'PUBLISHED')는 NULL이 되고, p.published_at IS NULL은 참이므로 SUM(p.published_at IS NULL)은 1이 됩니다. 이 집계 열들을 모두 실제 게시글의 수로 읽으면 안 되는 이유입니다.

여러 행을 한 문장으로 넣을 때

VALUES에 여러 행을 적은 INSERT 한 문장은 서버와의 왕복을 줄여 줍니다. InnoDB에서 IGNORE 없이 실행한 이 문장 가운데 한 행이라도 제약을 어기면, 그 문장이 넣은 행 전체가 함께 취소됩니다. 그래서 세 게시글 가운데 둘째 행만 본문이 비어 있었다면 세 행 모두 들어가지 않습니다. 다만 이것은 그 문장 하나의 원자성일 뿐, 앞에서 이미 성공한 다른 문장까지 취소하지는 않습니다. 여러 문장을 하나로 묶는 것은 8장 2편의 트랜잭션이 맡습니다.

문장 하나가 통째로 성공하거나 실패한다고 해서 재시도의 문제까지 사라지지는 않습니다. 네트워크가 끊겨 응답을 받지 못한 애플리케이션이 같은 INSERT를 다시 보내면 같은 게시글이 두 번 들어갈 수 있습니다. 앞 글에서 더한 request_key가 바로 이 경우를 위한 것이어서, 같은 요청은 재시도해도 같은 키를 보내고 uq_posts_author_request가 두 번째 저장을 거부합니다. 한 문장 안에 같은 이메일의 회원을 두 번 적어도 UNIQUE에 걸려 문장 전체가 실패합니다.

INSERT IGNORE는 이런 오류를 경고로 낮추고 문제가 된 행만 건너뜁니다. 데이터를 잃는 것을 명시적으로 허용하는 경우가 아니라면 쓰지 않습니다. 아주 큰 데이터를 가져올 때는 거대한 INSERT 하나보다, 본 테이블로 옮기기 전의 임시 적재 테이블(staging table)에 먼저 넣어 확인하고, 배치로 나누어 넣고, 거부된 행을 기록하는 절차가 필요합니다. 넣는 속도를 높이려고 제약을 끄지 말고, 배치의 크기와 트랜잭션의 길이를 조절합니다.

시각의 뜻도 구별해야 합니다. 기본값 CURRENT_TIMESTAMP는 서버가 문장을 실행한 시각이므로, 과거의 게시글을 옮겨 올 때 그 기본값에 맡기면 게시글이 실제로 쓰인 시각 대신 옮긴 시각이 저장됩니다. 그래서 이 글의 예제는 created_at을 명시했습니다. 빈 문자열의 이메일은 NOT NULL을 만족하므로, 형식과 공백을 검사하는 규칙도 따로 필요합니다. 예제나 기록에 실제 개인정보와 비밀값을 넣지 않는 것도 처음부터 지킬 습관입니다.

여기서 만든 데이터는 2장의 나머지 글에서 UPDATE와 DELETE를 실습하고 제약 오류를 진단할 때 이어 씁니다. 3장부터 5장까지는 결과를 고정하려고 따로 만든 실습 테이블을 쓰므로 이 데이터에 기대지 않습니다. 실습을 다시 할 때는 테이블을 무작정 비우지 말고, 이 글의 INSERT와 위의 확인 결과를 기준으로 삼습니다.

정리

• INSERT에는 열 목록을 적어 값이 테이블의 정의 순서에 묶이지 않게 합니다.
• 열을 생략하면 기본값이나 NULL을 받고, NULL을 적으면 값이 없다는 뜻이 그대로 전해지므로 NOT NULL 열에서는 오류가 납니다.
• LAST_INSERT_ID()와 사용자 변수는 연결마다의 값이며, 앞 문장이 실패했다면 LAST_INSERT_ID()를 믿지 않습니다.
• 여러 행을 한 문장으로 넣으면 그 문장은 통째로 성공하거나 실패하지만, 재시도의 중복은 요청 키 같은 별도의 장치로 막습니다.
• 넣은 뒤에는 회원마다의 수와 상태의 조합을 조회해 확인하고, LEFT JOIN이 만든 NULL 행의 집계를 주의해 읽습니다.

연습 문제

1. 서브쿼리로 부모를 찾아 넣기

새 회원 [email protected]을 넣고, 그 회원의 초안 하나와 공개 글 하나를 한 문장으로 넣어 보세요. 회원의 번호를 숫자로 가정하지 말고, 상태와 게시 시각의 조합도 지키세요.

INSERT INTO members (email, password_hash, name, created_at)
VALUES ('[email protected]', '{demo}hash-soo', '김수', '2026-07-06 00:00:00');

INSERT INTO posts (author_id, title, content, status, created_at, updated_at, published_at, request_key)
VALUES
((SELECT id FROM members WHERE email = '[email protected]'),
 '서브쿼리 초안', '스칼라 서브쿼리로 작성자를 찾습니다.', 'DRAFT',
 '2026-07-06 01:00:00', '2026-07-06 01:00:00', NULL, 'post-soo-001'),
((SELECT id FROM members WHERE email = '[email protected]'),
 'SELECT 공개', '공개 글은 게시 시각을 함께 저장합니다.', 'PUBLISHED',
 '2026-07-06 02:00:00', '2026-07-06 02:55:00', '2026-07-06 02:55:00', 'post-soo-002');

2. 다른 연결의 변수

연결 A에서 sora를 넣고 @sora_id에 번호를 저장했습니다. 새로 연 연결 B에서는 변수를 정하지 않고 본문의 게시글 INSERT를 실행했습니다. B는 A가 저장한 번호를 쓸 수 있나요?

3. 생략과 NULL을 비교하기

다른 필수 열은 모두 채우고 요청 키는 서로 다르게 한 채로, (가) status를 목록에서 뺀 INSERT와 (나) status에 NULL을 적은 INSERT, (다) published_at에 NULL을 적은 초안의 INSERT를 실행하면 각각 어떻게 되나요?

다음 글 UPDATE와 DELETE 안전성에서는 바꾸거나 지울 행을 SELECT로 미리 보고, 트랜잭션 안에서 영향받는 행의 수를 확인하며 범위를 통제하는 방법을 다룹니다.