[MySQL] 문자 구분 collation

 

collation 은 인코딩을 기반으로 글자를 비교하기 위한 정해진 규칙입니다.

같은 문자에 대해서 어떻게 비교하고 찾을 지 정하는 규칙이라고 볼 수 있으며, 대소문자 구분, 정렬 방식, 문자열 함수 결과 등등, 검색 시 어떤 결과를 도출할지 정해져 있습니다.

 

MySQL을 사용하고 데이터를 조회하는 기능을 개발을 수행할 때 주로 이 collation으로 인해 겪게 되는 문제들이 있습니다.

일반적으로 아무런 collation 설정을 하지 않으면 기본적인 ci collation 을 사용하기 때문에 like 조건을 사용하지 않는 이상, 검색하거나 비교하고자 하는 문자가 반드시 일치해야지만 조회할 수 있습니다.

따라서, 글 내용을 조회하거나 이모티콘과 같은 문자 비교 후 조회하는 기능이 특화되어있거나 중요한 기능들을 만들 때는 이 collation을 잘 설정해주는 것이 좋습니다.

 

 

collation 종류

MySQL 에 기본 설정 인코딩 값은 utf8mb4 이며, 해당 인코딩 값을 기준으로 흔히 볼 수 있는 collation은  utf8mb4_general_ci, utf8mb4_bin, utf8mb4_unicode_ci, utf8mb4_unicode_520_ci 정도가 있습니다.

(이 collation들 뿐만 아니라 많은 국가들의 언어를 지원하기 위해 다양한  collation이 있다고 합니다.)

 

 

collation 설정 확인

SELECT * FROM information_schema.COLLATIONS WHERE COLLATION_NAME like 'utf8mb4%';

위의 쿼리문을 통해 현재 자신이 사용하고 있는 MySQL의 collation을 쿼리를 통해 확인할 수 있습니다.

 

<로컬 DB>

 

 

<개발 DB>

 

 

<특정 운영 DB>

제가 사용하고 있는 DB들을 확인했을 때 개발 서버 DB 는 utf8mb4_general_ci, 로컬 DB와 특정 운영 DB는 utf8mb4_0900_ai_ci collation을 사용하고 있는 것을 알 수 있습니다.

 

또한 collation 확인 시 오른쪽에 PAD_ATTRIBUTE 라는 속성 옵션이 존재하는데, 이는 PAD SPACE 설정 값이면 문자열 끝에 빈 문자열이 존재할 경우에도 상관없이 그냥 해당 문자열의 마지막 빈 문자열을 제외하고 비교하며, NO PAD 설정 값이면 문자열 끝에 빈 문자열이 존재할 경우, 해당 빈 문자열까지 포함하여 비교합니다.

 

즉, 테이블에 "data " 라는 빈 문자열을 포함한 데이터가 존재할 경우 "data" 라는 텍스트로 해당 데이터를 검색하게 되었을 때, PAD SPACE인 경우에는 "data " 데이터가 정상적으로 조회되고, NO PAD 인 경우에는 데이터가 조회되지 않습니다.

 

 

collation 명칭 해석

예)

utf8mb4_0900_ai_ci

- utf8mb4 : 캐릭터셋 매핑 (mb4 : 4byte 지원), 바로 이어서 지역 및 언어를 나타내는 단어로 세분화되기도 함
- 0900 : version-9.0.0 UCA 표준을 따름
- ai : accent insensitive (이전버전에서는 악센트 구분이 안되었으며 MySQL 8.0 부터 추가됨)
- ci : case insensitive (대소문자 구분하지 않음)

Suffix	Meaning
_ai	악센트 구분 안함
_as	악센트 구분 함
_ci	대소문자 구분 안함
_cs	대소문자 구분 함
_ks	일본어(카타카나) 구분 함
_ws	전자/반자 구분 함
_bin	이진법 형식, 정의된 비트 패턴을 통해 SQL 데이블의 데이터 정렬
_vss	문자 변형 선택기 구분 정렬

​

 

collation 테스트

위에서 알아본 collation 들에 대해 실제로 데이터를 넣고 조회하면서 확인해보도록 하겠습니다.

 

 

테스트 테이블 생성

// utf8mb4_unicode_ci COLLATION
CREATE TABLE `test_utf8mb4_unicode_ci` (
    `data_id` bigint NOT NULL AUTO_INCREMENT,
    `data_text` varchar(20) not null,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 collate=utf8mb4_unicode_ci;

// utf8mb4_bin COLLATION
CREATE TABLE `test_utf8mb4_bin` (
    `data_id` bigint NOT NULL AUTO_INCREMENT,
    `data_text` varchar(20) not null,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 collate=utf8mb4_bin;

// utf8mb4_general_ci COLLATION
CREATE TABLE `test_utf8mb4_general_ci` (
    `data_id` bigint NOT NULL AUTO_INCREMENT,
    `data_text` varchar(20) not null,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

// utf8mb4_unicode_520_ci COLLATION
CREATE TABLE `test_utf8mb4_unicode_520_ci` (
    `data_id` bigint NOT NULL AUTO_INCREMENT,
    `data_text` varchar(20) not null,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 collate=utf8mb4_unicode_520_ci;

 

 

테스트 데이터 삽입

insert into test_utf8mb4_bin(data_text) values("data"),("DATA"),("アリガトウ"),("ｱﾘｶﾄｳ"),("😁"),("😂");
insert into test_utf8mb4_general_ci(data_text) values("data"),("DATA"),("アリガトウ"),("ｱﾘｶﾄｳ"),("😁"),("😂");
insert into test_utf8mb4_unicode_520_ci(data_text) values("data"),("DATA"),("アリガトウ"),("ｱﾘｶﾄｳ"),("😁"),("😂");
insert into test_utf8mb4_unicode_ci(data_text) values("data"),("DATA"),("アリガトウ"),("ｱﾘｶﾄｳ"),("😁"),("😂");

 

 

데이터 조회

[utf8mb4_bin]
select * from test_utf8mb4_bin where data_text = "data";
// 결과 : data
select * from test_utf8mb4_bin where data_text = "アリガトウ";
// 결과 : アリガトウ
select * from test_utf8mb4_bin where data_text = x'F09F9881';
// 결과 : 😁


[utf8mb4_general_ci]
select * from test_utf8mb4_general_ci where data_text = "data";
// 결과 : data, DATA
select * from test_utf8mb4_general_ci where data_text = "アリガトウ";
// 결과 : アリガトウ
select * from test_utf8mb4_general_ci where data_text = x'F09F9881';
// 결과 : 😁, 😂


[utf8mb4_unicode_520_ci]
select * from test_utf8mb4_unicode_520_ci where data_text = "data";
// 결과 : data, DATA
select * from test_utf8mb4_unicode_520_ci where data_text = "アリガトウ";
// 결과 : アリガトウ, ｱﾘｶﾄｳ
select * from test_utf8mb4_unicode_520_ci where data_text = x'F09F9881';
// 결과 : 😁


[utf8mb4_unicode_ci]
select * from test_utf8mb4_unicode_ci where data_text = "data";
// 결과 : data, DATA
select * from test_utf8mb4_unicode_ci where data_text = "アリガトウ";
// 결과 : アリガトウ, ｱﾘｶﾄｳ
select * from test_utf8mb4_unicode_ci where data_text = x'F09F9881';
// 결과 : 😁, 😂

 

 

결과

	utf8mb4_general_ci	utf8mb4_bin	utf8mb4_unicode_ci	utf8mb4_unicode_520_ci
알파벳 대소문자 구분	X	O	X	X
전각/반각 문자 구분 (알파벳, 숫자, 가타가나 등)	O	O	X	X
emoji 문자 구분	X	O	X	O

 

결론적으로 기본 설정값인 utf8mb4_general_ci, utf8mb4_bin 의 경우에는 구분 설정이 다수 포함되어있고  오래된 collation 이기 때문에 expansion(확장), contraction(축소), ignorable character(무시할 수 있는 문자) 를 지원하지 않아 위의 테스트 데이터를 조회할 시 명확한 단일 특정 데이터만 조회가 됩니다.

 

이에 반해 utf8mb4_unicode_ci, utf8mb4_unicode_520_ci 의 경우에는 구분 설정이 거의 존재하지 않고 좀 더 확장된 매핑 규칙(한 문자의 조합을 다른 문자와 비교)을 지원하기 때문에 많은 데이터를 조회할 수 있습니다.

 

단, 이러한 특성 때문에 성능은 unicode collation이 좋지만, 속도면에서는 기본 collation이 좋습니다.

 

 

+ 데이터 베이스, 테이블에 적용된 Collation 확인

전체 데이터베이스 Character Set 확인

SELECT SCHEMA_NAME AS 'database', DEFAULT_CHARACTER_SET_NAME AS 'character_set', DEFAULT_COLLATION_NAME AS 'collation'
FROM information_schema.SCHEMATA;

 

 

특정 데이터베이스 Character Set 확인

// 해당 데이터베이스 진입
(1) USE database_name;

// 확인
(2) SHOW VARIABLES LIKE 'character_set_database';

 

 

특정 테이블 Collation 확인

SHOW TABLE STATUS WHERE NAME LIKE '{확인 테이블 명}';

 

 

테이블 컬럼 Collation 확인

SHOW FULL COLUMNS FROM {테이블 명};

 

 

---

정리

위와 같이 collation 에 대해서 정리했을 때, 앞으로 DB 테이블을 설계할 때 개발하고자 하는 서비스의 특성에 따라 꼼꼼하게 설정해야 좀 더 좋은 DB 성능 도출할 수 있을 것입니다.