[MySQL] 4.2 데이터 정렬 - ORDER BY를 활용한 Natural Sorting

---

http://www.mysqltutorial.org 내용을 따라 익히며 정리한 글입니다.

---

1. Natural Sorting이란

데이터를 정렬할 때 사람에겐 다음 순서가 자연스럽다.
‘A-1’
‘A-2’
‘A-3’
‘A-4’
‘A-5’
‘A-10’
‘A-11’
‘A-20’
‘A-30’

하지만 콤퓨타가 출동하면 어떨까? 콤퓨타라면 아래처럼 정렬해놓고선 잘했다고 여길 수도 있다.
‘A-1’
‘A-10’
‘A-11’
‘A-2’
‘A-20’
‘A-3’
‘A-30’
‘A-4’
‘A-5’

앞의 예처럼 사람에게 자연스러운 순서로 정렬하는게 바로 Natural Sorting이다. PHP의 경우 natsort() 함수로 간단히 Natural Sorting을 할 수 있지만 MySQL은 얘기가 다르다. ORDER BY 를 활용해 제한적으로만 Natural Sorting이 가능한데 이런 테크닉에 대해서 알아보겠다.

먼저 id 와 item_no 라는 컬럼이 있는 items 란 테이블을 만든다.

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

다음은 위에서 만든 테이블에 데이터를 추가하겠다.

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

위에서 만든 데이터를 item_no 로 정렬해보자.

SELECT
    item_no
FROM
    items
ORDER BY item_no;

+---------+
| item_no |
+---------+
| 1       |
| 10Z     |
| 1C      |
| 2       |
| 20D     |
| 2A      |
| 3C      |
+---------+
7 rows in set (0.00 sec)

으으..
숫자 부분을 1, 2, 3, 10, 20 순으로 정렬하고 싶을 때 오더바이를 이용하면 그렇게 되지 않는다. 그렇다면 다음과 같은 결과를 얻으려면 어떻게 해야 할까?

+---------+
| item_no |
+---------+
| 1       |
| 1C      |
| 2       |
| 2A      |
| 3C      |
| 10Z     |
| 20D     |
+---------+
7 rows in set (0.00 sec)

CAST 함수를 사용해 다음과 같이 쿼리문을 작성하면 원하는 결과를 얻을 수 있다. CAST 함수는 어떤 값을 특정 타입으로 바꿔주는 역할을 한다.

SELECT
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

또 다른 예를 살펴보자. 먼저 아래 쿼리문으로 데이터를 추가한다.

TRUNCATE TABLE items;

INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

위 데이터를 Natural Sorting 하려면 문자값의 길이를 반환하는 LENGTH 함수를 활용할 수 있다.

SELECT
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

+---------+
| item_no |
+---------+
| A-1     |
| A-2     |
| A-3     |
| A-4     |
| A-5     |
| A-10    |
| A-11    |
| A-20    |
| A-30    |
+---------+
9 rows in set (0.00 sec)

좐~~