MySQL · 答疑釋惑· lower_case_table_names 使用問題 · 數據庫內核月報

**背景** 在MySQL中，表是和操作系統中的文件對應的，而文件名在有的操作系統下是區分大小寫的（比如linux），有的是不區分大小寫（比如Windows），表名與文件名的大小寫對應關系，MySQL 是通過[lower_case_table_names](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lower_case_table_names)?這個變量來控制的。這個變量的有效取值是0，1，2，按照[官方文檔](http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html)?的解釋： 0表示，表在文件系統存儲的時候，對應的文件名是按建表時指定的大小寫存的，MySQL 內部對表名的比較也是區分大小寫的；? 1表示，表在文件系統存儲的時候，對應的文件名都小寫的，MySQL 內部對表名的比較是轉成小寫的，即不區分大小寫；? 2表示，表在文件系統存儲的時候，對應的文件名是按建表時指定的大小寫存的，但是 MySQL 內部對表名的比較是轉成小寫的，即不區分大小寫。 0適用于區分大小寫的系統，1都適用，2適用于不區分大小寫的系統。如果在開始使用MySQL選定了一個合適的值后，就不要改變，不然的話在之后使用中就會出現問題。 **問題描述** 這里給出一個在使用過程中改變 lower_case_table_names 導致 drop database 失敗的案例。因為lower_case_table_names是個只讀變量，只能在啟動時指定參數設置值，或者 gdb 掛上去直接改內存。首先在啟動 mysqld 的時候，指定 lower_case_table_names = 0，我們執行這樣的語句： ~~~ create database db1; use db1; create table t1(a int) engine = InnoDB; create table t2(a int) engine = MyISAM; create table T3(a int) engine = InnoDB; create table T4(a int) engine = MyISAM; ~~~ 查看對應數據庫目錄下的表文件： ~~~ $ls db1 db.opt t1.frm t1.ibd t2.frm t2.MYD t2.MYI T3.frm T3.ibd T4.frm T4.MYD T4.MYI ~~~ 然后重啟mysqld，指定 lower_case_table_names =1，執行刪除db1 ~~~ mysql> drop database db1; ERROR 1010 (HY000): Error dropping database (can't rmdir './db1', errno: 39) ~~~ 可以看到刪庫語句執行失敗，我們再看下數據庫目錄下的表文件 ~~~ $ls a T3.frm T4.frm T4.MYD T4.MYI ~~~ 可以看到，大寫的 T3 和 T4 表沒有被刪掉，為什么呢？ **問題分析** mysqld 在執行 drop database 操作的時候，是調用 mysql_rm_db 這個函數，在刪除時先把db下的所有表都刪掉，然后再把db刪掉。為了找出對應db下的所有表，mysqld 是通過遍歷數據庫目錄下的文件來做的，具體是用 find_db_tables_and_rm_known_files 這個函數，遍歷數據庫目錄下的所有文件，然后構造出要 drop 的table列表，然而在構造刪除列表過程中，會有這樣一個判斷: ~~~ if (lower_case_table_names) table_list->table_name_length= my_casedn_str(files_charset_info, table_list->table_name); ~~~ 意思就是如果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) **建議** 上面的問題是改變 lower_case_table_names 導致 drop database 失敗，其實還有許多其它的因為lower_case_table_names值改變導致的問題，比如主備庫本來這個值本來是一致的，如果只改主庫的值的話，就會導致備庫復制中斷，報找不到表的問題，或者本來是不區分大小寫的，應用里的寫的SQL語句有大寫表名，也有小寫表名，之后改成區分大小寫，就會導致應用出錯。所以建議是: * 不要輕易的改變lower_case_table_names的值，如果真要改的話，要先檢查下已有的表是否有大小寫的問題，保證目前的表名和要改的模式是一致的，比如從區分大小寫改為不區分大小寫，那就不應該有大寫表存在，如果有的話，要先把大寫表rename成小寫的，如果本來有共存同名的大寫表和小寫表，就要想辦法去掉一個。 * 應用不要依賴于 mysql 的表名轉換機制，應用里的sql語句應該和表名一致，在不區分大小寫的時候，應用里對同一個表的使用，不要既有大寫表名，也有小寫表名。