[Database/Mysql] collate란 무엇일까? 왜 a랑 A가 같은 값일까?

로그인 할 때 대소문자 구분 없이 로그인이 되는 사이트를 본 적이 있을 것이다. 그럴 때 마다, 사용자로 부터 받은 아이디를 모두 소문자나, 대문자로 치환하여 DB에 전달하는게 아닐까 라는 생각을 한 적이 있다.

(collate가 모르는 사람이 구현을 했다면 이렇게 구현을 하지 않았을까..? 이렇게 생각 한 나처럼..) 

그러나 collate를 알고 있다면 전혀 그럴 필요가 없다는 사실..!

 

Collate란?

문자 집합에 포함된 문자들을 어떻게 비교하고 정렬할지를 정의하는 콜레이션을 설정 하는 키워드

- 문자열 데이터를 비교 할 때 대소문자를 구분할지 여부

- 문자 간 정렬 순서를 정의

- 언어별 특수 문자 처리 방식을 지원

 

Collate의 구조

예) utf8mb4_general_ci

- utf8mb4 : 문자 집합

- general : 비교 규칙(간단한 일반 비교)

- ci : Case Insensitive(대소문자 구분하지 않음)

 

Mysql 8.0 이상 부터의 기본 콜레이션은 utf8mb4_0900_ai_ci 이다. 아무런 설정 없이 테이블을 생성하면 당연히 해당 테이블도 utf8mb4_0900_ai_ci 을 따른다.

show variables like 'collation_server';

utf8mb4_0900_ai_ci 😊

- utf8mb4 : MySQL에서 사용하는 UTF-8 문자 인코딩으로, 4바이트까지 표현할 수 있는 확장된 UTF-8 버전

- 0900 : Unicode 9.0 표준을 기반으로 한 정렬 및 비교 규칙

- ai : Accent Insensitive (예: 'e' = 'é' → True)

- ci : Case Insensitive (예: 'A' = 'a' → True)

 

따라서, MySql 8.0 이상 부터 기본 설정으로 테이블을 설계하면, a라는 값과 A라는 값이 같으므로, pk를 id 하나로만 잡아놓는다면, duplicate 에러가 발생할 것이다!

 

그럼, 다른 값으로 취급하려면 어떻게 해야할까? 

-> Collate를 utf8mb4_general_cs, utf8mb4_0900_as_cs, utf8mb4_bin 같은 값으로 해당 컬럼만 변경해주거나, 테이블의 설정값을 변경해주면 된다.

 

컬럼 Collate 변경

ALTER TABLE my_table 
MODIFY COLUMN my_column VARCHAR(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

 

테이블 전체 Collate 변경

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

변경 전

변경 후

 

물론 데이터베이스와,  MySQL의 기본 설정도 변경 할 수 있다.