MySQL 大小寫敏感度完整指南：表名、欄位值、跨平台遷移避坑

MySQL 大小寫敏感度完整指南：表名、欄位值、跨平台遷移避坑

MySQL 的大小寫敏感度取決於作業系統檔案系統、lower_case_table_names 參數、字符集校對規則。跨平台遷移（Windows ↔ Linux）是典型踩坑區。本文系統梳理規則、參數含義、實測驗證與最佳實踐。

一、表名大小寫敏感度

1. 核心參數：lower_case_table_names

2. 關鍵限制

• 唯讀參數： 僅能在 my.cnf [mysqld] 區塊設置，需重啟生效
• 不可動態修改： SET GLOBAL lower_case_table_names=1 報錯 ERROR 1238 (HY000): read only variable
• 遷移風險： Linux(=0) 遷移到 Windows(=1) 或反向，若表名大小寫不一致會導致 Table doesn't exist

3. 實測驗證

-- Linux, lower_case_table_names=0
CREATE TABLE TABLE1 (id INT);
SHOW TABLES;  -- 顯示 TABLE1
SELECT * FROM TABLE1;  -- OK
SELECT * FROM table1;  -- ERROR 1146: Table 'test.table1' doesn't exist

-- 修改 my.cnf: lower_case_table_names=1, 重啟
SHOW TABLES;  -- 顯示 table1 (文件系統實際為小寫)
SELECT * FROM TABLE1;  -- OK (查詢轉小寫匹配)
SELECT * FROM table1;  -- OK

二、欄位值大小寫敏感度（校對規則）

1. 校對規則命名規範

• _ci (Case Insensitive)：不區分大小寫，如 utf8mb4_general_ci
• _cs (Case Sensitive)：區分大小寫，如 utf8mb4_cs (8.0+)
• _bin (Binary)：二進制比較，區分大小寫，且按字節碼排序，支援二進制數據

2. 表級/欄位級指定校對

-- 建表指定
CREATE TABLE t1 (name VARCHAR(10)) CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE t2 (name VARCHAR(10)) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 欄位級覆蓋
CREATE TABLE t3 (
    name_ci VARCHAR(10) COLLATE utf8mb4_general_ci,
    name_cs VARCHAR(10) COLLATE utf8mb4_bin
) CHARSET=utf8mb4;

-- 修改現有表
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE t1 MODIFY name VARCHAR(10) COLLATE utf8mb4_bin;

3. 實測對比

-- utf8mb4_bin (區分大小寫)
INSERT INTO t_bin VALUES ('A'),('a'),('B');
SELECT * FROM t_bin WHERE name LIKE 'a';  -- 僅返回 'a'
SELECT name, COUNT(*) FROM t_bin GROUP BY name;  -- A:1, B:1, a:1

-- utf8mb4_general_ci (不區分)
INSERT INTO t_ci VALUES ('A'),('a'),('B');
SELECT * FROM t_ci WHERE name LIKE 'a';  -- 返回 'A', 'a'
SELECT name, COUNT(*) FROM t_ci GROUP BY name;  -- a:2, B:1

-- BINARY 類型強制區分
ALTER TABLE t_ci MODIFY name VARCHAR(10) BINARY;
SELECT * FROM t_ci WHERE name LIKE 'A';  -- 僅返回 'A'

三、跨平台遷移最佳實踐

四、檢查腳本

-- 1. 檢查參數
SHOW VARIABLES LIKE 'lower_case_table_names';
SHOW VARIABLES LIKE 'collation%';

-- 2. 掃描大小寫不一致表名
SELECT TABLE_NAME, TABLE_SCHEMA 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
AND TABLE_NAME != LOWER(TABLE_NAME);

-- 3. 檢查校對規則不一致欄位
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
AND COLLATION_NAME NOT LIKE '%_ci'
AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

五、總結建議

1. 統一規範： 表名、欄位名**全小寫 + 下劃線** (snake_case)
2. 生產環境固定： lower_case_table_names=1，消除跨平台差異
3. 校對規則： 業務字符串用 _ci，精確匹配（如驗證碼、密碼哈希）用 _bin
4. 遷移前驗證： 用上述腳本掃描風險，預先重命名