[MySQL] SELECT INTO 사용 방법 및 예시

 

MySQL의 SELECT ... INTO 구문에 대해 상세히 알아보겠습니다. 이 구문은 SELECT 문의 결과를 새로운 테이블이나 변수에 저장하는 데 사용되는 강력한 기능입니다.

SELECT ... INTO의 주요 용도는 두 가지입니다:

1. 쿼리 결과를 새로운 테이블에 저장
2. 쿼리 결과를 변수에 저장

먼저, 새로운 테이블에 결과를 저장하는 방법을 살펴보겠습니다.

1. 새로운 테이블에 결과 저장하기

구문:

SELECT column1, column2, ...
INTO NEW_TABLE
FROM existing_table
WHERE condition;

이 구문은 기존 테이블에서 데이터를 선택하여 새로운 테이블을 생성하고 그 안에 데이터를 삽입합니다.

예시:

SELECT customer_id, first_name, last_name, city
INTO high_value_customers
FROM customers
WHERE total_purchases > 10000;

이 쿼리는 'customers' 테이블에서 총 구매액이 10,000을 초과하는 고객의 정보를 선택하여 'high_value_customers'라는 새로운 테이블을 생성하고 그 안에 데이터를 저장합니다.

주의할 점은 새로운 테이블이 이미 존재한다면 오류가 발생합니다. 따라서 테이블이 존재하지 않을 때만 이 구문을 사용해야 합니다.

2. 변수에 결과 저장하기

MySQL에서는 SELECT ... INTO OUTFILE을 사용하여 결과를 파일로 내보내거나, SELECT ... INTO @variable을 사용하여 결과를 변수에 저장할 수 있습니다.

변수에 저장하는 예시:

SELECT COUNT(*) INTO @total_customers FROM customers;

이 쿼리는 'customers' 테이블의 총 행 수를 계산하여 @total_customers 변수에 저장합니다.

여러 값을 동시에 변수에 저장할 수도 있습니다:

SELECT MIN(price), MAX(price), AVG(price)
INTO @min_price, @max_price, @avg_price
FROM products;

이 쿼리는 'products' 테이블에서 최소 가격, 최대 가격, 평균 가격을 계산하여 각각 @min_price, @max_price, @avg_price 변수에 저장합니다.

SELECT ... INTO의 장점:

1. 데이터 백업: 특정 조건을 만족하는 데이터를 새로운 테이블로 빠르게 백업할 수 있습니다.

2. 임시 테이블 생성: 복잡한 분석이나 보고서 작성 시 중간 결과를 저장하는 임시 테이블을 쉽게 만들 수 있습니다.

3. 변수 활용: 쿼리 결과를 변수에 저장하여 후속 쿼리나 프로그래밍 로직에서 활용할 수 있습니다.

4. 성능 최적화: 대규모 데이터셋에서 반복적으로 사용되는 결과를 미리 계산하여 저장함으로써 성능을 향상시킬 수 있습니다.

SELECT ... INTO 사용 시 주의사항:

1. 새 테이블 생성 시 기존 테이블의 구조만 복사되며, 인덱스, 외래 키 제약 조건 등은 복사되지 않습니다. 필요한 경우 별도로 추가해야 합니다.

2. 대량의 데이터를 새 테이블에 삽입할 때는 디스크 공간과 실행 시간에 주의해야 합니다.

3. 변수에 결과를 저장할 때는 단일 값 또는 단일 행만 저장 가능합니다. 여러 행의 결과를 저장하려면 다른 방법을 사용해야 합니다.

추가 예시:

1. 조건부 데이터 추출 및 새 테이블 생성:

SELECT product_id, product_name, unit_price, units_in_stock
INTO out_of_stock_products
FROM products
WHERE units_in_stock = 0;

이 쿼리는 재고가 없는 제품들의 정보를 새로운 'out_of_stock_products' 테이블에 저장합니다.

2. 집계 결과 저장:

SELECT category_id, AVG(unit_price) AS avg_price
INTO category_avg_prices
FROM products
GROUP BY category_id;

이 쿼리는 각 카테고리별 평균 가격을 계산하여 'category_avg_prices'라는 새 테이블에 저장합니다.

3. 다중 테이블 조인 결과 저장:

SELECT c.customer_id, c.company_name, 
       SUM(od.quantity * od.unit_price) AS total_order_amount
INTO high_value_customers
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.company_name
HAVING SUM(od.quantity * od.unit_price) > 10000;

이 쿼리는 총 주문 금액이 10,000을 초과하는 고객의 정보를 새로운 'high_value_customers' 테이블에 저장합니다.

4. 날짜 기반 데이터 추출:

SELECT o.order_id, o.order_date, c.customer_name
INTO recent_orders
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

이 쿼리는 최근 30일 동안의 주문 정보를 'recent_orders'라는 새 테이블에 저장합니다.

SELECT ... INTO는 데이터 분석, 보고서 생성, 데이터 마이그레이션 등 다양한 상황에서 유용하게 사용될 수 있는 강력한 도구입니다. 그러나 대용량 데이터를 다룰 때는 시스템 리소스 사용에 주의해야 하며, 필요에 따라 인덱싱이나 파티셔닝 등의 최적화 기법을 함께 고려해야 합니다.

또한, MySQL 8.0 이상 버전에서는 CTE(Common Table Expressions)와 함께 사용하여 더욱 복잡한 데이터 처리와 분석을 수행할 수 있습니다. 

예를 들어:

WITH cte AS (
    SELECT category_id, AVG(unit_price) AS avg_price
    FROM products
    GROUP BY category_id
)
SELECT p.product_id, p.product_name, p.unit_price, c.avg_price
INTO products_with_avg_category_price
FROM products p
JOIN cte c ON p.category_id = c.category_id;

이 쿼리는 CTE를 사용하여 각 카테고리의 평균 가격을 계산한 후, 이를 개별 제품 정보와 결합하여 새로운 테이블에 저장합니다.

SELECT ... INTO의 활용 팁:

1. 임시 테이블 생성: 복잡한 분석 작업 중 중간 결과를 저장하기 위해 임시 테이블을 생성할 때 유용합니다.

SELECT customer_id, COUNT(*) AS order_count
INTO TEMPORARY TABLE customer_order_counts
FROM orders
GROUP BY customer_id;

2. 데이터 아카이빙: 주기적으로 오래된 데이터를 아카이브 테이블로 이동할 때 사용할 수 있습니다.

SELECT *
INTO archived_orders_2022
FROM orders
WHERE YEAR(order_date) = 2022;

3. 데이터 샘플링: 대규모 데이터셋에서 샘플 데이터를 추출하여 새 테이블에 저장할 때 유용합니다.

SELECT *
INTO sampled_customers
FROM customers
ORDER BY RAND()
LIMIT 1000;

4. 데이터 정제: 특정 조건을 만족하는 정제된 데이터를 새 테이블에 저장할 수 있습니다.

SELECT *
INTO valid_email_customers
FROM customers
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

SELECT ... INTO는 강력하지만, 대규모 데이터베이스에서는 주의해서 사용해야 합니다. 특히 새 테이블 생성 시 충분한 디스크 공간이 있는지 확인하고, 실행 시간이 길어질 수 있음을 고려해야 합니다.

또한, SELECT ... INTO로 생성된 새 테이블은 원본 테이블의 인덱스나 제약 조건을 상속받지 않습니다. 따라서 필요한 경우 새 테이블에 적절한 인덱스를 추가하거나 제약 조건을 설정해야 합니다.

결론적으로, SELECT ... INTO는 MySQL에서 데이터를 효과적으로 추출하고 새로운 형태로 저장하는 데 매우 유용한 도구입니다. 데이터 분석, 보고서 생성, 데이터 마이그레이션 등 다양한 상황에서 활용될 수 있으며, 적절히 사용하면 데이터베이스 작업의 효율성을 크게 향상시킬 수 있습니다.