MySQL에서는 FULLTEXT 인덱스와 함께 MATCH() ... AGAINST() 구문을 사용하면 특정 열에 원하는 문자열이 포함된 행을 빠르고 효율적으로 검색할 수 있습니다. LIKE 연산자를 사용한 단순 패턴 매칭보다 성능이 뛰어나 대용량 텍스트 데이터 검색에 적합합니다. 이번 글에서는 예제를 통해 사용 방법을 단계별로 살펴보겠습니다.
1. 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable1833
(
Name varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
2. FULLTEXT 인덱스 추가
MATCH...AGAINST 구문을 사용하려면 검색 대상 열에 반드시 FULLTEXT 인덱스가 설정되어 있어야 합니다. ALTER TABLE 문으로 인덱스를 추가합니다.
mysql> alter table DemoTable1833 ADD FULLTEXT(Name);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
3. 샘플 데이터 삽입
INSERT 명령을 사용해 테이블에 여러 개의 레코드를 입력합니다.
mysql> insert into DemoTable1833 values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1833 values('Adam Smith');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1833 values('Chris Brown');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1833 values('John Smith');
Query OK, 1 row affected (0.00 sec)4. 전체 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select * from DemoTable1833;
실행 결과는 다음과 같습니다.
+-------------+
| Name |
+-------------+
| John Doe |
| Adam Smith |
| Chris Brown |
| John Smith |
+-------------+
4 rows in set (0.00 sec)
5. MATCH ... AGAINST로 문자열 검색하기
다음은 Name 열에서 'John' 또는 'Smith'라는 단어가 포함된 행을 검색하는 쿼리입니다.
mysql> select * from DemoTable1833 where MATCH(Name) AGAINST('+John Smith+');실행 결과는 다음과 같습니다.
+------------+
| Name |
+------------+
| John Smith |
| John Doe |
| Adam Smith |
+------------+
3 rows in set (0.00 sec)
결과를 보면 두 단어를 모두 포함한 'John Smith'가 가장 관련도가 높아 첫 번째로 반환되었고, 'John'만 포함한 'John Doe'와 'Smith'만 포함한 'Adam Smith'가 그 뒤를 이었습니다. 이처럼 MATCH...AGAINST는 일치 여부뿐 아니라 관련도(relevance)를 기준으로 결과를 정렬해 줍니다.
참고: 불리언 모드(Boolean Mode) 활용
검색어 앞에 붙인 + 기호는 불리언 모드에서 "반드시 포함"을 의미하는 연산자입니다. 불리언 모드를 명시적으로 지정하려면 다음과 같이 IN BOOLEAN MODE 옵션을 사용할 수 있습니다.
-- 'John'과 'Smith'를 모두 포함한 행만 검색
SELECT * FROM DemoTable1833
WHERE MATCH(Name) AGAINST('+John +Smith' IN BOOLEAN MODE);
이렇게 하면 'John Smith' 행만 정확히 반환됩니다. 상황에 맞게 자연어 모드와 불리언 모드를 선택해 사용하면 됩니다.