12.8.7 Using Collation in INFORMATION_SCHEMA Searches — MySQL 9.2

12.8.7 Использование сортировки в запросах INFORMATION_SCHEMA

Столбцы типа строка в таблицах INFORMATION_SCHEMA имеют сортировку utf8mb3_general_ci, которая не учитывает регистр. Однако для значений, соответствующих объектам, представленным в файловой системе, таким как базы данных и таблицы, поиск в столбцах типа строка INFORMATION_SCHEMA может быть чувствителен или нечувствителен к регистру в зависимости от характеристик базовой файловой системы и настроек системной переменной lower_case_table_names. Например, поиск может быть чувствителен к регистру, если файловая система чувствительна к регистру. Этот раздел описывает это поведение и то, как его изменить, если необходимо.

Предположим, запрос ищет в столбце SCHEMATA.SCHEMA_NAME базу данных test. В Linux файловые системы чувствительны к регистру, поэтому сравнения SCHEMATA.SCHEMA_NAME с 'test' совпадают, но сравнения с 'TEST' не совпадают:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'TEST';
Empty set (0.00 sec)

Эти результаты возникают, когда системная переменная lower_case_table_names установлена в 0. Настройка lower_case_table_names в 1 или 2 приводит к тому, что второй запрос возвращает тот же (непустой) результат, что и первый запрос.

Примечание

Запрещается запускать сервер с настройкой lower_case_table_names, которая отличается от настройки, используемой при инициализации сервера.

В Windows или macOS файловые системы нечувствительны к регистру, поэтому сравнения совпадают для 'test' и 'TEST':

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| TEST        |
+-------------+

Значение lower_case_table_names в этом контексте значения не имеет.

Это поведение возникает потому, что сортировка utf8mb3_general_ci не используется для запросов INFORMATION_SCHEMA при поиске значений, соответствующих объектам, представленным в файловой системе.

Если результат строковой операции над столбцом INFORMATION_SCHEMA отличается от ожидаемого, можно использовать явное условие COLLATE для принудительного применения подходящей сортировки (см. Раздел 12.8.1, «Использование COLLATE в SQL-запросах»). Например, для выполнения поиска без учета регистра используйте COLLATE с именем столбца INFORMATION_SCHEMA:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME COLLATE utf8mb3_general_ci = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+

Также можно использовать функцию UPPER() или LOWER():

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

Хотя сравнение без учета регистра может выполняться даже на платформах с чувствительными к регистру файловыми системами, как показано выше, это не всегда правильно. На таких платформах могут существовать несколько объектов с именами, отличающимися только регистром. Например, таблицы с именами city, CITY и City могут существовать одновременно. Подумайте, должен ли поиск сопоставлять все такие имена или только одно, и составьте запросы соответственно. Первое из следующих сравнений (с utf8mb3_bin) чувствительно к регистру; другие — нет:

WHERE TABLE_NAME COLLATE utf8mb3_bin = 'City'
WHERE TABLE_NAME COLLATE utf8mb3_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'

Поиск в столбцах INFORMATION_SCHEMA типа строка для значений, ссылающихся на саму INFORMATION_SCHEMA, использует сортировку utf8mb3_general_ci, поскольку INFORMATION_SCHEMA — это виртуальная база данных, не представленная в файловой системе. Например, сравнения с SCHEMATA.SCHEMA_NAME соответствуют 'information_schema' или 'INFORMATION_SCHEMA' независимо от платформы:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'information_schema';
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
+--------------------+

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
       WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
+--------------------+