[MYSQL] Temporary Table Control (임시 테이블 다루기)

Mysql Temporary Table 개요.

MySQL에서 임시테이블(Temporary table)은 단일 세션에서 여러 번 재사용할 수 있는 임시 결과 세트를 저장할 수 있는 특수한 유형의 테이블입니다.

임시 테이블은 반복적인 SELECT 쿼리를 해야 하거나, 대량의 집계 쿼리와 같이 비용이 클 때 유용하게 활용할 수 있습니다. 

 

Mysql temporary table의 특징

1) Mysql 임시 테이블 생성되는 시점는?

임시 테이블은 create temporary table 했을 때 테이블 생성됩니다. 

 

2) Mysql 임시 테이블 제거되는 시점는?

임시테이블은 세션이 종료 되거나, 연결이 종료될 때 자동으로 삭제됩니다. 물론, drop temporary table 문으로 명시적으로 실행했을 때 삭제됩니다.

 

3) Mysql 임시 테이블은 엑세스 허용 범위는?

임시 테이블을 생성한 클라이언트만 사용 가능하고 엑세스할 수 있습니다.

아래 예제처럼 첫번째 연결에서 tmp_x1라는 임시 테이블을 생성하고 연결이 유지한 상태에서 

새로운 세션(연결)을 생성하여 tmp_x1을 SELECT하면 "존재하지 않는 테이블"이라고 오류가 발생하게 됩니다. 

-- Connection#1)
create temporary table tmp_x1 ( id INT );
select * from tmp_x1;

-- Connection#2)
select * from tmp_x1;  -- error : table is not found

4) 같은 임시 테이블은 한 SQL에서 두번 이상 참조할 수 없습니다. 같은 임시 테이블을 아래와 같은 Sub-Query, Inline view, Join 등 사용할 수 없습니다. 이런 경우 With 문을 통해서 CTE 활용하는 것이 방법입니다. 

-- Create temporary table
create temporary table tmp_x1 ( id INT, val varchar(50) );
 
insert into tmp_x1(id, val) VALUES(1, 'a');
insert into tmp_x1(id, val) VALUES(2, 'b')

-- Error
select id, val 
from  tmp_x1 A
      join
      tmp_x1 B
      ON(A.id = B.id+1 )


-- Error
select id, val 
from  tmp_x1 A
WHERE id IN( SELECT id FROM tmp_x1 WHERE val = 'a' )

 

1. Create temporary table

temporary 키워드를 제외하고 create table문과 유사함.

-- Case1)CREATE TEMPORARY TABLE 
CREATE TEMPORARY TABLE credits(
    customerNumber INT PRIMARY KEY,
    creditLimit DEC(10,2)
);

INSERT INTO credits(customerNumber,creditLimit)
SELECT customerNumber, creditLimit
FROM customers
WHERE creditLimit > 0;



-- Case2) TEMPORARY TABLE ... SELECT * FROM 
CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;

 

728x90

 

2. Dropping temporary table

temporary 키워드를 제외하고 drop table문과 유사함.

-- Drop TEMPORARY TABLE...
DROP TEMPORARY TABLE top_customers;

 

 

<관련 포스팅>

[Mysql] Checking if a temporary table exists : 임시 테이블 존재여부 체크

[mysql] SQL split string to multiple rows

 

[MYSQL] Temporary Table Control