MySQL 大小寫敏感完整解析：Linux 下表名、列名、字段值的區分規則與配置

MySQL 大小寫敏感完整解析

MySQL 的大小寫敏感行為取決於作業系統、配置參數、對象類型 (資料庫/表/列/字段值) 四個維度。Linux 下預設區分大小寫，Windows 預設不區分，跨平台遷移極易踩坑。本文系統梳理規則、配置參數、實測驗證與最佳實踐。

核心結論速查表 (Linux / lower_case_table_names=0)

關鍵配置參數：lower_case_table_names

SHOW VARIABLES LIKE 'lower_case_table_names';
-- 0: 區分大小寫 (Linux 預設)
-- 1: 不區分，存儲時轉小寫 (Windows 預設)
-- 2: 存儲保持原大小寫，但比較時轉小寫 (僅限不區分大小寫文件系統)

修改方法： 編輯 my.cnf ([mysqld] 區段) 添加 lower_case_table_names=1，**必須重啟服務生效**。警告： 修改後現有大寫表名可能無法訪問，遷移前請先 RENAME TABLE 統一小寫。

實測驗證 (Linux 環境)

mysql> CREATE TABLE Ac (a VARCHAR(20), c VARCHAR(20)) ENGINE=InnoDB;
mysql> INSERT INTO Ac VALUES ('1q','1q'), ('1Q','1Q');
mysql> SELECT * FROM Ac WHERE a='1q';
+----+----+
| a  | c  |
+----+----+
| 1q | 1q |
| 1Q | 1Q |  -- 預設校對 utf8_general_ci 不區分大小寫
+----+----+

mysql> SELECT * FROM AC;  -- 錯誤: Table 'test.AC' doesn't exist
mysql> SELECT * FROM Ac WHERE A='1Q';  -- 列名不區分大小寫，正常查詢

字段值大小寫敏感控制：校對規則

字段值比較規則由 字符集 + 校對規則 決定：

• _ci (case-insensitive)：不區分大小寫 (預設 utf8_general_ci)
• _cs (case-sensitive)：區分大小寫
• _bin (binary)：二進制比較，按編碼值逐字節比較，區分大小寫且效能最高

建表指定欄位級校對

CREATE TABLE aa (
    a VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin,  -- 區分大小寫
    c VARCHAR(20)                                      -- 繼承表級校對 (預設 _ci)
) ENGINE=InnoDB;

-- 驗證
INSERT INTO aa VALUES ('a','C'), ('A','c');
SELECT * FROM aa WHERE a='a';  -- 僅返回 'a'
SELECT * FROM aa WHERE a='A';  -- 僅返回 'A'
SELECT * FROM aa WHERE c='c';  -- 返回 'C' 和 'c' (c 欄位不區分)

查詢時臨時指定校對

SELECT * FROM aa WHERE a = 'a' COLLATE utf8_bin;  -- 強制區分大小寫
SELECT * FROM aa WHERE a COLLATE utf8_bin = 'A';

工程最佳實踐

1. 統一命名規範： 資料庫、表、列**全部小寫 + 下劃線** (snake_case)，如 user_order_detail，消除跨平台差異。
2. 新專案預設 utf8mb4 + utf8mb4_0900_ai_ci (MySQL 8.0) 或 utf8mb4_general_ci，滿足多語言與 Emoji。
3. 需區分大小寫的業務欄位 (用戶名、編碼、密碼哈希) 顯式指定 COLLATE utf8mb4_bin 或 _cs。
4. 遷移檢查清單：
   • 導出結構時使用 mysqldump --no-data 檢視實際 DDL。
   • 目標庫參數 lower_case_table_names 與源庫一致。
   • 匯入前全量轉小寫表名/列名 (工具或腳本處理)。
5. 應用層防禦： ORM/查詢構建器統一輸出小寫標識符，避免手寫 SQL 大小寫不一致。

常見誤區澄清

• ❌ lower_case_table_names=1 會讓字段值也不區分大小寫 → 錯誤，該參數**僅影響資料庫/表名**。字段值靠校對規則控制。
• ❌ Windows 下建表 User，Linux 下 select * from user 能查到 → 視參數而定。若 Linux 為 0，則找不到。
• ✅ SHOW VARIABLES LIKE 'lower_case%'; 同時顯示 lower_case_file_system (文件系統特性)，輔助判斷。