텍스트 데이터 작업 시 여러 언어와 알파벳을 지원해야 할 때가 많습니다. 이를 위해 SQL에서는 정렬 규칙인 COLLATION을 사용합니다. COLLATION은 문자열 비교 및 정렬 방식을 정의합니다. 언어적 특징(예: ё ≠ е)과 올바르게 작업하기 위해서는 열, 테이블, 또는 쿼리 수준에서 COLLATION을 올바르게 설정하는 것이 중요합니다.
SELECT * FROM users ORDER BY username COLLATE 'ru_RU.UTF8';
이 쿼리는 사용자를 러시아 알파벳 순서로 정렬합니다. 다양한 DBMS에서 COLLATE 구문은 다를 수 있습니다.
COLLATE utf8mb4_unicode_ci 또는 대소문자 및 언어를 고려한 utf8mb4_ru_0900_as_cs.COLLATE Cyrillic_General_CS_AS — 러시아어 지원, 대소문자 구분(CS = 대소문자 구분, AS = 악센트 구분).중요: COLLATION은 정렬 뿐만 아니라 검색(LIKE, 비교)에도 영향을 미칩니다!
하나의 쿼리에서 서로 다른 COLLATION을 가진 문자열을 정렬할 때 어떤 문제가 발생하며, 명시적인 변환 없이 서로 다른 정렬 규칙을 가진 데이터를 집계할 수 있나요?
오류: COLLATION이 다를 경우(예: 한 열은 utf8mb4_unicode_ci, 다른 열은 utf8mb4_bin) UNION을 시도하거나 직접적으로 비교하려 할 때 COLLATION 호환성 오류가 발생합니다.
올바른 방법: 항상 문자열을 COLLATE 구문을 통해 동일한 COLLATION으로 변환하세요.
SELECT name COLLATE 'utf8mb4_unicode_ci' FROM customers UNION SELECT name COLLATE 'utf8mb4_unicode_ci' FROM suppliers;
이야기 1
대규모 e-commerce 플랫폼에서 러시아어로 된 고객 목록을 Excel로 내보내는 과정에서 'Ё'로 시작하는 이름을 가진 사용자들이 목록의 맨 끝에 나타나고, 'Е'로 시작하는 사용자들이 맨 앞에 나타나는 문제가 발견되었습니다. 그 원인은 LATIN 기본 COLLATION을 사용하여 정렬이 일반적인 알파벳 순서와 일치하지 않았기 때문입니다. 사용자들은 명확하지 않은 정렬에 불만을 표했습니다.
이야기 2
의료 시스템의 서로 다른 테이블에 기본적으로 및 명시적으로 러시아어 COLLATION을 가진 문자열 필드가 포함되어 있었습니다. 테이블 스키마 변경 후 집계 보고서가 작동하지 않으며 쿼리가 "COLLATION conflict"를 반환했습니다. 기술 지원팀은 수백 개의 쿼리에 COLLATE를 명시적으로 추가해야 했습니다.
이야기 3
성으로 검색하는 API는 대문자와 소문자의 정확한 입력에 대해서만 작동했지만, 사용자들은 대소문자에 관계없이 검색을 기대했습니다. 확인해보니 열이 COLLATION *_CS로 생성되어 대소문자에 민감한 검색이 이루어졌습니다. 이를 *_CI(대소문자 무시)로 변경하여 문제를 해결했습니다.