10.1.7.9 照合順序と INFORMATION_SCHEMA 検索

INFORMATION_SCHEMA テーブルでの文字列カラムには utf8_general_ci の照合順序があり、これは大文字と小文字を区別しません。ただし、INFORMATION_SCHEMA 文字列カラムでの検索には、ファイルシステムでの大文字と小文字の区別も影響します。データベースやテーブルの名前など、ファイルシステムで表されるオブジェクトに対応する値については、ファイルシステムで大文字と小文字を区別する場合は、検索で大文字と小文字が区別されることがあります。このセクションでは、必要に応じて、この問題を解決する方法を説明します。Bug #34921 も参照してください。

クエリーで、test データベースに対し SCHEMATA.SCHEMA_NAME カラムを検索するとします。Linux では、ファイルシステムで大文字と小文字を区別するので、SCHEMATA.SCHEMA_NAME の 'test' との比較は一致しますが、'TEST' との比較は一致しません。

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+
1 row in set (0.01 sec)

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

Windows または OS X では、ファイルシステムで大文字と小文字を区別しないので、比較は 'test' と 'TEST' の両方と一致します。

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'TEST';
+-------------+
| SCHEMA_NAME |
+-------------+
| TEST        |
+-------------+
1 row in set (0.00 sec)

lower_case_table_names システム変数の値はこのコンテキストでは違いをもたらしません。

ファイルシステム内でデータベースオブジェクトを検索するときに、INFORMATION_SCHEMA クエリーで utf8_general_ci 照合順序が使用されていないために、このような処理が行われます。これは、MySQL での INFORMATION_SCHEMA 検索に対して実装された最適化の結果です。これらの最適化の詳細は、セクション8.2.4「INFORMATION_SCHEMA クエリーの最適化」を参照してください。

INFORMATION_SCHEMA は 「仮想」データベースであり、ファイルシステムには表されないので、INFORMATION_SCHEMA 文字列カラム内で INFORMATION_SCHEMA 自体を参照する値を検索する場合、utf8_general_ci 照合順序を使用します。たとえば、SCHEMATA.SCHEMA_NAME との比較は、プラットフォームに関係なく、'information_schema' または 'INFORMATION_SCHEMA' に一致します。

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'information_schema';
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

INFORMATION_SCHEMA カラムでの文字列演算の結果が予想と異なる場合、回避策は、明示的な COLLATE 句を使用して、適切な照合順序を強制的に使用することです (セクション10.1.7.2「SQL ステートメントでの COLLATE の使用」)。たとえば、大文字と小文字を区別しない検索を実行するには、INFORMATION_SCHEMA カラム名とともに COLLATE を使用します。

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'test';
+-------------+
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'TEST';
| SCHEMA_NAME |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

UPPER() または LOWER() 関数を使用することもできます。

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

大文字と小文字を区別するファイルシステムのプラットフォームでも、大文字と小文字を区別しない比較を実行できますが、前述のように、必ずしも常に正しい処理になるとはかぎりません。このようなプラットフォームでは、大文字と小文字だけが異なる名前の複数のオブジェクトが存在する可能性があります。たとえば、city、CITY、および City という名前のテーブルがすべて同時に存在することが可能です。検索でこれらのすべての名前に一致するか、1 つだけに一致するかを検討し、それに応じてクエリーを作成します。

WHERE TABLE_NAME COLLATE utf8_bin = 'City'
WHERE TABLE_NAME COLLATE utf8_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'

これらのうち最初の比較 (utf8_bin 使用) では大文字と小文字を区別します。ほかの比較では区別しません。