MySQL에서 검색어와의 관련성을 기준으로 결과를 정렬하려면 CASE 문을 활용할 수 있습니다. CASE 문은 조건에 따라 우선순위를 부여하여, 검색어가 이름의 시작 부분에 있는 경우를 가장 높은 관련성으로 처리하는 식으로 유연한 정렬이 가능합니다.
개념을 이해하기 위해 실제 예제 테이블을 만들어 단계별로 살펴보겠습니다.
1. 예제 테이블 생성
먼저 사용자 정보를 저장할 테이블을 생성합니다.
mysql> create table OrderByRelevance
-> (
-> UserId int,
-> UserName varchar(200)
-> );
Query OK, 0 rows affected (0.51 sec)
2. 샘플 데이터 삽입
INSERT 명령을 사용해 'Carol'이라는 단어가 포함된 여러 형태의 이름을 포함한 레코드를 입력합니다.
mysql> insert into OrderByRelevance values(101,'Carol Smith');
Query OK, 1 row affected (0.18 sec)
mysql> insert into OrderByRelevance values(102,'Carol Adams');
Query OK, 1 row affected (0.17 sec)
mysql> insert into OrderByRelevance values(103,'Carolnathan Todd');
Query OK, 1 row affected (0.33 sec)
mysql> insert into OrderByRelevance values(104,'John Smith');
Query OK, 1 row affected (0.22 sec)
mysql> insert into OrderByRelevance values(105,'Sam Carol Bond');
Query OK, 1 row affected (0.12 sec)
3. 전체 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from OrderByRelevance;
출력 결과
+--------+------------------+
| UserId | UserName |
+--------+------------------+
| 101 | Carol Smith |
| 102 | Carol Adams |
| 103 | Carolnathan Todd |
| 104 | John Smith |
| 105 | Sam Carol Bond |
+--------+------------------+
5 rows in set (0.00 sec)
4. 관련성 기준 정렬 쿼리 실행
이제 CASE 문을 사용해 'Carol'과의 관련성 순서대로 정렬하는 쿼리를 작성합니다. 이 쿼리는 다음과 같은 우선순위를 적용합니다.
- 우선순위 0: 이름이 'Carol'로 시작하는 경우 (예: Carol Adams)
- 우선순위 1: 이름 중간에 독립된 단어로 'Carol'이 포함된 경우 (예: Sam Carol Bond)
- 우선순위 2: 이름 끝에 'Carol'로 끝나는 경우
- 우선순위 3: 그 외의 경우 (예: Carolnathan Todd처럼 다른 단어의 일부로 포함된 경우)
mysql> select max(UserId) as Id, UserName from OrderByRelevance
-> where UserName like '%Carol%' group by UserName
-> order by case when UserName like 'Carol%' THEN 0
-> WHEN UserName like '% %Carol% %' THEN 1
-> WHEN UserName like '%Carol' THEN 2
-> else 3
-> end, UserName;
출력 결과
+------+------------------+
| Id | UserName |
+------+------------------+
| 102 | Carol Adams |
| 101 | Carol Smith |
| 103 | Carolnathan Todd |
| 105 | Sam Carol Bond |
+------+------------------+
4 rows in set (0.00 sec)
결과 분석
출력 결과를 보면 'John Smith'는 검색 조건 '%Carol%'에 해당하지 않아 자동으로 제외되었습니다. 나머지 네 개의 레코드는 CASE 문에서 정의한 우선순위에 따라 정렬되었습니다.
'Carol Adams'와 'Carol Smith'는 이름이 'Carol'로 시작하므로 가장 높은 우선순위(0)를 받았고, 알파벳 순으로 Adams가 먼저 표시됩니다. 'Carolnathan Todd'는 'Carol'이 단어의 일부로만 포함되어 우선순위 3을, 'Sam Carol Bond'는 중간에 독립된 단어로 포함되어 우선순위 1을 받습니다.
이처럼 CASE 문과 LIKE 연산자를 조합하면 MySQL에서도 원하는 관련성 규칙에 따라 검색 결과를 정밀하게 정렬할 수 있습니다. 더 복잡한 전문 검색(full-text search)이 필요하다면 MATCH...AGAINST 구문도 함께 고려해 볼 수 있습니다.