MySQL :: MySQL 9.0 參考手冊 :: 12.8.7 在 INFORMATION_SCHEMA 搜尋中使用校對

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 在 INFORMATION_SCHEMA 搜尋中使用校對

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 節「在 SQL 陳述式中使用 COLLATE」)。例如，若要執行不區分大小寫的搜尋，請將 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 |
+--------------------+