문자열 (CHAR, VARCHAR) 타입의 비교 및 정렬 방식

DBMS 종류별로 CHAR 타입의 데이터를 읽어 오고 비교하는 방식에서 조금씩의 차이가 있다.

가끔 오라클과 같은 타 DBMS에 익숙한 사용자는 자주 이런 부분을 혼동하는 경우가 많다.

우선 CHAR 타입이 고정 길이로 관리된다는 것은 타 DBMS와 동일하다.

하지만, CHAR 타입의 필드를JDBC나 PHP 또는 C api를 이용하는 Application에서 읽어 왔을 때, 오라클과는 달리 지정된 길이만큼 공백으로 채워져 있지 않다는 것이다.

즉, CHAR(10)이라는 컬럼에 "ABC"라는 값을 저장해 두었다고 가정했을 때, Application에서 그 필드를 Fetch해 보면, 오라클은 "ABC       "와 같이 필드 값을 리턴하지만, MySQL 은 "ABC" 만 리턴하게 된다. 또한 MySQL은 사용자가 입력한 내용의 마지막에 공백이 있는 경우, 그 공백마저도 제거해 버리게 된다. 공백이 아닌 문자 뒤에 붙어 있는 공백만 그렇게 처리된다.

아래와 같이 저장된 값들은 Application에서 읽어 보면 실제 우측에 표기된 것과 같은 값을 얻게 된다.

l  " ABC"  -> " ABC"

l  "AB C"  -> "AB C"

l  "A BC " -> "A BC"

CHAR 타입의 경우, MySQL은 마지막에 붙어 있는 공백 문자가 사용자의 입력인지 MySQL이 내부적으로 자리 수를 채우기 위해서 붙여 넣은 공백인지 구분하지 않기 때문이다.

그리고, 당연히 "ABC"와 "ABC  "는 동일한 값으로 간주되기 때문에 UNIQUE 인덱스가 있는 경우에는 DUPLICATE-KEY 오류를 발생시키게 된다.

만약, 오라클과 같은 형태로 CHAR 타입의 자리 수만큼 공백이 추가된 상태로 값을 리턴 받기를 원한다면 my.cnf (또는 my.ini) 설정 파일에 sql_mode 라는 변수를 정의하고, 그 값에 "PAD_CHAR_TO_FULL_LENGTH" 설정을 추가 해주면 오라클과 동일한 형태의 결과를 Application에서 전달 받을 수 있게 된다.

VARCHAR 타입의 경우, 내부적인 실제 데이터의 길이를 보관하기 때문에 마지막에 붙어 있는 공백 문자를 같이 데이터로 간주하고 저장 및 읽어 올 때 같이 리턴하게 된다.

그리고, 또 한가지 MySQL에서 주의해야 할 사항은, CHAR 타입과 VARCHAR타입의 비교에서는 뒤에 붙은 공백 문자를 모두 제거하고 비교를 하게 된다.

MySQL의 문자열 타입이 가지게 되는 Collation이라는 속성이 있는데, 이 Collation이라는 것은 비교 및 정렬의 기준을 제공하는 속성이다.

사실은 이러한 공백을 제거한 비교 작업은 이 Collation이라는 속성의 특징으로 발생하는 결과이며, 즉 MySQL의 문자열의 비교 및 정렬은 모두 마지막 공백을 제거(Right Space Trim)한 후, 비교 및 정렬 작업을 수행하게 된다.

그래서 최종적으로 아래와 같은 결과를 발생시키게 되는 것이다.

CREATE TABLE test (

  fd1 CHAR(10) DEFAULT NULL,

  fd2 VARCHAR(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into test values ('ABC ','ABC  ');

insert into test values ('ABC ','ABC');

insert into test values ('ABC ','ABC      ');

insert into test values ('ABC ','ABC    ');

SELECT

  CONCAT('''',fd1,'''') as converted_fd1,

  CONCAT('''',fd2,'''') as converted_fd2,

  (fd1=fd2) as is_equal

FROM test

ORDER BY fd2 DESC;

+---------------+---------------+----------+

| converted_fd1 | converted_fd2 | is_equal |

+---------------+---------------+----------+

| 'ABC'         | 'ABC  '       |        1 |

| 'ABC'         | 'ABC'         |        1 |

| 'ABC'         | 'ABC      '   |        1 |

| 'ABC'         | 'ABC    '     |        1 |

+---------------+---------------+----------+

이 결과로 test 테이블 모든 레코드의 fd1 값과 fd2값은 모두 동일한 값이라는 것을 확인할 수 있으며,

뒤에 붙은 공백 문자는 정렬에 아무런 영향을 미치지 않는다는 것도 확인할 수 있다.