MYSQL에서 COLLATE의 역할과 다양한 COLLATE 간의 차이점에 대한 자세한 설명

MYSQL에서 COLLATE란 무엇인가요?

mysql에서 show create table 명령을 실행하면 테이블의 생성문을 볼 수 있습니다.

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

필드는 대부분 알 수 있지만 오늘은 이쯤 하겠습니다. COLLATE Character의 핵심에 대해 논의합니다. 이 값 뒤에 해당하는 utf8_unicode_ci는 무엇을 의미합니까? 인터뷰 중에 DBA 시험을 보기 위해 이 질문을 사용한다면 대부분의 사람들은 당황할 것입니다.

COLLATE는 어떤 용도로 사용되나요?

phpmyadmin을 사용하는 개발자는 중국어 헤더가 이미 답변을 제공했기 때문에 매우 친숙해 보일 수 있습니다.

phpmyadmin 스크린샷

소위 utf8_unicode_ci는 실제로 정렬에 사용되는 규칙입니다. VARCHAR, CHAR 및 TEXT 유형 열과 같은 mysql의 문자 유형 열의 경우 mysql에 열을 정렬하고 비교하는 방법을 알려주려면 COLLATE 유형이 필요합니다. 즉, COLLATE는 ORDER BY 문의 순서, WHERE 조건의 크거나 작은 기호, **DISTINCT**, **GROUP BY** 및 **HAVING*으로 결과를 필터링하는 순서에 영향을 미칩니다. * 쿼리 결과. 또한, MySQL이 인덱스를 구축할 때 인덱스 컬럼이 문자형이면 인덱스 생성에도 영향을 주지만 우리는 이러한 영향을 인지할 수 없습니다. 간단히 말해서, 문자 유형 비교나 정렬이 관련된 모든 것은 COLLATE와 관련됩니다.

다양한 COLLATE의 차이점

COLLATE은 일반적으로 데이터 인코딩(CHARSET)과 관련이 있습니다. 일반적으로 각 CHARSET에는 지원하는 여러 COLLATE가 있으며 각 CHARSET은 하나의 COLLATE를 기본값으로 지정합니다. 예를 들어, Latin1 인코딩의 기본 COLLATE는 latin1_swedish_ci이고, GBK 인코딩의 기본 COLLATE는 gbk_chinese_ci이며, utf8mb4 인코딩의 기본값은 utf8mb4_general_ci입니다.

여담으로 설명하겠습니다. mysql에는 utf8과 utf8mb4라는 두 가지 인코딩이 있습니다. mysql에서는 **utf8**을 잊어버리고 항상 **utf8mb4**를 사용하세요. 이는 MySQL의 레거시 문제입니다. MySQL의 UTF8은 최대 길이가 3바이트인 문자 인코딩만 지원할 수 있습니다. 4바이트를 차지해야 하는 일부 텍스트의 경우 MySQL의 UTF8은 이를 지원하지 않습니다.

많은 COLLATE에는 Case Insensitive의 약어인 _ci라는 단어가 있습니다. 이는 정렬 및 비교할 때 "A"와 "a"가 동일하게 처리된다는 의미입니다. field1="a"인 table1에서 선택 *은 field1의 값을 "A"로 선택할 수도 있습니다. 동시에 _cs 접미사가 있는 COLLATE의 경우 대소문자를 구분합니다.

mysql에서 지원하는 모든 COLLATE를 보려면 mysql에서 show collation 명령을 사용하세요. utf8mb4를 예로 들면, 이 인코딩에서 지원하는 모든 COLLATE는 아래 그림과 같습니다.

mysql의 utf8mb4와 관련된 모든 COLLATE

그림에서 여러 나라 언어의 대조 규칙을 볼 수 있습니다. 중국에서 일반적으로 사용되는 세 가지는 utf8mb4_general_ci(기본값), utf8mb4_unicode_ci 및 utf8mb4_bin입니다. 이 세 가지의 차이점을 살펴보겠습니다.

우선 utf8mb4_bin의 비교 방법은 모든 문자를 직접 이진 문자열로 처리한 다음 가장 높은 비트에서 가장 낮은 비트까지 비교하는 것입니다. 그러니 당연히 대소문자를 구분합니다.

실제로 중국어와 영어의 경우 utf8mb4_unicode_ci와 utf8mb4_general_ci 사이에는 차이가 없습니다. 우리가 가정용으로 개발한 시스템은 아무거나 선택하시면 됩니다. 일부 서방 국가의 문자의 경우 utf8mb4_unicode_ci가 utf8mb4_general_ci보다 언어 습관에 더 가깝습니다. 예를 들어, 독일어 문자 "ß"는 utf8mb4_unicode_ci의 두 문자 "ss"와 동일하지만(이는 독일 습관과 일치함) utf8mb4_general_ci에서는 문자 "s"와 동일합니다. 그러나 두 인코딩 간의 미묘한 차이는 일반적인 개발에서는 인식하기 어렵습니다. 한발 물러서서 한두 글자가 잘못 정렬되어도 실제로 시스템에 치명적인 결과를 가져올 수 있습니까? 인터넷에서 발견된 다양한 게시물과 토론을 보면 utf8mb4_unicode_ci를 사용하는 것을 권장하는 사람들이 늘어나고 있지만 기본값을 사용하는 시스템에는 그다지 저항력이 없으며 큰 문제가 없다고 생각합니다. 결론: utf8mb4_unicode_ci를 사용하는 것이 좋습니다. 이미 utf8mb4_general_ci를 사용하고 있는 시스템의 경우 수정하는 데 시간을 들일 필요가 없습니다.

또 주목해야 할 점은 mysql 8.0부터 mysql의 기본 CHARSET이 더 이상 Latin1이 아니라 utf8mb4(참조 링크)로 변경되었으며 기본 COLLATE도 utf8mb4_0900_ai_ci로 변경되었다는 것입니다. utf8mb4_0900_ai_ci는 일반적으로 유니코드의 추가 하위 분류입니다. 0900은 유니코드 비교 알고리즘(유니코드 대조 알고리즘 버전)의 번호를 나타내며, ai는 악센트를 구분하지 않음(발음은 무관함)을 의미합니다. 예를 들어 e, è, é, ë는 다음과 같습니다. 동등하게 대우받습니다. 관련 참고 링크 1, 관련 참고 링크 2

COLLATE 설정 수준 및 우선 순위

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

위 내용은 MYSQL에서 COLLATE의 역할과 다양한 COLLATE 간의 차이점에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!