質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.64%

NULLを含む挿入データは複合UNIQRE制約に引っかからないのですか?日付を含むUNIQRE制約について困っています

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 758

sanset

score 179

MySQLにおけるUNIQRE制約について2つの質問です。

1の質問

例として、このようなテーブルを作成しました。

![イメージ説明

test_id INT(11) プライマリキー オートインクリメント
test_user_id INT(11) デフォルト値0
test_date DATETIME デフォルト値NULL
※test_user_id + test_date で複合ユニークキー制約

※CREATEテーブルです。

CREATE TABLE `test_table` (
    `test_id` INT(11) NOT NULL AUTO_INCREMENT,
    `test_user_id` INT(11) NOT NULL DEFAULT '0',
    `test_date` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`test_id`),
    UNIQUE INDEX `test_user_id_test_date` (`test_user_id`, `test_date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

このテーブルを作成した後に、以下のクエリを何回か実行すると、何度もデータが挿入されてしまいます。

INSERT IGNORE INTO 
test_table
(test_user_id) VALUES(1)

INSERT IGNORE INTOはユニーク制約に引っかかる場合は無視するクエリですが、上記のケースは何度も実行できます。

NULLはユニーク制約にかからないことは知っておりましたが、複合ユニークキーで設定したtest_user_idは同じ1が挿入されているのにも関わらず、制約に引っかかりません。

INSERTするデータにNULLが含まれている場合は、その他に設定した複合ユニーク制約もすべて無視されてしまうのでしょうか。

2の質問

上記の問題で、こういった場合は、普段デフォルトに空文字や0を入れて対応しようとしたのですが、DATETIME型なのでその設定ができません。

なのでデフォルトにNULLを設定していたのですが、上記の問題が発生しました。

日付を含む複合ユニーク制約を設定しているのですが、日付を入れたい場合と入れたくない場合があり、日付を入れない場合、すなわちNULLとして挿入する場合は、その他のカラムで複合制約に引っかかるか判定をしたかったのですが、上記の仕様によりそれが難しい問題です。

この場合は、日付を含まない場合はダミーの日付データ「1970-01-01 00:00:00」などを挿入して、ユニーク制約を維持できるようにするのが主流なのでしょうか。

皆さんが上記の状況に陥った時の対応策についてお聞きしたいです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2018/08/17 16:00

    再現確認をとる必要がありそうなのでCREATE TABLE文を追記してください。

    キャンセル

  • sanset

    2018/08/17 16:03

    承知しました。本文に追記いたしました。

    キャンセル

  • momon-ga

    2018/08/17 16:23

    望みのユニーク制約は、`test_user_id`のみということ?

    キャンセル

  • sanset

    2018/08/17 16:37

    いえ、あくまで望みはtest_user_id + test_dateの複合ユニーク制約です。1, NULLが既に入っている状態で1, NULLがデータとして挿入出来てしまうのは何故か?というのと、それの対策というか代替案についてお聞きしています。

    キャンセル

回答 3

checkベストアンサー

+1

■ 1の質問

NULLは、すなわち「データとして存在しない」ってことです。

ないものは使えません。ないものを使ったデータは、作れません。

なので、
INSERT IGNORE INTO test_table (test_user_id) VALUES(1)
これでインサートされたデータは、複合ユニークキーになれる値が「ない」のです。

「ないもの」と「ないもの」は比較ができません(※NULL=NULLにはならない)ので、この insert igunore文は、何度でも実行できます。
※NULL=NULL にはならない、ので、
mysqlの条件文に使うNULLの比較は colname is NULL であって、 colname = NULLではありませんよね?

他の関数も全て「NULLはデータがないもの」として動いているはずです。

例えば、テーブルデータがこうだとしたら

test_id test_user_id test_date
1 1 NULL
2 2 NULL
3 3 2018-08-17 12:00:00
4 NULL NULL

※4行目は、上記テーブル定義では作れないデータですが、下記の関数の説明に使うのであえて書いています。

例えば、使用頻度の高そうなcount()
select count(test_id) from test_table → 4
ですが、
select count(test_date) from test_table → 1
NULLは数えられません。

文字列連結のgroup_concat()
select group_concat(test_id, test_date) from test_table; → 3,2018-08-17 12:00:00
test_id、test_dateがNULLの列は表示されません

計算式も実はそうです。
select avg(test_user_id) from test_table → 2
この式は、(1+2+3) / 4ではなく、(1+2+3) / 3 で、NULLの行は母数に入りません。


■ 2の質問
複合ユニークのカラムは、本来はNOT NULLであるべきなのだと思います。
(あえてNULLが無視されるのを利用するというのは、手法として有りですが)

今回の場合の解決策は、

・ダミーの日付データを使う
個人的には「1970-01-01 00:00:00」よりは「0000-00-00 00:00:00」を使いたいです。timestampではないので、デフォルト値に設定できるはず・・。

・日付型をやめる
日付を文字列や数字に型を変更する。NULLの代わりは、文字列なら空文字、数値なら0に。(これもデフォルト値に設定したらいいと思います)

くらいでしょうか。。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/17 17:44

    詳しい解説と2の質問にご回答いただきありがとうございます。
    1の解説と「複合ユニークのカラムは、本来はNOT NULLであるべき」というのにはとても腑に落ちました。
    SELECTで日付関数を使用したいからDATETIME型にこだわっていましたが、VARCHARでも日付の文字列なら参照できるみたいですね。
    ちょっと仕様について考えてみたいと思います。

    キャンセル

+1

一意制約があっても1つ以上の制約カラムにNULL値を持つ場合、INSERTできるのは仕様だった気がします。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/17 17:41

    ありがとうございます。NULLという属性に対してまた一つ詳しくなれました。

    キャンセル

0

INSERT IGNORE を使用しているからエラーが出ないのだと思います。
IGNORE キーワードを使用した場合、INSERTステートメントの実行中に発生したエラーは無視されると公式ドキュメントに記載されています。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/17 17:42

    ありがとうございます。NULLを含む行はないものとして扱われるみたいなので、挿入できてしまうみたいですね。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.64%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • PHPに関する質問
  • NULLを含む挿入データは複合UNIQRE制約に引っかからないのですか?日付を含むUNIQRE制約について困っています