MySQLの大文字と小文字の区別によって発生する問題の分析

MYSQLは大文字と小文字を区別します

言葉を見れば信じられます。タイトルを見れば内容がわかります。 MYSQL が大文字と小文字を区別するため騙されたことはありませんか?

以前、Alibaba Java 開発マニュアルを読んだことがありますが、MySql テーブル作成仕様に次のような記述がありました。

[必須] テーブル名とフィールド名には小文字または数字を使用する必要があります。数字で始まったり、2 つのアンダースコアの間に数字だけが含まれていたりすることはできません。データベースのフィールド名を変更するには多大なコストがかかり、事前リリースが不可能なため、フィールド名は慎重に検討する必要があります。

注意: MySQL は Windows では大文字と小文字を区別しませんが、Linux ではデフォルトで大文字と小文字を区別します。したがって、不必要な複雑さを避けるため、データベース名、テーブル名、フィールド名には大文字は使用できません。

肯定的な例: aliyun_admin、rdc_config、レベル 3_name 否定的な例: AliyunAdmin、rdcConfig、レベル 3 の名前

実際に同様の問題に遭遇したことがない場合は、これらの規制をただ無意味に読んだだけでは十分に理解できず、漠然と理解して暗記するだけになる場合があります。

01 文字の大きさについての話

最近、あるプロジェクトに取り組んでいます。自分のマシンでの開発とテストでは問題はなかったのですが、Linux サーバーにデプロイした後、エラーが見つかりました。ログ情報はおおよそ次のとおりです。

MySQLSyntaxErrorException: テーブル 'kytu.tb_sutyHo' が存在しません

ちょっと落ち込む問題があります。ローカル開発は問題ないのですが、サーバーにデプロイすると動作しません。幽霊はいるけど...慌てないで。エラーメッセージを見ると、tb_sutyHo テーブルが存在しないことは明らかです。

① そこで落ち着いてnv(navicat)を開いてテーブルが存在するか確認してみました。本当に存在していました。データベースではtb_sutyhoとなっていましたが、hが小文字になっていました。

②コードを確認すると、テーブル名は実際にはtb_sutyHoと書かれており、hは大文字のHで書かれていることがわかりました。

問題が見つかりました。SQLを記述する際に誤ってテーブル名を間違って記述していたことが判明しました。テーブル名を変更することで解決し、機能は正常になりました。普通ならここで話は終わるはずですよね？問題は発見され、修正され、すべてが順調で、後で鶏肉を食べることができます。

PUBG の遊び方がわからない私にとって、これで終わりではありません。問題を見つけて解決することは確かに重要ですが、問題の根本原因を見つけて、次回同じ問題を回避することがさらに重要です。プログラマーとして、同じ落とし穴に二度と落ちないようにしてください。

この質問について考えていましたが、なぜこのエラー メッセージがローカルの Windows 環境で表示されないのでしょうか?サーバーを展開するまで表示されません。何が問題なのでしょうか? (MySQL のサイズ感度についてよくご存知の場合は、以下の内容をスキップできます。)

そこで検索エンジンを使って調べたところ、MySQL のデータベース名とテーブル名の大文字と小文字の区別は、lower_case_table_names パラメータによって制御されることがわかりました。

ローカルの Windows 環境では次のように表示します。

mysql> '%case%' のような変数を表示します。
+------------------------+-------+
| 変数名 | 値 |
+------------------------+-------+
| 小文字ファイルシステム | オン |
| 小文字のテーブル名 | 1 |
+------------------------+-------+

Linux サーバーで次の点を確認してください。

mysql> '%case%' のような変数を表示します。
+------------------------+-------+
| 変数名 | 値 |
+------------------------+-------+
| 小文字ファイルシステム | オフ |
| 小文字のテーブル名 | 0 |
+------------------------+-------+

上記の結果から違いはわかりますが、この 2 つのパラメータについてはまだ感覚がつかめず、具体的に何を意味するのかわかりません。

lower_case_table_names を紹介する際に、lower_case_file_system についても説明しましょう。

小文字のファイルシステム

この変数は、データ ディレクトリが存在するファイル システム上のファイル名の大文字と小文字の区別を指定します。 OFF の場合、ファイル名は大文字と小文字を区別し、ON の場合、大文字と小文字を区別しません。この変数はファイル システムのプロパティを反映しており、これを設定してもファイル システムには影響がないため、読み取り専用です。

小文字のテーブル名

このパラメータは静的であり、0、1、または 2 に設定できます。

0 -- 大文字と小文字を区別します。 (Unix、Linux のデフォルト) 作成されたライブラリ テーブルはそのままディスクに保存されます。たとえば、create database TeSt; は TeSt ディレクトリを作成し、create table AbCCC ... は AbCCC.frm をそのまま生成します。 SQL ステートメントもそのまま解析されます。

1 -- 大文字と小文字を区別しません。 (Windows のデフォルト) ライブラリ テーブルを作成すると、MySQL はすべてのライブラリ テーブル名を小文字に変換し、ディスクに保存します。 SQL ステートメントは、ライブラリ名とテーブル名も小文字に変換します。 以前に作成した Testtable をクエリする必要がある場合 (Testtable.frm ファイルを生成)、select * from Testtable を実行しても、select * from testtable に変換され、エラー テーブルが存在しなくなります。

2 -- 大文字と小文字を区別しません (OS X のデフォルト) 作成されたライブラリ テーブルはそのままディスクに保存されます。 ただし、SQL ステートメントはライブラリ テーブル名を小文字に変換します。

Windows では、デフォルト値は 1 です。macOS では、デフォルト値は 2 です。Linux では、値 2 はサポートされていないため、サーバーは値を 0 に強制します。

Windows では、デフォルト値は 1 です。 macOS では、デフォルト値は 2 です。値 2 は Linux ではサポートされていません。サーバーは値を 0 に強制します。

また、公式 Web サイトには、データ ディレクトリが大文字と小文字を区別しないファイル システム (Windows や macOS など) に存在するシステムで MySQL を実行する場合は、lowercasetable_names を 0 に設定しないでください、というメッセージも表示されています。

Windows 10 環境で lower_case_table_names を 0 に設定しようとすると、MySQL サービスを開始できず、サービスの開始時にエラーが報告されました。 Windows システムでは大文字と小文字は区別されません。下の図を参照してください。

注: lower_case_table_names の値を変更する場合は、Windows では my.ini 構成ファイル、Linux では my.cnf 構成ファイルを変更します。サービスを再起動する必要があります。特定の操作に関する情報はオンラインで見つけることができます。

02 ノート

lowercasetable_names の変更によって発生する一般的な悪影響のリスク: lower_case_table_names=0 のときに大文字を含むライブラリ テーブルが作成された場合、lower_case_table_names=1 に変更すると、そのテーブルは見つかりません。

まずlower_case_table_names=0を設定します

テーブル「学生」を作成（
 `id` int(11) 符号なし NOT NULL AUTO_INCREMENT,
 `name` varchar(25) NOT NULL,
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

テーブルを表示します。
+----------------+
| テーブル_in_aflyun |
+----------------+
| 学生 |
+----------------+

次に、lower_case_table_names=1 を設定してクエリを実行します。テーブル名が大文字か小文字かに関係なく、テーブルが存在しないというメッセージが表示されます。

mysql> Student から * を選択します。
1146 - テーブル 'aflyun.Student' が存在しません

mysql> 学生から * を選択します。
1146 - テーブル 'aflyun.student' が存在しません

解決策: デフォルトの lower_case_tables_name を 0 から 1 に設定する場合は、まず既存のライブラリ テーブル名を小文字に変換する必要があります。

テーブル名に大文字のみが含まれる場合:

①. lower_case_tables_name=0 の場合、テーブル名を小文字に変更します。

②. lower_case_tables_name=1 に設定し、再起動して有効にします。

ライブラリ名に大文字が含まれている場合:

①. lower_case_tables_name=0 の場合、mysqldump を使用して古いデータベースをエクスポートして削除します。

②. lower_case_tables_name=1 に設定し、再起動して有効にします。

③. インスタンスにデータをインポートします。このとき、大文字を含むライブラリ名は小文字に変換されています。

03 結論

穴に足を踏み入れた経験により、冒頭で述べた Ali MySQL 仕様に対する理解が深まりました。オペレーティング システムが異なると、大文字と小文字の区別が不一致になります。開発時には、開発されたプログラムがさまざまなオペレーティング システムと互換性を持つように、大文字と小文字を区別するという原則に従う必要があります。したがって、開発およびテスト環境では、lower_case_table_names の値を 0 に設定して、開発中にコードの大文字と小文字の区別を厳密に制御し、コードの互換性と厳密さを向上させることをお勧めします。