블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.본 제목은 준비됐지 물론이지 밈(Meme)을 응용했습니다.
도입
SQL를 사용하다보면 가끔 이런 생각을 할 때가 있다.
"행과 열을 바꿔서 데이터를 보여주고 싶은데 어떻게 방법이 없을까?"
예를 들어 아래와 같은 Products 테이블이 존재한다고 가정해보자.
+------------+----------+-------+
| product_id | store | price |
+------------+----------+-------+
| 1 | Shop | 110 |
| 1 | LC_Store | 100 |
| 2 | Nozama | 200 |
| 2 | Souq | 190 |
| 3 | Shop | 1000 |
| 3 | Souq | 1900 |
+------------+----------+-------+만약 해당 테이블을 아래와 같이 만들어주고 싶다면 어떻게 해야 할까?
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1 | 100 | null | 110 | null |
| 2 | null | 200 | null | 190 |
| 3 | null | null | 1000 | 1900 |
+------------+----------+--------+------+------+위 예시는 알고리즘 문제 풀이 서비스 LeetCode에 실제로 존재하는 문제 2252. Dynamic Pivoting of a Table에 제시된 테이블이다.
해당 문제는 제목에서 알 수 있듯 동적 피벗 테이블(Dyanamic Pivot Talbe)을 만드는 게 핵심인데 앞서 설명했던 것처럼 행과 열을 바꾸는 걸 피벗 테이블(Pivot Table)이라 한다.
피벗 테이블
MySQL에서 피벗 테이블을 만드는 방법으로는 기존의 열을 행으로 옮기려는 필드의 값이 정해진 범위 내에서만 존재하는 상황을 의미하는 정적 피벗 테이블을 만드는 것과 동적으로 열이 바뀔 수 있는 상황을 의미하는 동적 피벗 테이블을 만드는 경우가 있다.
정적 피벗 테이블
만약 예시 테이블처럼 store 필드에 데이터가 LC_Store , Nozama , Shop , 그리고 Souq 만 존재한다고 생각해보자.
그러면 간단하게 아래처럼 GROUP BY 구 및 데이터에 대한 개별 필드를 생성해서 피벗 테이블을 구현할 수 있다.
SELECT
product_id,
SUM(IF(store = 'LC_Store', price, NULL)) AS LC_Store,
SUM(IF(store = 'Nozama', price, NULL)) AS Nozama,
SUM(IF(store = 'Shop', price, NULL)) AS Shop,
SUM(IF(store = 'Souq', price, NULL)) AS Souq
FROM Products
GROUP BY product_id그런데 만약 LeetCode 문제처럼 필드가 동적으로 주어져서 어떤 게 존재하는 지 알 수 없다면 어떨까?
한 가지 아이디어를 생각해보자.
위에서 정적으로 구현했던 SUM 함수 부분을 일종의 반복문 형태로 쭉 만들어서 사용할 수는 없을까?
이를 구현할 수 있는 방법은 꽤 있겠지만 오늘은 GROUP_CONCAT 함수 및 대기 구(Prepared Statements)를 사용한 방법을 살펴보고자 한다.
대기 구 및 GROUP_CONCAT
대기 구 (Prepared Statements)
Prepared Statements 를 준비된 명령문 으로 번역한 국내 글들도 더러 존재했는데 사용이 될 때까지 해당 구가 대기 중인 상태라는 의미에서 대기 구 라 번역했다.
대기 구는 그 이름에서도 알 수 있듯 실행을 대기하고 있는 구를 의미한다.
조금 더 풀어 설명하자면 문자열로 구를 만든 뒤에 이를 대기 구에 저장해두고 필요할 때 실행 시키는 형태다.
예를 들어 공식 문서에 작성되어 있는 간단한 예시를 하나 살펴보자.
PREPARE stmt FROM 'SELECT SQRT(POW(?, 2) + POW(?, 2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt USING @a, @b;
DEALLOCATE PREPARE stmt;동적으로 두 수의 제곱한 결과의 합을 루트로 나눠주는 구이다. stmt 는 이때 해당 구의 이름이 되고 이를 PREPARE 구에서 정의한 다음 EXECUTE 구에서 실행했다.
이렇게 SQL 쿼리문을 문자열 형태로 만든 다음 동적으로 PREPARE 구에서 사용하면 무엇보다 SQL 인젝션(Injection) 공격으로부터 안전해진다.
예를 들어서 아래와 같은 쿼리 로직을 통해 Users 테이블 내에 존재하는 access_token 값을 가져와 로그인하는 로직이 있다고 생각해보자.
SELECT access_token
FROM Users
WHERE (
user_id = 'TEST'
AND
user_password = 'TEST1234'
);이때 WHERE 구에 아래와 같이 조건을 바꿔버리면 '1' = 1 조건에 대한 결과는 무조건 참이 되서 로그인에 무조건 성공해 외부에 유출하기 싫은 개인정보들이 유출될 수 있다.
SELECT access_token
FROM Users
WHERE (
user_id = 'TEST'
AND
user_password = '
OR
'1' = 1
)만약 PREPARED 구를 사용할 경우 문자열로 구를 인지하여 실행시키기 때문에 이미 따옴표(Quote)로 감싸져 있는 상태라 이러한 SQL 인젝션 공격을 예방할 수 있다.
추가적으로 할당된 대기 구는 세션(Session) 내에서만 유지되기 때문에 따로 명시적인 할당 해제를 하지 않더라도 세션이 종료되면서 동시에 할당이 해제된다.
이는 바꿔 말하면 세션을 유지하고 있다면 계속해서 저장된 대기 구를 사용할 수 있다는 의미다.
명시적으로 대기 구를 해제할 때는 DEALLOCATE PREPARE 문을 사용한다.
이렇게 할당이 해제된 이후에 해당 대기 구를 다시 사용하려 하면 오류가 발생한다.
동시에 너무 많은 명령문이 작성될 수도 있기 때문에 서버 시스템 변수 중 max_prepared_stmt_count 변수 값을 임의로 설정하여 대기 구의 수를 제한해줄 수 있다.
GROUP_CONCAT
GROUP_CONCAT 함수는 쉽게 CONCAT 함수를 사용하고 싶은데 GROUP BY 구를 사용한 것처럼 원하는 필드를 묶고 싶을 때 유용하다.
예를 들어 앞선 예시에서 store 필드에 GROUP_CONCAT 함수를 사용하면 아래와 같다.
이때 DISTINCT 키워드를 활용해 중복을 제거했다는 점에 유의하자.
SELECT GROUP_CONCAT(DISTINCT store) AS stores;
FROM Products;
/*
+------------------------------+
| stores |
+------------------------------+
| Shop, LC_Store, Nozama, Souq |
+------------------------------+
*/GROUP_CONCAT 함수는 이처럼 CONCAT 함수와 GROUP BY 구를 함께 쓴 것만 같은 결과물을 반환해준다.
DISTINCT 키워드 외에도 두 번째 매개변수에 ORDER BY 구를 활용해 CONCAT 함수로 연결되는 값들의 순서를 결정해 줄 수 있고 세 번째 매개변수에 SEPARATOR 키워드를 활용하여 각 값의 구분자를 임의로 지정해줄 수 있다.
예를 들어 아래와 같다.
SELECT GROUP_CONCAT(DISTINCT store ORDER BY store SEPARATOR ';') AS stores;
FROM Products;
/*
+------------------------------+
| stores |
+------------------------------+
| LC_Store; Nozama; Shop; Souq |
+------------------------------+
*/기존과 달리 , 대신 ; 를 통해 값이 구분되고 store 필드가 사전순에 맞게 오름차순 정렬된 것을 확인할 수 있다.
GROUP_CONCAT 함수를 사용할 때 유의할 점은 문자열 길이 제한이 있기 때문에 이를 유의해서 설정해줘야 한다는 것이다.
기본적으로 1024 글자를 제한으로 두고 있는데 만약 바꾸기를 희망한다면 아래와 같이 SET 구를 활용해 group_concat_max_len 값을 변경하면 된다.
SET SESSION group_concat_max_len = 10000;문제 풀이
앞서 알게 된 PREPARE 구 및 GROUP_CONCAT 함수를 활용하여 처음 예제 문제를 해결하면 아래와 같다.
여러 쿼리를 동시에 실행하기 위해 프로시저(Procedure)를 만들었다.
CREATE PROCEDURE ProductPivot()
BEGIN
SET SESSION group_concat_max_len = 100000;
SET @store_stmt = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(store = "', store, '", price, NULL)) AS ', store)) INTO @store_stmt
FROM Products;
SET @prepared_stmt = CONCAT('SELECT product_id, ', @store_stmt, ' FROM Products GROUP BY product_id');
PREPARE stmt FROM @prepared_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END먼저 GROUP_CONCAT 함수를 활용해서 Products 테이블 내 존재하는 모든 store 필드에 대한 price 필드 값을 얻어낼 수 있는 구를 문자열로 생성했다.
해당 구문은 앞서 정적 피벗 테이블에서 살펴봤던 SUM(IF(store = "LC_Store", price, NULL)) AS LC_Store 부분과 동일하다.
이후 해당 부분을 다시 CONCAT 함수를 사용해서 실제 실행할 쿼리를 문자열로 만든다.
PREPARE 구를 통해 문자열로 작성된 최종 쿼리를 stmt 라는 구문 이름에 저장하고 EXECUTE 구를 통해 이를 실행해서 결괏값을 얻은 뒤 DEALLOCATE PREPARE 구를 통해 세션에서 해당 stmt 대기 구를 할당 해제한다.
결론
오늘은 간단하게 피벗 테이블을 만드는 방식을 통해 MySQL 속 PREPARED 구를 살펴봤다.
해당 구를 보통 피벗 테이블을 만들 때 사용하는 경우도 많지만 앞서 이야기했던 것처럼 SQL 인젝션 공격으로부터의 방어에 효과적이고 더욱이 이외에도 세션이 종료될 때까지 저장되어 있다는 특징에 의해 반복적으로 사용해야 하는 구문 혹은 쿼리를 저장해두었다가 필요할 때마다 동적으로 인자만 전달하여 EXECUTE 구를 통해 실행시키면 효율성이 무척 좋다.
더 자세한 내용이 작성되어 있는 공식문서와 관련된 LeetCode 문제는 아래 참고에서 확인 가능하다.
참고
LeetCode
MySQL 공식 문서
