[ MYSQL ] FULLTEXT Search

파라미터 변경

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

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

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