MySQL에서 VARCHAR 타입으로 저장된 숫자 데이터를 정렬하려면 LPAD(lower(컬럼명)) 함수를 활용하면 됩니다. VARCHAR는 문자열이기 때문에 일반적인 ORDER BY로 정렬하면 사전순으로 정렬되어 의도한 숫자 크기순 결과를 얻을 수 없습니다. 이번 글에서는 예제를 통해 해결 방법을 살펴보겠습니다.
1. 테이블 생성하기
먼저 CREATE 명령어를 사용해 테스트용 테이블을 생성합니다.
mysql> create table SortingvarcharDemo -> ( -> List varchar(10) -> ); Query OK, 0 rows affected (0.82 sec)
2. 데이터 삽입하기
INSERT 명령어를 사용하여 샘플 레코드를 삽입합니다.
mysql> insert into SortingvarcharDemo values("99");
Query OK, 1 row affected (0.12 sec)
mysql> insert into SortingvarcharDemo values("9");
Query OK, 1 row affected (0.17 sec)
mysql> insert into SortingvarcharDemo values("199");
Query OK, 1 row affected (0.19 sec)
mysql> insert into SortingvarcharDemo values("1");
Query OK, 1 row affected (0.11 sec)
mysql> insert into SortingvarcharDemo values("999");
Query OK, 1 row affected (0.10 sec)
mysql> insert into SortingvarcharDemo values("78");
Query OK, 1 row affected (0.17 sec)
3. 저장된 데이터 확인하기
SELECT 명령어로 저장된 레코드를 조회합니다.
mysql> select *from SortingvarcharDemo;
실행 결과는 다음과 같습니다.
+------+ | List | +------+ | 99 | | 9 | | 199 | | 1 | | 999 | | 78 | +------+ 6 rows in set (0.05 sec)
위 출력 결과를 보면 데이터가 오름차순도 아니고 내림차순도 아닌, 입력된 순서 그대로 뒤섞여 있는 것을 확인할 수 있습니다.
4. LPAD 함수로 숫자 기준 정렬하기
VARCHAR 필드를 숫자 크기 기준으로 오름차순 또는 내림차순 정렬하려면 아래와 같은 문법을 사용합니다.
SELECT * FROM yourTableName ORDER BY LPAD(lower(Column_name), value1, values2) asc;
문법 설명:
- lower(): 문자열을 소문자로 변환합니다.
- LPAD(column_name, value1, value2): 문자열의 왼쪽에 value2로 지정한 문자를 채워 전체 길이를 value1로 맞춥니다. 모든 값을 동일한 자릿수로 만들어 주므로 문자열 비교가 곧 숫자 크기 비교와 같아집니다.
실제 적용 쿼리는 다음과 같습니다.
mysql> SELECT * FROM SortingvarcharDemo ORDER BY LPAD(lower(List), 6,0) asc;
여기서는 자릿수를 6으로 맞추고, 부족한 자리는 '0'으로 채워 정렬했습니다. 실행 결과는 다음과 같습니다.
+------+ | List | +------+ | 1 | | 9 | | 78 | | 99 | | 199 | | 999 | +------+ 6 rows in set (0.17 sec)
위 결과를 보면 데이터가 1, 9, 78, 99, 199, 999 순으로 올바르게 오름차순 정렬된 것을 확인할 수 있습니다. 내림차순으로 정렬하고 싶다면 쿼리 마지막의 asc를 desc로 변경하면 됩니다.
참고: CAST를 사용한 대안
데이터가 모두 숫자 형태라면 LPAD 대신 CAST나 암시적 형변환을 사용할 수도 있습니다.
mysql> SELECT * FROM SortingvarcharDemo ORDER BY CAST(List AS UNSIGNED) asc;
다만 컬럼에 숫자가 아닌 값이 섞여 있거나 인덱스 활용이 중요한 경우에는 상황에 맞는 방법을 선택하는 것이 좋습니다. 근본적으로는 숫자만 저장하는 컬럼이라면 INT 같은 숫자 타입을 사용하는 것이 가장 바람직합니다.