その手の平は尻もつかめるさ

ギジュツ的な事をメーンで書く予定です

MySQL の lower_case_table_names について，テーブル名とデータベース名の中の大文字小文字について

MySQL には lower_case_table_names という変数があって，これはテーブル名の大文字小文字を区別するかどうかというのを設定するパラメータなわけですが，その詳細は以下のようになっています (参照: MySQL :: MySQL 5.6 Reference Manual :: 9.2.2 Identifier Case Sensitivity)．

Value 説明
0 大文字・小文字を区別してデータベース名やテーブル名をストアする．これらの名前の比較についても大文字・小文字を区別する．MySQL のサーバが動作する OS のファイルシステムが大文字と小文字を区別しない場合 (例えば OS X や Windows)，この値に設定すべきではない．そうしたファイルシステム上でこの値を0にして，なおかつ MyISAM のテーブル名に対して大文字小文字を混ぜてアクセスするとインデックスがぶっ壊れることがある．
1 テーブル名やデータベース名をストアするときにすべてを小文字に丸める．名前の比較については大文字・小文字を区別しない．
2 テーブル名やデータベース名をストアするときはそのままストアする (つまり大文字・小文字情報を保持したままストアする)．Lookup 時はこれらの名前が小文字に変換され，名前の比較については大文字・小文字を区別せずに行う．なおこの機能は，大文字・小文字を区別しないファイルシステムでのみ機能する．InnoDB のテーブル名は lower_case_table_names=1 に設定した時のようにすべて小文字で保存される．

手元でデフォルト値を調べてみたら，CentOS に入っている MySQL の lower_case_table_names は 0，OS X では 2，更に Windows では 1という風になっていてマジカオス！！　という感じで *1，実際に Windows 上で mysqldump で吐いた dump を Linux の MySQL に食わせたら "duplicated key" みたいな感じで死ぬ，みたいな事が起こりました．


これを避けるためには単一のプラットフォーム上で開発すれば良いわけですが，皆さんにおかれましては OS X で開発して Linux のプロダクション環境にデプロイするみたいな事も多いでしょうし，Windows での開発を貫く開発メンバーも一定数存在していることと存じます．


というわけで対策をする必要があります．公式のドキュメントにも載っていますが，取るべき方法は3つあります．

1. lower_case_table_names=1 をすべてのシステムで利用する．SHOW TABLES や SHOW DATABASE を叩いた時に，もとの大文字・小文字が区別された名前を見れないという欠点がある．
2. OS X や Windows では lower_case_table_names=2 を使用する．その他のシステムでは0を使用する．SHOW TABLES や SHOW DATABASE を叩いた時でも大文字と小文字が区別された名前を確認することが出来る．一方で OS X や Windows 上で，ユーザのステートメントがデータベース名やテーブル名をひくときに大文字と小文字が正確に区別されているかを確認する必要がある．例外として，InnoDB を使っていてなおかつこれらの問題を回避したい場合は，(この方法を使うよりも) すべてのプラットフォームで lower_case_table_names=1 を強制したほうが良い．
3. データベース名やテーブル名には常に小文字を使う．


3 の「データベース名やテーブル名には常に小文字を使う」というのがまあてっとり早くて良いと思います *2．
それに加えて1の方法を採って，lower_case_table_names=1 を設定し，小文字の利用をシステム側で強制しておくと事故が起こりにくくなって良いのではないかと思った次第です．


ちなみにlower_case_table_names=1 にしたい時は古いデータベースをコンバートする必要があるので注意．行動が必要になった際はドキュメントを読みましょう．
https://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

結論

とにかくデータベース名やテーブル名には大文字と小文字を混ぜない．小文字だけでやっていく，という意識が重要そう．併せて lower_case_table_names=1 を設定する．

あと，根本的な話として開発環境と本番環境はできるだけ近づける必要がある．Windows や Mac で開発して Linux の本番環境にデプロイとか，その逆とかをしていると MySQL に限らず問題が起こる可能性が高まるので何とかしましょう．現存するソリューションとしては仮想マシンなどがあります．

その他

  • InnoDB だったら，大文字小文字を区別しないファイルシステムであっても lower_case_table_names=0 を使っても問題ないのだろうか？
  • 大文字小文字を区別するファイルシステムで lower_case_table_names=2 にした時は，lower_case_table_names=0 に fall back される
  • 一部のドキュメントの意味がよくわからなかった……






*1:とは言えこれは仕方がない

*2:とはいえ色々あってそうも出来ないこともある