MySQL에서 ORDER BY로 키워드 일치 순서대로 정렬하는 방법

MySQL에서 특정 키워드가 포함된 행을 우선적으로 정렬하고 싶을 때가 있습니다. 이럴 때 ORDER BY 절과 CASE, INSTR() 함수를 함께 사용하면 원하는 키워드 일치 순서대로 결과를 정렬할 수 있습니다.

예제 테이블 생성하기

먼저 실습에 사용할 테이블을 생성합니다.

mysql> create table DemoTable602 (GameName text);
Query OK, 0 rows affected (0.55 sec)

샘플 데이터 삽입하기

INSERT 명령을 사용해 몇 개의 게임 이름 레코드를 삽입합니다.

mysql> insert into DemoTable602 values('Candy cash game');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable602 values('Pubg');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable602 values('cash Candy game');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable602 values('subway');
Query OK, 1 row affected (0.14 sec)

저장된 전체 레코드 확인하기

SELECT 문으로 테이블의 모든 레코드를 조회합니다.

mysql> select *from DemoTable602;

위 쿼리는 다음과 같은 결과를 출력합니다.

+-----------------+
| GameName        |
+-----------------+
| Candy cash game |
| Pubg            |
| cash Candy game |
| subway          |
+-----------------+
4 rows in set (0.00 sec)

키워드 일치 기준으로 ORDER BY 정렬하기

다음은 'Candy'와 'cash' 키워드가 포함된 행을 우선 정렬하는 쿼리입니다. CASE 문으로 'Candy'가 포함되지 않은 행을 뒤로 밀어내고, INSTR() 함수로 'cash'가 포함된 행을 앞쪽에 배치합니다.

mysql> select *from DemoTable602
    order by
      CASE WHEN instr(GameName, 'Candy') = 0 then 1 else 0 end,
      instr(GameName, 'cash') desc;

실행하면 다음과 같이 키워드가 포함된 행이 우선적으로 정렬된 결과를 확인할 수 있습니다.

+-----------------+
| GameName        |
+-----------------+
| Candy cash game |
| cash Candy game |
| Pubg            |
| subway          |
+-----------------+
4 rows in set (0.00 sec)

쿼리 동작 원리

INSTR(문자열, 검색어) 함수는 문자열 내에서 검색어가 처음 나타나는 위치를 반환하며, 포함되지 않으면 0을 반환합니다. 따라서 첫 번째 정렬 조건인 CASE 문은 'Candy'가 없는 행에 1을 부여해 뒤로 보내고, 두 번째 조건인 instr(GameName, 'cash') desc는 'cash'가 더 앞쪽에 위치한 행부터 내림차순으로 정렬합니다. 이 방식을 응용하면 여러 키워드의 가중치를 조합한 맞춤형 검색 정렬도 손쉽게 구현할 수 있습니다.