MYSQL 에서 FIND_IN_SET 함수와 LOCATE 함수의 차이점

MYSQL 을 사용하다보면 mysql 함수를 사용하면 더욱 편리한 경우가 있습니다. 예를 들어 특정 문자열로 시작하는 데이터를 가져올때 그런데 그 특정 문자열이 배열로 되어있다면 어떻게 해야할까요? 물론 조건을 사용해서 할 수가 있겠지만은 간단하게 mysql 함수로 더욱 간결하게 쿼리(query)를 작성할 수 있는 것이지요.

예를 들어 어느 데이터에서 특정 필드 값이 영문중에 a~i 까지의 소문자가 하나라도 들어간 데이터를 출력한다고 했을때 어떻게 해야할까요? 물론 조건문을 많이 사용하면 쿼리문장을 길게해서 사용할 수도 있지만 mysql 함수를 이용하면 손쉽고도 짧게 사용할 수가 있습니다. 참고로 제가 사용하는 단어는 쉬운 이해를 위해 사용하는 것이니 양해 부탁드립니다.

우선은 FIND_IN_SET 함수에 대해 알아보겠습니다. 우선은 함수의 역할을 이해하기 위해서 아래와 같이 query 를 실행하면.. 값은 2가 됩니다.

SELECT FIND_IN_SET('b','a,b,c,d,e,f,g,h,i');

함수 이름대로 어떠한 세트(?)안에서 찾는다?는 의미 인데요.. 즉 문자 b 를 값이 a,b,c,d,e,f,g,h,i 인 문자세트(?)에서 찾는다라는 의미가 됩니다. 그리고 여기서 값 2는 문자세트를 콤마로 구분되어지는 배열(세트) 이라고 볼때 b가 두번째에 위치해 있기 때문에 값이 2로 반환이 됩니다. 만일 f 를 찾는다면 값은 6이 되겠구요. 그런데 세트안에 없는 값인 j 또는 k 를 찾는다면 값은 0 을 반환합니다. 왜냐하면 세트(배열)안에 없기 때문이지요.

그런데 FIND_IN_SET 함수와는 다르게 LOCATE 함수가 있는데요. LOCATE 함수는 세트가 배열이라기 보다는 하나의 문자열로 인식한다고 봐야한다고 할까요? 함수 이름도 LOCATE로 위치를 뜻하기 때문에 세트(배열)의 의미는 없다고 봐야할것 같습니다. 즉 첫번째 매개변수의 값이 두번째 매개변수의 몇번째 위치에 있는지 숫자로 값이 나온다고 생각하시면 됩니다.

SELECT LOCATE('b','a,b,c,d,e,f,g,h,i');

위의 첫번째 FIND_IN_SET 함수와 매개변수 값은 동일하게 하면서 함수명을 LOCATE 로 바꾸었는데요. 값은 과연 얼마가 표시가 될까요? 여기서의 값은 바로 3 입니다. 왜냐하면 'a,b' 라는 문자열 중에서 3가 3번째에 위치해 있기 때문입니다. 즉 콤마(,)도 문자에 포함이 되어 카운트가 되기 때문입니다.

그런데 LOCATE 함수를 사용하실 때 주의하실 점이 있는데요. 바로 숫자의 경우에 3 이라는 값을 찾을때 13, 30 등 3이 들어간 값도 찾기 때문에 원치 않는 결과값을 얻을 수도 있습니다. 따라서 단지 숫자 3이라는 것만 찾을 때에는 FIND_IN_SET 함수를 이용하는게 맞을것 같구요. 단순히 문자열에서 특정 문자를 찾을때에는 LOCATE 함수를 사용하시는게 좋을것 같습니다.

아래의 예제를 보시면..

SELECT FIND_IN_SET(3,'11,12,13,30,33');

=> 0을 반환함 (값이 3 인것이 없기 때문에)

SELECT LOCATE(3,'11,12,13,30,33');

=> 8을 반환함 (값이 3이라는 것이 포함이 되어 있기 때문에.. 그리고 처음으로 찾은 위치값을 반환)

위의 보는것과 같이 각 함수의 역할이 다르기 때문에 각 특성에 맞게 사용하는게 좋습니다.

그리고 FIND_IN_SET 함수를 좀 더 적용하는 예제를 보여드리면..

select * from user where locate('010-0123', phone);

=> user 테이블에서 phone 이라는 필드값에서 010-0123 으로 시작되는 모든 데이터를 반환

 select * from user where find_in_set('010-0123-4567', phone);

=> user 테이블에서 phone 이라는 필드값에서 010-0123-4567 인 데이터 만을 반환

참고로 오른쪽은 왼쪽 매개변수보다 큰 범위?의 값이 들어가야 합니다. 방금 위의 예제 2개에서는 제가 필드명으로 선언하였구요. 

SELECT *   

FROM author   

WHERE FIND_IN_SET(left(aut_name,7),'William,steven,jung,angalo')>0;

예를 들어 위의 예제는 author 이라는 테이블에서 aut_name 값이 왼쪽부터 7자리의 값이 William 또는 steven 또는 jung 또는 angalo 인 데이터를 가져오라는 쿼리 문장입니다. 조건문으로 작성하려면 꽤 길어질 수 있는 문장인데 위의 문장으로 짧게 끝났습니다.

더 많은 정보를 참고하시려면 여기를 클릭하세요. 영문 사이트 이지만 도움이 되는것 같습니다.