데이터베이스 COLLATION 개념은?

 

 

📊 데이터베이스 COLLATION 개념

 

COLLATION은 데이터베이스에서 문자열의 정렬 순서와 비교 방법을 정의하는 설정입니다.

 

쉽게 말하면, 문자 데이터를 어떻게 비교하고 정렬할지를 결정하는 규칙입니다.

 

---

✅ 1. COLLATION의 구성 요소

COLLATION은 보통 다음과 같은 형식으로 표시됩니다:

css

 

[문자 집합(Character Set)]_[언어 또는 지역]_[비교 규칙]

예시:

• utf8_general_ci
• utf8mb4_unicode_ci
• latin1_swedish_ci

각 부분의 의미:

• utf8 / utf8mb4: 문자 집합 (Character Set)
• general / unicode: 비교 알고리즘 (Sorting/Comparison Rule)
• ci / cs: 대소문자 구분 설정
  • ci = Case-Insensitive (대소문자 구분 없음)
  • cs = Case-Sensitive (대소문자 구분함)

---

✅ 2. COLLATION의 주요 역할

1. 문자열 비교 규칙
   • 두 문자열이 같은지 비교할 때 사용.
   • 예: ‘A’와 ‘a’를 같은 것으로 볼지(ci) 다르게 볼지(cs) 결정.
2. 정렬 순서 결정
   • ORDER BY 쿼리에서 정렬 방식에 영향을 줌.
   • 예: utf8_general_ci는 A와 a를 동일하게 취급하고, B보다 앞에 정렬.
3. 검색 시 동작
   • LIKE나 = 비교 시 대소문자 구분 여부를 설정.

---

✅ 3. COLLATION 종류 및 차이

COLLATION설명대소문자 구분

utf8_general_ci	빠른 성능, 단순 비교	구분 안 함
utf8mb4_unicode_ci	유니코드 규칙에 따른 정확한 비교	구분 안 함
utf8_bin	이진 비교 (바이트 단위 비교)	구분함
latin1_swedish_ci	기본 MySQL 설정 (라틴1 문자 기반)	구분 안 함

• ci (Case-Insensitive): 대소문자 구분 없이 비교 ('A' = 'a')
• cs (Case-Sensitive): 대소문자 구분하여 비교 ('A' ≠ 'a')
• bin (Binary): 이진 비교로 처리 ('A'와 'a'는 바이트 값이 다름)

---

✅ 4. COLLATION 설정 위치

• 서버 수준: SHOW VARIABLES LIKE 'collation_server';
• 데이터베이스 수준: CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
• 테이블 수준: CREATE TABLE table_name (...) COLLATE utf8_general_ci;
• 컬럼 수준: ALTER TABLE table_name MODIFY column_name VARCHAR(100) COLLATE utf8_bin;
• 쿼리 수준: SELECT * FROM table_name ORDER BY column_name COLLATE utf8_bin;

---

🚩 5. COLLATION 변경 방법

(1) 데이터베이스의 COLLATION 변경

 

ALTER DATABASE db_name COLLATE utf8mb4_unicode_ci;

(2) 테이블의 COLLATION 변경

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(3) 컬럼의 COLLATION 변경

ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 

---

✅ 6. COLLATION 선택 시 고려 사항

• 성능 vs 정확성:
  • utf8_general_ci는 빠르지만, 복잡한 문자 비교에는 정확하지 않을 수 있음.
  • utf8mb4_unicode_ci는 정확하지만 성능이 약간 느릴 수 있음.
• 대소문자 구분 필요 여부:
  • 대소문자 구분이 필요한 경우 cs 또는 bin 사용.
• 다국어 지원:
  • 전 세계 언어를 지원하려면 utf8mb4_unicode_ci 또는 utf8mb4_0900_ai_ci 추천.

---

🎯 7. 실전 예제

(1) 대소문자 구분 없이 검색 (ci 사용)

SELECT * FROM users WHERE name = 'Alice'; -- 'alice', 'ALICE' 모두 검색됨 (utf8_general_ci 사용 시)

(2) 대소문자 구분하여 검색 (cs 사용)

SELECT * FROM users WHERE name COLLATE utf8_general_cs = 'Alice'; -- 'Alice'만 검색됨, 'alice'는 검색되지 않음

(3) 정렬 기준 비교

 

 

SELECT name FROM users ORDER BY name COLLATE utf8_bin; -- 정확한 이진 순서로 정렬 ('A'와 'a'를 구분)

---

🔑 8. 정리

• COLLATION은 데이터베이스에서 문자열 비교와 정렬 방식을 정의하는 규칙입니다.
• 대소문자 구분(ci, cs), 이진 비교(bin), 유니코드 호환성 등 다양한 옵션이 있습니다.
• 정확성이 중요하면 utf8mb4_unicode_ci, 성능이 중요하면 utf8_general_ci를 사용하세요.