MYSQL FULLTEXT SEARCH

MATCH (COL) AGAINST (‘contents’)

• MYSQL에서 전문검색 가능
• MYISAM DB만 가능(5.6 버전 이후는 INNO DB도 가능)
• 대상 컬럼 자체의 형태소들을 분리하여 검색

장점

• 매우 많은 데이터에 대한 검색도 일정 수준 이상의 속도를 보장해준다.

단점

• prefix search만 가능( keyword% 검색만 가능)
• 검색 품질은 …
• text, binary char, varchar 타입만 가능
• 한글검색은 utf-8만 가능

검색방식

1. 자연어검색
   - keyword가 포함되어 있는 row를 찾는다.
2. 불린 모드 검색
   - keyword가 포함된 row를 찾는것은 자연어 검색과 일치하지만 추가적인 규칙을 이용가능
3. 쿼리확장검색
   - 자연어검색을 이용하여 데이터 셋을 만들어 다시 검색

사용방법

1. 테이블 생성시 column에 FULLTEXT KEY 등록 or alter table을 통한 FULLTEXT KEY등록
2. select col from table where match(col) against(‘contents’)의 형태로 사용

사용 예

1. 테이블생성

CREATE TABLE `tbl_full` (
  `key` int(10) unsigned NOT NULL COMMENT '식별값',
  `content` text COMMENT '내용',
  PRIMARY KEY (`key`),
  FULLTEXT KEY letter_content (letter_content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테이블';

2. 실행(자연어검색)

select * from tbl_full where match(content) against ('내용');

2. 실행(boolean mode 검색)

select * from tbl_full where match(content) against ('내용*' in boolean mode);
-- col like '내용%' 의 형태로 검색되지만 동일하지는 않음 
-- "가나다라 내용마바" 검색가능
-- "가나다라내용마바" 검색불가능

2. 실행(쿼리확장검색)

select * from tbl_full where match(content) against ('내용*' WITH QUERY EXPANSION);
-- col like '내용%' 의 형태로 검색되지만 동일하지는 않음 
-- "가나다라 내용마바" 검색가능
-- "가나다라내용마바" 검색불가능

불린 검색시 사용 가능 키워드

• 연산자 없음: 정확하게 일치하는 단어 반환
  > against(‘keyword1 keyword2’)
  > 정확하게 keyword1 또는 keyword2 일치하는 row 검색
• + : keyword가 포함된 행 반환
  > against(‘+keyword1 +keyword2’ in boolean mode)
  > keyword1과 keyword2 포함한 row 검색
• - : keyword가 포함된 행 제외
  > against(‘+keyword1 -keyword2’ in boolean mode)
  > keyword1은 포함하지만 keyword2 포함하지 않는 row 검색
• * : keyword를 포함한 prefix 형태의 검색 가능
  > against(‘keyword1*’ in boolean mode)
  > keyword1은 포함(keyword1%)한 row 검색
• <> : 값의 관계 비교, 해당하는 값 반환
• “” : 해당 콤마 사이 keyword 반환
  > “keyword1 keyword2” 는 찾지만 “keyword1 keyword3 keyword2”는 찾을수 없음

참고

• FULLTEXT 쿼리(http://irismusic.tistory.com/28)
• MySQL 풀 텍스트(FULLTEXT) 검색하기(https://kmongcom.wordpress.com/2014/03/28/mysql-%ED%92%80-%ED%85%8D%EC%8A%A4%ED%8A%B8fulltext-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0/)