MySQL 문자열 varchar 컬럼 정렬하기

MySQL에서 varchar 컬럼을 숫자처럼 정렬하려면 어떻게 해야할까? 문자열을 숫자로 변환하여 정렬하는 방법은?

#Database #MySQL


컬럼 중에 문자열이 있다면?

테이블 컬럼 중에 varchar 타입을 사용하는 컬럼이 있다고 가정해보자. 테스트를 해보기 위해 아래와 같이 선수(player) 테이블이 있다고 가정해보자.

mysql> desc player;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | varchar(10) | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     |         |       |
| record | varchar(5)  | YES  |     |         |       |
| rank   | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

그리고 아래와 같이 데이터가 들어있다.

mysql> SELECT id, name, record, rank \
    -> FROM player;
+-----+---------+--------+------+
| id  | name    | record | rank |
+-----+---------+--------+------+
| id1 | 철수     | 10.63  |    1 |
| id2 | 영희     | 8.28   |    2 |
| id3 | 짱구     | 1.20   |    4 |
| id4 | 길동     | 7.61   |    3 |
+-----+---------+--------+------+

여기서 INT 타입인 rank 컬럼으로 ORDER BY 정렬을 사용해보자. 순위가 높은 순(값 자체는 낮은 순)으로 출력해보는 것이다.

mysql> SELECT id, name, record, rank \
    -> FROM player \
    -> ORDER BY rank ASC;
+-----+---------+--------+------+
| id  | name    | record | rank |
+-----+---------+--------+------+
| id1 | 철수     | 10.63  |    1 |
| id2 | 영희     | 8.28   |    2 |
| id4 | 길동     | 7.61   |    3 |
| id3 | 짱구     | 1.20   |    4 |
+-----+---------+--------+------+

원하는 대로 결과가 나오는 것을 알 수 있다. 그렇다면 기록을 뜻하는 record 컬럼으로 정렬해보면 어떻게 될까? 컬럼의 값이 큰 순서대로 정렬해보자. 참고로 이 컬럼은 VARCHAR 타입이다.

mysql> SELECT id, name, record, rank \
    -> FROM player \
    -> ORDER BY record DESC;
+-----+---------+--------+------+
| id  | name    | record | rank |
+-----+---------+--------+------+
| id2 | 영희     | 8.28   |    2 |
| id4 | 길동     | 7.61   |    3 |
| id1 | 철수     | 10.63  |    1 |
| id3 | 짱구     | 1.20   |    4 |
+-----+---------+--------+------+

결과를 확인해보니 순서가 조금 이상하다. “7.61” 값이 두 번째로 큰 순서로 정렬된다.


어떻게 해결할까?

MySQL에서 varchar 컬럼을 숫자처럼 정렬하는 방법을 살펴보자.

CAST 함수 사용

CAST 함수를 이용하여 명시적으로 변환하면 된다. 이때 DECIMAL 함수도 같이 사용한다.

mysql> SELECT id, name, record, rank \
    -> FROM player \
    -> ORDER BY CAST(record AS DECIMAL(4, 2)) DESC;
+-----+---------+--------+------+
| id  | name    | record | rank |
+-----+---------+--------+------+
| id1 | 철수     | 10.63  |    1 |
| id2 | 영희     | 8.28   |    2 |
| id4 | 길동     | 7.61   |    3 |
| id3 | 짱구     | 1.20   |    4 |
+-----+---------+--------+------+

결과를 확인해보면 의도한 대로 기록이 높은 순서대로 정렬이 잘 되었다.

정렬에 사용한 DECIMAL(P, D) 함수에서 P는 표현할 수 있는 유효 자릿수를 나타내며 허용되는 값의 범위는 1~65이다. D는 소숫점 자릿수를 뜻한다. 예를 들어, DECIMAL(6, 2) 인 경우 9999.99 ~ -9999.99 범위가 된다.

묵시적인 형변환

함수를 사용하여 명시적으로 형 변환하는 것이 아닌 묵시적으로 형 변환하는 방법이다. varchar 컬럼에 + 연산자를 이용하여 암묵적인 형 변환을 하도록 한다.

mysql> SELECT id, name, record, rank \
    -> FROM player \
    -> ORDER BY record+0 DESC;
+-----+---------+--------+------+
| id  | name    | record | rank |
+-----+---------+--------+------+
| id1 | 철수     | 10.63  |    1 |
| id2 | 영희     | 8.28   |    2 |
| id4 | 길동     | 7.61   |    3 |
| id3 | 짱구     | 1.20   |    4 |
+-----+---------+--------+------+

예제에서는 record+0을 사용했으나 record*1도 동일하다. 문자열에 +0으로 숫자 연산을 하여 숫자로 변경하게 하는 것이다.





댓글을 남기시려면 Github 로그인을 해주세요 :D