파라미터 변경
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 |