MySQL에서 데이터를 정렬하고 싶은 경우 ORDER BY를 사용합니다.
ORDER BY를 사용해 숫자를 정렬하는 경우에는, 작은 숫자부터 큰 숫자인 오름 차순 또는 큰 숫자부터 작은 숫자인 내림 차순으로 정렬이 됩니다.
하지만 문자열 형태인 숫자를 정렬하는 경우에는 원하는 형태로 출력이 안 되는 경우도 있습니다.
문자열 숫자를 정렬하는 예제와 해결 방법을 살펴보겠습니다.
샘플 데이터
테스트용 테이블을 생성하겠습니다.
CREATE TABLE test_sort (
no int(11) NOT NULL,
inttext text,
intvarchar varchar(45) default NULL,
PRIMARY KEY (no)
) ENGINE=MyISAM DEFAULT CHARSET=sjis;
샘플 데이터
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (1,'1','1');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (2,'2','2');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (3,'3','3');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (4,'100','100');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (5,'6','6');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (6,'5','5');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (7,'200','200');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (8,'9','9');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (9,'105','105');
INSERT INTO test_sort (no,inttext,intvarchar) VALUES (10,'30','30');SELECT * FROM test_sort ;
+----+---------+------------+
| no | inttext | intvarchar |
+----+---------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 100 | 100 |
| 5 | 6 | 6 |
| 6 | 5 | 5 |
| 7 | 200 | 200 |
| 8 | 9 | 9 |
| 9 | 105 | 105 |
| 10 | 30 | 30 |
+----+---------+------------+
ORDER BY
테스트용으로 만든 test_sort 테이블의 inttext 컬럼 타입은 TEXT, intvarchar 컬럼 타입은 VARCHAR로 문자열 형태입니다.
테스트용 데이터로 2개의 컬럼에 숫자 값을 등록시켰습니다.
컬럼 타입이 TEXT인 inttext 컬럼값으로 정렬을 해보겠습니다.
SELECT * FROM test_sort order by inttext;
+----+---------+------------+
| no | inttext | intvarchar |
+----+---------+------------+
| 1 | 1 | 1 |
| 4 | 100 | 100 |
| 9 | 105 | 105 |
| 2 | 2 | 2 |
| 7 | 200 | 200 |
| 3 | 3 | 3 |
| 10 | 30 | 30 |
| 6 | 5 | 5 |
| 5 | 6 | 6 |
| 8 | 9 | 9 |
+----+---------+------------+
100이 2보다 작은 값으로 인식되고 있습니다.
다음으로 컬럼 타입이 VARCHAR인 intvarchar 컬럼값으로 정렬을 해보겠습니다.
SELECT * FROM test_sort order by intvarchar;
+----+---------+------------+
| no | inttext | intvarchar |
+----+---------+------------+
| 1 | 1 | 1 |
| 4 | 100 | 100 |
| 9 | 105 | 105 |
| 2 | 2 | 2 |
| 7 | 200 | 200 |
| 3 | 3 | 3 |
| 10 | 30 | 30 |
| 6 | 5 | 5 |
| 5 | 6 | 6 |
| 8 | 9 | 9 |
+----+---------+------------+
이번에도 100이 2보다 작은 값으로 인식되고 있습니다.
입력된 형태는 숫자이지만 문자로 인식되고 있기 때문에 100이 2보다 작은 값이라고 판단을 하고 있기 때문입니다.
예를 들어 아래와 같은 알파벳을 정렬하는 경우를 보겠습니다.
AA
BB
AAB
ORDER BY 정렬
AA
AAB
BB
알파벳을 정렬하면 앞에 문자열부터 비교를 하게 됩니다.
BB 문자열의 처음 문자인 B는 AAB 문자열의 처음 문자 A보다 크기 때문에 AAB, BB 순서로 정렬이 됩니다.
테스트용 데이터를 확인해보면 문자열 2는 문자열 100의 첫 번째 문자인 1보다 크기 때문에 100이 2보다 앞으로 오게 정렬이 됩니다.
문자열 숫자 정렬
문자열 타입의 숫자를 정렬하기 위해서는 문자 타입을 숫자 타입으로 변환을 해서 정렬을 해야 합니다.
SELECT * FROM test_sort ORDER BY CAST(inttext AS SIGNED);
+----+---------+------------+
| no | inttext | intvarchar |
+----+---------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 6 | 5 | 5 |
| 5 | 6 | 6 |
| 8 | 9 | 9 |
| 10 | 30 | 30 |
| 4 | 100 | 100 |
| 9 | 105 | 105 |
| 7 | 200 | 200 |
+----+---------+------------+
CAST 함수를 사용해 문자열을 숫자로 형 변환을 해 정렬한 결과 작은 숫자부터 큰 숫자로 정렬이 되었습니다.
CAST 함수의 자세한 사용 방법은 아래를 참조해주세요.
VARCHAR타입인 intvarchar 컬럼을 형변환을 사용해 정렬을 해보겠습니다.
SELECT * FROM test_sort ORDER BY CAST(intvarchar AS SIGNED);
+----+---------+------------+
| no | inttext | intvarchar |
+----+---------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 6 | 5 | 5 |
| 5 | 6 | 6 |
| 8 | 9 | 9 |
| 10 | 30 | 30 |
| 4 | 100 | 100 |
| 9 | 105 | 105 |
| 7 | 200 | 200 |
+----+---------+------------+
정렬한 결과는 작은 숫자부터 큰 숫자로 정렬이 되어 출력되었습니다.
문자열 형태로 저장된 숫자를 원하는 형태로 정렬하기 위해서는 숫자 형태로 형변환을 해 정렬해야 합니다.
댓글