MySQL で 「NOT NULL にしない」のはどういうケースですか？

Question

前提

php と MySQL を使っています。

質問

MySQL で CREATE TABLE するときに、値がなくても良いカラムがあるとします。
例えば「ユーザが任意に定めるニックネーム」などです。

そういう値のカラムは NOT NULL にするか否か、どちらが一般的ですか？
・「NOT NULL にする」→ 値が不要なときは空文字を入れる
・「NOT NULL にしない」→ 値が不要なときは NULL を入れる

そして、「NOT NULL にしない」のはどういうケースですか？

個人的な (素人の) 考え

個人的には「NOT NULL にする」で統一した方が良いように思います。

なぜなら、「NOT NULL にしない」と、php のメンバ変数でいちいち?stringのように?をつけないといけなくて煩わしく思いますし、

class User
{
    protected int $id = 0;
    protected ?string $nickname = null;
}

それに「NULL との比較が常に false」となる点に鑑みれば、以下で「何も返されない」という点も扱いにくく思うのです。

CREATE TABLE users (
    id INT PRIMARY KEY,
    nickname VARCHAR(255)
);

INSERT INTO users (id, nickname) VALUES (1, NULL);
INSERT INTO users (id, nickname) VALUES (2, 'taro');

SELECT * FROM users WHERE nickname= 'taro'; -- 2行目が返される
SELECT * FROM users WHERE nickname= NULL; -- 何も返されない

以上のように「NOT NULL にする」で統一した方が良いように思うものの、「NOT NULL にしない」ことができるということは、それなりに活躍の場があるのだろうと思うのですが、どういうケースで便利なのかわかりません。

この辺りについてどながか教えて頂けませんでしょうか？
よろしくお願い致します。

Comments

[maisumakun]

確認ですが、あくまで「フリー入力できる文字列」に限った話でしょうか？

それとも、リレーションのキーになる値、数値、日付など「一定の形のデータ」を要求される場面でもNOT NULLのほうがいい、という主張でしょうか？

[退会済みユーザー]

NULL許容はパフォーマンス的に遅くなるので、必要がなければNOT NULLにすべきです。ただ、Oracleなど大昔のSQLがいまだに現役な歴史あるDBだと、空文字列をNULLと判定する文化があり、その名残で必要もないのにNOT NULLがついてないカラムがあるかもしれません。
MySQL対象なら何も考えずに必要がなければNOT NULLにすればいいだけ。ただし、稼働中のDBで「理由が分からないのに勝手にNOT NULLを付けたら」問題が起こる可能性が高いです。理由そのものは列挙できるようなものではありません。

[munekun]

maisumakunさん
「フリー入力できる文字列」は質問用に例として挙げてみただけです。なので外部キー制約の件を踏まえてのご回答はまさに知りたいことでした。ありがとうございます。

ところで、「フリー入力できる文字列」に限っていえば、「NOT NULL にする」のだと理解してもよろしいでしょうか？

[munekun]

dameoさん
パフォーマンスというのは全く知りませんでした。大事な視点ですね。ありがとうございます。

Answer 1

「NOT NULL にする」→ 値が不要なときは空文字を入れる
php のメンバ変数でいちいち?stringのように?をつけないといけなくて煩わしく思います

テーブル単独での参照であれば、良いでしょうが、outer join による取得の場合にはnot nullの項目であってもNullが返却されるので、Nullに関する対処が不要になる事は無いかと思います。

Comments

[munekun]

ありがとうございます。

> not nullの項目であってもNullが返却される
こちらはどういう意味でしょうか？outer join を使ったことがなく、返却とか対処が何を指しているのか理解が及びませんでした。

[sazi]

outer join は外部結合の事です。left (outer) join や right (outer) join が該当します。
外部結合の場合結合先がHITしなければ、全項目Nullが返却されますので。
select b.* from a left join b on a.key=b.key
上記でa.key=b.keyで無い場合、b.*は全てNullです。
上記の場合でbの項目を参照する場合にはnullを想定する必要があるという事です。

[munekun]

> b.*は全てNull
あっなるほど、イメージできました。
具体的なコードで示していただき大変助かりました。
outer join を使わない開発なんてないでしょうから、NULL への対処をしなくて済むかもという目論見で NOT NULL にするというのはおかしな話でしたね。
ありがとうございます。

Answer 2

個人的には「NOT NULL にする」で統一した方が良いように思います。

「テーブル設計ではNULLを避けるべき」というのは一般的なセオリーだといってよいと思います。SQL設計の際にNULLを考慮せねばならなかったり、インデックスやオプティマイザがうまく働かず、パフォーマンスに影響が出たりと、NULLの扱いの難しさをあげだすときりがありません。

・「NOT NULL にする」→ 値が不要なときは空文字を入れる

「NULLの代わりにデフォルト値を利用する」というのは、一般的な手法だと思います。ただし、この手法を採用する場合は、デフォルト値の意味をドキュメントなどに残し、チーム内で正しく共有する必要があります。

そして、「NOT NULL にしない」のはどういうケースですか？

NULLが厄介者になるのは、テーブルをうまく正規化して設計した場合です。逆にいえば、正規化にそぐわないデータや正規化する必要がないデータをテーブルで扱う場合、NULLを用いても構わないということになります。たとえば、処理中にデータを一時的に格納する場所としてテーブルを利用する、テーブル結合によるパフォーマンス低下を避けるため、あえてテーブルを非正規化するなどが考えられます。

Comments

[munekun]

ありがとうございます。知りたかった点のそれぞれに詳しいご回答をつけていただき感謝です。

Answer 3

NULLを許すことによってユニーク条件を回避することができます。
逆にその条件があるからNOT NULLにすべきという考え方もあります。

参考

create table tbl(id int primary key,code varchar(10) unique null,name varchar(100));
insert ignore into tbl values
(1,1,'test1'),
(2,2,'test2'),
(3,2,'test2'), /* これはinsertされない */
(4,null,'test3'),
(5,null,'test3'); /* これはinsertされる */

Comments

[munekun]

ありがとうございます。なるほど、空文字だとユニーク制約が効くようになるのですね。NOT NULL にするか否かの大事な基準ですね。

Answer 4

フリー入力できる列はさておき、外部キーをかけた列に入れられるものは、外部キーとして存在するもの以外ではNULLだけです。

「適当に空白にしておく」という選択肢は取れません。

Comments

[maisumakun]

> SELECT * FROM users WHERE nickname= NULL; -- 何も返されない

単なる書き間違いです。IS NULL、あるいはIS DISTINCT FROMなどNULLにも使える演算子を使ってください。

[munekun]

ありがとうございます。必要最低限でスパッと回答するのはいつもかっこいいのですが、初心者だとそこから演繹できるだけの経験がなく、具体例をイメージができません。
もう少し具体的に知りたいと思い調べてみたのですが、外部キーで ON DELETE SET NULL を指定するときに「NOT NULL にしない」のだ。ということであっていますか？