MySQL에서 문자열 숫자를 숫자로 정렬하기 CAST와 CONVERT 활용법

MySQL에서 문자열로 저장된 숫자 데이터를 숫자로 정렬하는 것은 데이터 분석 및 보고서 작성에 있어 중요한 과제입니다. 문자열 타입(VARCHAR, CHAR 등)으로 저장된 숫자 데이터는 기본적으로 문자열 기준으로 정렬되기 때문에, 예상치 못한 정렬 순서를 가져올 수 있습니다. 예를 들어, 문자열로 저장된 숫자 '10'이 '2'보다 앞서 정렬되는 등의 결과가 발생할 수 있습니다. 이러한 문제를 해결하기 위해서는 CAST 또는 CONVERT 함수를 사용하여 쿼리에서 문자열 컬럼을 숫자형으로 변환한 후 정렬해야 합니다.

 

 

문제의 발단

No

1

4

31

20

위의 테이블과 같은 데이터가 mysql 데이터베이스에 들어있습니다.

이 숫자들을 order by 해보면 원하는 결과는 1, 4, 20, 31 이 나와야 하지만 1, 20, 31, 4 로 나오는 경험들이 있으실꺼에요.

숫자인줄 알았는데 데이터테이블 자료형을 찾아보니 문자열이더라구요.

그래서 문자열을 숫자로 변경해야 합니다.

 

숫자로 정렬하기 위한 CAST 사용

CAST 함수를 사용하여 문자열 컬럼을 숫자형으로 변환한 후 정렬할 수 있습니다.

다음 쿼리는 your_column을 UNSIGNED INTEGER로 캐스팅하여 숫자 기준으로 정렬합니다.

SELECT your_column
FROM your_table
ORDER BY CAST(your_column AS UNSIGNED INTEGER);

 

숫자로 정렬하기 위한 CONVERT 사용

CONVERT 함수를 사용하는 방법도 있습니다.

이 방법은 CAST와 유사하게 동작합니다.

다음 쿼리는 your_column의 값을 숫자형으로 변환한 후 숫자 기준으로 정렬합니다.

SELECT your_column
FROM your_table
ORDER BY CONVERT(your_column, UNSIGNED INTEGER);

 

문자열 숫자를 숫자로 정렬 예시

컬럼 price에 저장된 값이 문자열 타입이고, '10', '2', '30'과 같이 저장되어 있을 때, 다음 쿼리를 사용하면 숫자 기준으로 정렬하여 '2', '10', '30' 순서로 결과를 얻을 수 있습니다.

SELECT price
FROM products
ORDER BY CAST(price AS UNSIGNED INTEGER);

 

주의사항

컬럼에 숫자가 아닌 문자가 포함되어 있는 경우, CAST 또는 CONVERT를 사용해도 예상치 못한 결과가 발생할 수 있습니다.

가능하면 숫자 데이터는 숫자 타입의 컬럼에 저장하는 것이 좋습니다.

대규모 데이터를 다룰 때는 문자열 컬럼을 숫자로 변환하여 정렬하는 과정에서 성능 저하가 발생할 수 있습니다.

성능을 고려하여 적절한 데이터 모델링과 인덱싱 전략을 사용하는 것이 중요합니다.

 

결론

이러한 방법들을 통해 MySQL에서 문자열로 저장된 숫자 데이터를 숫자 기준으로 정확하게 정렬할 수 있습니다.

원하는대로 정렬이 잘되는지 보기위해선 데이터의 구조를 잘보고 접근한다면 오류를 최소한으로 줄을 수 있을거에요.

 

함께보면 좋은글

[MYSQL] 숫자, 문자열, 날짜시간 변환하기 CAST함수