1. UPSERT란 무엇인가?
개요
“UPSERT”는 “INSERT”와 “UPDATE” 작업을 결합한 데이터베이스 기능을 의미합니다. 즉, 데이터가 아직 존재하지 않으면 삽입하고, 동일한 데이터가 이미 존재하면 업데이트합니다. 이 기능을 사용하면 데이터 일관성을 유지하면서 효율적인 작업을 수행할 수 있습니다.
MySQL에서는 INSERT ... ON DUPLICATE KEY UPDATE 구문을 사용하여 이 기능을 구현합니다. 이 구문을 이용하면 중복 키 오류를 방지하고, 중복 키가 발생해도 기존 레코드를 업데이트할 수 있습니다.
사용 사례
- 고객 관리 시스템 : 고객 데이터가 없을 경우 새로 추가하고, 기존 고객 정보가 변경되면 업데이트합니다.
- 제품 재고 관리 : 새로운 제품을 추가하면서 기존 제품의 재고 수량을 업데이트합니다.
MySQL UPSERT의 장점
- 중복 키 오류 방지
- SQL 쿼리 단순화
- 데이터 무결성 유지
2. MySQL에서 UPSERT 기본 사용법
MySQL에서는 INSERT ... ON DUPLICATE KEY UPDATE 구문을 사용해 UPSERT 작업을 수행합니다. 이 구문을 사용하면 중복 키가 발생했을 때 새 데이터를 삽입하는 대신 기존 데이터를 일부 혹은 전체를 업데이트할 수 있습니다.
기본 구문
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;
설명:
INSERT INTO를 사용해 데이터를 삽입합니다.- 삽입하려는 데이터가 이미 테이블에 존재하면
ON DUPLICATE KEY UPDATE절이 실행되어 기존 데이터가 업데이트됩니다.
예시:
INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';
위 예시에서 user_id가 1인 사용자가 이미 존재한다면 name 필드가 ‘Taro Tanaka’로 업데이트됩니다. 사용자가 존재하지 않으면 새로운 레코드가 삽입됩니다.
3. UPSERT의 상세 SQL 구문 및 예시
여러 컬럼 업데이트
UPSERT를 사용할 때 특정 컬럼만 업데이트하고 싶을 경우가 있습니다. 이때는 ON DUPLICATE KEY UPDATE 절에 필요한 컬럼만 지정하면 됩니다.
INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
위 예시에서는 product_id가 100인 제품이 이미 존재할 경우 price 컬럼만 업데이트되고, name과 같은 다른 컬럼은 그대로 유지됩니다.
4. 다른 데이터베이스와의 차이점
MySQL 외의 데이터베이스에서도 유사한 기능을 제공합니다. 예를 들어 PostgreSQL과 SQLite는 INSERT ... ON CONFLICT 또는 MERGE 문을 사용해 UPSERT와 유사한 동작을 구현합니다.
PostgreSQL 예시
INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';
PostgreSQL 및 SQLite에서는 중복 키 오류가 발생했을 때 동작을 제어하기 위해 ON CONFLICT 절을 사용합니다. 반면 MySQL은 ON DUPLICATE KEY UPDATE 절을 사용합니다.
MySQL 전용 특성
- MySQL은
INSERT ... ON DUPLICATE KEY UPDATE구문을 사용하며, 다른 데이터베이스와 구문이 다르기 때문에 시스템 간 마이그레이션 시 특별한 주의가 필요합니다.
5. 고급 UPSERT 기법
대량 UPSERT (다중 레코드 배치 처리)
UPSERT는 단일 레코드뿐만 아니라 여러 레코드를 한 번에 처리할 수도 있습니다. 이를 통해 데이터베이스 작업 효율성을 크게 향상시킬 수 있습니다.
INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
위 예시에서는 여러 제품 레코드를 한 번에 삽입합니다. 중복 키가 존재하면 해당 레코드의 price 필드만 업데이트됩니다.
UPSERT를 위한 저장 프로시저 활용
UPSERT 처리를 최적화하려면 저장 프로시저를 사용할 수도 있습니다. 이를 통해 데이터베이스 내부에 재사용 가능한 로직을 만들 수 있어 코드의 가독성과 유지 관리성을 모두 향상시킵니다.
6. 일반적인 함정 및 중요한 고려 사항
트랜잭션 및 데드락
UPSERT를 사용할 때—특히 대량의 데이터를 다룰 경우—데드락이 발생할 수 있습니다. MySQL의 트랜잭션 격리 수준이 REPEATABLE READ로 설정되어 있으면 갭 락이 발생할 가능성이 높아집니다.
갭 락 방지
- 트랜잭션 격리 수준을
READ COMMITTED로 변경하면 데드락 발생 가능성을 줄일 수 있습니다. - 필요하다면 대규모 UPSERT 작업을 더 작은 배치로 나누어 하나의 큰 문장 대신 여러 개의 쿼리를 실행하는 것을 고려하십시오.
7. 결론
MySQL UPSERT 기능은 중복 키 오류를 방지하면서 데이터 삽입 및 업데이트를 간소화하는 데 매우 유용합니다. 그러나 UPSERT를 구현하려면 잠재적인 데드락 및 트랜잭션 설정을 신중히 고려해야 합니다. 적절히 사용하면 보다 간단하고 효율적인 데이터베이스 작업을 가능하게 합니다.
