MySQLの一意制約とNULLについて詳しく解説

ここに記録されているのは、ずっと前に発生したバグです。主に MySQL の固有性 constraints と NULL に関する関連情報を紹介しています。学習価値をすべての人に提供しています。必要な友達は、以下を参照してください。

前書き

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

CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A, B, C);

この時点で、A、B、C の 3 つの列は NULL 値を許可せず、一意性制約も機能します。

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

ALTER TABLE testTable
DROP INDEX IDX_UN_LOAN_PLAN_APP,
ADD UNIQUE KEY `IDX_UN_LOAN_PLAN_APP` (A, B, C, D);

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

青い女王。 。 。何か問題が発生しました。 D を追加すると、一意性制約は基本的に無効になります。

Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK
Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK
Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK

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

後で確認したところ、MySQL の公式ドキュメントには、これが明確に記載されていることがわかりました。一意のインデックスでは、複数の NULL 値の存在が許可されます。

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row 
with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

どのような型が使用されても、以下の表からもわかります。ストレージ エンジンでは、一意のキーを作成するときに複数の NULL が存在することが許可されます。 。 。 。

よく考えてみると、結局のところ、MySQL では NULL が「不明」を表すと考えられています。 SQL では、任意の値と NULL を比較すると、TRUE ではなく NULL が返されます。NULL と NULL を比較した場合でも、NULL が返されます。

だから、私たちはそれを修正することしかできません。 。 。解決策は非常にシンプルかつ大雑把です。オンライン データを更新し、「1970-01-01 08:00:00」をデフォルト値として設定し、その列を NULL を許可しないように変更します。

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

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

概要

以上がMySQLの一意制約とNULLについて詳しく解説の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。