MySQL에서 VARCHAR 타입으로 저장된 숫자 데이터를 정렬할 때, 문자열 기준으로 정렬되어 의도한 결과가 나오지 않는 경우가 자주 발생합니다. 이럴 때 CAST() 연산자를 사용하면 대부분 해결되지만, 만약 CAST가 제대로 작동하지 않는다면 열 이름에 1을 곱하는 방법( yourColumnName * 1 )을 사용할 수 있습니다.
방법 1: 열에 1을 곱하여 정렬하기
숫자 형태의 문자열에 1을 곱하면 MySQL이 자동으로 해당 값을 숫자로 변환(암시적 형변환)하기 때문에, 숫자 기준으로 정렬됩니다. 기본 문법은 다음과 같습니다.
SELECT yourColumnName1, yourColumnName2, ...N
FROM yourTableName
ORDER BY yourColumnName * 1 DESC;
방법 2: CAST() 연산자 사용하기
CAST() 함수를 사용하면 VARCHAR 값을 명시적으로 숫자 타입으로 변환할 수 있습니다. 문법은 다음과 같습니다.
SELECT yourColumnName1, yourColumnName2, ...N
FROM yourTableName
ORDER BY CAST(yourColumnName AS DECIMAL(8,2)) DESC;
실습 예제: 테이블 생성
위 문법을 직접 확인해 보기 위해 테이블을 생성해 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.
mysql> create table VarcharColumnAsFloatDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Amount varchar(20),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (1.01 sec)
샘플 데이터 삽입
INSERT 명령어를 사용해 몇 개의 레코드를 삽입합니다.
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3446.23');
Query OK, 1 row affected (0.10 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('2464.46');
Query OK, 1 row affected (0.16 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.78');
Query OK, 1 row affected (0.13 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6464.98');
Query OK, 1 row affected (0.44 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('645.90');
Query OK, 1 row affected (0.19 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.99');
Query OK, 1 row affected (0.23 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3745.76');
Query OK, 1 row affected (0.14 sec)저장된 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from VarcharColumnAsFloatDemo;
실행 결과는 다음과 같습니다.
+----+---------+
| Id | Amount |
+----+---------+
| 1 | 3446.23 |
| 2 | 2464.46 |
| 3 | 6465.78 |
| 4 | 6464.98 |
| 5 | 645.90 |
| 6 | 6465.99 |
| 7 | 3745.76 |
+----+---------+
7 rows in set (0.00 sec)
CAST()로 VARCHAR를 숫자처럼 내림차순 정렬
이제 CAST 연산자를 사용해 VARCHAR 열을 DECIMAL로 변환한 뒤 내림차순으로 정렬하는 쿼리입니다.
mysql> select Id, Amount from VarcharColumnAsFloatDemo
order by cast(Amount as DECIMAL(8,2)) DESC;
실행 결과는 다음과 같습니다.
+----+---------+
| Id | Amount |
+----+---------+
| 6 | 6465.99 |
| 3 | 6465.78 |
| 4 | 6464.98 |
| 7 | 3745.76 |
| 1 | 3446.23 |
| 2 | 2464.46 |
| 5 | 645.90 |
+----+---------+
7 rows in set (0.00 sec)
열에 1을 곱하는 방식으로 정렬
두 번째 방법인 Amount * 1을 활용한 정렬 결과도 동일하게 나타납니다.
mysql> select Id, Amount from VarcharColumnAsFloatDemo
order by Amount * 1 desc;
실행 결과는 다음과 같습니다.
+----+---------+
| Id | Amount |
+----+---------+
| 6 | 6465.99 |
| 3 | 6465.78 |
| 4 | 6464.98 |
| 7 | 3745.76 |
| 1 | 3446.23 |
| 2 | 2464.46 |
| 5 | 645.90 |
+----+---------+
7 rows in set (0.00 sec)
정리 및 추가 팁
두 방법 모두 VARCHAR로 저장된 숫자 데이터를 올바르게 내림차순 정렬했습니다. 참고로 몇 가지 유의할 점은 다음과 같습니다.
- CAST vs 곱셈 변환: 두 방법 모두 암시적·명시적 형변환을 통해 숫자 비교를 수행하지만, 코드의 의도를 명확히 드러내려면
CAST()나CONVERT()를 사용하는 것이 좋습니다. - DECIMAL 권장: 금액처럼 소수점 정확도가 중요한 데이터는 FLOAT보다
DECIMAL타입으로 변환하는 것이 오차 없이 안전합니다. - 비숫자 데이터 주의: 열에 숫자가 아닌 값이 섞여 있으면 변환 시 경고가 발생하고 해당 값은 0으로 처리되므로, 사전에 데이터 정합성을 점검하는 것이 좋습니다.
- 근본적인 해결책: 애초에 숫자 데이터라면 컬럼 타입을 VARCHAR 대신
DECIMAL또는FLOAT로 설계하는 것이 성능과 인덱스 활용 면에서 가장 바람직합니다.