[MySql] 테이블의 character set 확인 및 변경

데이터를 다루다 보면 원하는 데이터로 가공을 하는 과정에서 join이 쓰이는 경우가 많다. 

필자도 인턴을 하면서 데이터들을 다루다보면 분산되어 있는 데이터들을 하나의 원하는 데이터로 가공을 하는 과정에서 join을 많이 사용한다. 데이터가 워낙 크다보니 index를 잘 이용해야만 join을 사용하여도 원하는 결과가 나왔다. 

같은 스키마를 가진 다른 디비에서 같은 쿼리로 데이터를 join 하면서 select를 하는데 어떤 DB에서는 explain을 통해 보았을 때 index를 잘 타면서 데이터를 빠르게 추출할 수 있지만 어떤 DB에서는 index를 타지 않는 경우가 있었다. 이러한 경우 때문에 한참 멘붕을 겪었었는데 이러한 경우가 문자열값을 가지는 column을 join 하였을 때만 나타났다. 사수님께 여쭤봐서 결론을 찾게 되었다. 여러 디비 서버에 데이터를 분산하여 저장하다 보면 character set이 변경되는 경우가 생길 수 있다. 이러한 과정에서 join을 하는 데이블들이 다른 character set을 사용하고 있으면 같은 column이더라도 index를 못 타게 된다. 이러한 문제를 해결하기 위한 방법을 아래에 정리하고자 한다.

---

테이블의 character set을 확인하기 위한 쿼리

show full columns from '테이블명';

이때 특정한 column의 character set을 확인하고자 할 때는 아래의 쿼리를 사용하면 된다.

show full columns from '테이블명' where field = '칼럼명';

테이블의 character set을 변경하기 위한 쿼리

alter table '테이블 명' convert to character set utf8;

이 때 character set은 문자가 컴퓨터에 어떠한 코드로 저장되는 지에 대한 규칙의 집합이다. 대표적으로 ASKII, utf8, euckr(utf8, euckr은 한글 지원 코드)가 있다. 

그러나 데이터베이스에서 저장된 값들을 검색하거나 정렬 등의 작업을 위해 문자들 간에 서로 비교를 하기 위해서는 collation이라는 규칙이 필요하다. 예를들어 A 테이블의 id가 utf8_general_ci 이고, B 테이블의 id가 utf8_unicode_ci 일 때  

select a.id, b.id from A left join B on a.id = b.id;

위의 쿼리를 통해 두 테이블을 조인하려 한다면 index를 타지 못한다. 

왜냐하면 두 테이블의 id는 같은 character set을 가지지만 다른 collation을 가지므로 DB내에서 비교가 불가하게 되어 join을 걸어도 index를 타지 못한다. 그러므로 테이블의 character set을 변경해주거나 처음 테이블을 생성할 때 collate를 같이 지정해주는 것이 더 좋다.

예를 들어 테이블의  character set을 변경하고자 할 때는 

alter table '테이블명' conver to character set utf8 collate utf8_general_ci;

와 같이 collatetion까지 같이 변경하기를 추천한다.