MySQL에서 SELECT ... WHERE id IN(...) 쿼리를 실행하면 기본적으로 조건에 일치하는 행이 임의의 순서로 반환됩니다. 하지만 FIELD() 함수를 ORDER BY 절과 함께 사용하면, 지정한 값 목록의 순서 그대로 결과를 정렬할 수 있습니다.
기본 문법
FIELD() 함수를 활용한 정렬 문법은 다음과 같습니다.
SELECT * FROM yourTableName
WHERE yourColumnName IN('value1','value2',.......N)
ORDER BY FIELD(yourColumnName,'value1','value2',.......N);FIELD() 함수는 첫 번째 인수로 전달된 컬럼 값이 이후 인수 목록 중 몇 번째 위치와 일치하는지를 반환합니다. 따라서 ORDER BY 절에서 이 함수를 사용하면 IN 절에 나열한 값의 순서대로 결과가 정렬됩니다.
예제 테이블 생성
문법을 실제로 이해하기 위해 먼저 테이블을 생성해 보겠습니다.
mysql> create table SelectOrderbyField
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(30),
-> Age int,
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.62 sec)샘플 데이터 삽입
INSERT 명령으로 몇 개의 레코드를 추가합니다.
mysql> insert into SelectOrderbyField(Name,Age) values('John',23);
Query OK, 1 row affected (0.13 sec)
mysql> insert into SelectOrderbyField(Name,Age) values('Carol',25);
Query OK, 1 row affected (0.16 sec)
mysql> insert into SelectOrderbyField(Name,Age) values('Bob',21);
Query OK, 1 row affected (0.15 sec)
mysql> insert into SelectOrderbyField(Name,Age) values('Mike',28);
Query OK, 1 row affected (0.17 sec)
mysql> insert into SelectOrderbyField(Name,Age) values('Sam',26);
Query OK, 1 row affected (0.12 sec)
mysql> insert into SelectOrderbyField(Name,Age) values('David',23);
Query OK, 1 row affected (0.19 sec)전체 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from SelectOrderbyField;
실행 결과는 다음과 같습니다.
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 1 | John | 23 | | 2 | Carol | 25 | | 3 | Bob | 21 | | 4 | Mike | 28 | | 5 | Sam | 26 | | 6 | David | 23 | +----+-------+------+ 6 rows in set (0.00 sec)
ID 컬럼 기준으로 원하는 순서 정렬하기
WHERE 절의 IN 목록 순서(5, 1, 3, 2, 6, 4)대로 결과를 정렬하려면 다음과 같이 쿼리를 작성합니다.
mysql> select *from SelectOrderbyField where Id IN(5,1,3,2,6,4)
-> order by field(Id,5,1,3,2,6,4);실행 결과를 확인해 보면 ID가 IN 절에 나열된 순서 그대로 출력되는 것을 알 수 있습니다.
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 5 | Sam | 26 | | 1 | John | 23 | | 3 | Bob | 21 | | 2 | Carol | 25 | | 6 | David | 23 | | 4 | Mike | 28 | +----+-------+------+ 6 rows in set (0.00 sec)
Name 컬럼 기준으로 원하는 순서 정렬하기
ID뿐만 아니라 Name처럼 문자열 컬럼을 기준으로도 동일하게 정렬할 수 있습니다.
mysql> select *from SelectOrderbyField where Name IN('Sam','John','Bob','Carol','David','Mike')
-> order by field(Name,'Sam','John','Bob','Carol','David','Mike');실행 결과 역시 지정한 이름 순서대로 정확히 정렬되어 반환됩니다.
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 5 | Sam | 26 | | 1 | John | 23 | | 3 | Bob | 21 | | 2 | Carol | 25 | | 6 | David | 23 | | 4 | Mike | 28 | +----+-------+------+ 6 rows in set (0.03 sec)
마무리
이처럼 MySQL의 ORDER BY FIELD() 구문을 활용하면 WHERE IN 절로 조회한 결과를 개발자가 의도한 임의의 순서대로 손쉽게 정렬할 수 있습니다. 특정 항목을 우선적으로 노출해야 하는 배너 순서, 카테고리 정렬 등 다양한 실무 상황에서 유용하게 활용할 수 있으니 참고하시기 바랍니다.