데이터베이스를 운영하다 보면 일정 기간이 지난 오래된 데이터를 주기적으로 정리해야 하는 경우가 많습니다. MySQL에서는 DELETE 문과 INTERVAL 키워드를 조합하여 30일 이상 지난 레코드를 손쉽게 삭제할 수 있습니다. 현재 날짜(NOW())보다 이전인 레코드를 찾기 위해 비교 연산자 <(미만)를 사용합니다.
1. 예제 테이블 생성하기
먼저 실습에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> UserMessage text,
-> UserMessageSentDate date
-> );
Query OK, 0 rows affected (0.59 sec)
2. 샘플 데이터 삽입하기
INSERT 명령어로 여러 개의 레코드를 추가합니다.
mysql> insert into DemoTable values('Hi','2019-06-01');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Hello','2019-07-02');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Awesome','2019-05-04');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Good','2019-01-10');
Query OK, 1 row affected (0.35 sec)
3. 전체 레코드 조회하기
SELECT 문으로 테이블에 저장된 모든 데이터를 확인합니다.
mysql> select *from DemoTable;
출력 결과
+-------------+---------------------+ | UserMessage | UserMessageSentDate | +-------------+---------------------+ | Hi | 2019-06-01 | | Hello | 2019-07-02 | | Awesome | 2019-05-04 | | Good | 2019-01-10 | +-------------+---------------------+ 4 rows in set (0.00 sec)
4. 30일 이상 지난 레코드 삭제하기
다음은 30일보다 오래된 모든 행을 삭제하는 핵심 쿼리입니다.
mysql> delete from DemoTable where UserMessageSentDate < now() - interval 30 DAY; Query OK, 3 rows affected (0.11 sec)
now() - interval 30 DAY는 현재 시점에서 30일 전 날짜를 계산하며, 그보다 이전 날짜에 해당하는 레코드가 모두 삭제됩니다. 위 예제에서는 총 3개의 행이 삭제되었습니다.
5. 삭제 후 결과 확인하기
테이블의 레코드를 다시 한번 조회해 보겠습니다.
mysql> select *from DemoTable;
출력 결과
+-------------+---------------------+ | UserMessage | UserMessageSentDate | +-------------+---------------------+ | Hello | 2019-07-02 | +-------------+---------------------+ 1 row in set (0.00 sec)
실행 결과, 30일 이내의 'Hello'(2019-07-02) 레코드 하나만 남고 나머지 오래된 데이터는 모두 삭제된 것을 확인할 수 있습니다.
실무 활용 팁
날짜 컬럼에 인덱스를 설정해 두면 대량의 데이터를 삭제할 때 성능이 크게 향상됩니다. 또한 실수로 필요한 데이터를 삭제하지 않도록, DELETE 문을 실행하기 전에 동일한 WHERE 조건으로 SELECT 문을 먼저 실행해 대상 레코드를 확인하는 습관을 들이는 것이 좋습니다. 주기적인 자동 삭제가 필요하다면 MySQL 이벤트 스케줄러(EVENT)와 함께 활용할 수도 있습니다.