[MYSQL] 문자열을 구분자로 분리 후 일치하는지 구분하는 방법

 

# 함수추천 : FIND_IN_SET

 

요구사항 : 여러 문자열을 구분자로 연결한 컬럼을 대상으로 분리하는 경우 검색어와 완벽히 일치하는 조건 검색이 필요하다.

장점 : 그룹개수에 재한이 없고, 쿼리문이 단순해진다. 문자열을 분리 후 검색어와 비교하는 쿼리를 검색해보면 복잡한 쿼리가 많이 보인다.

단점 : 구분자는 쉼표만 지원한다. 인덱스를 타지 않는다.

 

///

 

FIND_IN_SET 함수란?

FIND_IN_SET(str, strlist)

콤마로 구분되어 있는 strlist 문자열('a,b,c,d')에 매칭되는 str문자('b')가 있는지 확인해줍니다.

매칭되는 것이 있을 경우 몇번째에 있는지 포지션 값을 리턴하고 없으면 0을 리턴합니다.(strlist 가 빈문자열이어도 0 리턴) 즉 strlist 라는 SET 에 일치하는 문자열이 있는지 FIND하는 함수이죠.

mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2

첫번째 인자에 상수이고 두번째 인자가 SET 타입의 컬럼이라면 비트 연산이 가능합니다. SET 타입의 특성 때문이죠. 몇가지 정해진 리스트가 있는 SET 타입 컬럼에 특정 값을 찾는 용도로는 괜찮을 것 같지만, 그 외에는 사용을 권장할 것 같지 않습니다. 예를 들어 ID 성격의 컬럼에 저렇게 사용하는 것은 좋은 방법이 아닐 것 같습니다.

​

컬럼을 두번째 인자에 쓰는 방법

참고로 아래 두가지는 비슷하지만 결과는 다릅니다.

set_col 이 '3, 33, 333' SET으로 이루어져 있을 경우 3을 찾는다고 가정하면 결과가 달라질 것입니다.

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value', set_col) > 0; -- not same mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

컬럼을 첫번째 인자에 쓰는 방법

반대로 컬럼을 앞에 사용해서 컬럼의 내용이 자신이 정한 SET 에 몇번째 인자인지 검사할 수도 있습니다.

이런경우 매칭 안되어서 0인 결과를 원하지 않는다면, IN 으로 대체해도 좋을 것 같습니다.

포지션을 꼭 알아야 한다면 한번 검색하고 다시 서브쿼리로 해당 함수를 사용할수도 있지만 성능과 분산을 고려한다면 간단한 연산은 application 에서 처리하는 것이 좋을 것 같습니다.

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET(col, 'red,blue,white');

​

사용해야할까?

사용하라고 함수를 만들었으니 사용해야 겠지만, 성능을 고려한다면 제한적으로 사용하는 것이 좋을 것 같습니다.

컬럼을 함수로 감싸면 인덱스를 활용할 수가 없습니다. 다른 조건으로 검색할 row 가 충분히 성능적으로 커버가 된 상태에서 제한적으로 사용하거나 전체 건수가 적은 경우 사용하는 것이 바람직할 것입니다.

​

ID에 해당하는 컬럼이었는데, 첫번째 인자에 이 컬럼을 사용하는 경우를 봤습니다.

아마도 두번째 strlist 가 다른 쿼리의 결과로 문자열을 리턴받았을 가능성이 있습니다. 이런경우 string 에서 값을 찾으려고 하다보니 FIND_IN_SET 을 자구책으로 사용했고 인덱스를 활용하지 못해 풀스캔하는 문제가 발생할 수 있습니다. 이런 경우 IN (123, 449, 906) 으로 변경해주는 것이 그나마 range 스캔을 할 수 있는 방법일 것 같습니다.

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET(col_id, '123,449,905');

​

​

참고자료

FIND_IN_SET 함수 설명

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set