Charset vs Collation in MySQL, MariaDB

오늘 회사에서 만난 묘하고 신기한 개념이었던 Charset 과 Collation에 대한 내용입니다.

(아마도 일반적으로) 많이 알고 있는 InnoDB(Database Engine) 설정 이슈와 같이,
알고 있으면 빠르게 해결하지만, 모르고 있으면 시간을 낭비할 수 있는 내용이니, 한번 정도 읽어보면 좋을 내용입니다. 😊

1. Get started

결론부터 말하면,
Charset은 문자 집합을 나타내는 인코딩이고
Collation는 해당 인코딩에서 지원 되는 정렬 방식입니다.

즉,
Charset은 문자를 어떻게 압축하고
Collation은 어떻게 정렬하는지에 대한 설정입니다.

1.1. WHEN?

우리는 다양한 이유로 말 그대로 이해할 수 없는 에러를 마주합니다.
저번달과 똑같은 Library, GUI로 똑같이 코딩을 해도 때로는 새로운 에러를 만납니다.

그렇듯, 한번쯤은 이런 에러를 만날 수도 있겠네요.

Cannot create table 'database_name'.'table_name'
 (errno: 150 "Foreign key constraint is incorrectly formed")

외래키 참조를 걸때, 두 칼럼이 다음의 내용이 일치하지 않을 때 만날 수 있습니다.

1. 자료형 (VARCHAR, INT 등)
2. 제약사항 (NOT NULL 등)
3. 인코딩, 정렬 방식 불일치 (Charset, Collation)

1.2. HOW?

다음의 2단계를 통해서 이 문제를 해결할 수 있습니다.

1. 테이블의 charset, collation 확인
2. 테이블의 charset, collation 변경

1.2.1. 테이블의 charset, collation 확인

SHOW FULL COLUMNS FROM table_name;

• MariaDB docs - SHOW COLUMN

1.2.2. 테이블의 charset, collation 변경

주의사항!!!!
이미 데이터가 존재하는 DB의 Charset, Collation의 변경은 데이터의 손실을 발생시킬 수 있습니다. 따라서 호환여부를 체크하고 변환을 시도해야합니다.

-- Database Level
ALTER DATABASE databaes_name COLLATE = 'charset_name';
-- Table Level
ALTER TABLE table_name
	CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
-- Column Level
ALTER  TABLE  table_name 
	MODIFY  column_name 자료형 CHARACTER SET charset_name;

• MariaDB docs - Setting character sets and collation

1.3. MORE

마지막으로, 모든 정답은 공식문서에 있다라는 명언 😊 과 함께,
(아마도) SQL Database의 공식문서에는 Charset과 이에 호환되는 Collation 목록이 있습니다.

아래는 MariaDB에 올라와 있는 공식문서 내용입니다.

• MariaDB docs - supported charactor sets and collation

2. 마지막으로, UTF8 vs latin1

마지막은 사설인데,
가끔 SQL Database에 딱 하나의 칼럼만 닌자처럼 latin1 포맷으로 설정된 것을 볼 수 있었다.

음,,,
UTF8은 모든 인코딩을 표현할 수 있다.
아마도 크기, 성능, 특별한 요구사항을 제외하고는
Charset은 utf8로 Collation은 utf8_general_ci로 하는 것이 안전하다.

자,
다들 SHOW FULL COLUMNS FROM table_name;로 DB 확인해봐요!

• 오늘은 화요일,,, 미스테리