[MySQL] 텍스트 검색(Full-Text Search)

안녕하세요.

오늘은 MySQL로 전체 텍스트 검색(Full-Text Search)을 테이블에 적용하는 구체적인 예시를 통해 알려드리겠습니다.

 

 MySQL로 전체 텍스트 검색하기

▣ 테이블 생성

- blog_posts라는 테이블을 생성하여 content 컬럼에 텍스트 값들을 삽입

 

 

CREATE TABLE blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE blog_posts
ADD FULLTEXT INDEX ft_index (title, content);

INSERT INTO blog_posts (title, content, author) VALUES
('MySQL 전체 텍스트 검색 소개', '전체 텍스트 검색은 MySQL의 강력한 기능입니다. 이를 통해 대량의 텍스트 데이터를 효율적으로 검색할 수 있습니다.', 'John Doe'),
('PostgreSQL vs MySQL', 'PostgreSQL과 MySQL은 둘 다 인기 있는 관계형 데이터베이스 시스템입니다. 각각의 장단점을 비교해 보겠습니다.', 'Jane Smith'),
('데이터베이스 최적화 기법', '인덱싱, 쿼리 최적화, 캐싱 등 다양한 기법을 통해 데이터베이스 성능을 향상시킬 수 있습니다. MySQL과 PostgreSQL 모두 이러한 기법을 지원합니다.', 'Mike Johnson'),
('SQL 기초 가이드', 'SQL은 관계형 데이터베이스를 위한 표준 쿼리 언어입니다. SELECT, INSERT, UPDATE, DELETE 등의 기본 명령어를 배워봅시다.', 'Sarah Lee'),
('NoSQL vs SQL 데이터베이스', 'NoSQL 데이터베이스는 SQL 데이터베이스와 다른 특징을 가지고 있습니다. 각각의 사용 사례를 살펴보겠습니다.', 'Tom Wilson');

 

▣ MySQL 쿼리 실행하기

 - MySQL에서 텍스트 검색을 위한 쿼리는 일반적인 SELECT~WHERE문에 MATCH() ~ AGAINST()를 활용하면 됨

 - 보통 컬럼에서 특정 문자열이 포함된 값을 찾을 때 LIKE문을 많이 사용하는데 데이터의 수가 많을수록 MATCH()가 효율적임

 - 일반적인 모드의 검색은 AGAINST('target text' IN NATURAL LANGUAGE MODE) 이런식으로 실행하면 됨

SELECT * FROM blog_posts
WHERE MATCH(title, content) AGAINST('MySQL 데이터베이스' IN NATURAL LANGUAGE MODE);

 

- 결과에서 보이듯이 title과 content 열에서 MySQL 또는 데이터베이스가 포함된 row만 추출됨

id | title                       | content                                                            | author       | created_at
1  | MySQL 전체 텍스트 검색 소개   | 전체 텍스트 검색은 MySQL의 강력한 기능입니다. 이를 통해 대량의...   | John Doe     | 2023-07-08 10:00:00
2  | PostgreSQL vs MySQL         | PostgreSQL과 MySQL은 둘 다 인기 있는 관계형 데이터베이스 시스템...  | Jane Smith   | 2023-07-08 10:01:00
3  | 데이터베이스 최적화 기법      | 인덱싱, 쿼리 최적화, 캐싱 등 다양한 기법을 통해 데이터베이스 성능... | Mike Johnson | 2023-07-08 10:02:00

 

- 또한, boolean 연산자를 활용할 수도 있음

+ : 반드시 포함,  - : 반드시 포함되면 안됨
> < : 단어의 랭킹 높낮이, * : 와일드카드 문자,  "" : 특정 문자 정확히 일

SELECT * FROM blog_posts
WHERE MATCH(title, content) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);

 

 - 'MySQL'이 반드시 포함되고, 'PostgreSQL'이 반드시 미포함된 row는 하나밖에 없음

id | title                       | content                                                            | author   | created_at
1  | MySQL 전체 텍스트 검색 소개   | 전체 텍스트 검색은 MySQL의 강력한 기능입니다. 이를 통해 대량의...   | John Doe | 2023-07-08 10:00:00

 

 - 전체 텍스트 검색에서 고난이도 수준인 관련성 순위는 해당 단어의 빈도, 위치, 근접성, 문서 길이로 계산함

SELECT *, MATCH(title, content) AGAINST('데이터베이스' IN NATURAL LANGUAGE MODE) AS relevance
FROM blog_posts
WHERE MATCH(title, content) AGAINST('데이터베이스' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

 

 - 관련성 컬럼인 relevance를 만들어 '데이터베이스'와 가장 관련성이 높은 순으로 데이터를 검색해줌

id | title                       | content                                                            | author       | created_at           | relevance
3  | 데이터베이스 최적화 기법      | 인덱싱, 쿼리 최적화, 캐싱 등 다양한 기법을 통해 데이터베이스 성능... | Mike Johnson | 2023-07-08 10:02:00  | 0.22771
5  | NoSQL vs SQL 데이터베이스    | NoSQL 데이터베이스는 SQL 데이터베이스와 다른 특징을 가지고 있습...  | Tom Wilson   | 2023-07-08 10:04:00  | 0.18217
2  | PostgreSQL vs MySQL         | PostgreSQL과 MySQL은 둘 다 인기 있는 관계형 데이터베이스 시스템...  | Jane Smith   | 2023-07-08 10:01:00  | 0.13663