12.8.7 Using Collation in INFORMATION_SCHEMA Searches

12.8.7 INFORMATION_SCHEMA 검색에서 Collation 사용하기

INFORMATION_SCHEMA table의 string column은 utf8mb3_general_ci collation을 가지며, 이는 대소문자를 구분하지 않습니다. 그러나 file system에 의해 표현되는 object(예: database, table)에 해당하는 값에 대해 INFORMATION_SCHEMA string column에서 검색을 수행하는 경우, 대소문자 구분 여부는 기반이 되는 file system의 특성과 lower_case_table_names system variable 설정에 따라 대소문자를 구분할 수도 있고, 구분하지 않을 수도 있습니다.

예를 들어, file system이 대소문자를 구분하는 경우, 검색이 대소문자를 구분할 수 있습니다. 이 절에서는 이러한 동작과 필요할 경우 이를 수정하는 방법을 설명합니다.

어떤 query가 SCHEMATA.SCHEMA_NAME column에서 test database를 검색한다고 가정해 봅니다. Linux에서는 file system이 대소문자를 구분하므로, SCHEMATA.SCHEMA_NAME을 'test'와 비교하는 경우에는 일치하지만, 'TEST'와 비교하는 경우에는 일치하지 않습니다:

1mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
2       WHERE SCHEMA_NAME = 'test';
3+-------------+
4| SCHEMA_NAME |
5+-------------+
6| test        |
7+-------------+
8
9mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
10       WHERE SCHEMA_NAME = 'TEST';
11Empty set (0.00 sec)

이 결과는 lower_case_table_names system variable이 0으로 설정되었을 때 발생합니다. lower_case_table_names 값을 1 또는 2로 설정하면 두 번째 query도 첫 번째 query와 동일한(비어 있지 않은) 결과를 반환합니다.

참고

서버를 초기화할 때 사용한 설정과 다른 lower_case_table_names 값으로 서버를 시작하는 것은 허용되지 않습니다.

Windows 또는 macOS에서는 file system이 대소문자를 구분하지 않으므로, 비교 시 'test'와 'TEST' 모두가 일치합니다:

1mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
2       WHERE SCHEMA_NAME = 'test';
3+-------------+
4| SCHEMA_NAME |
5+-------------+
6| test        |
7+-------------+
8
9mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
10       WHERE SCHEMA_NAME = 'TEST';
11+-------------+
12| SCHEMA_NAME |
13+-------------+
14| TEST        |
15+-------------+

이 경우에는 lower_case_table_names 값은 차이를 만들지 않습니다.

앞에서 설명한 동작은 file system에 의해 표현되는 object에 해당하는 값을 검색할 때는 INFORMATION_SCHEMA query에 utf8mb3_general_ci collation이 사용되지 않기 때문에 발생합니다.

INFORMATION_SCHEMA column에 대한 string operation의 결과가 예상과 다를 경우, 우회 방법은 적절한 collation을 강제로 사용하도록 명시적인 COLLATE 절을 사용하는 것입니다(Section 12.8.1, “Using COLLATE in SQL Statements” 참조). 예를 들어, 대소문자를 구분하지 않는 검색을 수행하려면 INFORMATION_SCHEMA column 이름에 COLLATE를 사용합니다:

1mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
2       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'test';
3+-------------+
4| SCHEMA_NAME |
5+-------------+
6| test        |
7+-------------+
8
9mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
10       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'TEST';
11+-------------+
12| SCHEMA_NAME |
13+-------------+
14| test        |
15+-------------+

UPPER() 또는 LOWER() function을 사용할 수도 있습니다:

1WHERE UPPER(SCHEMA_NAME) = 'TEST'
2WHERE LOWER(SCHEMA_NAME) = 'test'

방금 본 것처럼, 대소문자를 구분하는 file system을 사용하는 platform에서도 대소문자를 구분하지 않는 비교를 수행할 수 있지만, 이것이 항상 올바른 방법은 아닐 수 있습니다. 이러한 platform에서는 이름이 대소문자만 다르게 여러 개의 object를 동시에 가질 수 있습니다.

예를 들어, city, CITY, City라는 이름의 table이 동시에 모두 존재할 수 있습니다. 검색이 이러한 모든 이름과 일치해야 하는지, 아니면 하나만 일치해야 하는지를 고려한 뒤 그에 맞게 query를 작성해야 합니다. 다음 비교들 중 첫 번째(utf8mb3_bin 사용)는 대소문자를 구분하고, 나머지는 구분하지 않습니다:

1WHERE TABLE_NAME COLLATE utf8mb3_bin = 'City'
2WHERE TABLE_NAME COLLATE utf8mb3_general_ci = 'city'
3WHERE UPPER(TABLE_NAME) = 'CITY'
4WHERE LOWER(TABLE_NAME) = 'city'

INFORMATION_SCHEMA string column에서 INFORMATION_SCHEMA 자체를 참조하는 값에 대한 검색은, INFORMATION_SCHEMA가 file system에 표현되지 않는 “virtual” database이기 때문에 utf8mb3_general_ci collation을 사용합니다.

예를 들어, SCHEMATA.SCHEMA_NAME과의 비교는 platform에 관계없이 'information_schema' 또는 'INFORMATION_SCHEMA'와 일치합니다:

1mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
2       WHERE SCHEMA_NAME = 'information_schema';
3+--------------------+
4| SCHEMA_NAME        |
5+--------------------+
6| information_schema |
7+--------------------+
8
9mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
10       WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';
11+--------------------+
12| SCHEMA_NAME        |
13+--------------------+
14| information_schema |
15+--------------------+