회원가입   로그인
IMG-LOGO
공지사항 :

SQL의 INSERT 구문

lmkfox - 2025-09-25 06:40:59 9 Views 0 Comment

1) 개요 — 역할과 권한

  • INSERT는 테이블에 새 행(row)을 추가하는 DML 문입니다.

  • 테이블에 INSERT하려면 그 테이블에 대한 INSERT 권한이 필요합니다. 

2) 기본 형태(문법) — 여러 변형

  1. VALUES 방식 (단일/복수 행)

INSERT INTO tbl_name (col1, col2) VALUES (v1, v2);
INSERT INTO tbl_name (a,b,c)
VALUES (1,2,3), (4,5,6), (7,8,9);  -- 다중 행 삽입

  1. INSERT … SELECT (다른 테이블의 결과를 삽입)

INSERT INTO tgt (a,b,c)
SELECT x,y,z FROM src WHERE ...;

  1. INSERT … SET (MySQL 특유 문법)

INSERT INTO users SET username='kim', email='k@example.com';

  1. INSERT … TABLE (한 테이블에서 다른 테이블로 삽입)

INSERT INTO tgt TABLE src;

  • INSERT는 컬럼 리스트를 생략하면 테이블의 모든 컬럼에 값을 제공해야 하고, 컬럼값이 없으면 컬럼의 기본값(default) 또는 암묵적 기본값이 사용됩니다. (INSERT INTO tbl () VALUES (); 로 모든 컬럼에 default를 넣을 수도 있습니다.) 

(자세한 문법·옵션 — MySQL 매뉴얼의 INSERT 문서 참고). 

3) 중복/충돌 처리 (Upsert 계열)

MySQL에서는 여러 방식으로 “없으면 삽입, 있으면 갱신” 같은 동작을 처리합니다.

  1. INSERT … ON DUPLICATE KEY UPDATE

    • 기본 동작: 삽입하려는 행이 PRIMARY KEY 또는 UNIQUE 제약과 충돌하면 UPDATE를 수행합니다.

    • 예:

INSERT INTO products (sku, qty, price)
VALUES ('A123', 1, 10.0)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), price = VALUES(price);

  • MySQL 8.x 계열에서는 VALUES() 함수 사용이 점진적으로 deprecated 되어, VALUES(...) AS alias 방식으로 값에 대한 별칭(alias) 을 주고 alias.col로 참조하는 문법 권장(즉 ... VALUES (...) AS new ON DUPLICATE KEY UPDATE col = new.col)합니다. 

  • ON DUPLICATE KEY UPDATE의 affected-rows 규칙: 삽입되면 1, 기존 행이 업데이트되면 2, 업데이트했지만 값이 실제로 바뀌지 않으면 0(특수 플래그 사용 시 변경 가능). 

  1. INSERT IGNORE

    • 무시 가능한 오류(예: 중복키, 데이터 경계 위반 등)를 경고로 바꾸고 문제가 되는 행은 건너뜁니다. 전체 문은 계속 실행됩니다. 

  2. REPLACE INTO

    • 중복 키가 있으면 기존 행을 삭제하고 새 행을 삽입(DELETE + INSERT와 유사). 부작용(트리거 작동, 삭제로 인한 FK 영향 등)이 있으므로 주의. 

4) 반환값 / 확인 방법

  • LAST_INSERT_ID(): AUTO_INCREMENT 컬럼이 있을 때 생성된 첫번째(문맥상) 자동증가 값을 얻습니다. 다중 행 삽입의 경우 첫 번째로 생성된 id를 반환합니다. 

  • ROW_COUNT() / mysql_affected_rows(): 문으로 영향받은 행 수. ON DUPLICATE KEY UPDATE 사용 시 행당 영향값(1/2/0) 규칙이 적용됩니다. 

  • mysql_info() / SHOW WARNINGS: INSERT 실행 후 Records: N Duplicates: M Warnings: K 같은 요약 정보와 상세 경고 확인 가능. 

5) 트랜잭션·원자성·autocommit

  • MySQL은 기본적으로 autocommit=ON 이므로, 트랜잭션을 명시적으로 시작하지 않으면 각 DML 문은 독립적으로 커밋됩니다. START TRANSACTION으로 여러 DML을 하나의 원자적 단위로 묶을 수 있습니다. (문장 단위로는 기본적으로 원자적: 문장 실행 중 에러가 나면 그 문장은 롤백됩니다.) 

6) 성능 최적화(실전 팁)

  1. 가능하면 여러 행을 한 번에 전송: 다중-VALUES 리스트는 네트워크 왕복·파싱 오버헤드 절감.

  2. 대량 적재는 LOAD DATA INFILE가 가장 빠름 (CSV → 테이블). LOCAL 옵션은 클라이언트 측 파일 읽기 기능과 보안 차이 있음. 

  3. 트랜잭션으로 묶기: 다수의 INSERT를 하나의 트랜잭션(COMMIT 한 번)으로 처리하면 속도 향상. 

  4. 인덱스 비용 고려: 대량 INSERT 시 인덱스 수가 많으면 삽입 비용이 크게 증가. 가능하면 배치 전에 인덱스 제거 후 재생성하거나, 작은 배치 단위로 삽입. (또는 LOAD DATA 활용) 

  5. 서버 파라미터/옵션: innodb_flush_log_at_trx_commit, binlog_format 등 설정에 따라 퍼포먼스·내구성이 달라짐 — 운영환경에서는 신중 조정.

    (정식 가이드: MySQL 매뉴얼의 INSERT 최적화 항목 참고). 

7) 대량삽입/배치 전략(권장 패턴)

  • LOAD DATA INFILE → 빠름(가능하면 이 방법 사용). 

  • 또는 INSERT 다중-VALUES를 적당한 배치 크기(예: 1,000 ~ 50,000 행, 시스템·행 크기에 따라 조정)로 나눠 전송.

  • 대량작업 시 외래키 검사 일시 중지(SET FOREIGN_KEY_CHECKS=0) 또는 유니크 체크 비활성을 고려할 수 있으나 데이터 무결성/복구 계획을 반드시 검토해야 함.

  • 온라인 프로덕션에서는 pt-online-schema-change/gh-ost 같은 도구로 스키마 변경과 병행 삽입 처리.

(성능 튜닝은 테이블 구조·하드웨어·버전마다 달라 테스트 필수). 

8) Prepared statement & 보안(권장)

  • 항상 파라미터화(Prepared Statements) 해서 SQL 인젝션 방지. 대부분의 DB 드라이버에서 바인딩 사용 권장. MySQL의 서버측 prepared문과 클라이언트 라이브러리의 prepared 기능을 모두 지원합니다. 

예 — PHP (PDO):

$stmt = $pdo->prepare("INSERT INTO users (username,email) VALUES (?, ?)");
$stmt->execute([$username, $email]);
$id = $pdo->lastInsertId();

예 — Python (mysql-connector-python / PyMySQL):

cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))
conn.commit()
print(cur.lastrowid)  # 또는 conn.insert_id()

9) 에러/경고 처리 · 디버깅

  • INSERT IGNORE는 오류를 경고로 바꿔 건너뜁니다(원인 기록은 WARNINGS). SHOW WARNINGS;로 확인. 

  • 중복키/충돌을 처리하려면 ON DUPLICATE KEY UPDATE가 더 명확한 선택. (REPLACE는 행 삭제-삽입의 부작용 주의) 

  • SHOW ERRORS / SHOW WARNINGS / 드라이버의 예외(예: PDOException)를 통해 상세 원인 파악.

10) 몇 가지 자주 묻는 질문(FAQ)

  • 여러 행 INSERT는 모두 성공해야 하나요?

    • 단일 INSERT ... VALUES (...), (...) 문장은 문장 전체가 하나의 작업으로 처리됩니다. 에러 발생 시 그 문장 전체가 실패(롤백)됩니다(autocommit 모드일 때 문장 단위로 처리). 트랜잭션 안에서 여러 문장을 묶으면 원하는 원자성 확보 가능. 

  • 다중 행 삽입에서 LAST_INSERT_ID()는 어떤 값을 반환하나요?

    • 다중 행 삽입 시 LAST_INSERT_ID()는 “첫 번째”로 생성된 AUTO_INCREMENT 값을 반환합니다(즉 배치의 첫 id). 

  • MySQL에 RETURNING 절 있나요?

    • MySQL(표준 8.0 계열 기준)은 PostgreSQL/Oracle처럼 INSERT ... RETURNING을 네이티브로 지원하지 않습니다(따라서 삽입된 값을 바로 반환하려면 LAST_INSERT_ID() 또는 트랜잭션 내에서 SELECT를 별도로 실행해야 함). 일부 다른 DBMS에만 존재하는 기능입니다. 

11) 실용 예제 모음

  1. 단일 행

INSERT INTO users (username, email, created_at)
VALUES ('yoon', 'y@example.com', NOW());

  1. 다중 행 (배치)

INSERT INTO events (name, happened_at)
VALUES 
  ('e1', '2025-09-01'),
  ('e2', '2025-09-02'),
  ('e3', '2025-09-03');

  1. INSERT … SELECT (데이터 복사)

INSERT INTO archive_orders (order_id, total)
SELECT id, total FROM orders WHERE created_at < '2024-01-01';

  1. Upsert (alias 방식 권장)

INSERT INTO inventory (sku, qty)
VALUES ('A1', 10), ('B2', 5) AS new
ON DUPLICATE KEY UPDATE qty = qty + new.qty;

(위 예제는 MySQL 8.0.19+의 alias 사용 권장을 따릅니다; VALUES() 함수는 향후 제거될 수 있어 대체 방법으로 alias 사용 권장). 

  1. 대량 CSV 적재 (빠름)

LOAD DATA INFILE '/var/data/users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
(username,email,created_at);

(LOCAL 옵션의 사용·보안 차이 주의`). 

12) 권장 실무 체크리스트 (요약)

  • 파라미터화된 쿼리(Prepared Statement) 사용 — 보안. 

  • 대량 삽입은 LOAD DATA 또는 다중-VALUES + 트랜잭션으로 처리. 

  • 중복 처리에는 ON DUPLICATE KEY UPDATE(alias 사용 권장) 또는 INSERT IGNORE/REPLACE 상황에 맞게 선택. 

  • LAST_INSERT_ID() 동작(다중삽입 시 첫 id 반환) 기억. 

  • 프로덕션에서 큰 변경(인덱스, 제약 포함)은 스테이징에서 검증하고, 온라인 DDL 도구 고려.


댓글