개요
최근 서비스에서 사용하고 있는 프로시저의 코드를 분석하면서 TEMPORARY TABLE이라는 키워드를 발견했다. 확인해보니 임시 테이블을 다룰 때 사용하는 키워드인 것 같았다.
이번 글에서는 임시 테이블의 개념과 사용에 대해서 정리해보려고 한다.
임시 테이블
임시 테이블은 현재 세션 내에서만 유지되며 세션이 종료되면 자동으로 삭제되는 테이블이다. 데이터베이스에 저장되지 않기 때문에 테이블에 쿼리의 중간 결과를 저장할 필요가 있지만, 계속 유지할 필요는 없을 때 사용할 수 있다. 생성하면 세션 내에서는 여러 번 재사용할 수 있고, SELECT문이 데이터를 쿼리하는데 비용이 많이 드는 경우 유용하다.
임시 테이블은 생성한 클라이언트만 접근할 수 있다는 특징이 있다. 다른 클라이언트에 영향을 주지 않기 때문에 서로 다른 클라이언트가 각자 같은 이름의 임시 테이블을 생성할 수 있다. 하지만 같은 세션 내에서는 임시 테이블 이름이 중복되서는 안 된다.
추가로, 임시 테이블은 일반 테이블과 동일한 이름으로 생성할 수도 있다. 단, 이 경우 일반 테이블에는 접근할 수가 없게 된다. 또한 혼란을 야기할 수 있기 때문에 일반 테이블과 동일한 이름으로 임시 테이블을 생성하는 것을 권장되지 않는다. 특히 임시 테이블은 데이터베이스와 느슨한 관계를 가지고 있어 데이터베이스를 삭제한다고 해서 임시 테이블이 함께 삭제되는 것은 아니다.
임시 테이블을 만들기 위해서는 임시 테이블 생성 권한이 필요하다. 하지만 세션이 임시 테이블을 생성한 후에는 서버에서 권한 검사를 수행하지 않는다. 즉, 임시 테이블을 생성한 세션은 해당 테이블에 대한 삭제, 추가, 갱신, 조회 등의 작업을 수행할 수 있다.
생성
임시 테이블은 CREATE TEMPORARY TABLE 구문으로 생성할 수 있다.
CREATE TEMPORARY TABLE <temporary-table_name>
(
<column1> <datatype>,
<column2> <datatype>,
....................,
table_constraints like PRIMARY KEY, FOREIGN KEY
);
# 예시
CREATE TEMPORARY TABLE temp_sales (
sale_id INT,
product_id INT,
sale_amount DECIMAL(10, 2)
);TEMPORARY 키워드가 붙는 것 외에는 일반 테이블 생성과 동일하다.
데이터 추가/조회
데이터 추가나 조회는 일반 테이블을 다루는 것과 동일하게 수행하면 된다.
# 추가
INSERT INTO temp_sales (sale_id, product_id, sale_amount) VALUES
(1, 101, 150.00),
(2, 102, 200.00),
(3, 101, 250.00),
(4, 103, 300.00);
# 조회
SELECT *
FROM temp_sales
;
조회 결과
테이블 삭제
생성한 임시 테이블은 세션이 종료되면 자동으로 삭제되지만, DROP TEMPORARY TABLE 문을 사용해 명시적으로 삭제할 수도 있다.
DROP TEMPORARY TABLE IF EXISTS temp_table_name;
# 예시
DROP TEMPORARY TABLE IF EXISTS temp_sales;다만 connection pool이나 persistent connection 기반이라면 세션이 종료되도 임시 테이블의 삭제를 보장하지 않는다.
참고 문서
https://www.geeksforgeeks.org/mysql-temporary-table/
https://dev.mysql.com/doc/refman/8.4/en/create-temporary-table.html