[MySQL] TRUNCATE 완벽 가이드 사용법(테이블의 모든 데이터 삭제)

MySQL에서 TRUNCATE 명령어는 테이블의 모든 데이터를 삭제하는 데 사용됩니다. TRUNCATE는 데이터를 삭제하면서도 테이블 구조와 속성은 유지하기 때문에 DELETE 명령어와는 구별됩니다. 속도가 빠르고 간단한 테이블 초기화 작업에 적합합니다.

TRUNCATE와 DELETE의 차이점

TRUNCATE와 DELETE는 모두 데이터를 삭제할 수 있지만 몇 가지 차이점이 있습니다.

• TRUNCATE는 테이블의 모든 데이터를 즉시 삭제하며 롤백할 수 없습니다.
• DELETE는 WHERE 절을 사용하여 특정 데이터를 선택적으로 삭제할 수 있으며 트랜잭션 처리가 가능합니다.
• TRUNCATE는 테이블의 AUTO_INCREMENT 값을 초기화합니다.

TRUNCATE 사용 방법

기본 문법

TRUNCATE TABLE 테이블명;

예제 1: 단순 데이터 삭제

-- users 테이블의 모든 데이터 삭제
TRUNCATE TABLE users;

예제 2: AUTO_INCREMENT 초기화 확인

-- 데이터 삽입
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');

-- 데이터 확인
SELECT * FROM users;

-- 테이블 데이터 초기화
TRUNCATE TABLE users;

-- AUTO_INCREMENT 초기화 후 데이터 삽입
INSERT INTO users (id, name) VALUES (1, 'Charlie');
SELECT * FROM users;

이 예제에서 TRUNCATE TABLE 명령어를 실행하면 기존 데이터와 함께 AUTO_INCREMENT 값도 초기화됩니다. 새 데이터를 삽입하면 id가 다시 1부터 시작합니다.

반응형

TRUNCATE의 제약 조건

1. 참조 무결성 제약 조건
외래 키 제약 조건(Foreign Key)이 설정된 테이블은 TRUNCATE 명령어를 직접 사용할 수 없습니다. 이 경우 데이터를 삭제하려면 DELETE 명령어를 사용하거나 외래 키를 일시적으로 비활성화해야 합니다.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

2. 권한 필요
TRUNCATE 명령어를 실행하려면 테이블에 대한 DROP 권한이 필요합니다. 일반 DELETE 권한만으로는 실행할 수 없습니다.

실전 예제: 여러 테이블 초기화

예제 1: 하나의 테이블 초기화

TRUNCATE TABLE employees;

예제 2: 여러 테이블 순차 초기화

외래 키 관계가 없는 테이블에 대해 여러 TRUNCATE 명령어를 실행할 수 있습니다.

TRUNCATE TABLE employees;
TRUNCATE TABLE departments;
TRUNCATE TABLE projects;

예제 3: 외래 키 관계가 있는 테이블 초기화

-- 외래 키 제약 조건 해제
SET FOREIGN_KEY_CHECKS = 0;

-- 테이블 데이터 초기화
TRUNCATE TABLE orders;
TRUNCATE TABLE order_items;

-- 외래 키 제약 조건 활성화
SET FOREIGN_KEY_CHECKS = 1;

TRUNCATE 사용 시 주의사항

1. 데이터 복구 불가능
   TRUNCATE는 데이터를 영구적으로 삭제하며 트랜잭션 로그를 생성하지 않으므로 복구가 불가능합니다. 실행 전에 반드시 백업을 수행하세요.
2. 성능
   대량의 데이터를 삭제할 때 TRUNCATE는 DELETE보다 빠릅니다. 테이블을 초기화하거나 테스트 데이터를 재설정할 때 유용합니다.
3. 데이터 삭제 트리거
   TRUNCATE는 테이블과 연결된 트리거(Trigger)를 실행하지 않습니다. 트리거를 활용한 작업이 필요하다면 DELETE를 사용해야 합니다.

TRUNCATE의 활용 사례

1. 테스트 환경 초기화
   소프트웨어 개발 중 테스트 데이터를 반복적으로 생성하고 삭제할 때 TRUNCATE는 빠르고 효율적인 방법을 제공합니다.
2. 대규모 데이터 삭제
   대량의 데이터를 삭제할 때 TRUNCATE는 DELETE보다 훨씬 빠르며 테이블을 초기 상태로 복구하는 데 적합합니다.
3. 스키마 유지 필요
   테이블 구조를 그대로 유지하면서 데이터를 삭제해야 할 때 유용합니다.

반응형

TRUNCATE와 함께 조합 가능한 명령어

예제: 테이블 데이터 초기화와 데이터 삽입

TRUNCATE TABLE logs;

-- 새로운 로그 데이터 삽입
INSERT INTO logs (id, message, created_at) VALUES
(1, 'System initialized', NOW()),
(2, 'User logged in', NOW());

SELECT * FROM logs;

TRUNCATE 관련 추가 팁

• 외래 키로 연결된 테이블이 많다면, 데이터베이스의 FOREIGN_KEY_CHECKS 옵션을 적절히 조정하세요.
• 중요한 데이터가 포함된 테이블에서 TRUNCATE를 사용할 때는 특히 주의하세요.

참고

MySQL 공식 문서: https://dev.mysql.com/doc/refman/8.0/en/