문자열 처리

FIND_IN_SET을 이용한 콤마 구분 값 검색

FIND_IN_SET(substring, str) 함수는 주어진 하위 문자열이 콤마로 구분된 문자열 내에 존재하는지 확인하고, 첫 번째 일치 위치를 반환합니다. 매칭되지 않으면 0을 반환하며, 대상 문자열이 콤마로 분리되지 않은 경우에도 정상 동작하지 않습니다.

SELECT FIND_IN_SET('b', 'a,b,c'); -- 결과: 2
SELECT FIND_IN_SET('x', 'a,b,c'); -- 결과: 0
SELECT FIND_IN_SET('ab', 'abc');  -- 결과: 0 (콤마 없음)

실제 업무에서 사용자 유형(user_type)이 "1,2"처럼 다중 값으로 저장되는 경우가 있습니다. 예를 들어 프론트엔드에서 {"userType": "1,2,3"} 조건으로 필터링할 때, 해당 값 중 하나라도 포함된 사용자를 조회해야 합니다. 다음과 같은 테이블이 있다고 가정합니다:

SELECT user_type FROM users LIMIT 6;
+-----------+
| user_type |
+-----------+
| 1         |
| 2         |
| 3         |
| 1,2       |
| 2,3       |
| 1,2,3     |
+-----------+

MyBatis를 사용할 경우, 입력된 userType 문자열을 분할하여 각각에 대해 FIND_IN_SET을 적용할 수 있습니다:

<select id="findUsers" resultType="User">
    SELECT id, user_type AS userType
    FROM users
    WHERE 
    <foreach item="type" collection="userType.split(',')" separator=" OR " open="(" close=")">
        FIND_IN_SET(#{type}, user_type) > 0
    </foreach>
</select>

이 쿼리는 전달된 각 사용자 유형이 user_type 필드에 포함되어 있는지 확인하고, 하나라도 일치하면 결과에 포함시킵니다.

특정 문자의 출현 빈도 계산

문자열 내 특정 문자(예: 콤마)가 몇 번 등장하는지 세려면 전체 길이와 치환 후 길이의 차이를 이용합니다. 다국어 지원을 위해 CHAR_LENGTH를 사용하는 것이 더 안정적입니다.

SELECT 
    CHAR_LENGTH(data_column) - CHAR_LENGTH(REPLACE(data_column, ',', '')) AS comma_count
FROM data_table;

예를 들어 'a,b,c' 문자열은 두 개의 콤마를 포함하므로 결과는 2가 됩니다.

날짜 함수 활용

특정 월의 총 일수 구하기

어떤 달이 며칠까지 있는지 알고 싶을 때, LAST_DAY() 함수로 해당 월의 마지막 날짜를 얻고, DAY() 함수로 일(day) 값을 추출합니다.

SELECT DAY(LAST_DAY('2022-10-15')) AS total_days;
-- 결과: 31

이 방식은 윤년이나 월별 일수 차이를 자동으로 처리해주므로 매우 유용합니다.

Pagination: LIMIT과 OFFSET 이해

LIMIT 절은 결과 수 제한에 사용되며, 다양한 형태로 사용됩니다.

• LIMIT 3: 처음 3개 행만 반환
• LIMIT 1, 3: 인덱스 1부터 시작해 3개 행 선택 (즉, 2, 3, 4번째 행)
• LIMIT 1 OFFSET 3: 인덱스 3부터 1개 행 반환 (즉, 4번째 행)

예시:

SELECT * FROM employees LIMIT 1, 3;
-- 두 번째 행부터 3개 가져옴

SELECT * FROM employees LIMIT 1 OFFSET 3;
-- 네 번째 행 단일 조회

집계 보고서 작성: WITH ROLLUP

GROUP BY에 WITH ROLLUP을 추가하면 소계 및 총계 행이 자동으로 포함됩니다. 다음은 창고별 재고 합계와 전체 합계를 동시에 출력하는 예제입니다:

CREATE TABLE inventory (
    id INT PRIMARY KEY,
    warehouse VARCHAR(50),
    product VARCHAR(30),
    quantity INT
);

INSERT INTO inventory VALUES
(1, 'San Jose', 'iPhone', 100),
(2, 'San Fransisco', 'iPhone', 60),
(3, 'San Jose', 'huawei', 200),
(4, 'San Fransisco', 'huawei', 100);

쿼리 실행:

SELECT warehouse, SUM(quantity) AS sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;

결과:

warehouse	sum
San Fransisco	160
San Jose	300
NULL	460

총계 행의 warehouse 값은 NULL이 되므로, 이를 가독성 있게 변경하려면 COALESCE() 함수를 사용합니다. 이 함수는 첫 번째로 NULL이 아닌 값을 반환합니다.

SELECT COALESCE(warehouse, '전체합계') AS warehouse, SUM(quantity) AS sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;

최종 결과:

warehouse	sum
San Fransisco	160
San Jose	300
전체합계	460