데브러너 (DevRunner)

12.8.5 binary Collation과 _bin Collation의 비교

이 섹션은 binary 문자열에 대한 binary collation이 nonbinary 문자열에 대한 _bin collation과 어떻게 비교되는지를 설명합니다.

binary 문자열(데이터 타입 BINARY, VARBINARY, BLOB을 사용하여 저장됨)에는 binary라는 문자 집합과 collation이 있습니다. binary 문자열은 바이트의 시퀀스이며, 해당 바이트의 숫자 값이 비교 및 정렬 순서를 결정합니다. 자세한 내용은 Section 12.10.8, “The Binary Character Set”을 참조하십시오.

nonbinary 문자열(데이터 타입 CHAR, VARCHAR, TEXT을 사용하여 저장됨)에는 binary가 아닌 문자 집합과 collation이 있습니다. 주어진 nonbinary 문자 집합은 여러 개의 collation을 가질 수 있으며, 각각은 그 집합에 있는 문자에 대해 특정 비교 및 정렬 순서를 정의합니다.

대부분의 문자 집합에서는 이들 중 하나가 binary collation이며, collation 이름의 _bin 접미사로 표시됩니다. 예를 들어, latin1 및 big5에 대한 binary collation은 각각 latin1_bin 및 big5_bin입니다. utf8mb4는 예외적으로 두 개의 binary collation utf8mb4_bin 및 utf8mb4_0900_bin을 가집니다. 자세한 내용은 Section 12.10.1, “Unicode Character Sets”을 참조하십시오.

binary collation은 여러 측면에서 _bin collation과 다르며, 다음 섹션에서 논의합니다:

The Unit for Comparison and Sorting

binary 문자열은 바이트의 시퀀스입니다. binary collation의 경우, 비교 및 정렬은 바이트의 숫자 값에 기반합니다. nonbinary 문자열은 문자(멀티바이트일 수 있음)의 시퀀스입니다. nonbinary 문자열에 대한 collation은 비교 및 정렬을 위해 문자 값의 순서를 정의합니다. _bin collation의 경우, 이 순서는 문자 코드의 숫자 값에 기반하며, 문자 코드 값이 멀티바이트일 수 있다는 점을 빼면 binary 문자열에 대한 정렬과 유사합니다.

Character Set Conversion

nonbinary 문자열은 문자 집합을 가지며, 문자열에 _bin collation이 있는 경우에도 많은 상황에서 자동으로 다른 문자 집합으로 변환됩니다:

다른 문자 집합을 가진 다른 컬럼에 컬럼 값을 할당할 때:

1UPDATE t1 SET utf8mb4_bin_column=latin1_column;
2INSERT INTO t1 (latin1_column) SELECT utf8mb4_bin_column FROM t2;

문자열 리터럴을 사용하여 INSERT 또는 UPDATE에 컬럼 값을 할당할 때:

1SET NAMES latin1;
2INSERT INTO t1 (utf8mb4_bin_column) VALUES ('string-in-latin1');

서버에서 클라이언트로 결과를 전송할 때:

1SET NAMES latin1;
2SELECT utf8mb4_bin_column FROM t2;

binary 문자열 컬럼의 경우에는 변환이 발생하지 않습니다. 위와 유사한 경우에 문자열 값은 바이트 단위로 그대로 복사됩니다.

Lettercase Conversion

nonbinary 문자 집합에 대한 collation은 문자에 대한 대소문자 정보(레터케이스 정보)를 제공하므로, nonbinary 문자열에 있는 문자는 _bin collation이 정렬을 위해 대소문자를 무시하더라도 한 대소문자에서 다른 대소문자로 변환될 수 있습니다:

1mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
2mysql> SELECT LOWER('aA'), UPPER('zZ');
3+-------------+-------------+
4| LOWER('aA') | UPPER('zZ') |
5+-------------+-------------+
6| aa          | ZZ          |
7+-------------+-------------+

대소문자라는 개념은 binary 문자열의 바이트에는 적용되지 않습니다. 대소문자 변환을 수행하려면, 먼저 해당 문자열을 그 안에 저장된 데이터에 적합한 문자 집합을 사용하는 nonbinary 문자열로 변환해야 합니다:

1mysql> SET NAMES binary;
2mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
3+-------------+------------------------------------+
4| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
5+-------------+------------------------------------+
6| aA          | aa                                 |
7+-------------+------------------------------------+

Trailing Space Handling in Comparisons

MySQL collation에는 pad 속성이 있으며, 값은 PAD SPACE 또는 NO PAD입니다:

대부분의 MySQL collation은 pad 속성 값이 PAD SPACE입니다.
UCA 9.0.0 이상을 기반으로 하는 Unicode collation은 pad 속성 값이 NO PAD입니다. 자세한 내용은 Section 12.10.1, “Unicode Character Sets”을 참조하십시오.

nonbinary 문자열(CHAR, VARCHAR, TEXT 값)에 대해서는, 문자열 collation의 pad 속성이 문자열 끝의 후행 공백을 비교 시 어떻게 처리할지를 결정합니다:

PAD SPACE collation의 경우, 비교 시 후행 공백은 중요하지 않습니다. 후행 공백을 고려하지 않고 문자열을 비교합니다.
NO PAD collation은 후행 공백을 다른 문자와 동일하게 비교에서 중요한 것으로 처리합니다.

서로 다른 동작은 두 개의 utf8mb4 binary collation을 사용하여 보여줄 수 있습니다. 하나는 PAD SPACE, 다른 하나는 NO PAD입니다. 이 예제는 또한 INFORMATION_SCHEMA의 COLLATIONS 테이블을 사용하여 collation에 대한 pad 속성을 확인하는 방법을 보여줍니다.

1mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE
2       FROM INFORMATION_SCHEMA.COLLATIONS
3       WHERE COLLATION_NAME LIKE 'utf8mb4%bin';
4+------------------+---------------+
5| COLLATION_NAME   | PAD_ATTRIBUTE |
6+------------------+---------------+
7| utf8mb4_bin      | PAD SPACE     |
8| utf8mb4_0900_bin | NO PAD        |
9+------------------+---------------+
10mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
11mysql> SELECT 'a ' = 'a';
12+------------+
13| 'a ' = 'a' |
14+------------+
15|          1 |
16+------------+
17mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
18mysql> SELECT 'a ' = 'a';
19+------------+
20| 'a ' = 'a' |
21+------------+
22|          0 |
23+------------+

참고

이 문맥에서 말하는 “비교(comparison)”에는 LIKE 패턴 매칭 연산자는 포함되지 않습니다. LIKE에서는 collation과 관계없이 후행 공백이 항상 중요한 의미를 갖습니다.

binary 문자열(BINARY, VARBINARY, BLOB 값)의 경우, 후행 공백을 포함하여 모든 바이트가 비교에서 중요한 의미를 가집니다:

1mysql> SET NAMES binary;
2mysql> SELECT 'a ' = 'a';
3+------------+
4| 'a ' = 'a' |
5+------------+
6|          0 |
7+------------+

Trailing Space Handling for Inserts and Retrievals

CHAR(N) 컬럼은 길이가 _N_인 nonbinary 문자열을 저장합니다. insert 시, N 문자보다 짧은 값은 공백으로 확장됩니다. retrieval 시에는 후행 공백이 제거됩니다.

BINARY(N) 컬럼은 길이가 N 바이트인 binary 문자열을 저장합니다. insert 시, N 바이트보다 짧은 값은 0x00 바이트로 확장됩니다. retrieval 시에는 아무 것도 제거되지 않으며, 항상 선언된 길이의 값이 반환됩니다.

1mysql> CREATE TABLE t1 (
2         a CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
3         b BINARY(10)
4       );
5mysql> INSERT INTO t1 VALUES ('x','x');
6mysql> INSERT INTO t1 VALUES ('x ','x ');
7mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;
8+------+------------------------+--------+----------------------+
9| a    | b                      | HEX(a) | HEX(b)               |
10+------+------------------------+--------+----------------------+
11| x    | 0x78000000000000000000 | 78     | 78000000000000000000 |
12| x    | 0x78200000000000000000 | 78     | 78200000000000000000 |
13+------+------------------------+--------+----------------------+