한글 영문 정렬

mysql에서는 영어와 숫자는 정렬이 잘되지만 한글이 정렬되려면 설정을 바꾸어야 한다.

mysql 설치시 문자셋을 euc_kr로 설정해야 한다. 만약 설정을 하지 않았다면 데몬 실행시에 문자셋을 euc_kr로 설정할수 있다. 하지만 그것도 안되었다면 아래의 명령으로 현재 mysql문자셋을 체크한다. 한글 문자셋이 아니라면 binary()함수로 한글을 정렬가능하다. 원래 목적은 영어의 대소문자를 구분하기 위해 사용한다.

 

mysql의 문자셋이 euc_kr이거나 데몬실행시 문자셋을 한글 문자셋으로 주었다면 binary()함수없이 한글 정렬이 가능하다.

 

3.28버전에서 문자셋 보기

 

show variables like 'character_set';
--현재 mysql에 설정된 문자셋 출력
show variables like 'character_sets';
--현재 mysql에서 지원하는 문자셋 출력

 

4.1버전에서 문자셋 보기

 

show variables like '%character_set%';

버전 3.23 에서 정렬 테스트

 

create table test1
(
 a varchar(20)
);

insert into test1 values('lee');
insert into test1 values('sik');
insert into test1 values('이');
insert into test1 values('준');
insert into test1 values('김');
insert into test1 values('aaa');
insert into test1 values('99');
insert into test1 values('111');

select * from test1 order by a;

+------+
| a    |
+------+
| 111  |
| 99   |
| aaa  |
| 이   |
| 준   |
| lee  |
| sik  |
| 김   |
+------+

select * from test1 order by binary(a);

+------+
| a    |
+------+
| 111  |
| 99   |
| aaa  |
| lee  |
| sik  |
| 김   |
| 이   |
| 준   |
+------+

 

select a from test1 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1);

//정렬할 필드의 첫문자의 아스키 값을 받아 128 밑에 있는거는 큰수, 아닌건 작으수를 넣어
1차 정렬을 이값으로 합니다.

+------+
| a    |
+------+
| 이   |
| 준   |
| 김   |
| lee  |
| sik  |
| aaa  |
| 99   |
| 111  |
+------+

 

select a from test1 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1) , binary(a);

//위의 방법으로 정렬하게 되면 한글->숫자->영어 순으로 정렬하게 된다.

 

+------+
| a    |
+------+
| 김   |
| 이   |
| 준   |
| 111  |
| 99   |
| aaa  |
| lee  |
| sik  |
+------+

 

create table test2
(
 a varchar(20) binary not null
);
insert into test2 values('lee');
insert into test2 values('sik');
insert into test2 values('이');
insert into test2 values('준');
insert into test2 values('김');
insert into test2 values('aaa');
insert into test2 values('99');
insert into test2 values('111');

select * from test2 order by a;

+-----+
| a   |
+-----+
| 111 |
| 99  |
| aaa |
| lee |
| sik |
| 김  |
| 이  |
| 준  |
+-----+

select * from test2 order by binary(a);

+-----+
| a   |
+-----+
| 111 |
| 99  |
| aaa |
| lee |
| sik |
| 김  |
| 이  |
| 준  |
+-----+

 

select a from test2 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1);

//정렬할 필드의 첫문자의 아스키 값을 받아 128 밑에 있는거는 큰수, 아닌건 작으수를 넣어
1차 정렬을 이값으로 합니다.

 

+-----+
| a   |
+-----+
| 이  |
| 준  |
| 김  |
| lee |
| sik |
| aaa |
| 99  |
| 111 |
+-----+

 

select a from test2 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1) , a;

//위의 방법으로 정렬하게 되면 한글->숫자->영어 순으로 정렬하게 된다.

 

+-----+
| a   |
+-----+
| 김  |
| 이  |
| 준  |
| 111 |
| 99  |
| aaa |
| lee |
| sik |
+-----+

 

버전 4.1 에서 정렬 테스트

 

create table test1
(
 a varchar(20)
);

insert into test1 values('lee');
insert into test1 values('sik');
insert into test1 values('이');
insert into test1 values('준');
insert into test1 values('김');
insert into test1 values('aaa');
insert into test1 values('99');
insert into test1 values('111');

select * from test1 order by a;

+------+
| a    |
+------+
| 111  |
| 99   |
| aaa  |
| 이   |
| 준   |
| lee  |
| sik  |
| 김   |
+------+

select * from test1 order by binary(a);

+------+
| a    |
+------+
| 111  |
| 99   |
| aaa  |
| lee  |
| sik  |
| 김   |
| 이   |
| 준   |
+------+

select a from test1 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1);

//정렬할 필드의 첫문자의 아스키 값을 받아 128 밑에 있는거는 큰수, 아닌건 작으수를 넣어
1차 정렬을 이값으로 합니다.

+------+
| a    |
+------+
| 이   |
| 준   |
| 김   |
| lee  |
| sik  |
| aaa  |
| 99   |
| 111  |
+------+

select a from test1 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1) , binary(a);

//위의 방법으로 정렬하게 되면 한글->숫자->영어 순으로 정렬하게 된다.

 

+------+
| a    |
+------+
| 김   |
| 이   |
| 준   |
| 111  |
| 99   |
| aaa  |
| lee  |
| sik  |
+------+

create table test2
(
 a varchar(20) binary not null
);
insert into test2 values('lee');
insert into test2 values('sik');
insert into test2 values('이');
insert into test2 values('준');
insert into test2 values('김');
insert into test2 values('aaa');
insert into test2 values('99');
insert into test2 values('111');

select * from test2 order by a;

+-----+
| a   |
+-----+
| 111 |
| 99  |
| aaa |
| lee |
| sik |
| 김  |
| 이  |
| 준  |
+-----+

select * from test2 order by binary(a);

+-----+
| a   |
+-----+
| 111 |
| 99  |
| aaa |
| lee |
| sik |
| 김  |
| 이  |
| 준  |
+-----+

 

select a from test2 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1);

//정렬할 필드의 첫문자의 아스키 값을 받아 128 밑에 있는거는 큰수, 아닌건 작으수를 넣어
1차 정렬을 이값으로 합니다.

 

+-----+
| a   |
+-----+
| 이  |
| 준  |
| 김  |
| lee |
| sik |
| aaa |
| 99  |
| 111 |
+-----+

 

select a from test2 order by if(ASCII(SUBSTRING(a, 1)) < 128, 2, 1) , a;

//위의 방법으로 정렬하게 되면 한글->숫자->영어 순으로 정렬하게 된다.

 

+-----+
| a   |
+-----+
| 김  |
| 이  |
| 준  |
| 111 |
| 99  |
| aaa |
| lee |
| sik |
+-----+

 

출처 : http://www.zetswing.com/bbs/board.php?bo_table=MySQL_LEC&wr_id=33&page=2