MySQL FIND_IN_SET 설명: 쉼표로 구분된 값을 올바르게 검색하는 방법

1. 소개

MySQL에서 콤마로 구분된 데이터 검색의 어려움

데이터베이스를 다룰 때, 여러 값이 콤마로 구분되어 하나의 컬럼에 저장되는 경우를 마주할 수 있습니다. 예를 들어, 컬럼에 "1,3,5"와 같은 문자열이 들어 있을 수 있으며, 여기서 값 “3”을 포함하는 레코드만 추출하고 싶을 때가 있습니다.

이러한 경우, 표준 = 연산자나 IN 절을 사용해도 기대한 결과가 나오지 않을 때가 많습니다. 이는 콤마로 구분된 문자열이 하나의 문자열 값으로 취급되어, 비교가 전체 문자열에 대해 수행되기 때문이며 개별 요소에 대해 비교되지 않습니다.

FIND_IN_SET 함수란?

이와 같은 상황에서는 MySQL FIND_IN_SET 함수가 매우 유용합니다.
이 함수는 지정한 값이 콤마로 구분된 문자열 안에 존재하는지를 손쉽게 판단할 수 있게 해줍니다.

예를 들어, 다음 SQL 문을 살펴보세요:

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

이 쿼리에서는 favorite_ids 컬럼에 있는 콤마로 구분된 문자열(예: "1,2,3,4")에 값 “3”이 포함되어 있는 레코드를 추출할 수 있습니다.

이 글의 목적 및 대상 독자

이 글은 FIND_IN_SET 함수를 기본부터 명확하고 체계적으로 사용하는 방법을 설명합니다. 기본 구문부터 실용 예시, 다른 검색 방법과의 비교, 주요 고려사항, FAQ까지 다루며 실제 개발에 바로 적용 가능한 실용 지식을 제공합니다.

이 글은 다음과 같은 독자를 위해 작성되었습니다:

• MySQL을 정기적으로 사용하는 웹 엔지니어 및 백엔드 개발자
• 콤마로 구분된 데이터를 저장하는 기존 시스템을 다루어야 하는 개발자
• 부분 매칭 및 값 기반 검색에 어려움을 겪는 SQL 초보자

2. FIND_IN_SET 함수의 기본 구문 및 동작

FIND_IN_SET 구문

FIND_IN_SET은 콤마로 구분된 문자열 안에 특정 값이 존재하는지를 판단하기 위해 사용되는 MySQL 함수입니다. 기본 구문은 다음과 같습니다:

FIND_IN_SET(search_value, comma_separated_string)

예시:

SELECT FIND_IN_SET('3', '1,2,3,4'); -- Result: 3

이 예시에서 “3”이 세 번째 위치에 나타나므로 함수는 숫자값 3을 반환합니다.

반환값 규칙

FIND_IN_SET 함수는 다음 규칙에 따라 동작합니다:

예시 (위치 반환)

SELECT FIND_IN_SET('b', 'a,b,c'); -- Result: 2

예시 (값을 찾지 못함)

SELECT FIND_IN_SET('d', 'a,b,c'); -- Result: 0

예시 (NULL 포함)

SELECT FIND_IN_SET(NULL, 'a,b,c'); -- Result: NULL

WHERE 절에서의 사용 예시

이 함수는 WHERE 절 내에서 필터링할 때 가장 많이 사용됩니다.

SELECT * FROM users WHERE FIND_IN_SET('admin', roles);

위 예시에서는 roles 컬럼에 문자열 “admin”이 포함된 행만 반환됩니다. 컬럼 값이 "user,editor,admin"와 같이 여러 값으로 이루어져 있어도 매치됩니다.

숫자와 문자열에 대한 중요한 주의사항

FIND_IN_SET은 비교를 문자열로 수행하므로 다음과 같이 동작합니다:

SELECT FIND_IN_SET(3, '1,2,3,4');     -- Result: 3
SELECT FIND_IN_SET('3', '1,2,3,4');   -- Result: 3

숫자값과 문자열값 모두에 대해 동작하지만, 데이터 타입이 명확하지 않을 경우 예상치 못한 동작이 발생할 수 있습니다. 따라서 가능한 한 값을 문자열로 명시적으로 처리하는 것이 권장됩니다.

3. 실용 예시

콤마로 구분된 문자열을 저장하는 컬럼 검색

실제 시스템에서는 ID나 권한과 같은 여러 값이 하나의 컬럼에 콤마로 구분된 문자열 형태로 저장되는 경우가 많습니다. 예를 들어, 다음 users 테이블을 살펴보세요.

값 3을 포함하는 사용자를 “조회”하고 싶을 때, FIND_IN_SET 함수가 매우 편리합니다.

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

이 SQL을 실행하면 “Taro”와 “Jiro” 레코드가 반환됩니다.

값이 숫자처럼 보이더라도 잘 작동합니다

favorite_ids가 숫자를 포함하는 것처럼 보이더라도, FIND_IN_SET은 문자열 기반 비교를 수행하므로, 인수를 따옴표로 묶은 문자열로 전달하는 것이 가장 안전합니다.

-- OK
SELECT * FROM users WHERE FIND_IN_SET('5', favorite_ids);

-- Works, but strictly speaking not recommended
SELECT * FROM users WHERE FIND_IN_SET(5, favorite_ids);

쿼리를 읽기 쉽게 하고 동작을 예측 가능하게 유지하기 위해, 값을 문자열로 명시적으로 지정하는 것을 권장합니다.

동적 검색 (플레이스홀더와 변수)

웹 애플리케이션에서 SQL을 동적으로 생성할 때, 변수나 바인드 매개변수를 사용하는 것이 일반적입니다.

MySQL 변수를 사용할 경우, 다음과 같습니다:

SET @target_id = '3';
SELECT * FROM users WHERE FIND_IN_SET(@target_id, favorite_ids);

애플리케이션 계층(PHP, Python, Node.js 등)에서 바인딩할 때, 플레이스홀더를 사용하여 유사하게 처리할 수 있습니다.

여러 값 검색 처리 방법

유감스럽게도, FIND_IN_SET은 한 번에 하나의 값만 검색할 수 있습니다.
“3 또는 4”를 포함하는 레코드를 검색하려면, OR를 사용하여 여러 번 작성해야 합니다.

SELECT * FROM users
WHERE FIND_IN_SET('3', favorite_ids) OR FIND_IN_SET('4', favorite_ids);

조건이 더 복잡해지면, 애플리케이션에서 SQL을 동적으로 빌드하거나 정규화된 테이블 구조로 마이그레이션하는 것을 고려해야 합니다.

4. FIND_IN_SET을 다른 검색 방법과 비교하기

일반적인 대안: IN과 LIKE

MySQL에서 FIND_IN_SET 외에도, 값이 포함되어 있는지 확인하기 위해 IN 절이나 LIKE 절을 사용하는 것을 볼 수 있습니다. 그러나 각 방법은 다르게 동작하며, 잘못된 것을 사용하면 잘못된 쿼리 결과가 발생할 수 있습니다.

여기서는 FIND_IN_SET과 어떻게 다른지, 그리고 각 접근 방식을 언제 사용하는지 명확히 해보겠습니다.

IN 절과의 비교

IN 절은 일반적으로 값이 여러 상수 값 중 하나와 일치하는지 확인하는 데 사용됩니다.

-- Example of IN (this does NOT search inside "favorite_ids" for the value 3)
SELECT * FROM users WHERE favorite_ids IN ('3');

이 경우, favorite_ids가 “3”과 정확히 일치하는 레코드만 반환됩니다. 즉, "1,3,5" 같은 값은 일치하지 않으며, 열 값이 정확히 "3"인 행만 일치합니다.

반대로, FIND_IN_SET은 쉼표로 구분된 목록 내 요소의 위치를 확인하여, 다음과 같이 “3”을 포함하는 레코드를 정확히 검색할 수 있게 합니다:

SELECT * FROM users WHERE FIND_IN_SET('3', favorite_ids);

✅ 주요 사용 지침:

• IN : 정규화된 테이블에서 사용 (예: SELECT * FROM posts WHERE category_id IN (1, 3, 5) )
• FIND_IN_SET : 비정규화된 쉼표로 구분된 문자열에서 사용

LIKE 절과의 비교

기술적으로 LIKE를 부분 매칭에 사용할 수 있지만, 중요한 함정이 있습니다.

-- A common mistake with LIKE
SELECT * FROM users WHERE favorite_ids LIKE '%3%';

이 쿼리는 진정으로 “값 3을 포함”하는 것을 의미하지 않습니다—문자 “3”을 포함하는 모든 문자열과 일치하며, "13", "23", 또는 "30"을 잘못 매칭할 수 있습니다.

이로 인해 3이 독립적인 값으로 존재하는지 신뢰할 수 있게 감지할 수 없습니다.

✅ 주요 사용 지침:

• LIKE : 퍼지 텍스트 검색에 유용하지만, 쉼표로 구분된 경계를 인식할 수 없음
• FIND_IN_SET : 쉼표로 구분된 목록 내 독립적인 값 매칭을 정확히 확인

성능 차이

특히, FIND_IN_SET은 인덱스를 사용할 수 없으며 종종 전체 테이블 스캔을 유발합니다. 대규모 데이터셋을 다룰 경우, 스키마를 재고해야 할 수 있습니다.

5. 중요한 주의사항 및 모범 사례

쉼표를 포함하는 값과 호환되지 않음

FIND_IN_SET 함수는 쉼표로 구분된 단순한 값 목록을 전제로 합니다. 따라서 목록의 개별 요소 자체에 쉼표가 포함되어 있으면, 함수는 의도대로 동작하지 않습니다.

잘못된 예시:

SELECT FIND_IN_SET('1,2', '1,2,3,4'); -- Result: 1

이와 같이 사용하면 전체 문자열을 잘못 평가하기 때문에 부정확한 매치가 발생할 수 있습니다.
개별 값에 쉼표가 포함되지 않음을 보장할 수 있을 때만 이 함수를 사용해야 합니다.

성능 우려

FIND_IN_SET은 인덱스를 사용할 수 없으므로 전체 테이블 스캔을 수행합니다. 따라서 대용량 테이블에서 사용하면 쿼리 성능이 크게 저하될 수 있습니다.

해결 방법:

• 쉼표로 구분된 값을 저장하는 대신 관계를 정규화 하고 별도의 테이블에서 관리합니다.
• 성능이 중요한 환경에서는 임시 테이블 확장 이나 JOIN 기반 전략 을 고려합니다.

예를 들어 user_favorites와 같은 중간 테이블을 만들면 인덱스를 활용해 더 빠르게 검색할 수 있습니다:

SELECT users.*
FROM users
JOIN user_favorites ON users.id = user_favorites.user_id
WHERE user_favorites.favorite_id = 3;

가독성 및 유지보수성

FIND_IN_SET은 편리해 보일 수 있지만 다음과 같은 여러 단점을 가지고 있습니다:

• 쿼리가 직관적이지 않음(위치 값을 반환)
• 값 추가·삭제가 번거로움
• 단일 컬럼에 여러 의미가 들어가 데이터 무결성을 보장하기 어려움

따라서 유지보수성과 데이터 무결성이 중요할 때는 스키마 자체를 수정하는 것이 가장 좋은 방법입니다.

FIND_IN_SET을 반드시 사용해야 할 때

레거시 시스템이나 타사 제품 등에서 쉼표로 구분된 컬럼을 사용할 수밖에 없는 경우가 있습니다. 이런 경우 다음과 같은 주의를 기울이세요:

• 검색 범위를 줄이기 위해 다른 필터 조건을 먼저 적용
• 이중 쉼표, 앞·뒤 공백 등 포맷 오류 방지
• 가능하면 애플리케이션 레이어에서 보조 처리를 수행

6. 자주 묻는 질문 (FAQ)

FIND_IN_SET은 인덱스를 사용할 수 있나요?

아니요, FIND_IN_SET은 인덱스를 사용할 수 없습니다. 내부적으로 문자열을 분할·평가하기 때문에 MySQL의 인덱스 최적화 혜택을 받지 못합니다.

그 결과 대용량 테이블에서 사용하면 쿼리 성능이 크게 저하될 수 있습니다. 성능이 중요한 시스템에서는 스키마를 재설계하거나 데이터를 정규화하는 것을 고려하세요.

혼합된 숫자와 문자열에서도 올바르게 작동하나요?

대체로는 정상 동작하지만 비교가 문자열 기준으로 이루어진다는 점을 기억해야 합니다. 숫자와 문자열 값이 혼합될 경우 예상치 못한 동작이 발생할 수 있습니다.

예를 들어 다음 두 쿼리는 모두 3에 매치됩니다:

SELECT FIND_IN_SET(3, '1,2,3,4');     -- Result: 3
SELECT FIND_IN_SET('3', '1,2,3,4');   -- Result: 3

하지만 FIND_IN_SET('03', '01,02,03')처럼 앞에 0이 붙은 경우 매칭 동작에 영향을 줄 수 있습니다.
값의 포맷을 통일하는 것이 가장 안전합니다.

한 번에 여러 값을 검색하려면 어떻게 해야 하나요?

FIND_IN_SET은 단일 검색 값만 받을 수 있기 때문에 “3 또는 4”와 같이 여러 값을 찾고 싶다면 OR을 사용해 여러 번 호출해야 합니다:

SELECT * FROM users
WHERE FIND_IN_SET('3', favorite_ids)
   OR FIND_IN_SET('4', favorite_ids);

조건이 복잡해지면 애플리케이션 레이어에서 동적으로 SQL을 구성하거나 정규화된 테이블 구조로 마이그레이션하는 것을 고려하세요.

FIND_IN_SET이 성능 문제를 일으킵니다. 어떻게 해야 하나요?

다음과 같은 전략이 효과적입니다:

• 정규화된 테이블 설계로 전환
• 검색 범위를 줄이기 위해 먼저 필터링 조건 적용
• 소규모 데이터셋에만 사용
• 전체 텍스트 검색이나 JSON 데이터 타입 등 구조화된 포맷으로 마이그레이션

최신 MySQL 버전은 JSON 데이터 타입을 지원합니다. 예를 들어, roles 열을 JSON 배열로 관리한다면 JSON_CONTAINS()를 사용하여 유연하고 효율적인 검색을 수행할 수 있습니다.

미래에 FIND_IN_SET이 폐기될까요?

MySQL 8.0 기준으로 FIND_IN_SET은 공식적으로 폐기되지 않았습니다. 그러나 비정규화된 데이터 구조(쉼표로 구분된 열)는 권장되지 않으므로, 이 함수의 실질적인 사용은 시간이 지나면서 감소할 것으로 예상됩니다.

데이터베이스를 재설계할 때, 정규화된 구조나 JSON 기반 설계를 채택하는 것이 이상적입니다.

7. 결론

FIND_IN_SET의 기능과 장점 검토

FIND_IN_SET 함수는 쉼표로 구분된 문자열을 검색할 때 MySQL에서 매우 유용합니다. 특히 여러 값을 저장하는 단일 열 내에서 특정 값을 포함하는 레코드를 추출해야 할 때 특히 도움이 됩니다.

간단한 구문을 통해 LIKE나 IN 절로 정확하게 달성하기 어려운 독립적인 값 일치 확인을 가능하게 합니다. 쉼표로 구분된 목록 내에서 고유한 요소를 감지하는 이 능력이 그 가장 큰 강점입니다.

사용 시 중요한 고려사항

동시에 여러 제한사항과 중요한 고려사항이 있으므로, 신중하게 생각하지 않고 과도하게 사용해서는 안 됩니다:

• 인덱스를 사용할 수 없음 (검색 속도가 느려질 수 있음)
• 쉼표를 포함하는 값과 호환되지 않음
• 비정규화된 구조를 가정함
• 단일 값 검색만 지원함 (다중 검색은 OR 조건이 필요함)

이러한 특성을 이해하는 것이 함수를 적절히 사용하는 데 필수적입니다.

언제 사용해야 하고 — 언제 사용하지 말아야 하는가

실무에서 적용하는 방법

• 기존 데이터베이스 구조 내에서 작업하는 유연한 도구로 이해하기
• 미래에 정규화된 데이터 설계를 채택할지 결정할 때 참조점으로 사용하기
• 빠른 해결책으로 사용하는 대신, 함수가 실제로 무엇을 하는지 명확히 이해하기

유지보수성과 가독성을 우선시하는 개발자에게는, 이를 “일시적으로 사용하지만 결국 졸업하는” 함수로 생각하는 것이 가장 좋습니다.