MySQL에서는 ORDER BY 절과 함께 여러 개의 LIKE 연산자를 조합하여, 특정 키워드에 더 많이 일치하는 레코드를 우선적으로 정렬할 수 있습니다. 이 기법은 검색 결과의 관련도 순으로 데이터를 정렬하고 싶을 때 특히 유용합니다.
기본 문법
여러 LIKE 연산자를 ORDER BY와 함께 구현하는 기본 문법은 다음과 같습니다.
SELECT * FROM yourTableName
ORDER BY
(
yourColumnName LIKE '%yourValue1%'
)
+
(
yourColumnName LIKE '%yourValue2%'
)
+
.
.
N
DESC;동작 원리
MySQL에서 LIKE 조건식은 일치하면 1, 일치하지 않으면 0을 반환합니다. 따라서 여러 LIKE 조건을 덧셈(+)으로 연결하면 각 행이 몇 개의 패턴에 일치하는지를 나타내는 값이 되고, 이를 내림차순(DESC) 정렬하면 가장 많은 키워드와 일치하는 레코드가 맨 위로 올라오게 됩니다.
예제 테이블 생성
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> CREATE TABLE demo2 -> ( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(100), -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (1.53 sec)
레코드 삽입
INSERT 명령을 사용하여 테이블에 샘플 데이터를 입력합니다.
mysql> INSERT INTO demo2(name) VALUES('John');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO demo2(name) VALUES('David');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO demo2(name) VALUES('John Smith');
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO demo2(name) VALUES('John Doe');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO demo2(name) VALUES('David Miller');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO demo2(name) VALUES('Chris');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO demo2(name) VALUES('Bob Doe');
Query OK, 1 row affected (0.09 sec)데이터 확인
SELECT 문을 사용하여 테이블의 전체 레코드를 조회해 보겠습니다.
mysql> SELECT * FROM demo2;
위 쿼리는 다음과 같은 결과를 출력합니다.
+----+--------------+ | id | name | +----+--------------+ | 1 | John | | 2 | David | | 3 | John Smith | | 4 | John Doe | | 5 | David Miller | | 6 | Chris | | 7 | Bob Doe | +----+--------------+ 7 rows in set (0.00 sec)
여러 LIKE 연산자를 활용한 정렬 쿼리
이제 'Doe'와 'David' 두 패턴에 대해 여러 LIKE 연산자를 적용한 정렬 쿼리를 실행해 보겠습니다.
mysql> SELECT * FROM demo2 -> ORDER BY -> ( -> name LIKE '%Doe%' -> ) -> + -> ( -> name LIKE '%David%' -> ) DESC;
실행 결과
+----+--------------+ | id | name | +----+--------------+ | 2 | David | | 4 | John Doe | | 5 | David Miller | | 7 | Bob Doe | | 1 | John | | 3 | John Smith | | 6 | Chris | +----+--------------+ 7 rows in set (0.00 sec)
결과 분석
출력 결과를 살펴보면 다음과 같은 규칙으로 정렬된 것을 확인할 수 있습니다.
- 'David'와 'Doe' 두 패턴 중 하나라도 일치하는 레코드(David, John Doe, David Miller, Bob Doe)가 상위에 배치됩니다.
- 두 패턴 모두 일치하지 않는 레코드(John, John Smith, Chris)는 하위에 배치됩니다.
- 일치 개수가 동일한 레코드들 사이의 순서는 보장되지 않으므로, 필요하다면 보조 정렬 기준(예:
id ASC)을 추가로 지정하는 것이 좋습니다.
이처럼 ORDER BY 절에서 여러 LIKE 연산자를 합산하면, 별도의 FULLTEXT 인덱스 없이도 간단한 관련도 기반 정렬을 손쉽게 구현할 수 있습니다.