MySQL · 答疑釋惑· lower_case_table_names 使用問題

<b>背景</b>

0表示，表在檔案系統存儲的時候，對應的檔案名是按建表時指定的大小寫存的，mysql 内部對表名的比較也是區分大小寫的；

1表示，表在檔案系統存儲的時候，對應的檔案名都小寫的，mysql 内部對表名的比較是轉成小寫的，即不區分大小寫；

2表示，表在檔案系統存儲的時候，對應的檔案名是按建表時指定的大小寫存的，但是 mysql 内部對表名的比較是轉成小寫的，即不區分大小寫。

0适用于區分大小寫的系統，1都适用，2适用于不區分大小寫的系統。

如果在開始使用mysql標明了一個合适的值後，就不要改變，不然的話在之後使用中就會出現問題。

<b>問題描述</b>

這裡給出一個在使用過程中改變 lower_case_table_names 導緻 drop database 失敗的案例。因為lower_case_table_names是個隻讀變量，隻能在啟動時指定參數設定值，或者 gdb 挂上去直接改記憶體。

首先在啟動 mysqld 的時候，指定 lower_case_table_names = 0，我們執行這樣的語句：

檢視對應資料庫目錄下的表檔案：

然後重新開機mysqld，指定 lower_case_table_names =1，執行删除db1

可以看到删庫語句執行失敗，我們再看下資料庫目錄下的表檔案

可以看到，大寫的 t3 和 t4 表沒有被删掉，為什麼呢？

<b>問題分析</b>

mysqld 在執行 drop database 操作的時候，是調用 mysql_rm_db 這個函數，在删除時先把db下的所有表都删掉，然後再把db删掉。為了找出對應db下的所有表，mysqld 是通過周遊資料庫目錄下的檔案來做的，具體是用 find_db_tables_and_rm_known_files 這個函數，周遊資料庫目錄下的所有檔案，然後構造出要 drop 的table清單，然而在構造删除清單過程中，會有這樣一個判斷:

意思就是如果lower_case_table_names非0的話，就把 table_name 轉成小寫的，t3 和 t4 就被轉成 t3 和 t4，這樣生成的 table_list 中的對應的表是 t1,t2,t3,t4。之後拿着這樣的 table_list 通過 mysql_rm_table_no_locks 一個個删表，這樣就隻把t1,t2 給删了，t3和t4不存在，并且删表時的邏輯是帶有 if exists 的，是以也不會報錯。

在list表都删除完後，調用rm_dir_w_symlink來删除db目錄，此時db1目錄下還有 t3 t4 對應的檔案，這個函數會調用系統的 rmdir 函數，而當目錄非空的時候，rmdir是執行失敗的。

是以我們看到最終的錯誤提示 error dropping database (can't rmdir './db1', errno: 39)

<b>建議</b>

上面的問題是改變 lower_case_table_names 導緻 drop database 失敗，其實還有許多其它的因為lower_case_table_names值改變導緻的問題，比如主備庫本來這個值本來是一緻的，如果隻改主庫的值的話，就會導緻備庫複制中斷，報找不到表的問題，或者本來是不區分大小寫的，應用裡的寫的sql語句有大寫表名，也有小寫表名，之後改成區分大小寫，就會導緻應用出錯。

是以建議是:

不要輕易的改變lower_case_table_names的值，如果真要改的話，要先檢查下已有的表是否有大小寫的問題，保證目前的表名和要改的模式是一緻的，比如從區分大小寫改為不區分大小寫，那就不應該有大寫表存在，如果有的話，要先把大寫表rename成小寫的，如果本來有共存同名的大寫表和小寫表，就要想辦法去掉一個。

應用不要依賴于 mysql 的表名轉換機制，應用裡的sql語句應該和表名一緻，在不區分大小寫的時候，應用裡對同一個表的使用，不要既有大寫表名，也有小寫表名。