MySQL에서 길이가 서로 다른 영숫자(alphanumeric) 데이터를 단순히 ORDER BY로 정렬하면 문자열 기준으로 비교되기 때문에 의도한 순서대로 정렬되지 않는 경우가 많습니다. 이 문제는 SUBSTRING_INDEX() 함수와 산술 연산을 조합하면 깔끔하게 해결할 수 있습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable1623
-> (
-> StudentCode varchar(20)
-> );
Query OK, 0 rows affected (0.47 sec)
2. 샘플 데이터 삽입하기
INSERT 명령을 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable1623 values('STU-MIT-143');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1623 values('STU-MIT-10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1623 values('STU-MIT-150');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable1623 values('STU-MIT-148');
Query OK, 1 row affected (0.22 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1623;
실행 결과는 다음과 같습니다.
+-------------+
| StudentCode |
+-------------+
| STU-MIT-143 |
| STU-MIT-10 |
| STU-MIT-150 |
| STU-MIT-148 |
+-------------+
4 rows in set (0.00 sec)
4. 영숫자 열을 숫자 순서대로 정렬하는 쿼리
아래 쿼리는 하이픈(-)을 기준으로 문자열을 분리한 뒤, 마지막 부분에 + 0을 적용해 숫자로 변환하여 정렬합니다. 이렇게 하면 길이가 달라도 숫자 크기 순서대로 올바르게 정렬됩니다.
mysql> select * from DemoTable1623
-> order by substring_index(StudentCode, '-', 1),
-> substring_index(StudentCode, '-', -1) + 0;
정렬 결과
+-------------+
| StudentCode |
+-------------+
| STU-MIT-10 |
| STU-MIT-143 |
| STU-MIT-148 |
| STU-MIT-150 |
+-------------+
4 rows in set (0.00 sec)
핵심 포인트 정리
SUBSTRING_INDEX(컬럼, '-', 1): 첫 번째 하이픈 앞의 접두사 부분을 추출합니다.SUBSTRING_INDEX(컬럼, '-', -1): 마지막 하이픈 뒤의 숫자 부분을 추출합니다.- 추출된 값에
+ 0을 더하면 MySQL이 해당 값을 자동으로 숫자로 변환하므로, '10'이 '143'보다 앞에 오도록 자연스러운 숫자 정렬이 가능합니다.
이 방법은 학생 코드, 제품 코드처럼 접두사 + 구분자 + 숫자 형태로 구성된 데이터를 정렬할 때 특히 유용합니다.