Поведение SELECT для information_schema.routines на основе системной переменной lower_case_table_names

Это запрос, который я использую для получения списка хранимых процедур в базе данных MySQL.

SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'database_name' COLLATE utf8_bin AND ROUTINE_TYPE = 'PROCEDURE';

Я заставляю использовать utf8_bin в качестве сопоставления, чтобы получить правильный результат, если для системной переменной lower_case_table_names установлено значение 0.

Но я получаю пустой набор результатов с тем же запросом, если для параметра lower_case_table_names установлено значение 2, а имя базы данных имеет символы верхнего регистра.

Кроме того, если для параметра lower_case_table_names установлено значение 0 в системе с именами файлов, нечувствительными к регистру, я получаю пустой набор результатов с запросом.

В обоих этих случаях, если «COLLATE utf8_bin» удален из запроса или заменен на «COLLATE utf8_general_ci», я получаю правильный набор результатов.

Цитата из: http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

  • 0
    Имена таблиц и баз данных сохраняются на диске с использованием регистра букв, указанного в операторе CREATE TABLE или CREATE DATABASE. Сравнение имен чувствительно к регистру. Вы не должны устанавливать эту переменную в 0, если вы используете MySQL в системе, которая имеет имена файлов без учета регистра (например, Windows или Mac OS X). Если вы установите для этой переменной значение 0 с параметром --lower-case-table-names=0 в файловой системе, нечувствительной к регистру, и получите доступ к именам таблиц MyISAM, используя разные регистры букв, это может привести к повреждению индекса.

  • 1
    Имена таблиц хранятся на диске в нижнем регистре, и при сравнении имен регистр не учитывается. MySQL преобразует все имена таблиц в нижний регистр при хранении и поиске. Это поведение также относится к именам баз данных и псевдонимам таблиц.

  • 2
    Имена таблиц и баз данных хранятся на диске с использованием регистра букв, указанного в операторе CREATE TABLE или CREATE DATABASE, но MySQL преобразует их в нижний регистр при поиске. Сравнение имен не чувствительно к регистру. Это работает только в файловых системах, которые не чувствительны к регистру! Имена таблиц InnoDB хранятся в нижнем регистре, как и для lower_case_table_names=1.

Если для lower_case_table_names установлено значение 2

«MySQL преобразует их в нижний регистр при поиске»

Что это значит?

Я знаю, что для параметра lower_case_table_names не должно быть установлено значение 0 в Windows/OS X, но я не могу контролировать среду, в которой будет выполняться запрос.

Но я нахожу поведение странным.

SELECT для INFORMATION_SCHEMA.EVENTS также ведет себя так же.

ОБНОВЛЕНИЕ

Значения в столбце ROUTINE_SCHEMA в таблице INFORMATION_SCHEMA.ROUTINES указаны в нижнем регистре, если lower_case_table_names = 2, или когда lower_case_table_names = 0 в Windows. Но столбец ROUTINE_SCHEMA в INFORMATION_SCHEMA.TABLES имеет значения с тем же регистром букв, который использовался при создании базы данных. Даже в наборе результатов SHOW DATABASES есть значения с тем же регистром букв, который использовался при создании базы данных. Почему эта разница?


mysql database-schema collation
person user1391868    schedule 10.01.2014    source источник
comment
Почему такая разница? - похоже, это ошибка.   -  person eggyal    schedule 11.01.2014

Ответы (1)


arrow_upward
1
arrow_downward

Значение lower_case_table_names влияет на две вещи:

  • регистр букв, используемый для имен файлов данных, хранящихся на диске; и

  • неявное преобразование идентификаторов объектов схемы в командах SQL.

Ваша проблема заключается в том, что при запросе INFORMATION_SCHEMA единственными идентификаторами объектов схемы являются ссылки на сам INFORMATION_SCHEMA (в вашем запросе таблица ROUTINES и ее столбец названия). Ссылки на ваши объекты схемы (например, значения в столбце ROUTINE_SCHEMA и ваш литерал 'database_name' и т. д.) не являются идентификаторами объекта схемы, а скорее обычными строками (которые не получают преимуществ от Неявное преобразование регистра букв MySQL в соответствии с системной переменной lower_case_table_names).

Итак, с lower_case_table_names = 2 ваша база данных хранится на диске (и в INFORMATION_SCHEMA) с использованием регистра букв, который использовался в команде CREATE DATABASE. Если вы используете сопоставление с учетом регистра при запросе INFORMATION_SCHEMA, поиск по понятным причинам завершится ошибкой (если только вы не используете тот же регистр букв, который использовался при создании базы данных). Решение, очевидно, состоит в том, чтобы использовать сопоставление без учета регистра, такое как utf8_general_ci.

person eggyal    schedule 10.01.2014