MySQL におけるユニーク制約と NULL の詳細な説明

序文

説明を簡略化するために以前に設定した要件は、他のグループから MQ メッセージを受信し、データベースにレコードを挿入することです。メッセージが繰り返し送信されたり、重複レコードが複数挿入されたりするのを防ぐために、テーブル内のいくつかの列に一意のインデックスが追加されます。

testTable (A、B、C) に一意のインデックス IDX_UN_LOAN_PLAN_APP を作成します。

この時点では、列 A、B、C では NULL 値は許可されておらず、一意性制約も機能します。

その後、要件の変更により、以前の一意性制約が変更され、さらに 1 つの列が追加されました。 （追加された理由については詳しく説明しません）。

ALTER TABLE テストテーブル
インデックスIDX_UN_LOAN_PLAN_APPを削除します。
一意のキー「IDX_UN_LOAN_PLAN_APP」(A、B、C、D) を追加します。

新しく追加された D は datetime 型で、NULL を許可し、デフォルト値は NULL です。デフォルト値が NULL になっているのは、すべてのレコードにこの時刻があるわけではないからです。マジック値 (「1970-01-01 08:00:00」など) を強制的にデフォルト値に設定すると、見た目がおかしくなります。

青の女王。 。 。その時、物事はうまくいかなくなるのです。 D を追加すると、一意性制約は基本的に無効になります。

testTable (A,B,C,D) に VALUES (1,2,3,NULL) を挿入します。 --- OK
testTable (A,B,C,D) に VALUES (1,2,3,NULL) を挿入します。 --- OK
testTable (A,B,C,D) に VALUES (1,2,3,NULL) を挿入します。 --- OK

上記の 3 つの SQL ステートメントはすべて正常に実行され、データベースには複数の同一レコードが存在します。以前のアイデアによれば、最後の 2 つの SQL ステートメントを実行するときに、「重複キー」例外がスローされるはずです。

後で調べたところ、MySQL の公式ドキュメントにすでにこの点が明記されていました。ユニーク インデックスでは複数の NULL 値が存在することが可能です。

UNIQUE インデックスは、インデックス内のすべての値が一意でなければならないという制約を作成します。既存の行と一致するキー値を持つ新しい行を追加しようとすると、エラーが発生します。すべてのエンジンで、UNIQUE インデックスは、NULL を含めることができる列に対して複数の NULL 値を許可します。

下の表から、どのタイプのストレージ エンジンが使用されているかに関係なく、一意のキーを作成するときに複数の NULL が許可されていることもわかります。 。 。 。

よく考えてみると、これは実はかなり合理的です。結局のところ、MySQL では NULL は「不明」を表します。 SQL では、NULL と比較される値は TRUE ではなく NULL を返し、NULL と NULL の比較でも NULL が返されます。

したがって、唯一の解決策はそれを修正することです。 。 。解決策は非常に単純で大雑把です。オンライン データを更新し、デフォルト値として「1970-01-01 08:00:00」を設定し、列を NULL を許可しないように変更しただけです。

MySQL の公式 Web サイトで多くの人がこの問題について議論しています。これは MySQL のバグだと考える人もいれば、機能だと考える人もいます。リンクを添付します。

MySQL バグ: #8173: 一意のインデックスで null 値の重複が許容される

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に少しでもお役に立てれば幸いです。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただき、ありがとうございます。