Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

안녕하세요.
Mysql이나 Mariadb에서 full-text 검색이 필요 할때가 있습니다.
like 또는 instr 검색이 아니라 fulltext 인덱스를 생성해서 검색의 속도도 높이고 검색 옵션도 다양하게 이용 할 수 있는 방법을 같이 알아 보겠습니다.

참고로 mysql 버전 5.5이상, innodb를 사용한다면 5.6버전 이상 부터 사용 하실 수 있습니다.

Fulltext 인덱스 생성

우선 fulltext 기능을 사용하기 위해서는 검색을 하고자 하는 컬럼에 fulltext 인덱스가 생성 되어 있어야 합니다.
fulltext 검색을 위한 fulltext 인덱스 생성 명령은 아래와 같습니다.

ALTER TABLE 테이블명
ADD FULLTEXT INDEX 인덱스명 (대상컬럼명) VISIBLE;

위 명령어로 먼저 인덱스를 생성 하시기 바랍니다.
만약, fulltext 인덱스를 생성하지 않고 기능을 사용 하신다면 아래와 같은 에러가 발생 합니다.

Can't find FULLTEXT index matching the column list.

fulltext 인덱스 에러

Fulltext 검색 글자수 설정 my.cnf

 

 

 

다음은 Fulltext 검색시 최소 글자수를 설정 하는 방법 입니다.
기본적으로 아마 4로 셋팅이 되어 있을겁니다.
이는, 무조건 4글자 이상의 검색어가 들어와야 한다는 의미 입니다.
만약 이것을 3글자로 바꾸시고 싶으시면 아래와 같이 my.cnf 에서 값을 수정 해주시면 됩니다.

ft_min_word_len = 3
innodb_ft_min_token_size = 3

my.cnf에서 수정하시는것을 잊지 마세요!
그리고 아래 쿼리를 통해서 제대로 수정이 되었는지를 확인 해주시면 됩니다.

show variables like '%ft_min%';

그리고 간혹 인덱스 재성정을 하셔야 한다면 아래 쿼리로 진행 하시기 바랍니다.

-- innodb
OPTIMIZE TABLE 테이블명;

Fulltext 검색 예제

자, 이젠 그럼 검색을 해보아야 겠죠.
실제 검색은 아래와 같은 방법으로 하시면 됩니다.
조회할 테이블과 컬럼, 그리고 검색어에 맞게 수정하여 사용 하시면 되겠습니다.

-- 검색어1 또는 검색어2 (or 검색)
SELECT * FROM Table_Name
WHERE MATCH(Col_Name) AGAINST('검색어1 검색어2');
 
-- 검색어 중간에 공백이 들어가는 경우
SELECT * FROM Table_Name
WHERE MATCH(Col_Name) AGAINST('"검색어 검색어"');
 
-- 검색어1을 검색한 결과에서 검색어2가 들어간것을 제외 할 때
SELECT * FROM Table_Name
WHERE MATCH(Col_Name) AGAINST('"검색어1" -검색어2' in boolean mode);
 
-- 검색어1, 검색어2 함께 검색이 되는 경우 (and 검색) 
SELECT * FROM Table_Name
WHERE MATCH(Col_Name) AGAINST('+"검색어1" +"검색어2"' in boolean mode);
 
 -- 검색어1과 검색어2 And 검색과 함께 검색어2의 결과도 함께 출력
SELECT * FROM Table_Name
WHERE MATCH(Col_Name) AGAINST('"검색어1" +"검색어2"' in boolean mode);

위 샘플을 잘 활요하시면 fulltext 검색을 잘 활용 하실 수 있을것 입니다.
감사합니다.

by.sTricky