MySQL에서 영숫자 열을 정렬하는 방법 – ORDER BY 활용 가이드

MySQL에서 "100X", "2Z"처럼 숫자와 문자가 섞여 있는 영숫자(alphanumeric) 값을 담은 열을 정렬하려면 ORDER BY 절을 활용하면 됩니다. 단순히 문자열 기준으로 정렬하면 "100X"가 "2Z"보다 앞에 오는 등 의도와 다른 결과가 나올 수 있습니다. 이번 글에서는 이 문제를 해결하는 방법을 단계별로 살펴보겠습니다.

1단계: 테이블 생성

먼저 데모용 테이블을 생성합니다.

mysql> create table DemoTable
-> (
-> StudentId varchar(100)
-> );
Query OK, 0 rows affected (0.52 sec)

2단계: 데이터 삽입

INSERT 명령으로 샘플 레코드를 추가합니다.

mysql> insert into DemoTable values('2X');
Query OK, 1 row affected (0.21 sec)

mysql> insert into DemoTable values('100Y');
Query OK, 1 row affected (0.20 sec)

mysql> insert into DemoTable values('100X');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable values('2Z');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable values('2Y');
Query OK, 1 row affected (0.23 sec)

mysql> insert into DemoTable values('100Z');
Query OK, 1 row affected (0.17 sec)

3단계: 전체 데이터 조회

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

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+-----------+
| StudentId |
+-----------+
| 2X        |
| 100Y      |
| 100X      |
| 2Z        |
| 2Y        |
| 100Z      |
+-----------+
6 rows in set (0.00 sec)

현재 데이터는 입력된 순서 그대로 저장되어 있으며, 아직 정렬되지 않은 상태입니다.

영숫자 열 정렬 쿼리

이제 MySQL에서 영숫자 열을 정렬하는 핵심 쿼리입니다.

mysql> select *from DemoTable order by (StudentId+0), right(StudentId, 1);

실행 결과:

+-----------+
| StudentId |
+-----------+
| 2X        |
| 2Y        |
| 2Z        |
| 100X      |
| 100Y      |
| 100Z      |
+-----------+
6 rows in set, 6 warnings (0.00 sec)

쿼리 동작 원리

이 쿼리가 어떻게 작동하는지 자세히 살펴보겠습니다.

• (StudentId + 0): 문자열에 0을 더하면 MySQL이 암시적 타입 변환을 수행하여 문자열 앞부분의 숫자를 기준으로 비교합니다. 덕분에 "2"와 "100"이 문자열 순서가 아닌 실제 숫자 크기대로 정렬됩니다.
• right(StudentId, 1): 값의 마지막 한 글자, 즉 알파벳 부분을 추출해 2차 정렬 기준으로 사용합니다. 그 결과 같은 숫자를 가진 값들이 X, Y, Z 순으로 깔끔하게 정렬됩니다.

이 방식을 응용하면 제품 코드("ITEM9", "ITEM10")나 학번처럼 숫자와 문자가 결합된 다양한 데이터를 사람이 읽기 좋은 자연스러운 순서로 정렬할 수 있습니다.