DB/MYSQL

[ MYSQL ] FULLTEXT Search

HEON.D 2022. 1. 12. 16:17

파라미터 변경

ngram_token = 2
innodb_ft_min_token_size = 2
ft_min_word_len = 2

 

적용확인

show variables like 'ft_min%';
show variables like 'innodb_ft_min%';

 

검색 인덱스 추가

ALTER TABLE MANUAL ADD FULLTEXT(칼럼명);

 

조회 쿼리

SELECT 칼럼, MATCH (칼럼) AGAINST ('키워드*' IN BOOLEAN MODE) AS score
FROM 테이블 ORDER BY score desc;

 

SELECT manual_title,
  (
    ((LENGTH(manual_title) - LENGTH((REPLACE(manual_title, '관리', '')))) / LENGTH('관리'))
    + ((LENGTH(manual_title) - LENGTH((REPLACE(manual_title, '안전', '')))) / LENGTH('안전'))
    + ((LENGTH(manual_title) - LENGTH((REPLACE(manual_title, '발주', '')))) / LENGTH('발주'))
  ) AS score_title
FROM MANUAL
ORDER BY score_title DESC

 

https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

 

MySQL :: MySQL 5.7 Reference Manual :: 12.10.2 Boolean Full-Text Searches

12.10.2 Boolean Full-Text Searches MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier. With this modifier, certain characters have special meaning at the beginning or end of words in the search string. In the following query,

dev.mysql.com

https://database.guide/how-the-match-function-works-in-mysql/

 

How the MATCH() Function Works in MySQL | Database.Guide

In MySQL, the MATCH() function performs a full-text search. It accepts a comma separated list of table columns to be searched. The table/s must have a FULLTEXT index before you can do a full-text search against them (although boolean queries against a MyIS

database.guide

 

'DB > MYSQL' 카테고리의 다른 글

[ MYSQL ] DB 커넥션 정보 utf8mb4 이모지  (0) 2022.05.20
[ MYSQL ] AutoIncrement 값 초기화  (0) 2022.01.13
[ MYSQL ] RDS time_zone 변경  (0) 2021.11.19
[ MYSQL ] Timezone 확인  (0) 2021.11.19
[ MYSQL ] 컬럼 구조 확인  (0) 2021.11.17