전체 텍스트 검색
MySQL의 전체 텍스트 검색은 단순 키워드 검색보다 고급 방식으로 텍스트 기반 데이터를 검색할 수 있는 검색 기능입니다.
텍스트 필드 내에서 특정 단어 또는 구를 검색하고 관련성을 기준으로 결과의 순위를 지정할 수 있습니다.
FULLTEXT INDEX생성
전체 텍스트 인덱스는 innoDB, MyISAM 테이블만 지원한다.
CHAR, VARCHAR, TEXT의 열에만 생성된다.
인덱스 힌트의 사용이 일부 제한된다.
여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.
- 문법
-- 형식1
CREATE TABLE 테이블이름(
컬럼1 데이터_타입,....
FULLTEXT 인덱스_이름(컬럼1)
);
-- 형식 2
CREATE TABLE 테이블_이름(
컬럼1 데이터_타입, ....
);
ALTER TABLE 테이블_이름
ADD FULLTEXT(컬럼1);
-- 형식3
CREATE TABLE 테이블_이름(
컬럼1 데이터_타입, ....
);
CREATE FULLTEXT INDEX 인덱스_이름
ON 테이블_이름 (컬럼1);중지 단어
전체 텍스트에 인덱스를 적용하면 양이 너무 커진다.
이것을 조금이라도 줄이려면 굳이 인덱스를 적용 시키지 않아도 되는 단어들은 빼는 것이 좋다.
이것을 행하는 것이 '중지단어'이다.
예를 들면 [너무, 아주, 꼭, 그래서] 이런 단어로 검색할 일이 없으니 인덱스를 적용하지 않는 것이 좋다는 것이다.
검색
-
LIKE 연산자 사용
-
LIKE 연산자는 특정 문자열이 다른 문자열 내에 포함되어 있는지를 확인하는 데 사용됩니다.
-
다음과 같은 문법을 사용하여 검색할 수 있습니다.
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '%검색어%'; -
위의 문법에서 %는 임의의 문자열을 나타냅니다.
-
-
자연어 검색 사용
-
FULLTEXT 검색은 MySQL에서 제공하는 전문 검색 기능입니다.
-
다음과 같은 문법을 사용하여 검색할 수 있습니다.
SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('책'); -- 책이, 책을 이런거 X 딱 '책'만 검색한다. SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('책 연필'); -- 책과 연필 둘중 하나가 포함된 글을 찾을 수 있다. -
위의 문법에서는 FULLTEXT 인덱스가 지정된 컬럼명을 사용해야 합니다.
-
-
불린 모드 검색
-
문장이 정확히 일치하지 않는 것도 검색한다.
-
IN BOOLEAN MODE 옵션을 추가한다.
-
'+' 필수 , '-' 제외, '*' 부분 검색 기능을 제공한다.
SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('영화*' IN BOOLEAN MODE); -- 영화를, 영화가, 등 영화가 앞에 들어간 결과를 검색할 수 있다. SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('영화' IN BOOLEAN MODE); -- 정확히 '영화'가 결과인 글을 검색할 수 있다. SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('영화 +공포' IN BOOLEAN MODE); -- 영화가 들어간 결과중 공포를 포함한 것을 검색 반대로 '-'라면 제외한 결과를 검색한다.
-
-
REGEXP 사용
-
REGEXP는 정규 표현식을 사용하여 검색하는 방법입니다.
-
다음과 같은 문법을 사용하여 검색할 수 있습니다.
SELECT * FROM 테이블명 WHERE 컬럼명 REGEXP '검색어'; -
위의 문법에서는 검색어가 정규 표현식으로 표현되어야 합니다.
-
위의 방법 중에서 FULLTEXT 검색을 사용하는 것이 가장 효율적인 방법입니다. 하지만 FULLTEXT 인덱스를 지정해야 하기 때문에, 데이터베이스에 대한 권한이 있는 경우에만 사용할 수 있습니다.
Share Prompt
검색 키워드 최소 길이 설정
- innodb_ft_min_token_size=2 → 최소 길이 2로 설정 (기본값은 3이다.)
FULLTEXT INDEX 삭제
ALTER TABLE 테이블_이름
DROP INDEX FULLTEXT(열이름);실습
- fulltexttbl - 실습 테이블 결과
- 남자를 포함한 글 검색
select * from fulltexttbl where description like '%남자%';- 결과 - 모든 인덱스를 순회하여 결과를 찾았다. → 성능이 안좋음
- 전체 텍스트 인덱스 생성 및 확인
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);
SHOW INDEX FROM FulltextTbl;- index type → FULLTEXT
- 검색 - 인덱스를 사용
SELECT * FROM FulltextTbl WHERE MATCH(description) AGAINST('남자*' IN BOOLEAN MODE);- 결과 - FULLTEXT INDEX 사용하여 검색된다.
FULLTEXT INDEX로 만들어진 단어 확인
SET GLOBAL innodb_ft_aux_table = 'fulltextdb/fulltexttbl';
SELECT word, doc_count, doc_id, position
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
실습 - 중지단어 추가
- FULLTEXT INDEX는 수정이 불가능하여, 삭제 후 다시 만들어야 한다.
DROP INDEX idx_description ON FulltextTbl;- 중지 단어를 저장할 테이블 생성 ( 열 이름과 타입은 반드시 아래와 같이 작성한다.)
- 열 이름: value
- 테이터 타입 : varchar
CREATE TABLE 테이블_이름 (value varchar(30));- 중지 단어 추가
INSERT INTO 테이블_이름 values ('같은'),('가진');- 중지 단어를 추가했으니 시스템변수
innodb_ft_server_stopword_table에 적용시킨다.- 여기 따옴표 안에 입력되는 값은 반드시 소문자로 입력한다.
SET GLOBAL innodb_ft_server_stopword_table='fulltextdb/user_stopword';
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';- FULLTEXT INDEX 생성 및 확인
SELECT word, doc_count, doc_id, position
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;- 결과 - 중지 단어가 적용된 것을 확인할 수 있다.
