移転しました。

約3秒後に自動的にリダイレクトします。

MySQLとmacOSにおける大文字・小文字の扱いについて知ったこと

こんにちは。開発部でWebエンジニアをしているjwmxです。

最近、よく利用しているスーパーがセルフレジを大幅に増設し、5・6台あった従来のレジが2台になりました。
時代の流れを感じます。

さて、先日、ローカル開発環境(Mac)で通ったMySQLのSELECT文を別の環境(GCP Cloud SQL)で実行するとシンタックスエラーになることがありました。
原因はテーブル名のエイリアスの大文字・小文字を間違えていたという凡ミスでしたが、その時はじめてmacOSMySQLにおける大文字・小文字の扱いを意識したので調べたことを簡単ですがまとめます。

以下は調査したときの環境です。

MySQLにおける大文字・小文字の区別

公式ドキュメントの9.2.3 Identifier Case Sensitivityにありました。

MySQLにおいてデータベースはデータディレクトリ内のディレクトリ・ファイルに対応しているため、ファイルシステムが大文字・小文字を区別するかどうかが影響するそうです。
具体的にはデータベース名、テーブル名、テーブルのエイリアス、トリガー名が影響を受けます。

また、テーブル名とデータベース名(エイリアスも)についてはlower_case_table_namesというMySQLのシステム変数の影響も受けます。

以下は設定できる値と意味です。
※記載内容はMySQL5.7のドキュメントと同じだったので日本語訳してあるMySQL5.6のドキュメントから持ってきてます。

意味
0 テーブル名とデータベース名は、CREATE TABLE または CREATE DATABASE ステートメントで指定された大文字または小文字を使用してディスク上に格納されます。名前比較では大文字と小文字が区別されます。大文字小文字を区別しないファイル名を持つシステム (WindowsOS X など) で MySQL を実行する場合、この変数を 0 に設定しないでください。大文字と小文字を区別しないファイルシステムで --lower-case-table-names=0 を使用して強制的にこの変数を 0 に設定し、大文字と小文字を変えて MyISAM テーブル名にアクセスした場合、インデックスが破損することがあります。
1 テーブル名はディスク上に小文字で格納され、名前比較では大文字と小文字は区別されません。MySQL では、保存およびルックアップ時にすべてのテーブル名が小文字に変換されます。この動作はデータベース名やテーブルエイリアスにも適用されます。
2 テーブル名とデータベース名は、CREATE TABLE または CREATE DATABASE ステートメントで指定された大文字または小文字を使用してディスク上に格納されますが、MySQL ではルックアップ時に小文字に変換されます。名前比較では大文字と小文字が区別されません。これは大文字と小文字が区別されないファイルシステムでのみ機能します。InnoDB テーブル名は lower_case_table_names=1 のように、小文字で格納されます。

デフォルトの設定値は、UnixmacOS除く)は0、Windowsは1、macOSは2です。
大文字・小文字を区別するのはlower_case_table_names=0のみで、lower_case_table_names=2となっていたローカルのMacで0を指定してもMySQLは起動エラーとなりました。

Cloud SQLのOSが分かる情報は見つけられなかったですが、ふるまいから察するに大文字・小文字を区別するプラットフォームのようですね。

こういったプラットフォーム毎の違いによる問題を回避するために、一貫した命名規則を設けることを公式ドキュメントでは推奨しています。

macOSにおける大文字・小文字の区別

macOSはフォーマット時に大文字・小文字を区別するかどうかを選択できます。

Mac の「ディスクユーティリティ」で利用できるファイル・システム・フォーマットより抜粋。

macOS 10.13 以降を使用する Mac コンピュータに、以下の APFS ファイル・システム・フォーマットのいずれかを選択します。

  • APFS:APFS フォーマットを使用します。
  • APFS(暗号化):APFS フォーマットを使用し、ボリュームを暗号化します。
  • APFS(大文字/小文字を区別):APFS フォーマットを使用し、ファイル名およびフォルダ名の大文字/小文字を区別します。たとえば、「Homework」と「HOMEWORK」という名前のフォルダは、2 つの異なるフォルダです。
  • APFS(大文字/小文字を区別、暗号化):APFS フォーマットを使用し、ファイル名およびフォルダ名の大文字/小文字を区別し、ボリュームを暗号化します。たとえば、「Homework」と「HOMEWORK」という名前のフォルダは、2 つの異なるフォルダです。

新品のMacを購入したときは「APFS」になっているはずですし、いままでフォーマットするときも最初に選択してある「APFS」を無意識に選択していました。

ちなみに、「APFS(大文字/小文字を区別)」でフォーマットされていれば、MySQLlower_case_table_namesに0を設定できました。デフォルトで0が設定されます。

なお、大文字・小文字を区別しないファイルシステムmacOSAdobe製品をインストールできない問題があるようです。

以下はmacOS 10.12以前のファイルシステムであるHFSで発生した問題で、APFSでも発生するか試していないため不確かですが、「APFS(大文字/小文字を区別)」を使いたい場合は注意した方が良いかなと思います。

helpx.adobe.com

まとめ

MySQLの実態はディレクトリ・ファイルということを実感できる良い機会でした。
これまでなんとなくMySQLを使っていたんだと改めて感じました。