MySQL ‐ MySQL Fundamentals

MySQL - MySQL Fundamentals

MySQL INSERT Guide

-- [동작] id(PK 또는 UK) 충돌 시 UPDATE, 없으면 INSERT
-- [장점] INSERT/UPDATE를 단일 쿼리로 처리 (Upsert 패턴)
-- [주의] VALUES()는 MySQL 8.0.20부터 deprecated → alias 방식 권장, AUTO_INCREMENT 컬럼이 있을 경우, 충돌 시에도 내부 카운터가 증가하므로 ID 단절(gap) 발생 가능
INSERT INTO users (id, name, email, age, city)
    value (100,'김철수', 'kim@naver.com', 13, 'Seoul')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age),
    city = VALUES(city),
    updated_at = NOW();

-- [동작] PK/UK 충돌 시 해당 행을 조용히 무시(skip), 에러 미발생
-- [장점] 중복 데이터 유입 방어에 간편하게 사용 가능
-- [주의] 충돌 외 다른 에러(NOT NULL 위반 등)도 silently 무시, 데이터 정합성 문제가 로그 없이 묻힐 수 있어 운영 환경에서 위험
INSERT IGNORE INTO users (id, name, email, age, city)
    value
    (100,'김철수', 'kim@naver.com', 13, 'Seoul'),
    (100,'김철수', 'kim@naver.com', 13, 'Seoul');

-- [동작] PK/UK 충돌 시 기존 행을 DELETE 후 새 행을 INSERT → 내부적으로 DELETE + INSERT이므로 AUTO_INCREMENT 값이 항상 증가
-- [장점] 문법이 단순
-- [위험1] 운영 환경에서 사용 비권장 - 충돌한 행이 삭제되므로 연관 테이블에 CASCADE DELETE가 걸려 있으면 자식 데이터까지 삭제될 수 있음
-- [위험2] 삭제-삽입이므로 binlog 기반 복제 환경에서 부하 증가
-- [위험3] created_at, 소프트딜리트 컬럼 등 기존 데이터가 전부 유실됨
REPLACE INTO users (id, name, email, age,city)
VALUE (1, 'a', 'b', 3, 's')

-- INSERT ... WHERE NOT EXISTS (중복 방지 패턴)
-- [동작] 서브쿼리로 중복 확인 후 없을 때만 INSERT
-- [문제] Race Condition 위험
-- [개선] UNIQUE 제약 + ON DUPLICATE KEY UPDATE 조합이 원자적(atomic)으로 안전
INSERT INTO users (name, email, age, city) SELECT '새 사용자', 'test메일', 25, 'seoul'
    WHERE NOT EXISTS(
        SELECT 1 FROM users WHERE email = 'test메일'
    );

-- 배치 INSERT (대량 데이터 삽입)
-- [동작] 단일 INSERT로 다수 행을 한 번에 삽입
-- [장점] 단건 INSERT 반복 대비 네트워크 왕복 및 파싱 비용 절감
-- [주의] 한 번에 너무 많은 행을 넣으면 아래 문제 발생
  -- 단일 트랜잭션이 길어져 언두 로그(undo log) 폭증 → InnoDB 성능 저하
  -- max_allowed_packet 초과 시 쿼리 자체가 실패
  -- 실패 시 전체 롤백 → 재처리 범위가 커짐
-- [권장] 1,000 ~ 5,000건 단위로 청크 분할 + 트랜잭션 명시
INSERT INTO users (name, email, age, city) VALUES
    ('User1', 'user1@test.com', 20, 'Seoul'),
    ('User2', 'user2@test.com', 21, 'Busan'),
-- ... 많은 레코드
    ('User10000', 'user10000@test.com', 30, 'Daegu');

-- LOAD DATA INFILE (CSV 파일 직접 로드)
-- [동작] MySQL 서버가 직접 파일을 읽어 테이블에 삽입 (배치 INSERT보다 훨씬 빠름)
-- [장점] 대용량 데이터 로드에서 가장 빠른 방식 (bulk insert path 사용)
-- [위험] 보안 및 운영 이슈 다수 존재
-- [개선] 클라이언트 측 파일을 사용하는 LOCAL 옵션 또는 S3 연동
LOAD DATA INFILE '/path/to/users.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (name, email, age, city);

MySQL UPDATE Guide