MYSQL 테이블 Collation 확인, 변경 방법 (Character Set과의 차이)

SQL문을 통해 테이블 Collation을 확인하고 원하는 대로 변경할 수 있습니다. Collation은 Character set과 다른 개념입니다. 쉽게 말해 utf8mb4와 utf8mb4_unicode_ci는 다릅니다. 전자는 캐릭터셋, 후자는 콜레이션입니다. 데이터베이스의 캐릭터셋이 같더라도 테이블 간 콜레이션이 다르면 테이블 조인 시 에러가 발생합니다. 

테이블 Collation 변경 방법

1. 데이터베이스 캐릭터셋 확인

데이터베이스(DB)는 특정한 기본 캐릭터셋이 적용됩니다. 이를 확인하는 SQL은 아래와 같습니다.

mysql> USE 데이터베이스명;
mysql> SHOW VARIABLES LIKE 'character_set_database';

[예시]

mysql> use mysql
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.01 sec)

2. 테이블 Collation 확인

SHOW TABLE STATUS WHERE NAME LIKE '테이블명';

[예시]

mysql> show table status where name like 'time_zone';

이렇게 조회하면 여러 가지 컬럼이 조회되는데, Collation에 해당하는 값을 찾으면 됩니다. time_zone 테이블의 콜레이션은 utf8mb3_general_ci입니다.

3. 테이블 Collation 변경

ALTER TABLE 테이블명 CONVERT TO CHARACTER SET 캐릭터셋 COLLATE 콜레이션;

[예시]

ALTER TABLE time_zone CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

캐릭터셋, 콜레이션의 차이?

문자를 데이터베이스에 저장하려면 캐릭터셋을 사용해야 합니다. 컴퓨터는 문자를 읽을 수 없기 때문입니다. 그래서 컴퓨터가 이해할 수 있는 방식으로 문자를 저장합니다. 예를 들면, A=0, B=1, C=2 … 처럼 약속하는 것입니다. 예시에서 A를 0으로 변환하는 작업이 인코딩입니다. 이런 약속 체계를 캐릭터셋이라고 합니다. 전 세계적으로 가장 많이 사용되는 캐릭터셋이 바로 UTF-8입니다. 

 

콜레이션은 문자를 비교하는 규칙을 의미합니다. 아주 간단한 예로, A, B, C, D...를 오름차순으로 정렬할 수 있는 건 콜레이션이 있기 때문입니다. A는 0이고 B는 1이니까 A가 앞서 정렬됩니다. 좀 더 복잡하게 들어가면 A와 a의 비교, a와 B의 비교 등이 가능할 것입니다. 하나의 캐릭터셋에도 여러 콜레이션이 존재합니다. 

콜레이션을 알아야 하는 이유

"콜레이션이 대충 뭔지는 알겠는데, 그래서 그게 뭐가 중요한가?" 싶을 수 있습니다. 하지만 알고 있어야 합니다. 테이블 별로 콜레이션이 다르면 테이블 조인 시 에러가 발생합니다. 예를 들어, A 테이블의 콜레이션은 utf8mb4_unicode_ci인데 다른 B 테이블의 콜레이션은 utf8mb4_general_ci면 함께 조회했을 때 문제가 생깁니다. 이런 경우 두 테이블의 콜레이션을 일치시키는 작업을 해야 합니다.

 

출처 : MYSQL 테이블 Collation 확인, 변경 방법 (Character Set과의 차이) (tistory.com)