union시 문자셋 에러 해결에 대하여

UNION시 문자셋 에러 해결에 대하여

 

Gravity DBA 이승연

 

집합 A와 집합 B을 상하로 결합하여 새로운 하나의 집합을 만드는 것이 UNION이라고 소개한 봐가 있다. 그리고, UNION할 때의 지켜야 하는 규칙들 또한 설명한적이 있다. 그 규칙들을 준수하여도 발생하는 에러에 대하여 알아보고, 그 에러에 대한 해결 방법을 알아보자.

 

먼저 다음 에러메시지를 보자.

 

메시지457, 수준16, 상태1, 줄1

데이터 정렬 충돌로 인해 값의 데이터 정렬이 확인되지 않았으므로 varchar 값을 varchar(으)로 암시적으로 변환할 수 없습니다.

 

이와 같은 메시지는 UNION하는 column중에 문자셋이 일치하지 않아 발생하는 문제이다. 예제를 통하여 해당 문제를 재현하고 해결 방법을 알아보자.

 

SELECT convert(sysname, serverproperty(N'collation')) AS [Collation]   Collation -------------------------------------------------------------------------------------------------------- Korean_Wansung_CI_AS

 

일단, 위와 같이 현재 서버의 문자셋을 알아본다. 현재 서버의 문자셋은 코리안 완성형에 대/소문자 구분 안하고, 악센트 구분하고, 일본어 가나 구분 안하고, 전자/반자 구분을 안 한다는 것이다. 여기에 테이블을 만들면 그 테이블 안에 있는 column의 문자셋은 자동으로 Korean_Wansung_CI_AS일 것이다.

 

CREATE TABLE collate_test1(                            name1 varchar(10              ,            name2 varchar(10) );   SELECT  table_name, column_name, Collation_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'collate_test1';   table_name      column_name         Collation_name ------------- --------------- ----------------------------- collate_test      name1             Korean_Wansung_CI_AS collate_test      name2             Korean_Wansung_CI_AS

 

그 다음에 기본 문자셋인 Korean_Wansung_CI_AS 말고 다른 문자셋으로 테스트 Table을 만들자.

 

CREATE TABLE collate_test2(                            name1 varchar(10 COLLATE SQL_Latin1_General_CP1_CI_AS              ,            name2 varchar(10) );   SELECT  table_name, column_name, Collation_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'collate_test2';   table_name      column_name         Collation_name ------------- --------------- ----------------------------- collate_test      name1             SQL_Latin1_General_CP1_CI_AS collate_test      name2             Korean_Wansung_CI_AS

 

위와 같이 name1 column의 문자셋을 SQL_Latin1_General_CP1_CI_AS으로 생성을 하였다. 이 문자셋은 영어(미국)이며 대/소문자 구분과 악센트 구분을 하지 않으며 일본 가나 구분과 전자/반자 구분을 하지 않는다는 것이다.

 

이 두 Table에 data을 입력을 하고 UNION으로 합쳐보자.

 

INSERT INTO collate_test1 SELECT 'a', 'b'   INSERT INTO collate_test2 SELECT 'c', 'd'   SELECT * FROM (              SELECT  name1              ,            name2              FROM collate_test1              UNION all              SELECT  name1              ,            name2              FROM collate_test2 ) AS A

 

이와 같이 실행을 하면 아마도 우리가 맨 처음에 보았던 에러메시지를 보게 될 것이다. 무엇이 문제인 것 인가? column의 개수도 맞추었고, column들의 데이터 형식도 동일하고, column의 overflow가 발생하지도 않았음에도 불구하고 에러가 발생한다. 그렇다. Column의 문자셋이 틀려서 데이터를 정렬할 때 충돌이 일어난 것이다. 해당 에러를 해결 할 수 있는 방법은 2개가 존재할 수가 있다.

 

l  SELECT할 때 Column의 문자셋을 맞추어 준다.

l  ALTER TABLE 명령문으로 해당 문자셋을 수정해 준다.

 

그럼 해결 방법을 살펴보자.

 

SELECT  table_name ,            column_name ,            Collation_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'collate_test1'; SELECT  table_name ,            column_name ,            Collation_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'collate_test2';

 

이렇게 두 Table을 비교하면 name1 의 Collation이 다르다는 것을 알 수가 있다.

하나는 SQL_Latin1_General_CP1_CI_AS 이고 다른 하나는 Korean_Wansung_CI_AS이기 때문에 둘 중에 한쪽으로 Collation을 맞추어 주어야 한다.

 

SELECT * FROM (              SELECT  name1              ,            name2              FROM collate_test1              UNION all              SELECT  name1 collate Korean_Wansung_CI_AS AS name1              ,            name2              FROM collate_test2 ) AS A

 

위와 같은 방법을 둘 중에 한쪽 Collation으로 맞추어 주면 더 이상 에러가 발생하지 않는다.

그런데 우리는 위와 같이 하면 UNION을 해야 할 때 마다 Collation을 바꾸어 주어야 하는 번거로움이 발생을 한다. 그래서 그 다음 방법이 ALTER TABLE문으로 column의 Collation을 바꾸어 버리는 것이다.

 

ALTER TABLE collate_test2              ALTER COLUMN name1 varchar(10) COLLATE Korean_Wansung_CI_AS

 

이렇게 ALTER TABLE으로 해당 column의 collation을 바꾸고 확인하여 보자. 그리고 SELECT절의 collate을 빼고 UNIOIN을 해 보자. 그럼 이제 에러는 더 이상 발생하지 않을 것이다.