MySQL INSERT의 모든 것

1. 기본 데이터 준비

실습을 위해 스키마를 생성하고, 공항 정보를 담을 airports 테이블과 백업 데이터용 old_airports 테이블을 정의함.

-- Schema 생성
CREATE SCHEMA mysql_practice;

-- 생성된 스키마 사용
USE mysql_practice;

-- 실습용 airports 테이블 생성
CREATE TABLE airports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) NOT NULL UNIQUE,
    open_year INT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 데이터 복사 실습을 위한 old_airports 테이블 생성
CREATE TABLE old_airports (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  old_city VARCHAR(255),
  status VARCHAR(50),
  contact_email VARCHAR(255) NOT NULL UNIQUE,
  open_year INT,
  metadata JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

2. 데이터 삽입 기초

2-1. 단일 Row 삽입

기본 형태는 INSERT INTO 테이블이름 (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...);임.
컬럼명을 명시하지 않고도 데이터 삽입은 가능하나, 테이블 구조 변경 시 쿼리가 오동작할 수 있어 비추천함.

-- [권장] 컬럼명 명시: 인천공항 데이터 넣기
INSERT INTO airports (name, city, contact_email) VALUES ('인천공항', '인천', 'iis@airport.kr');

-- [비권장] 컬럼명 생략: 모든 컬럼의 값을 순서대로 넣어야 함
INSERT INTO airports VALUES (NULL, '김포공항', '김포', 'gmp@airport.kr', NULL, NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

(참고) 명시적 ID 삽입과 VALUES/VALUE 차이

AUTO_INCREMENT인 ID에 값을 직접 넣으면(예: 10), 그 사이의 ID(3~9)는 버려지고 10부터 번호가 매겨짐. 이는 추후 ID 기반 페이징 등에서 문제를 일으킬 수 있음.
VALUES는 SQL 표준 문법이며, VALUE는 MySQL 고유 문법임. 호환성을 위해 VALUES 사용을 권장함.

-- ID를 10으로 강제 지정하여 삽입
INSERT INTO airports (id, name, city, contact_email) VALUES (10, '인천공항', '인천', 'iis@airport.kr');

2-2. 다중 Row 삽입 (Bulk Insert)

여러 개의 값을 삽입할 땐 한 번의 쿼리로 처리하는 것이 좋음.
장점: DB 커넥션 연결/종료 비용 감소, 쿼리 파싱 및 실행 계획 수립 횟수 최소화로 효율적임.
단점: 중간에 오타 등 오류 발생 시, 정확히 어느 데이터가 문제인지 찾기 어려움.

INSERT INTO airports (name, city, contact_email) VALUES 
    ('청주공항', '청주', 'cjj@airport.kr'),
    ('광주공항', '광주', 'kwj@airport.kr'),
    ('무안공항', '무안', 'mwx@airport.kr');

-- 결과 확인
SELECT id, name, city, contact_email FROM airports;
-- +----+----------+------+----------------+
-- | id | name     | city | contact_email  |
-- +----+----------+------+----------------+
-- |  1 | 인천공항 | 인천 | iis@airport.kr |
-- |  2 | 김포공항 | 김포 | gmp@airport.kr |
-- | 10 | 제주공항 | 제주 | cju@airport.kr |
-- | 11 | 청주공항 | 청주 | cjj@airport.kr |
-- | 12 | 광주공항 | 광주 | kwj@airport.kr |
-- | 13 | 무안공항 | 무안 | mwx@airport.kr |
-- +----+----------+------+----------------+

3. 중복 및 조건부 데이터 처리

3-1. ON DUPLICATE KEY UPDATE

중복되는 데이터(PK, Unique Key)가 발견될 시 UPDATE로 취급하여 쿼리를 실행함.
내부적으로 SELECT(키 확인) → 존재 시 UPDATE / 미존재 시 INSERT 과정을 거침.

INSERT INTO airports (id, name, city, contact_email) VALUES
    (10, '대구공항', '대구', 'tae@airport.kr')
    ON DUPLICATE KEY UPDATE
    -- 중복이 발생하면 어떤 컬럼에 어떤 값을 넣을지 설정 (VALUES() 함수로 새 값 참조)
    name = VALUES(name),
    city = VALUES(city),
    contact_email = VALUES(contact_email),
    updated_at = NOW();
    
-- 결과 확인 (ID 10번 데이터가 '제주'에서 '대구'로 변경됨)
SELECT id, name, city FROM airports WHERE id=10;
-- +----+----------+------+
-- | id | name     | city |
-- +----+----------+------+
-- | 10 | 대구공항 | 대구 |
-- +----+----------+------+

3-2. INSERT IGNORE

중복 데이터 발견 시 에러를 발생시키지 않고 조용히 건너뜀(무시함).
단순 무시 처리이므로 ON DUPLICATE KEY UPDATE보다 성능상 약간 더 빠름.

INSERT IGNORE INTO airports (id, name, city, contact_email) VALUES
(10, '제주공항', '제주', 'cju@airport.kr');

3-3. REPLACE INTO

중복 레코드 발견 시 기존 레코드를 삭제(DELETE)한 후 새 레코드를 삽입(INSERT)함.
주의사항: 삭제 후 재삽입되므로 AUTO_INCREMENT 값이 변경되거나, 다른 테이블에서 참조 중인 외래키(Foreign Key) 제약 조건에 의해 연쇄 삭제가 발생할 수 있음.

REPLACE INTO airports (id, name, city, contact_email) VALUES
(10, '사천공항', '사천', 'hin@airport.kr');

3-4. 조건부 삽입하기 (WHERE NOT EXISTS)

ON DUPLICATE...나 INSERT IGNORE는 PK/UK 제약조건이 기준임.
제약조건과 상관없이 특정 데이터가 없을 때만 넣고 싶다면 INSERT INTO ... SELECT ... WHERE NOT EXISTS 패턴을 사용함.

INSERT INTO airports (name, city, contact_email)
    SELECT '사천공항', '사천', 'hin@airport.kr' -- ① 넣고자 하는 값
    WHERE NOT EXISTS( -- ② 데이터가 존재하지 않으면 INSERT 실행
        SELECT 1 FROM airports WHERE city='사천' -- ③ city가 '사천'인 데이터 확인
    );

4. 테이블 간 데이터 복사

애플리케이션을 거치지 않고 MySQL 내부에서 테이블 통째로 데이터를 복사할 수 있어 매우 빠름.
SELECT 조회 결과를 바로 INSERT 하는 방식임.

-- 1. 조건부 복사: old_airports에서 status가 'open'인 데이터만 복사
INSERT INTO airports (name, city, contact_email)
    SELECT name, city, contact_email
    FROM old_airports
    WHERE status = 'open';

-- 2. 데이터 가공 후 복사: 문자열을 합치거나 연산하여 새로운 값으로 삽입
INSERT INTO airports (name, city, contact_email, open_year)
    SELECT
        CONCAT('new_', name) as name,
        CONCAT('new_', city) as city,
        CONCAT('new_', contact_email) as contact_email,
        open_year+1 as open_year
    FROM old_airports
    WHERE open_year > 1975;

5. 파일 데이터 대량 적재

LOAD DATA INFILE

CSV 파일 등의 데이터를 읽어 테이블에 통째로 밀어 넣는 쿼리임.
일반적인 Batch INSERT보다도 압도적인 쓰기 성능을 보여줌.
- MySQL 엔진 레벨의 파싱 최적화
- 작업 시 인덱스 비활성화 후 일괄 처리
- 네트워크 연결 없이 파일 시스템에서 직접 읽어 버퍼 최적화(메모리 효율성) 가능
주의사항: CSV 파일 포맷이 엄격해야 하며, DB 서버의 로컬 파일 접근 권한 설정이 필요하여 실무 적용 시 제약이 있을 수 있음.

LOAD DATA INFILE '/path/airports.csv'
    INTO TABLE airports
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' -- OS 환경에 따라 '\n' 등으로 변경 필요
    IGNORE 1 LINES -- CSV 헤더(첫 번째 줄) 무시
    (name, city, contact_email);

[참고] airports.csv 파일 예시

"name","city","contact_email",
"인천공항","인천","icn@airport.kr",
"김포공항","김포","gmp@airport.kr",
"청주공항","청주","cjj@airport.kr"

MySQL INSERT의 모든 것

목차

1. 기본 데이터 준비

2. 데이터 삽입 기초

2-1. 단일 Row 삽입

(참고) 명시적 ID 삽입과 VALUES/VALUE 차이

2-2. 다중 Row 삽입 (Bulk Insert)

3. 중복 및 조건부 데이터 처리

3-1. ON DUPLICATE KEY UPDATE

3-2. INSERT IGNORE

3-3. REPLACE INTO

3-4. 조건부 삽입하기 (WHERE NOT EXISTS)

4. 테이블 간 데이터 복사

5. 파일 데이터 대량 적재

LOAD DATA INFILE

이 글은 어떠셨나요? 자유롭게 의견을 남겨주세요! 💬 응답 취소

목차

1. 기본 데이터 준비

2. 데이터 삽입 기초

2-1. 단일 Row 삽입

(참고) 명시적 ID 삽입과 VALUES/VALUE 차이

2-2. 다중 Row 삽입 (Bulk Insert)

3. 중복 및 조건부 데이터 처리

3-1. ON DUPLICATE KEY UPDATE

3-2. INSERT IGNORE

3-3. REPLACE INTO

3-4. 조건부 삽입하기 (WHERE NOT EXISTS)

4. 테이블 간 데이터 복사

5. 파일 데이터 대량 적재

LOAD DATA INFILE

🔗 MySQL 카테고리의 다른 글

이 글은 어떠셨나요? 자유롭게 의견을 남겨주세요! 💬 응답 취소