MySQL에서 VARCHAR로 저장된 숫자 정렬하기 – 앞자리 0(085, 090 등)이 붙은 경우 올바른 정렬 방법

MySQL에서 숫자가 VARCHAR 타입으로 저장되어 있고, 일부 값이 '085', '090'처럼 앞에 0이 붙어 있는 경우 단순히 ORDER BY만 사용하면 문자열 기준으로 정렬되어 의도한 순서대로 나오지 않습니다. 이럴 때는 컬럼 값에 1을 곱해 암시적으로 숫자로 변환한 뒤 정렬하면 해결할 수 있습니다.

기본 문법

select *from yourTableName order by yourColumnName*1, yourColumnName;

yourColumnName*1은 VARCHAR 값을 숫자로 변환하여 먼저 수치 기준으로 정렬합니다. 이후 같은 숫자 값끼리는 다시 yourColumnName(문자열) 기준으로 정렬되므로, '45'와 '045'처럼 값은 같지만 표현이 다른 데이터도 자연스럽게 인접하게 배치됩니다.

예제 테이블 생성

먼저 테이블을 생성해 보겠습니다.

mysql> create table DemoTable
(
   Value varchar(100)
);
Query OK, 0 rows affected (0.62 sec)

insert 명령을 사용해 테이블에 레코드를 삽입합니다.

mysql> insert into DemoTable values('90');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('86');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('45');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('85');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('085');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('090');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('045');
Query OK, 1 row affected (0.10 sec)

select 문으로 테이블의 모든 레코드를 조회합니다.

mysql> select *from DemoTable;

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

+-------+
| Value |
+-------+
|    90 |
|    86 |
|    45 |
|    85 |
|   085 |
|   090 |
|   045 |
+-------+
7 rows in set (0.00 sec)

앞자리 0이 있는 VARCHAR 숫자 정렬 쿼리

다음은 앞에 0이 붙은 값까지 포함해 VARCHAR로 저장된 숫자를 정렬하는 쿼리입니다.

mysql> select *from DemoTable order by Value*1,Value;

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

+-------+
| Value |
+-------+
|   045 |
|    45 |
|   085 |
|    85 |
|    86 |
|   090 |
|    90 |
+-------+
7 rows in set (0.00 sec)

결과를 보면 '045'와 '45', '085'와 '85', '090'과 '90'이 각각 인접하게 정렬된 것을 확인할 수 있습니다. 이는 Value*1로 수치 정렬을 우선 적용한 뒤, 동일한 수치 범위 안에서 문자열 기준으로 다시 정렬했기 때문입니다.

참고: 다른 변환 방법

Value*1 대신 CAST(Value AS SIGNED)나 Value+0을 사용해도 동일한 결과를 얻을 수 있습니다. 다만 컬럼에 숫자가 아닌 값이 섞여 있으면 경고가 발생할 수 있으므로, 데이터가 순수한 숫자 형식일 때 이 방법을 사용하는 것이 좋습니다.